Click here to Skip to main content
13,410,685 members (31,044 online)
Click here to Skip to main content
Add your own
alternative version


61 bookmarked
Posted 3 May 2011

C# & SQLite 1007000

, 3 May 2011
Rate this:
Please Sign up or sign in to vote.
.NET project with a SQLite embedded database


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


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

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


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....

[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"


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".


[Pic. Solution Explore]

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.


  • 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.


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

using System.IO;

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


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;

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

                // 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_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

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

            catch (Exception)
                // Always 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;

                // Open the connection

                // Save the DataRow. This triggers an event OnRowUpdated

                // We here, OK!
                return true;
            catch (Exception)
                // 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.


[Pic. ContactList]


[Pic. ContactList - ContextMenustrip]

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.


[Pic. BaseContact]


[Pic. NewContact]


[Pic. EditContact]


[Pic. DeleteContact]


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


  • 3rd May, 2011: Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Web Developer
Belgium Belgium
Developer within C#, Dynamics NAV (Navision), Php environments.

You may also be interested in...

Comments and Discussions

QuestionThanks and a Question - Transactions Pin
JTSteer8-Aug-13 13:08
memberJTSteer8-Aug-13 13:08 
QuestionFew functions that may be useful for dBHelper.cs Pin
Chucky6418-Sep-12 23:34
memberChucky6418-Sep-12 23:34 
Questionjust a question Pin
Aka018-Dec-11 17:15
memberAka018-Dec-11 17:15 
AnswerRe: just a question Pin
kribo31-Jan-12 1:08
memberkribo31-Jan-12 1:08 
Generalwe also have csharp sqlite Pin
Unruled Boy3-May-11 17:45
memberUnruled Boy3-May-11 17:45 
GeneralRe: we also have csharp sqlite Pin
kribo4-May-11 0:33
memberkribo4-May-11 0:33 
GeneralRe: we also have csharp sqlite Pin
dave.dolan13-Jun-11 18:09
memberdave.dolan13-Jun-11 18:09 
Question1007000 Pin
AspDotNetDev3-May-11 8:48
mvpAspDotNetDev3-May-11 8:48 
AnswerRe: 1007000 [modified] Pin
kribo4-May-11 0:32
memberkribo4-May-11 0:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180221.1 | Last Updated 3 May 2011
Article Copyright 2011 by kribo
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid