Notes: SQLite for ASP.NET

Why. Safer than MS-Access. Doesn’t require your hosting account to have any JET drivers for MS-Access. Simpler than SQL Express User Instances. Doesn’t require understanding or doing the MS-SQL administrative steps. Doesn’t even need a ODBC DSN to be set up.

What. The SQLite ADO.NET Provider.

How.
How to configure the Data Provider

From documentation, add this to web.config.

<configuration>
<system.data>
<DbProviderFactories>
<remove invariant=”System.Data.SQLite”/>
<add name=”SQLite Data Provider”
invariant=”System.Data.SQLite”
description=”.Net Framework Data Provider for SQLite”
type=”System.Data.SQLite.SQLiteFactory, System.Data.SQLite” />
</DbProviderFactories>
</system.data>
</configuration>

Add System.Data.SQLite.DLL to \bin\

Add your SQLite data file to \App_Data\   The framework will not server files in App_data, so the data will be safe from a user downloading and looking at your data directly.

You do not need to add any other files to the target server! Even though SQLite is a C/C++ based data access API, the System.Data.SQLite.DLL file has everything it needs. It does to interop calls, so the website will probably need to be trusted enough to do interop. It seems the C dll is compiled into the .NET dll, something I didn’t know was possible.

To connect, it helps to have the full file path:

<connectionStrings>
<add name=”ConnectionString”
connectionString=”Data Source=d:\hosting\mywebbsite\App_Data\data.s3db;New=False;Version=3″/>
</connectionStrings>

The code is straight forward…
Imports System.Data.SQLite

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim con As New Data.SQLite.SQLiteConnection
con.ConnectionString = ConfigurationManager.ConnectionStrings(“ConnectionString”).ConnectionString
Dim com As New Data.SQLite.SQLiteCommand
con.Open()
com.Connection = con
Dim t As SQLiteTransaction = con.BeginTransaction()
com.CommandText = “SELECT * FROM Test”
Me.gvSampleData.DataSource = com.ExecuteReader
gvSampleData.DataBind()
t.Commit()
con.Close()
End Sub
End Class

SQLite Membership Provider. One exists. I haven’t used it yet.  That one has support for Membership and Roles.  I’m still looking for something with profiles support.  The other ASP.NET 2.0 database things would be SQL Session and Webparts, neither of which are anything you’d likely do with a small database anyhow.

Comments are closed.