SQL Lite used by Martin Wangel

SQL Lite in C#

SQLite (SQL lite) is an open source database engine. For more information about SQLite, please refer to the SQLite home page.

I found the directions for using sqlite not quite transparent. C# did not make it any easier for me by not allowing calling convention to be set on callbacks, fortunately it seems that sqlite 3 has moved away from callbacks.

Anyway after some experimenting and guessing, I came up with the following code to use sqlite from c#.

Step 1 is to put the sqlite3.dll in the same directory as your project output (bin\Debug and bin\Release).

Step 2 is to declare some external functions.

	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_open")]
	public static extern int sqlite3open( string filename, out IntPtr dbhandle );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_close")]
	public static extern int sqlite3close( IntPtr dbhandle );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_exec" )]
	public static extern int sqlite3exec( IntPtr dbhandle, string sql, ExecuteCallbackFunctionType callback, IntPtr callbackArg1, out IntPtr errorMessage );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_changes" )]
	public static extern int sqlite3_changes( IntPtr dbhandle );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_total_changes" )]
	public static extern int sqlite3totalchanges( IntPtr dbhandle );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_prepare" )]
	public static extern int sqlite3prepare( IntPtr dbhandle, string sql, int stringlength, out IntPtr statementhandle, out IntPtr unusedsql );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_step" )]
	public static extern int sqlite3step( IntPtr statementhandle );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_finalize" )]
	public static extern int sqlite3finalize( IntPtr statementhandle );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_reset" )]
	public static extern int sqlite3reset( IntPtr statementhandle );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_column_count" )]
	public static extern int sqlite3columncount( IntPtr statementhandle );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_column_name" )]
	public static extern string sqlite3columnname( IntPtr statementhandle, int columnindex );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_column_type" )]
	public static extern int sqlite3columntype( IntPtr statementhandle, int columnindex );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_column_int" )]
	public static extern int sqlite3columnint( IntPtr statementhandle, int columnindex );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_column_double" )]
	public static extern double sqlite3columndouble( IntPtr statementhandle, int columnindex );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_column_text" )]
	public static extern string sqlite3columntext( IntPtr statementhandle, int columnindex );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_column_blob" )]
	public static extern IntPtr sqlite3columnblob( IntPtr statementhandle, int columnindex );
	
	[DllImport("Sqlite3.Dll", EntryPoint="sqlite3_column_bytes" )]
	public static extern int sqlite3columnbytes( IntPtr statementhandle, int columnindex );

Step 3: define a class member variable to hold you database pointer. Add one for column name caching too. Also add a class to hold some column data

	IntPtr mdbhandle;
	protected Hashtable mhtStatementhandle2columndata; 


	internal class columndata
	{
		public int index = 0;
		public int datatype = 0;

		public columndata( int indx, int type )
		{
			index  = indx;
			datatype = type;
		}
	}

Step 4: Add a couple of helper methods.

	internal void GetColumnDataForStatement( IntPtr statementhandle )
	{
		// Kolla så att vi inte har sparat namn/index-listan för detta statement redan.
		if( mhtStatementhandle2columndata.ContainsKey( statementhandle ) )
			return;

		// Skapa en hashtabell som mappar kolumnnamn till kolumnindex.
		Hashtable columnname2position = new Hashtable();

		// Lägg in alla kolumnnamnen i columnname2position.
		int ncols = sqlite3columncount( statementhandle );
		for( int icol=0; icol < ncols; icol++ )
		{
			string colname = sqlite3columnname( statementhandle, icol );
			colname = colname.Trim().ToUpper();

			int ityp = sqlite3columntype( statementhandle, icol );
			if( ityp == 0 )
				return; //this will happen if sqlite3step has not been called yet.

			columndata cd = new columndata( icol, ityp );
			columnname2position.Add( colname, cd );
		}

		// Lagra den nya hashtabell i medlemshashtabellen så att vi kan hitta
		// den med hjälp av ett statement handle.
		mhtStatementhandle2columndata.Add( statementhandle, columnname2position );
	}

	internal void ClearColumnDataForStatement( IntPtr statementhandle )
	{
		if( mhtStatementhandle2columndata.ContainsKey( statementhandle ) )
			mhtStatementhandle2columndata.Remove( statementhandle );
	}

	internal void dbg( string s )
	{
		System.Diagnostics.Debug.WriteLine( s );
	}

Step 5: Action!

	void main()
	{
		int openres = sqlite3open( dbfilename, out mdbhandle );
	
	
		int numTotChanges = sqlite3totalchanges( mdbhandle );
		int closeresult = sqlite3close( mdbhandle );
	}


	internal object ExecuteScalar( string sql, out int numChanges )
	{
		object x = 0;
		numChanges = 0;

		IntPtr statementhandle, unusedsql;
		bool firstrow = true;

		// förbered
		int prepres = sqlite3prepare( mdbhandle, sql, sql.Length, out statementhandle, out unusedsql );
		if( prepres == SQLITE_OK )
		{
			int stepres = SQLITE_ROW;
			while( stepres == SQLITE_BUSY || stepres == SQLITE_ROW )
			{
				// kör
				stepres = sqlite3step( statementhandle );

				if( stepres == SQLITE_BUSY )
				{
					dbg( "Waiting for SQLite execution." );
					System.Threading.Thread.Sleep( DB_WAIT_SLEEP_MS );
				}
				else if( stepres == SQLITE_ROW )
				{
					dbg( "Found an SQLite data row." );
					if( firstrow )
					{
						firstrow = false;
						GetColumnDataForStatement( statementhandle );

						// Get the data here and now
						Hashtable httmp = mhtStatementhandle2columndata[ statementhandle ] as Hashtable;
						foreach( object ox in httmp.Values ) //there will only be one
						{
							columndata cd = ox as columndata;
							int cix = cd.index;
							x = sqlite3columnint( statementhandle, cix );
						}

					}
				}
				else if( stepres == SQLITE_DONE )
				{
					dbg( "SQLite is at end of data." );
					numChanges = sqlite3_changes( mdbhandle );
				}			
			}

		}
		else
		{
			dbg( "Error: sqlite3prepare returned " + prepres.ToString() );
		}

		// rensa
		int finalres = sqlite3finalize( statementhandle );
		ClearColumnDataForStatement( statementhandle );

		return x;
	}

Other kinds of results can be read with the same technique as in the ExecuteScalar method above. Just keep stepping through the rows until you hit SQLITE_DONE. With the hash table that maps column names to other column information you will be able to write methods like "bool Read()" and "object GetValue( string columnname )" to step through opened result sets and get data from them. You'll need to choose among and then use the "sqlite3columnXXX" methods to get the data.

/Martin Wangel