• Download Contact - 1.6 MB

Introduction

Building an VS2010 project using a SQLite embedded database...

Background

First of all you'll have to aquire 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 Admin Tool
This tool I chose the Full install and I'm pleased with the result..

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

Using the code

SQLite

First we create a SQLite dB named "Contact.3db" using the SQLite Admin tool.
Iadded 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 ) 

VS2O1O & .NET 4.0

Next I created a VS2010 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 , SQLiet.Interop.dll] must be included to your project
just under the root element of your project. So when 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 to classes that handle
all dB transactions.. [dBFunctions.cs , dBHelper.cs]
[see pic. Solution Explore]

Next In 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]

And this includes my files within my project.

You can download the source code here Download Contact.zip - 1.6 MB

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. And 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 seperate class I've given myself the posiblity 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);
                }
            }
        }
    }
} 

Next are the forms I created...

The "ContactList" form and the title I forgat to modify "cc" is teh main form of this appi-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 achieave this one must ulter the [BaseContact.Designer.cs] class by ultering
a few security policies of some methods.

[Pic. BaseContact]

[Pic. NewContact]

[Pic. EditContact]

[Pic. DeleteContact]

Conclusion

For detail coding details one can best download the source code Download Contact.zip - 1.6 MB

History

Keep a running update of any changes or improvements you've made here.