C# & SQLite 1007000

• Download source - 1.49 MB

Introduction

This article discusses how to build an VS2010 project using a SQLite embedded database.

Background

First of all, you'll have to acquire a few files and install a few necessary software packets.

• SQLite ADO.NET provider http://sqlite.phxsoftware.com
• SQLite Administrator http://sqliteadmin.orbmu2k.de

SQLite ADO.NET Provider

I installed this tool in a "C:\Temp" file and I chose not to register the DLL files. This is because I just needed to include the main DLL in my project.

SQLite Admin Tool

I chose the full install of this tool and I'm pleased with the result.

Using the Code

SQLite

First, we create a SQLite dB named "Contact.3db" using the SQLite Admin tool.
I added a table "Contact" with a few fields and inserted a few records of data to play with....

CREATE TABLE [Contact](
[contact_id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[FirstName] vARCHAR(50)  NULL,
[LastName] vARCHAR(50)  NULL )

VS 2O1O & .NET 4.0

Next, I created a VS 2010 project named "Contact" and added a few folders and files. Add a folder [Database] and insert an existing Item [Contact.3db] the database. Change the property "Copy to Output Directory" -> "Copy Always".

Next, I added the essential SQLite DLL to my project.

• System.Data.SQLite.dll
• Change the property "Copy to Output Directory" => "Copy Always"
• SQLite.Interop.dll
• Change the property "Copy to Output Directory" => "Copy Always"

Remark

Both files [System.Data.SQLite.dll, SQLite.Interop.dll] must be included to your project
just under the root element of your project. So when you install on a 3-party PC, they be
in the same directory as the *.exe file.
[see pic. Solution Explore]

Next, I added a folder named "Classes" and created the two classes that handle all dB transactions. [dBFunctions.cs, dBHelper.cs]
[See pic. Solution Explore]

Next, I added a folder named "Forms" and created a few forms to handle the user interaction / interface.
[ContactList.cs, BaseContact.cs, NewContact.cs, EditContact.cs, DeleteContact.cs].

This includes my files within my project.

You can download the source code here.

Small Remark

In the download, I included the "Debug" dierectory because that's where I stored the database which is the topic of this article, which you can see in the first class "dBFunctions".

The Database Classes

In the [dBFunction] class, I stored the connection string and location of the database, which is in the "Debug" directory.
By putting the connectionstring in a separate class, I've given myself the possiblity to create different instances of the [dBHelper] class by only using a single instance of
the connectionString [dBFunctions] class.

Conclusion

• Use the [dBFunctions] class to connect to the database
• Use the [dBHelper] class to connect to the Tables within the database

With these two classes, all interaction with the database is handled.

dBFunctions.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.IO;

namespace Kribo.Class
{
    class dBFunctions
    {
        public static string ConnectionStringSQLite
        {
            get
            {
                string database =
                    AppDomain.CurrentDomain.BaseDirectory + "\\Database\\Contact.s3db";
                string connectionString =
                    @"Data Source=" + Path.GetFullPath(database);
                return connectionString;
            }
        }
    }
}  

dBHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

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

namespace Kribo.Class
{
    class dBHelper
    {
        // Declartion internal variables
        private SQLiteConnection m_connection = null;
        private string m_connectionString = "";
        private SQLiteDataAdapter m_dataAdapter = null;
        private DataSet m_dataSet = null;
        private string m_fieldNameID = "";

        // The DataSet is filled with the methode LoadDataSet
        public DataSet DataSet
        {
            get { return m_dataSet; }
        }

        // Constructor -> ConnectionString is required
        public dBHelper(string connectionString)
        {
            m_connectionString = connectionString;
        }

        // Load the DataSet 
        public bool Load(string commandText, string fieldNameID)
        {
            // Save the variables
            m_fieldNameID = fieldNameID;

            try
            {
                // Open de connectie
                m_connection = new SQLiteConnection(m_connectionString);
                m_connection.Open();

                // Make a DataAdapter
                m_dataAdapter = new SQLiteDataAdapter(commandText, m_connection);

                // Link a eventhandler to the RowUpdated-event of the DataAdapter
                //m_dataAdapter.RowUpdated += new SqlRowUpdatedEventHandler
						(m_dataAdapter_RowUpdated);
                m_dataAdapter.RowUpdated += m_dataAdapter_RowUpdated;
                m_dataSet = new DataSet();

                // For a save --> create Commands 
                if (!string.IsNullOrEmpty(fieldNameID))
                {
                    SQLiteCommandBuilder commandBuilder = 
				new SQLiteCommandBuilder(m_dataAdapter);
                    m_dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
                    m_dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
                    m_dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
                }

                // Fill the DataSet
                m_dataAdapter.Fill(m_dataSet);

                // We're here, OK!
                return true;
            }

            catch (Exception)
            {
                throw;
            }
            finally
            {
                // Always close
                m_connection.Close();
            }
        }
        // Load the DataSet
        public bool Load(string commandText)
        {
            return Load(commandText, "");
        }
        // Save the DataSet
        public bool Save()
        {
            // Save is only posible if ID is known
            if (m_fieldNameID.Trim().Length == 0)
            {
                return false;
            }

            try
            {
                // Open the connection
                m_connection.Open();

                // Save the DataRow. This triggers an event OnRowUpdated
                m_dataAdapter.Update(m_dataSet);

                // We here, OK!
                return true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                // Close
                m_connection.Close();
            }
        }

        // Save is only posible if ID is known
        void m_dataAdapter_RowUpdated(object sender, 
                                      System.Data.Common.RowUpdatedEventArgs e)
        {
            // The (just receaved?) ID is only interesting with a new record

            if (e.StatementType == StatementType.Insert)
            {
                // Determin the just receaved ID
                SQLiteCommand command = new SQLiteCommand
                                       ("SELECT last_insert_rowid() AS ID", m_connection);
                
                // Get the new ID and Save in the according field
                object newID = command.ExecuteScalar();

                // BIf errors then no ID --> thus testing required
                if (newID == System.DBNull.Value == false)
                {
                    // Put the ID in the DataRow
                    e.Row[m_fieldNameID] = Convert.ToInt32(nieuweID);
                }
            }
        }
    }
} 

User Interaction

Next are the forms I created...

The "ContactList" form and the title I forgot to modify "cc" is the main form of this app-demo. It has a toolMenuStrip, dataGridView and contextMenuStrip. The contextMenuStrip is linked to the datGridView.

Next, I created the forms responsable for all modification to the database, the Insert, Edit and Delete forms. These forms are inhertence based forms upon my base form "BaseContact". So a little example of OOP. To achieve this, one must alter the [BaseContact.Designer.cs] class by altering a few security policies of some methods.

Conclusion

For coding details, one can best download the source code from here.

History

• 3^rd May, 2011: Initial post