SQLite is an open source lightweight database engine. It has a very small footprint for the database file. You may find more information about SQLite at the SQLite Website. SQLite and Firebird are the best ones for embeded databases. Postgres and MySQL are better suited for client/server architecture comparing to SQLite and Firebird. At Cherry City Software you may find MySQL OLE DB Provider and Firebird OLE DB Provider.

SQLite provides a command line application to manage databases. SQLite library comes with API's. The SQLite OLE DB Provider is a thin wrapper of those SQLite Version 3.2.7+ API's. This provider is being implemented with ATL OLE DB Provider templates so that the provider itself has very small footprint even though the provider does not have many dependencies. It only depends on SQLite library along with Windows OS runtime. You may use ADO or ADO.NET 2.0 to access data with SQLite OLE DB Provider.

Using this data provider, Cherry City Software created a .NET log utility named CherryLog4Net to store log information for other .NET applications.

The following are the features that this SQLite OLE DB Provider supports:

• Transaction
• Record scroll
• Parameters
• Bookmarks
• Database Schema - tables and their columns
• Extended error information
• Multiple queries in one single execution
• Unicode
• Transparency - all SQLite scripts are transparent to SQLite engine
• Command Preparation and Column Information on command object  

Provider=OleSQLite.SQLiteSource; Data Source=DatabaseFileFullPath;

Where

• DatabaseFileFullPath is the database file full path. For example, C:\public\customer.dat

Using ADO or ADO.NET to work with SQLite OLE DB Provider is really simple. This provider makes developers' life a lot easy in order to make use of SQLite database. You may find sample code using ADO and ADO.NET.

		
'define connection used through out in the form
Private mConn As ADODB.Connection 

'//Synposis:
'//1. Create an ADO connection
'//2. Enable Read Data button if the table exists
Private Sub Form_Load()
	On Error GoTo Trap
	Set mConn = New ADODB.Connection
	mConn.Open "Provider=OleSQLite.SQLiteSource.1; " _
	    & "Data Source=C:\public\customer.dat"
	If HasCustTable Then
		Me.Read.Enabled = True
	End If
	Exit Sub
Trap:          		
	MsgBox Err.Description
End Sub

'//Synposis:
'//1. Create a table named cust if it does not exist. If the table exits,
'//delete all records from the table.
'//2. Insert some records into cust table
'//3. Update the records using parameters which makes it easy for
'//binary data manipulation
Private Sub Create_Click()
	On Error GoTo Trap
	Dim cmd As New ADODB.Command
	cmd.ActiveConnection = mConn
	If HasCustTable Then    'delete all data from the table if table exists
		cmd.CommandText = "DELETE FROM cust;"
	Else  'create cust table if it does not exists
		cmd.CommandText = "CREATE TABLE cust(ID " _
		    & "INTEGER PRIMARY KEY, Name VARCHAR(60), "
			& "Address VARCHAR(120), Picture BLOB);"
	End If 
	cmd.Execute 
	cmd.CommandText = ""
	
	'insert data
	Dim I As Integer
	For I = 100 To 110 
		cmd.CommandText = cmd.CommandText _
		     & "INSERT INTO cust (ID, Name) VALUES (" _
		     & I & ", 'Sean " & I & "');"
	Next I 
	cmd.Execute 
	
	
	'update records with Parameters
	Dim BA(999) As Byte 
	For I = 0 To 999
	  BA(I) = (I + 1) Mod 256 
	Next I 
	
	Dim params(2) As Parameter
	Set params(0) = New ADODB.Parameter 
	params(0).Type = adInteger 'integer
	params(0).Value = 100
	Set params(1) = New ADODB.Parameter
	params(1).Type = adBSTR     'string
	params(1).Value = "1010 Main St, Salem, OR"
	Set params(2) = New ADODB.Parameter
	params(2).Type = adBinary
	params(2).Value = BA
	params(2).Size = 1000
	cmd.CommandText = "UPDATE cust SET Address=?, Picture=? WHERE ID=?;"
	
	'add parameters according to commend text 
	cmd.Parameters.Append params(1)
	cmd.Parameters.Append params(2)
	cmd.Parameters.Append params(0)
	cmd.Execute
	
	'using transaction
	params(0).Value = 101
	mConn.BeginTrans
	cmd.Execute
	mConn.RollbackTrans 'You will see the address is NOT updated
	params(0).Value = 102
	mConn.BeginTrans
	cmd.Execute
	mConn.CommitTrans 'Address is updated when read back
	
	Set cmd = Nothing
	MsgBox "Done"
	Exit Sub
