. NET calls to SQLite database

I recently completed a project called the Address Book software. This is a very simple system, and the business side will not say. I want to share, why use the SQLite database.

When we develop the address book, we hope that when the contacts are connected or disconnected, we can query the contact information. That will need to synchronize the contents of the Address Book to local, SQLite is a relatively lightweight database, very good for storing locally. The solution is very simple, when the Internet, I directly access the SQL Server database server, but when the network is not available, we only need to read SQLite data.

(1) Create an SQLite table

I downloaded a sqlite Expert Professional 3 tool to create a table that stores information about your contacts directly. The SQLite Expert Professional 3 tool is very useful, but the official version is for money. In the process of creating a data table, we need to be aware of how the Datatime type of data is stored. In the SQLite database, I think the best way is to save the date as a string, and if necessary, take it out and deal with it.

(2) Introducing DLLs

When using the SQLite database, we introduced System.Data.SQLite.dll in Project engineering.

(3) C # classes that use SQLite databases

This is the most important part of our article, is to share the sqlite operation of the basic class, the code is as follows:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace AddressBook
{
    class SQLiteHelper
    {
        private SQLiteConnection conn = null;

        private string connString = string.Empty;

        public string ConnString
        {
            get {return connString;}
            set {connString = value;}
        }

        private string err = string.Empty;

        public string Err
        {
            get {return err;}
            set {err = value;}
        }


        public SQLiteHelper ()
        {
            string connString = "Data Source =" + Environment.CurrentDirectory + "/AddressBook.db";
            conn = new SQLiteConnection (connString); // Create a database instance, specify the file location
            //conn.Open();//Open the database, if the file does not exist, it will be automatically created
        }

        /// <summary>
        /// create table
        /// </ summary>
        /// <returns> </ returns>
        public int CreateTables (string sql)
        {
            SQLiteCommand cmdCreateTable = new SQLiteCommand (sql, conn);
            cmdCreateTable.ExecuteNonQuery (); // If the table does not exist, create a data table

            return 1;
        }

        /// <summary>
        /// operate the database
        /// </ summary>
        /// <param name = "sql"> </ param>
        /// <returns> </ returns>
        public int ExecNoQuery (string sql)
        {
            int retValue = -1;

            try
            {
                conn.Open ();

                SQLiteCommand cmd = new SQLiteCommand (sql, conn);

                retValue = cmd.ExecuteNonQuery ();

                conn.Close ();
            }
            catch (Exception ex)
            {
                err = ex.Message;
                return -1;
            }

            return retValue;
        }

        /// <summary>
        /// Obtain department data
        /// </ summary>
        /// <returns> </ returns>
        public DataSet GetDataSet (string sql)
        {
            try
            {
                DataSet dataset = new DataSet ();
                SQLiteDataAdapter adapter = new SQLiteDataAdapter ();
                adapter.SelectCommand = new SQLiteCommand (sql, conn);
                adapter.Fill (dataset);
                return dataset;
            }
            catch (Exception ex)
            {
                err = ex.Message;
                return null;
            }
        }

        /// <summary>
        /// return 1 string
        /// </ summary>
        /// <param name = "sql"> </ param>
        /// <returns> </ returns>
        public string ExecReturenOne (string sql)
        {
            string result = string.Empty;
            try
            {
                conn.Open ();

                SQLiteCommand cmd = new SQLiteCommand (sql, conn);

                object obj = cmd.ExecuteScalar ();

                if (obj! = null)
                {
                    result = obj.ToString ();
                }

                conn.Close ();
            }
            catch (Exception ex)
            {
                if (conn.State == System.Data.ConnectionState.Open)
                {
                    conn.Close ();
                }

                err = ex.Message;
                return "";
            }

            return result;
        }
    }
} 

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

. NET calls to SQLite database