"Wrong is right." -- Thelonius Monk
I've been working with the SQLite Database for well over two years now, and I was extremely pleased when I found out that Robert Simpson, a developer par excellence who has been using this amazing little database engine for a large commercial project he works on, decided to take the time to port the ADO.NET SQLite provider to the full ASP.NET 2.0 ADO.NET provider model. When I say "full", I mean he implemented designer support and even user defined functions.
Actually the word "Port" is an insult, Robert used Generics and literally started with the Provider model from scratch. Take a look at the benchmarks he provides against other popular databases, most of which are much larger and extremely complicated, and you'll share my enthusiasm for SQLite.
Recently I was cruising the .NET and found an excellent article by "andriniana" at codeproject with a well-done pass at Roles and Membership provider classes for the MySQL database. Well, it didn't take me more than ten seconds to figure out that with some careful reconstruction of the sample provider classes and the required SQL, I could have a set of ASP.NET 2.0 membership and role providers for SQLite (which in my opinion, runs rings around MYSQL in terms of being a no-installation and very lightweight, "drop in the dll" database engine). So, that's what I did!
Microsoft provides a Membership provider in the 2.0 Framework but it is only for SQL Server. However, there is provider sample code included in the framework SDK which can be used as a model. What this work here does is wire up the necessary web.config settings and the required class implementations to automatically use a SQLite provider for membership and roles. So, if you drop a Login Control or a CreateUser control on your form, they will automatically use SQLite!
I hope experienced developers will get the portability picture on this - you don't need to "install" anything, you don't even need the MS Jet OleDb provider to be installed. You can just copy or distribute your solution to any IIS Webserver, possibly with a pre-populated database, and provided that the account identity your app is running under has read/write permissions to your SQLite XXX.db provider database file, that's it! Perfect for shared hosting solutions, or custom distributions to a customer that need to "work out of the box".
I've included a sample web.config, completely filled out, a sample provider.db SQLite Database, the SQL script required to generate the tables and indexes, and completely reworked SQLiteMembershipProvider and SQLiteRoleProvider classes that can be dropped into your APP_CODE folder, and you are ready to roll! It even works with the ASP.NET 2.0 Web Site Administration Tool web page, right out of the box, so if you click on the little black arrow at the top right of a Login or Create User Control and choose Administration, you'll be completely hooked into your SQLite Database automatically with this setup (you can still use Access or Sql Server for other sites).
NOTE: SQLite connection strings are maps to a physical database file, just like JET connection strings. Therefore, unless you happen to store your web folder in the exact location on your C drive that I did when developing this solution, you'll need to change this element to match the location on your machine in the web.config:
<add name="ConnString" connectionString="Data Source=C:\CSHARPBIN2\SQLiteProvider\Provider.db;Version=3;"/>
The sample solution has most of the various Login - related controls already on it, along with a separate "Welcome" page that serves as the success target of the Login Control. If you want to test the Recover Password control, you'll need to use the WebSite Configuration web page to configure your SMTP server settings; the current block is commented out in the web.config.
Let's take a look at a sample method, CreateUser, from one of these classes, the SQLiteMembershipProvider class, to see what's involved:
public override MembershipUser CreateUser(string username,
string password,
string email,
string passwordQuestion,
string passwordAnswer,
bool isApproved,
object providerUserKey,
out MembershipCreateStatus status)
{
ValidatePasswordEventArgs args =
new ValidatePasswordEventArgs(username, password, true);
OnValidatingPassword(args);
if (args.Cancel)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
if (RequiresUniqueEmail && GetUserNameByEmail(email) != "")
{
status = MembershipCreateStatus.DuplicateEmail;
return null;
}
MembershipUser u = GetUser(username, false);
if (u == null)
{
DateTime createDate = DateTime.Now;
if (providerUserKey == null)
{
providerUserKey = Guid.NewGuid();
}
else
{
if (!(providerUserKey is Guid))
{
status = MembershipCreateStatus.InvalidProviderUserKey;
return null;
}
}
SQLiteConnection conn = new SQLiteConnection(connectionString);
SQLiteCommand cmd = new SQLiteCommand("INSERT INTO `" + tableName + "`" +
" (PKID, Username, Password, Email, PasswordQuestion, " +
" PasswordAnswer, IsApproved," +
" Comment, CreationDate, LastPasswordChangedDate, LastActivityDate," +
" ApplicationName, IsLockedOut, LastLockedOutDate," +
" FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, " +
" FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart)" +
" Values($PKID, $Username, $Password, $Email, $PasswordQuestion, " +
" $PasswordAnswer, $IsApproved, $Comment, $CreationDate, $LastPasswordChangedDate, " +
" $LastActivityDate, $ApplicationName, $IsLockedOut, $LastLockedOutDate, " +
" $FailedPasswordAttemptCount, $FailedPasswordAttemptWindowStart, " +
" $FailedPasswordAnswerAttemptCount, $FailedPasswordAnswerAttemptWindowStart)", conn);
cmd.Parameters.Add("$PKID", DbType.String).Value = providerUserKey.ToString();
cmd.Parameters.Add("$Username", DbType.String, 255).Value = username;
cmd.Parameters.Add("$Password", DbType.String, 255).Value = EncodePassword(password);
cmd.Parameters.Add("$Email", DbType.String, 128).Value = email;
cmd.Parameters.Add("$PasswordQuestion", DbType.String, 255).Value = passwordQuestion;
cmd.Parameters.Add("$PasswordAnswer", DbType.String, 255).Value = EncodePassword(passwordAnswer);
cmd.Parameters.Add("$IsApproved", DbType.Boolean).Value = isApproved;
cmd.Parameters.Add("$Comment", DbType.String, 255).Value = "";
cmd.Parameters.Add("$CreationDate", DbType.DateTime).Value = createDate;
cmd.Parameters.Add("$LastPasswordChangedDate", DbType.DateTime).Value = createDate;
cmd.Parameters.Add("$LastActivityDate", DbType.DateTime).Value = createDate;
cmd.Parameters.Add("$ApplicationName", DbType.String, 255).Value = pApplicationName;
cmd.Parameters.Add("$IsLockedOut", DbType.Boolean).Value = false;
cmd.Parameters.Add("$LastLockedOutDate", DbType.DateTime).Value = createDate;
cmd.Parameters.Add("$FailedPasswordAttemptCount", DbType.Int32).Value = 0;
cmd.Parameters.Add("$FailedPasswordAttemptWindowStart", DbType.DateTime).Value = createDate;
cmd.Parameters.Add("$FailedPasswordAnswerAttemptCount", DbType.Int32).Value = 0;
cmd.Parameters.Add("$FailedPasswordAnswerAttemptWindowStart", DbType.DateTime).Value = createDate;
try
{
conn.Open();
int recAdded = cmd.ExecuteNonQuery();
if (recAdded > 0)
{
status = MembershipCreateStatus.Success;
}
else
{
status = MembershipCreateStatus.UserRejected;
}
}
catch (SQLiteException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "CreateUser");
}
status = MembershipCreateStatus.ProviderError;
}
finally
{
conn.Close();
}
return GetUser(username, false);
}
else
{
status = MembershipCreateStatus.DuplicateUserName;
}
return null;
}
Virtually all the methods in both providers are overrides of the base class. For example, the class definition of the MembershipProvider class:
namespace PAB.Web
{
public sealed class SQLiteMembershipProvider : MembershipProvider
{
Notes on Designer Support
After posting this article, a reader asked about alternative access to the database and asked about a SqlDataSource. I responded incorrectly. Yes, you can certainly use a SqlDataSource control with the ASP.NET 2.0 SQLite / ADO.NET Provider. But, design-time support doesn't come free. When you download the distribution of the Provider from SourceForge.net (the link can be found at Robert's site above) you will see that there is a Designer folder containing the following files:
install.cmd
Install.vbs
readme.txt
SQLite.Designer.dll
SQLiteDataObjectSupport.xml
SQLiteDataViewSupport.xml
SQLiteDesigner.reg
To install designer support, execute the "Install.cmd" batch file which will install the System.Data.SQLite.dll assembly in the GAC, and the required Registry entries for designer support. Then, you'll be able to add a SqlDataSource control to your WebForms, and configure it using the SQLite data source which should now appear in the list. Note that this is only a requirement for Design-time support, it has nothing to do with deployment, which required nothing more than your System.Data.SQLite.dll assembly and your database file in locations where your ASP.NET 2.0 application expects them. With the simplified deployment model, that would be in the APP_CODE folder for the dll, and wherever your connection string specifies for the database file. Users of x64 OS versions should be aware of the fact that the entries in the vbs and .reg files will need to be changed to reflect the fact that things are different for x64 support.
It is also important to note that the account identity that your application is running under (which could be one of several different accounts, depending on how your app and IIS is configured) needs to have write permissions on the folder that the SQLite database file is located in. What this means is that if your IIS application is running under the ASPNET, IIS_WPG, or IUSR_["machineName"] accounts, the respective account must either be granted write permissions on the folder where the SQLite database file is resident, or you can use the
<identity impersonate="true" userName="privilegedUser" password="userPassword" />
web.config element to make the app run under specified credentials that have write privileges. This is no different from (and is virtually identical to) the requirements for using an MS Access database MDB file, with the exception that the OleDb JET provider is NOT required to be installed on the target machine for SQLite database usage. Once you get used to using SQLite instead of MS Access you will see huge improvements in throughput and speed, so believe me -- its well worth the effort in terms of the additional study and testing that are required to master this.
You can
download and unzip the Web Site zip file below into your favorite folder anywhere on your hard drive, and load the WebSite in Visual Studio.NET 2005 using the File Access option. No IIS Configuration is required. The SQLite engine and ADO.NET 2.0 provider DLL is included as is the complete web.config, and the provider.db SQLite Database already has 2 users "test1" and "test2" with passwords "test1" and "test2" respectively. The Default page has both a login control and a Create New User control for you to play with.
I have also included a second zip file inside the main download with a working copy of the SQLiteQA app that works a lot like Query Analyzer, which I have modified to work with SQLite 3.0 databases. NOTE: If you want to use the included SQL Script file in SQLiteQA, you'l need to highlite each separate CREATE statement separately and press F5 to execute it.
Download the Visual Studio.NET 2005 Web Site folder zip file accompanying this article.
|