10 Steps to consume SQLite in WPF

SQL Lite is the compact database system used by more client applications to manage local data. It is more lightweight and easy to use. This article will explain how to create SQL Lite DB and connect it to a WPF client application. Let’s create a simple client app which read employees data from local SQL Lite DB.

1. SQL Lite Adminstrator tool can be downloaded from the following link. From where we can manage our tables, queries, triggers and stored procedures.

http://sqliteadmin.orbmu2k.de/

2. SQL Lite binaries can be downloaded from below link. (The following assemblies compiled against .NET 2.0).

http://sourceforge.net/projects/sqlite-dotnet2/

3. Now let’s jump into WPF client application. We can go ahead with MVVM pattern. Our application will contain a main view where we can have a DataGrid to list the employees. And we need a viewmodel class which holds the EmployeeCollection property. Also we need a model class class which can store details of Employee

4. Now lets’ create the data. Open the SQL Lite Admin tool. Create a new database. (DB.Employee). Run the following query to create a new table (Employee).

CREATE TABLE Employee(
EmployeeID NUMERIC PRIMARY KEY,
Name NVARCHAR(30),
Email NVARCHAR(30),
Phone NVARCHAR(30)) ;

5. Add a new configuration file to your project to add connection string as below. Since the SQLLite assemblies compiled against 2.0 it will not work under .NET 4.0 environment. To make it compatible, set the attribute useLegacyV2RuntimeActivationPolicy to true. Otherwise it will crash the application.

<connectionStrings>
    <add name=”SQLiteDb” connectionString=”Data Source=AppData/DB.Employee.s3db;Version=3;” />
</connectionStrings>

6. EmployeeService class will have static methods to query the DB. We are going to use this class from our viewmodel.

        public static string ConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings[“SQLiteDb”].ConnectionString;
            }
        }       

public static List GetEmployees()
        {
            var list = new List();
            string query = “Select * from Employee”;
            using (var connection = new SQLiteConnection(ConnectionString))
            {
                using (var command = new SQLiteCommand(query, connection))
                {
                    connection.Open();                   

SQLiteDataReader rd = command.ExecuteReader();                   

while (rd.Read())
                    {
                        var model = new EmployeeModel();
                        model.Name = rd[1].ToString();
                        model.Email = rd[2].ToString();
                        model.Phone = rd[3].ToString();
                        list.Add(model);
                    }
                }
            }
            return list;
        }

7. On constructor of ViewModel, call the GetEmployees method to populate the collection.

this.Employees = EmployeeService.GetEmployees();

8. Set EmployeeViewModel as DataContext for shell window.9. Bind Employees property to DataGrid ItemsSource property.

<DataGrid Margin=”5″ ItemsSource=”{Binding Employees}”/>

10. Run the application. That’s it.Stay tuned for more articles on SQL Lite.

Download Sample