Trap:
	MsgBox Err.Description
End Sub


'//Synposis:
'//Create a recordset to get all data from cust table and present the
'//data in a data grid.
'//To view the binary data read back from the database, you may open
'//open a watch window in the debug mode
Private Sub Read_Click()
	Dim BA() As Byte
	On Error GoTo Trap
	Me.MSHFlexGrid1.Clear
	Dim Rs As New ADODB.Recordset
	Rs.Open "SELECT * FROM cust;", mConn
	If Not Rs.EOF Then
		BA = Rs("Picture").Value 'add to watch window to view byte array
	End If
	Set Me.MSHFlexGrid1.Recordset = Rs
	Exit Sub
Trap:
	MsgBox Err.Description 
End Sub

'//Synposis:
'//Clear all data from the data grid   
Private Sub Clear_Click()
	Me.MSHFlexGrid1.Clear
End Sub

'//Synposis:
'//Check if the cust table exists. Return true if it does. Return false
'//otherwise.  
Private Function HasCustTable() As Boolean
	Dim Rs As ADODB.Recordset
	Set Rs = mConn.OpenSchema(adSchemaTables)
	While Not Rs.EOF
		If Rs("table_name").Value = "cust" Then
			HasCustTable = True
			GoTo funExit
		End If
		Rs.MoveNext
	Wend
funExit:
	Rs.Close
	Set Rs = Nothing
End Function
				

    
            
//create a connection used in through out the form class
private System.Data.OleDb.OleDbConnection mConn 
    = new System.Data.OleDb.OleDbConnection();

//Synposis:
//1. Assign a connection string to the connection object
//2. Open connection
//3. Enable Read Data button if the cust table exists
private void Form1_Load(object sender, System.EventArgs e)
{
	try
	{
		mConn.ConnectionString = @"Provider=OleSQLite.SQLiteSource.1;"
			+ @"Data Source=C:\public\customer.dat";
		mConn.Open();
		if (HasCustTable())
		{
			this.btnRead.Enabled = true;
		}
	}
	catch (System.Data.OleDb.OleDbException ex)
	{
		System.Diagnostics.Debug.WriteLine(ex.Message);
	}
	catch (System.Exception ex)
	{
		System.Diagnostics.Debug.WriteLine(ex.Message);	
	}
}

//Synopsis:
//1. Create a table named cust if it does not exist. If the table exits,
//delete all records from the table.
//2. Insert some records into cust table
//3. Update the records using parameters which makes it easy for
//binary data manipulation		
private void btnCreate_Click(object sender, System.EventArgs e)
{
	try
	{
		System.Data.OleDb.OleDbCommand cmd
                       = new System.Data.OleDb.OleDbCommand();
		cmd.Connection = mConn;
		//delete all data from the table if table exists
		if (HasCustTable())
		{	
			cmd.CommandText = "DELETE FROM cust;";
		}
		else   //create cust table if it does not exists
		{
			cmd.CommandText = "CREATE TABLE cust(ID "
			    + "INTEGER PRIMARY KEY, Name VARCHAR(60), "
				+ "Address VARCHAR(120), Picture BLOB);";
		}
		cmd.ExecuteNonQuery();
		
		cmd.CommandText = "";

		//insert data
		int I; 
		for (I = 100; I <= 110; I++)
		{
			cmd.CommandText = cmd.CommandText 
			+ "INSERT INTO cust (ID, Name) VALUES (" 
			+ I.ToString() + ", 'Sean " + I.ToString() + "');";
		}
		cmd.ExecuteNonQuery();
		//enable the read button once the table exists
		this.btnRead.Enabled = true;
				
		//update records with Parameters
		byte[] BA = new byte[1000];
		for (I = 0; I < 1000; I++)
		{
			BA[I] = (byte)(I + 1);
		}
		System.Data.OleDb.OleDbParameter[] oParams 
			= {new System.Data.OleDb.OleDbParameter(), 
			   new System.Data.OleDb.OleDbParameter(), 
			   new System.Data.OleDb.OleDbParameter()};
		oParams[0].Value = 100;
		oParams[1].Value = "1010 Main St, Salem, OR";
		oParams[2].Value = BA;
		cmd.CommandText = 
                  "UPDATE cust SET Address=?, Picture=? WHERE ID=?;";
		
		//add parameters according to commend text
		cmd.Parameters.Add(oParams[1]);
		cmd.Parameters.Add(oParams[2]);
		cmd.Parameters.Add(oParams[0]);
		cmd.ExecuteNonQuery();
		cmd = null;
		MessageBox.Show("Done");
	}
	catch (System.Data.OleDb.OleDbException ex)
	{
		MessageBox.Show(ex.Message);
	}
	catch (System.Exception ex)
	{
		System.Diagnostics.Debug.WriteLine(ex.Message);
	}
}

