C#: How to Retrieve Data from SQLite Database

SQLite is a small database engine that is suitable for memory constraint systems like Windows mobile, Smart Phones, embedded devices and for single user desktop applications where it only requires small memory. I already discussed How to connect to SQLite Database. For this blog entry, I will try to show you how to retrieve or load data from SQLite table using the ADO.NET 2.0 Provider for SQLite.



To start your C# + SQLite project, follow these easy steps.
1. Download ADO.NET 2.0 Provider for SQLite.
2. Create your C# Project and Copy the DLL (System.Data.SQLite.DLL) to your project then add a reference to it.
3. Since SQLite doesn't come with a GUI administration then you might want to download free SQLite GUI clients like SQLite Administrator.
4. You can start creating your database using your downloaded SQLite GUI Client.
5. Include the following namespaces on the Using directive.

using System.Data;
using System.Data.SQLite;

6. Now to load the content of your database, let's use SQLiteDataReader class.

            SQLiteConnection cn = new SQLiteConnection();
            try
            {

                //Connect to SQLite database
                cn.ConnectionString = @"Data Source=D:\MyProjects\Personal\Sample C#\SQLiteSample\db\test.s3db";
                cn.Open();

                //Create the SQL Command
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = cn;
                cmd.CommandText = "SELECT * FROM Customer";

                //Retrieve the records using SQLiteDataReader
                SQLiteDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    //display records
                    MessageBox.Show(dr["FirstName"].ToString() + " " + dr["LastName"].ToString());
                }

            }
            catch(Exception ex)
            {

                //display any exeptions
                MessageBox.Show(ex.Message); 

            }
            finally
            {
                cn.Close();
            }

* for detailed explanation on how to connect to SQLite read this.

If everything is correct, you should be able to see a the records from your test table.

To stay up-to-date on Technology news, subscribe now.