C# SQLite Example

• Download Precompiled Binaries for Windows from http://www.sqlite.org/download.html
• Download ADO.Net provider for SQLite http://sourceforge.net/projects/sqlite-dotnet2/. The Project page is here http://sqlite.phxsoftware.com/
• Download SQLite GUI client. This is a very basic one. wxSQLite http://cfred.free.fr/download.php#wxsqliteplus

Create new SQLite database using wxSQLite or command line c:\temp\contacts.db

In Your C# Project

• Add References to System.Data.SQLite
• Add sqlite3.dll (as link) to your project and set Copy to Output Directory property to Copy if newer

C# Test to create table and read some data.

[TestFixture]
public class TestSqlLite
{
    #region sql
    private string createSql = @"
DROP TABLE IF EXISTS Contacts;
CREATE TABLE Contacts(
FirstName TEXT,
LastName TEXT
);
INSERT INTO Contacts
SELECT 'Michael','Jordan'
UNION SELECT 'Scottie','Pippen'
;
";
    private string selectSql = @"SELECT * FROM Contacts";
    #endregion
    [Test]
    public void Test()
    {
        //String connString = "Data Source=contacts.db"; from current directory
        String connString = "Data Source=C://temp/contacts.db"; //from specific location
                    
        using (SQLiteConnection conn = new SQLiteConnection(connString))
        {
            Console.WriteLine("Opening Connection");
            conn.Open();
            Console.WriteLine("Creating 'Contacts' Table");
            using (SQLiteCommand cmd = new SQLiteCommand(createSql, conn))
            {                    
                cmd.ExecuteNonQuery();
            }
            Console.WriteLine("Query 'Contacts' Table");
            using (SQLiteCommand cmd = new SQLiteCommand(selectSql, conn))
            {                    
                using (SQLiteDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {                            
                        Console.WriteLine("  "+dr.GetString(dr.GetOrdinal("FirstName")) + " " + dr.GetString(dr.GetOrdinal("LastName")));
                    }
                }
            }
        }            
    }
}