Email Password Remember me?  Lost your password?

• Download source - 929.86 KB

Introduction

This is an implementation of the Membership, Role, and Profile providers for ASP.NET 2.0. It is fully functional, except there is no support for saving or reading binary objects to/from the profile provider.

This is the same code that is included with the shipping release of Gallery Server Pro, so you can be assured it is production ready. However, if you do find any issues, please contact me and I will correct them.

It is intended that this code can be used as a drop-in replacement for the SQL Server providers by Microsoft. You can even use the backup/restore functionality in Gallery Server Pro to migrate your SQL Server membership, role, and profile data to SQLite, or the other way around. (You don't have to use the rest of the Gallery Server Pro functionality if all you need is help migrate your users.)

Background

I am the creator and lead developer for Gallery Server Pro, an open source photo, video, audio, and document web gallery (read the CodeProject article). It was originally designed to store data in SQL Server, but in September 2008, I added SQLite as the default data provider, while keeping SQL Server as an option.

I couldn't find any off-the-shelf SQLite providers, so I started with the work done by another CodeProject author. I thank mascix for getting me started, but in the end, I did a nearly complete rewrite of the code. The original code had a lot of bugs, a few security holes, inefficiencies, behavior inconsistent with the SQL Server providers, and no support for transactions (which is critical for performance in SQLite).

I addressed these issues to create a solid set of providers for SQLite. I thought they might be useful to the general community, so I refactored them out of the Gallery Server Pro codebase and wrote up this article. Enjoy!

Using the Sample Code

The source code is a web application containing the SQLite providers and an empty SQLite database. Download the code, compile it, and run the ASP.NET Configuration tool to add a few roles and users. (In Visual Studio, choose ASP.NET Configuration from the Project menu.)

Using the Code in Your Own Project

Step 1: Get the latest version of System.Data.SQLite, the ADO.NET wrapper around the SQLite core DLL. Robert Simpson has done an incredible job with this wrapper. Send this guy a donation!

Step 2: Copy the SQLite database file into the App_Data directory of your project. You will find it at ~/App_Data/app_data.sqlite in the download code. Or, create a new, empty database, and execute the SQL in the InstallMembership.sql file to set up the tables.

Step 3: Add the code files containing the classes SQLiteMembershipProvider, SQLiteRoleProvider, and SQLiteProfileProvider to your project.

Step 4: Edit web.config to configure the providers, like this:

<?xml version="1.0" ?>
  <configuration>
  <connectionStrings>
    <clear />
    <add name="SQLiteDbConnection"
     connectionString="Data Source=
        |DataDirectory|app_data.sqlite;Version=3;" />
  </connectionStrings>

  <system.web>
    <compilation debug="false" />
    <authentication mode="Forms" />

    <!-- Configure the Membership provider.-->
    <membership defaultProvider="SQLiteMembershipProvider">
      <providers>
        <clear />
        <add applicationName="SQLite ASP.NET Provider" 
                passwordFormat="Clear"
                minRequiredNonalphanumericCharacters="0" 
                minRequiredPasswordLength="2"
                maxInvalidPasswordAttempts="2" 
                enablePasswordReset="true" 
                enablePasswordRetrieval="true"
                passwordAttemptWindow="10" 
                requiresQuestionAndAnswer="false"
                requiresUniqueEmail="false" 
                connectionStringName="SQLiteDbConnection"
                name="SQLiteMembershipProvider" 
                type="TechInfoSystems.Data.SQLite.SQLiteMembershipProvider, 
                     TechInfoSystems.Data.SQLiteProvider" />
      </providers>
    </membership>

    <!-- Configure the Role provider.-->
    <roleManager enabled="true" cacheRolesInCookie="true" 
                    cookieProtection="Validation"
                    defaultProvider="SQLiteRoleProvider">
      <providers>
        <clear />
        <add applicationName="SQLite ASP.NET Provider" 
            connectionStringName="SQLiteDbConnection"
            name="SQLiteRoleProvider" 
            type="TechInfoSystems.Data.SQLite.SQLiteRoleProvider, 
                 TechInfoSystems.Data.SQLiteProvider" />
      </providers>
    </roleManager>

    <!-- Configure the Profile provider.-->
    <profile defaultProvider="SQLiteProfileProvider">
      <providers>
        <clear />
        <add applicationName="SQLite ASP.NET Provider" 
            connectionStringName="SQLiteDbConnection"
            name="SQLiteProfileProvider" 
            type="TechInfoSystems.Data.SQLite.SQLiteProfileProvider, 
                 TechInfoSystems.Data.SQLiteProvider" />
      </providers>
    </profile>

  </system.web>
</configuration>

Step 5: Add a reference to System.Data.SQLite.DLL to your web project. Choose either the 32-bit or 64-bit version, depending on your server. The download for this article includes both versions, but there is probably a newer version available from here.

Step 6: Compile your application, and you are ready to go! For example, start the ASP.NET Configuration tool to add a few roles and users. (In Visual Studio, choose ASP.NET Configuration from the Project menu.)

At this point, you can use any of the membership, role, and profile functions. Remember that the one piece that is not implemented is binary object storage in the profile provider.

Managing Your SQLite Database

There are several management tools available that let you view/edit the tables and other objects. Here are a few I have used:

• System.Data.SQLite - The ADO.NET wrapper around the SQLite core DLL also includes excellent data management tools that integrate directly into Visual Studio. Sweet!
• SQLite Manager - This is an add-on that plugs into various applications, such as Mozilla Firefox. It is lightweight and easy to use.
• SQLite Administrator - I don't use this any more, as the first two get the job done. But, this one also works well.

Transaction Support

It is well known among SQLite developers that executing multiple SQL statements in individual transactions result in terrible performance. Consider the following:

foreach (ProfileInfo profile in ProfileManager.GetAllInactiveProfiles(
  ProfileAuthenticationOption.Anonymous,
  DateTime.Today))
{
  Membership.DeleteUser(profile.UserName, true);
}

This code deletes all anonymous users who visited your site before today. In Gallery Server Pro, I run this code each time the application starts. However, each call to DeleteUser executes in its own transaction, so if there are dozens or hundreds of users to delete, this code can take a very long time.

This code is not a problem for SQL Server, but it is a serious performance issue for SQLite. The solution is to explicitly start a transaction and then commit it when finished:

private static void DeleteAnonymousProfiles()
{
  BeginTransaction();

  try
  {
    foreach (ProfileInfo profile in ProfileManager.GetAllInactiveProfiles(
      ProfileAuthenticationOption.Anonymous,
      DateTime.Today))
    {
      Membership.DeleteUser(profile.UserName, true);
    }
    CommitTransaction();
  }
  catch
  {
    RollbackTransaction();
    throw;
  }
}

/// <summary>
/// Begins a new database transaction. All subsequent database actions occur within 
/// the context of this transaction. Use <see cref="CommitTransaction"/> to 
/// commit this transaction or <see cref="RollbackTransaction" /> to abort it. 
/// If a transaction is already in progress, then this method returns without any 
/// action, which preserves the original transaction.</summary>
/// <remarks>Transactions are supported only when the client is a web application.
/// This is because the transaction is stored in the HTTP context Items property. If 
/// the client is not a web application, then 
/// <see cref="System.Web.HttpContext.Current"
/// /> is null. When this happens, this method returns without taking any action.
/// </remarks>
public override void BeginTransaction()
{
  // Create new connection and transaction and place in HTTP context.
  if (System.Web.HttpContext.Current == null)
    return;

  if (IsTransactionInProgress())
    return;

  SQLiteConnection cn = GetDBConnection();
  if (cn.State == ConnectionState.Closed)
    cn.Open();

  SQLiteTransaction tran = cn.BeginTransaction();

  System.Web.HttpContext.Current.Items["SQLiteTran"] = tran;
}

/// <summary>
/// Commits the current transaction, if one exists. A transaction is created with 
/// the <see cref="BeginTransaction"/> method. If there is not an existing 
/// transaction, no action is taken. If this method is called when a datareader 
/// is open, the actual commit is delayed until all datareaders are disposed.
/// </summary>
/// <remarks>Transactions are supported only when the client is a web 
/// application. This is because the transaction is stored in the HTTP context Items 
/// property. If the client is not a web application, then <see 
/// cref="System.Web.HttpContext.Current" /> is null. When this happens, this 
/// method returns without taking any action.</remarks>
public override void CommitTransaction()
{
  // Look in HTTP context for previously created connection and transaction.
  // Commit transaction.
  if (System.Web.HttpContext.Current == null)
    return;

  SQLiteTransaction tran =
    (SQLiteTransaction)System.Web.HttpContext.Current.Items["SQLiteTran"];
   
  if (tran == null)
    return;

  // This closes the connection and nulls out the Connection property on the transaction.
  tran.Commit(); 

 System.Web.HttpContext.Current.Items.Remove("SQLiteTran");
}

/// <summary>
/// Aborts the current transaction, if one exists. A transaction is created with 
/// the <see cref="BeginTransaction"/> method. If there is not an existing 
/// transaction, no action is taken./// </summary>
/// <remarks>Transactions are supported only when the client is a web 
/// application. This is because the transaction is stored in the HTTP context Items 
/// property. If the client is not a web application, then <see 
/// cref="System.Web.HttpContext.Current" /> is null. When this happens, this 
/// method returns without taking any action.</remarks>
public override void RollbackTransaction()
{
  // Look in HTTP context for previously created connection and transaction.
  // Abort transaction.
  if (System.Web.HttpContext.Current == null)
    return;

  SQLiteTransaction tran = 
    (SQLiteTransaction)System.Web.HttpContext.Current.Items["SQLiteTran"];
    
  if (tran == null)
    return;

  // This closes the connection and nulls out the Connection property on the transaction.
  tran.Rollback();

  System.Web.HttpContext.Current.Items.Remove("SQLiteTran");
}

/// <summary>
/// Determines whether a database transaction is in progress.
/// </summary>
/// <returns>
///     <c>true</c> if a database transaction is in progress; 
/// otherwise, <c>false</c>.
/// </returns>
/// <remarks>A transaction is considered in progress if an instance of 
/// <see cref="SQLiteTransaction"/> is found in the <see 
/// cref="System.Web.HttpContext.Current"/> Items property and its connection 
/// string is equal to the current provider's connection string.</remarks>
private static bool IsTransactionInProgress()
{
  if (System.Web.HttpContext.Current == null)
    return false;

  SQLiteTransaction tran = 
      (SQLiteTransaction)System.Web.HttpContext.Current.Items["SQLiteTran"];

  if ((tran != null) && (String.Equals
        (tran.Connection.ConnectionString, _connectionString)))
    return true;
  else
    return false;
}

The SQLite providers are hard-coded to look for an instance of a SQLiteTransaction object in the current HttpContext Items bag. If it finds one, it uses that transaction for the current action. If it doesn't find one - either because you didn't add a transaction to HttpContext or because the current app is not a web application, the current action runs in its own transaction.

Often, you don't need to worry about transactions. For example, say you are creating a user with Membership.CreateUser. Behind the scenes, this will execute just one SQL statement (or at most two or three), so you won't gain much by wrapping it in a transaction. Just worry about those cases where you are making a lot of calls in a single HTTP request.

I recommend you place the functions BeginTransaction, CommitTransaction, and RollbackTransaction in your data layer, along with the provider implementations. This will save you from having to reference the SQLite DLL in your UI layer. For an example of how these providers are used in a production application, download the source code for Gallery Server Pro.

Migrating Data Between SQL Server and SQLite

If you have existing accounts in SQL Server, you can import them into SQLite using the backup / restore function in Gallery Server Pro. The basic steps are:

1. Install Gallery Server Pro and configure it to point to your existing membership, role, and profile data in SQL Server.
2. Use the backup function in Gallery Server Pro to export your user accounts to an XML file.
3. Re-run the Gallery Server Pro Web Installer, this time selecting SQLite as the data store.
4. Use the restore function to import your user accounts from the XML file.
5. Use your SQLite management tool to open the SQLite database (~/App_Data/galleryserverpro_data.sqlite). Delete the tables that begin with "gs_". These are Gallery Server Pro tables and you don't need them - you just want to keep the ones that start with "aspnet_".
6. Delete the Gallery Server Pro web application, but keep the SQLite database file.
7. Now, your users, roles, and profile data are in SQLite!

This is admittedly a kind of hacky way to do the migration, but if you absolutely must move accounts between SQL Server and SQLite, it will save you a lot of time.

Detailed instructions for installing Gallery Server Pro and using the backup and restore functions can be found in the Administrator's Guide, available here.

You can migrate the other direction, too, from SQLite to SQL Server, by modifying the steps accordingly.

Points of Interest

I asked Microsoft if there were any unit tests available to verify that new providers (such as these SQLite ones) behave the same as their SQL Server counterparts. Unfortunately, the answer was no. This makes it hard to guarantee that the SQLite providers are 100% compatible with the SQL Server ones.

But, to the best of my ability, I ensured the behavior is the same. I used Reflector to study the existing SQL Server providers. I looked at the SQL Server Stored Procedures to see what was going on under the hood.

All SQL is parameterized and protected against SQL injection attacks.

History

March 30, 2009 - Fixed a few bugs and corrected behavior that was inconsistent with the SQL Server providers.

• SQLiteMembershipProvider.CreateUser: LastLoginDate and LastPasswordChangedDate now default to the CreateDate instead of 1753-01-01. This is consistent with the SQL Server Membership provider.
• SQLiteMembershipProvider.ChangePassword: It now verifies that the passwords conform to validation requirements during a password change, such as MinRequiredNonAlphanumericCharacters, PasswordStrengthRegularExpression, and MinRequiredPasswordLength.
• SQLiteMembershipProvider.ChangePassword: Fixed bug where the isNewUser parameter of the ValidatePasswordEventArgs constructor was incorrectly set to true. It is now set to false.
• SQLiteMembershipProvider.ChangePasswordQuestionAndAnswer: Fixed bug where SQL parameter names were inconsistent between the SQL and the subsequent code that assigns values to the parameters.
• SQLiteRoleProvider.GetUsersInRole: Fixed bug where method does not return any users when a role name has one or more upper case letters.
• SQLiteRoleProvider.CreateRole: It now throws a ProviderException if a user attempts to create a role with a name that differs only by case with another role. Previously it allowed roles that differed by case (example: SysAdmin and sysadmin are no longer both valid). This is consistent with the SQL Server Role provider.
• The fix for the previous two bugs required adding a new column LoweredRoleName to the aspnet_Roles table. This change caused a ripple effect where most of the SQL statements had to be updated to take into account the new column.
• SQLiteRoleProvider.DeleteRole: Fixed bug where the user/role relationship records were not deleted from the aspnet_UsersInRoles table when a role is deleted.

November 3, 2008 - Added XML comments to code, fixed a few bugs where DataReaders were incorrectly closed, refactored a couple minor constants to variables, and corrected an error in the sample code in this article.

September 9, 2008 - Initial release

You must Sign In to use this message board.

Per page 102550

FirstPrevNext

Security Issues? topperdel 11hrs 9mins ago  Hi! I tried your SQLiteProvider and it works as expected. But I realised, that the User-Passwords are saved in the databse - so if I open up the database in the management console, I can see all passwords from the users! This shouldn't be how it is supposed to, does it? Normally, you just save a hash, and not the password itself... I don't want to have all passwords clearly readable in the database! Any hints on this? Kind regards, TopperDEL Sign In·View Thread·PermaLink Re: Security Issues? Roger Martin 5hrs 8mins ago  The provider supports saving passwords 3 ways: clear text, encrypted, and hashed. This is the same as the SQL Server providers. Choose the style that works best for you. Roger Martin Gallery Server Pro Sign In·View Thread·PermaLink Errors in SQLMembershipProvider ? Kay Herzam 7:49 16 Jul '09   Roger Thanks a lot for a very useful component. It worked right away, however I have found some issues. Maybe (probably) the error is on my side. SQLiteMembershipProvider::CreateUser() ---------------------------------------------------------------------------- When the email address is null, the statement cmd.Parameters.AddWithValue("$Email", email); fails. SQLiteMembershipProvider::GetUser(object providerUserKey, bool userIsOnline) ---------------------------------------------------------------------------- Getting the user by its ID always returns null. If I change the query to work without parameter (which is of course not recommended) it works. If you could look into these issues, I'd be most grateful. Thanks again and best wishes from Switzerland -Kay Sign In·View Thread·PermaLink Re: Errors in SQLMembershipProvider ? Roger Martin 9:04 16 Jul '09   Yes, the CreateUser bug is one I found just a few weeks ago. I haven't updated the code here yet, but the fix is easy: In the CreateUser method, change this line: cmd.Parameters.AddWithValue("$LoweredEmail", email.ToLowerInvariant()); to this cmd.Parameters.AddWithValue("$LoweredEmail", (email != null ? email.ToLowerInvariant() : null)); As for the GetUser method, I can't repro the issue. I can call it with something like this and get a valid object returned: MembershipUser user = Membership.GetUser("30e190f7-d674-4b2d-9264-293340f65626" as object, true); Roger Martin Gallery Server Pro Sign In·View Thread·PermaLink Usage in VB.Net Websites Phantom208 6:24 4 Jun '09   I normally write in VB.Net but can do C#. I had been using the Mascix provider but wanted to try this one. My normal routine is to compile the providers into a dll and then include it with the VB code. This worked fine before but I get an error with a useless error message: "An error was encountered. Please return to the previous page and try again." when using this provider. If anyone has successfully done this in VB, I would appreciate some hints. Thanks. Sign In·View Thread·PermaLink Re: Usage in VB.Net Websites Roger Martin 6:32 4 Jun '09   Whatever the issue, I doubt it is related to VB. Can you provide any more details about what might be happening? What is your web page trying to do? Can you get a call stack? Can you run in debug mode and let the exception assistant in Visual Studio help you out? The error sounds like something a browser generates rather than IIS, ASP.NET, or even your web site. You might check your OS's event log for clues. Roger Martin Gallery Server Pro Sign In·View Thread·PermaLink Re: Usage in VB.Net Websites Phantom208 7:09 4 Jun '09   Thanks for your quick reply and suggestion. I found the error in the event log. I had failed to remove the default authentication entry in Web.Config so it created an error. However, when the WebAdmin opened, I got the SQLServer options not the SQLite provider. I cut and pasted your Web.Config changes into the file and added before the in the connection string section. VS2008 inserts some config sections that are not automatically inserted in VS 2005. Included in these are roleService and profileService. Do I need to delete these setions? Also, do I need to add the TechInfoSystems.Data.SQLiteProvider.dll assembly to the assemblies section like I do for System.Data.SQLite? I did not have to do that for the Mascix provider dlls. (From these questions you can easily see that web development is not my area. I appreciate your help.) Sign In·View Thread·PermaLink Re: Usage in VB.Net Websites Phantom208 7:13 4 Jun '09   The missing line in the space was (without opening and closing punctuation which seems to keep it from appearing): remove name = "LocalSQLServer" Sign In·View Thread·PermaLink Re: Usage in VB.Net Websites Phantom208 7:23 4 Jun '09   Added information from the event log when trying to use webadmin: Failed to initialize the AppDomain:699b547d Exception: System.Configuration.ConfigurationErrorsException Message: Unrecognized configuration section membership. (E:\VisualStudio2005\Projects\NewTLC\web.config line 33) StackTrace: at System.Web.HttpRuntime.HostingInit(HostingEnvironmentFlags hostingFlags) at System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters) at System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters) at System.Web.Hosting.ApplicationManager.CreateAppDomainWithHostingEnvironment(String appId, IApplicationHost appHost, HostingEnvironmentParameters hostingParameters) at System.Web.Hosting.ApplicationManager.CreateAppDomainWithHostingEnvironmentAndReportErrors(String appId, IApplicationHost appHost, HostingEnvironmentParameters hostingParameters) Sign In·View Thread·PermaLink Re: Usage in VB.Net Websites Roger Martin 7:32 4 Jun '09   The roleService and profileService are not needed by SQLite, but they don't hurt either. Leave them in or take them out - your preference. Is your project a web site or web application project (WAP)? I believe with web sites you need to include references in the web.config file, so you might need something, but I am not sure if it goes in the assemblies section (is there a references section?). It might be sufficient to just have the dll in the bin directory. I don't use the web site model as WAP gives me many less headaches. With WAP you do not need to reference the dll in the assemblies section. Just make sure it is in the bin directory. Roger Martin Gallery Server Pro Sign In·View Thread·PermaLink Re: Usage in VB.Net Websites Phantom208 8:36 4 Jun '09   It is a WAP. Removing the role and profile services did not make a difference. I got the same error and stack. Sign In·View Thread·PermaLink Bug in SQLiteMembershipProvider [modified] marc15 7:30 30 Mar '09   Hi Roger, I found two small bugs in your SQLiteMembershipProvider. 1. ChangePasswordQuestionAndAnswer() procedure: If you take a closer look, the command text parameters do not match the name of the parameters used for the parameters collection (I also posted the bug description here). 2. GetUsersInRole() procedure: You should remove ToLowerInvariant(), because r.RoleName IS case sensitive. In GetRolesForUser() the call to ToLowerInvariant() was OK and had a meaning, here not. BTW, great job. Thank you. Marcel Sign In·View Thread·PermaLink Re: Bug in SQLiteMembershipProvider Roger Martin 4:44 1 Apr '09   Thanks for the bug reports. I fixed those and, along with several others that I had been accumulating, posted the latest code in the article. Look at the history section for more details on what was fixed. Roger Martin Gallery Server Pro Sign In·View Thread·PermaLink Problems Getting It To Work AlexCruzVBPR 6:55 18 Feb '09   Roger, Thanks for the great article. Cant wait to play with it but yesterday, I spent a couple of hours trying to add it to a VS2005 project and could not get it to work. Is there anything special that needs to be done for vs2005? Tnaks :( Sign In·View Thread·PermaLink Re: Problems Getting It To Work Roger Martin 7:03 18 Feb '09   You shouldn't have to do anything special. I use the code in Gallery Server Pro, which is released as a .NET 2.0 project in VS 2005 format. Roger Martin Gallery Server Pro Sign In·View Thread·PermaLink Re: Problems Getting It To Work AlexCruzVBPR 7:07 18 Feb '09   ok, its probably me then, I will try again tonight. By the way, great product Gallery Server Pro. What I want to do is add it to an existing web project. I did not have any luck with that last night either, So I just installed it as a separate application and added a link from my main page. I will continue again to try because I really want to get it to work, specially since I do not want to use sql server. I would rather user sqlite. Thanks Alex Sign In·View Thread·PermaLink you will probably like this mascix 21:45 7 Jan '09   at some point I needed webparts ability in my projects and I remembered that article and your rewrite. anyway I added sqlite personalization provider to my article check it out. blog(in turkish) Sign In·View Thread·PermaLink Caution !!! mercede 20:29 4 Nov '08   Although it is a good effort to create SQlite Provider but what I've gathered from my experience is that Sqlite provider usually fails in ASP.NET for few technical reasons. Sqlite is not a multi-user data base and was never designed to be so. A connection therfore cannot be shared among multiple threads, or different processes. If used either the threads hangs or errors out. ASP.NET on the other hand is a multi-user technology. for each in comming request it invokes a thread from its pool. now if some earlier thread is already serving (i.e has a connection opened) the other requests would be refused. Even if you somehow use it there are risks of corrupting the database easily. Sqlite provider is nice for learning but I'd not recommend using it in any commerical website or even a website that has a few thousand users visiting it. Sign In·View Thread·PermaLink Re: Caution !!! Roger Martin 10:55 5 Nov '08   You raise an excellent point that all users should be aware of. SQLite is a file-based database, not client-server. As such only one thread can write to it at a time (although multiple threads *can* read without locking it). On a server that is write-intensive, some connections can be starved and you may get a timeout error. The general rule of thumb is that SQLite is appropriate for apps that are small enough to fit on a single server. There is also more info here. Roger Martin Gallery Server Pro Sign In·View Thread·PermaLink Re: Caution !!! aaberg 23:37 10 Nov '08   SQLite has no problems with multiple connections, as long as the threads only reads from the database. When multiple threads are writing to the database, only one thread can write at the time forcing the other threads to wait for their turn. But it will not corrupt the database. According to SQLite.org, SQLite has been demonstrated to work on websites with 1.000.000 hits/day. Se this link: link Sign In·View Thread·PermaLink Re: Caution !!! aaberg 23:39 10 Nov '08   The link is: http://sqlite.org/whentouse.html[^] Sign In·View Thread·PermaLink Re: Caution !!! mercede 21:53 12 Nov '08   there's really nothing to get emotional about Sqlite/Website issue. You dont have to browse through lots of documentation and other stuff to get an understanding. How exactly would you make sure that only 1 thread has the write access to the database, when the threads are choosen at random from the thread pool? Would you use critical sections/mutexes/semaphores or what? None of these are recomended in the web applications. If there's a website XYZ, User A accesses it from USA and User B from brazil and both want to modify information. how would you make sure that both of them should not invoke the modification operation at the same time? Or would u prefer displaying "please wait while the other user is accessing it" Sqlite engine locks the entire file. Is it allowed in a multiuser environment? The only solution is to create a seperate process that accesses the file and your requests must be served through this process. Thats exactly how oracle/mssql and others work by running this process as a service. Im not against sqlite and use it in a few desktop applications. Always analyze the situation and then make a decision wisely. that being said, if you still want to use Sqlite in your commercial web applications, go ahead! Sign In·View Thread·PermaLink Re: Caution !!! aaberg 23:03 12 Nov '08   As I understand it, you don't have to handle multithreaded access to your database, the SQLite engine handles it for you. If the user from USA and the user from Brazil tries to write something to the database at the same time, the user from USA have to wait the extra 20ms it takes for database to store the Brazilian guy's data. This could be a problem, if there are a serious amount of people writing to the database at the same time, but for the majority of websites there are a few moderators and a lot of readers. For commercial use, you have to consider security when choosing your database. If you have to store sensitive data, I don't think it is wise to use SQLite. But I don't think there are any technical threading issues when using SQLite for small/medium sized websites. Sign In·View Thread·PermaLink Re: Caution !!! mercede 0:02 13 Nov '08   Very few C# programs would actually look for the SQLITE_THREADSAFE macro, change it, recompile and then make changes in the ADO.NET provider. By default Sqlite or its provider are not thread safe either. I use this provider "http://sqlite.phxsoftware.com[^]. maybe you should read this post http://sqlite.phxsoftware.com/forums/t/56.aspx[^] here's an extract: If multiple threads must talk to the same database file, then make multiple connections to it and make sure you don't share them. Also remember that SQLite's locking mechanism is not row or table level, it is file level locking. Therefore you cannot be actively iterating through a datareader while a write operation is pending, and all write operations are suspended while a datareader is reading. The ADO.NET wrapper has built-in retry and timeout mechanisms to handle these scenarios, but if you are using multiple threads and attempt to mix a read with a write, or a write with a read, you're going to hit a deadlock. Sign In·View Thread·PermaLink Re: Caution !!! aaberg 1:06 13 Nov '08   That must be a problem with the ADO.NET provider you use, because SQLite will not hit a deadlock, when writing and reading at the same time. Please read this on table locking in SQLite: http://www.sqlite.org/lockingv3.html[^] And this threadsafe-test: http://www.sqlite.org/cvstrac/wiki?p=MultiThreading[^] And by the way, the SQLITE_THREADSAFE option is set by default on the Windows Binary. And as I understand, it is also set in the http://sqlite.phxsoftware.com[^] ADO.NET provider by default. Sign In·View Thread·PermaLink

Last Visit: 18:06 20 Nov '09     Last Update: 18:06 20 Nov '09 12 Next »