Click here to Skip to main content
11,482,039 members (65,217 online)
Click here to Skip to main content

C# & SQLite 1007000

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

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

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

SolutionExplore.GIF

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

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.

ContactList.GIF

[Pic. ContactList]

ContactList_ContextMenustrip.GIF

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

BaseContact.GIF

[Pic. BaseContact]

NewContact.GIF

[Pic. NewContact]

EditContact.GIF

[Pic. EditContact]

DeleteContact.GIF

[Pic. DeleteContact]

Conclusion

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

History

  • 3rd May, 2011: Initial post

License

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

Share

About the Author

kribo
Software Developer
Belgium Belgium
Developer within C#, Dynamics NAV (Navision), Php environments.
Follow on   Twitter   LinkedIn

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    Rant Rant    Admin Admin   

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

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