//Synopsis:
//1. create an OleDbcommand
//2. assign command text
//3. execute command
//4. read data from db, the results are 
//  written into the debug output window 
private void btnRead_Click(object sender, System.EventArgs e)
{
	try
	{
		System.Data.OleDb.OleDbCommand cmd
			= new System.Data.OleDb.OleDbCommand();
		cmd.Connection = mConn;
		cmd.CommandText = "SELECT ID, Name, Address FROM cust;"
                  System.Data.OleDb.OleDbDataAdapter da = 
                        new System.Data.OleDb.OleDbDataAdapter(cmd); 
                  System.Data.DataTable dt = new DataTable ();  
                  da.Fill(dt); 
                  dataGridView1.DataSource = dt;	
	
                  System.Data.OleDb.OleDbDataReader oReader = cmd.ExecuteReader();
		while (oReader.Read())
		{
			System.Diagnostics.Debug.WriteLine(oReader.GetInt32(0));
			System.Diagnostics.Debug.WriteLine("");
			System.Diagnostics.Debug.WriteLine(oReader.GetString(1));
			System.Diagnostics.Debug.WriteLine("");
			if (!oReader.IsDBNull(2))
			{
				System.Diagnostics.Debug.WriteLine(
                                            oReader.GetString(2));
				System.Diagnostics.Debug.WriteLine("");
			}
		}
		oReader.Close();
	}
	catch (System.Data.OleDb.OleDbException ex)
	{
		System.Diagnostics.Debug.WriteLine(ex.Message);
	}
	catch (System.Exception ex)
	{
		System.Diagnostics.Debug.WriteLine(ex.Message);
	}
}

//Synopsis:
//check if the cust table exists. Return true if it does. 
//Return false otherwise.
private bool HasCustTable()
{
	System.Data.OleDb.OleDbCommand cmd 
	    = new System.Data.OleDb.OleDbCommand();
	using (cmd)
	{
		cmd.Connection = mConn;
		cmd.CommandText = 
			"SELECT COUNT(*) FROM SQLITE_MASTER "
			+ "WHERE TYPE = 'table' AND NAME = 'cust';";
		if ((int)cmd.ExecuteScalar()==1) return true;
	}
	return false;
}		

You may visit CherryLog4Net page to find out more about how SQLite OLE DB Provider is used in .NET. The CherryLog4Net is a log utility class library that comes along with C# source code. Most of you can use it 'AS IS'. However, you shall be able to customize the log utility easily in order to meet your needs.

Using SQLite OLE DB Provider with Visual Studio 2005 IDE is very handy. You can readily add SQLite OLE DB Provider into Visual Studio 2005 IDE by adding Data connections via the OLE DB provider.

Refer to Using SQLite Provider to transfer SQLite data into SQL Server.

SQLite OLE DB Provider must be activated before you can use it. To activate it, you must download the provider and get your passcode from the Activator included in the package. Click here to get activation code.

Update History:

Date	Changes
2007-01-20	Unlocked all features as long as it is activated; Fixed an activation defect; Improved performances.
2006-10-06	Implemented GetColumnInfo on command object.
2006-09-24	Implemented schema rowset for views.
2006-09-17	Implemented ICommandPrepare interface.
2006-08-03	Upgraded the provider with SQLite Engine 3.3.6 along with a minor exception fix.
2006-03-05	Fixed a critical activation bug and a string parameter binding issue.
2006-01-02	Fixed a binary input parameter binding issue.
2005-11-27	Upgraded to support ADO.NET 2.0.
2005-11-17	Upgraded the provider with SQLite Engine 3.2.7.
2005-07-01	Corrected provider behavior on database exception.
2005-06-23	Reduced Provider Size.

SQLite OLE DB Provider
Provider VB Test source
CSharp Sample Source

This whole post here including the downloads is provided 'as-is', without any express or implied warranty. In no event will the author be held liable for any damages arising from the use of this software.

To send me feedback, click here