SQLite簡介及SQLite在.NET中的應用

在IIS上發布好了WCF之後，我一直在努力尋找除了XML外最簡單的資料庫。雖然對SQLite早有耳聞，今天聽說android和ios裡用的都是sqlite，今天來嘗嘗鮮

在官網上有各種平台的版本，找到你需要的平台。如下

然後在平台下的各種版本中選一個，我們選32位.NET4.0的bundle版本：這裡大家可以看到一個是bundle的，另一個是不帶bundle的；bundle的表示System.Data.SQLite.dll裡混合了SQLite.Interop.dll。

我們這裡下載bundle混合版本的（如果下的是另一個版本，在項目中添加SQLite.Interop.dll會出錯，如果不添加SQLite.Interop.dll也會儲存）。

 

還有一項準備工作，下載一個工具：sqlitespy，用來操作sqlite資料庫的。

 

工具都準備好了，就可以開始了

1. 用spy來建立個資料庫

DROP TABLE [BOOK];CREATE TABLE [Book]([ID] INTEGER NOT NULL PRIMARY KEY autoincrement,[BookName] VARCHAR(50) NOT NULL,[Price] REAL NOT NULL);

 

2.在vs裡寫好資料庫操作類(寫的比較簡陋, 大家自行完善)

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SQLite;namespace SQLiteDemo{    public class SQLiteDatabase    {        String dbConnection;        SQLiteConnection cnn;        #region ctor        /// <summary>        ///     Default Constructor for SQLiteDatabase Class.        /// </summary>        public SQLiteDatabase()        {            dbConnection = "Data Source=recipes.s3db";            cnn = new SQLiteConnection(dbConnection);        }        /// <summary>        ///     Single Param Constructor for specifying the DB file.        /// </summary>        /// <param name="inputFile">The File containing the DB</param>        public SQLiteDatabase(String inputFile)        {            dbConnection = String.Format("Data Source={0}", inputFile);            cnn = new SQLiteConnection(dbConnection);        }        /// <summary>        ///     Single Param Constructor for specifying advanced connection options.        /// </summary>        /// <param name="connectionOpts">A dictionary containing all desired options and their values</param>        public SQLiteDatabase(Dictionary<String, String> connectionOpts)        {            String str = "";            foreach (KeyValuePair<String, String> row in connectionOpts)            {                str += String.Format("{0}={1}; ", row.Key, row.Value);            }            str = str.Trim().Substring(0, str.Length - 1);            dbConnection = str;            cnn = new SQLiteConnection(dbConnection);        }        #endregion        /// <summary>        ///     Allows the programmer to run a query against the Database.        /// </summary>        /// <param name="sql">The SQL to run</param>        /// <returns>A DataTable containing the result set.</returns>        public DataTable GetDataTable(string sql)        {            DataTable dt = new DataTable();            try            {                SQLiteConnection cnn = new SQLiteConnection(dbConnection);                cnn.Open();                SQLiteCommand mycommand = new SQLiteCommand(cnn);                mycommand.CommandText = sql;                SQLiteDataReader reader = mycommand.ExecuteReader();                dt.Load(reader);                reader.Close();                cnn.Close();            }            catch (Exception e)            {                throw new Exception(e.Message);            }            return dt;        }        public DataTable GetDataTable(string sql, IList<SQLiteParameter> cmdparams)        {            DataTable dt = new DataTable();            try            {                SQLiteConnection cnn = new SQLiteConnection(dbConnection);                cnn.Open();                SQLiteCommand mycommand = new SQLiteCommand(cnn);                mycommand.CommandText = sql;                mycommand.Parameters.AddRange(cmdparams.ToArray());                mycommand.CommandTimeout = 180;                SQLiteDataReader reader = mycommand.ExecuteReader();                dt.Load(reader);                reader.Close();                cnn.Close();            }            catch (Exception e)            {                throw new Exception(e.Message);            }            return dt;        }        /// <summary>        ///     Allows the programmer to interact with the database for purposes other than a query.        /// </summary>        /// <param name="sql">The SQL to be run.</param>        /// <returns>An Integer containing the number of rows updated.</returns>        public bool ExecuteNonQuery(string sql)        {            bool successState = false;            cnn.Open();            using (SQLiteTransaction mytrans = cnn.BeginTransaction())            {                SQLiteCommand mycommand = new SQLiteCommand(sql, cnn);                try                {                    mycommand.CommandTimeout = 180;                    mycommand.ExecuteNonQuery();                    mytrans.Commit();                    successState = true;                    cnn.Close();                }                catch (Exception e)                {                    mytrans.Rollback();                }                finally                {                    mycommand.Dispose();                    cnn.Close();                }            }            return successState;        }        public bool ExecuteNonQuery(string sql, IList<SQLiteParameter> cmdparams)        {            bool successState = false;            cnn.Open();            using (SQLiteTransaction mytrans = cnn.BeginTransaction())            {                SQLiteCommand mycommand = new SQLiteCommand(sql, cnn, mytrans);                try                {                    mycommand.Parameters.AddRange(cmdparams.ToArray());                    mycommand.CommandTimeout = 180;                    mycommand.ExecuteNonQuery();                    mytrans.Commit();                    successState = true;                    cnn.Close();                }                catch (Exception e)                {                    mytrans.Rollback();                    throw e;                }                finally                {                    mycommand.Dispose();                    cnn.Close();                }                            }            return successState;        }        /// <summary>        ///     暫時用不到        ///     Allows the programmer to retrieve single items from the DB.        /// </summary>        /// <param name="sql">The query to run.</param>        /// <returns>A string.</returns>        public string ExecuteScalar(string sql)        {            cnn.Open();            SQLiteCommand mycommand = new SQLiteCommand(cnn);            mycommand.CommandText = sql;            object value = mycommand.ExecuteScalar();            cnn.Close();            if (value != null)            {                return value.ToString();            }            return "";        }        /// <summary>        ///     Allows the programmer to easily update rows in the DB.        /// </summary>        /// <param name="tableName">The table to update.</param>        /// <param name="data">A dictionary containing Column names and their new values.</param>        /// <param name="where">The where clause for the update statement.</param>        /// <returns>A boolean true or false to signify success or failure.</returns>        public bool Update(String tableName, Dictionary<String, String> data, String where)        {            String vals = "";            Boolean returnCode = true;            if (data.Count >= 1)            {                foreach (KeyValuePair<String, String> val in data)                {                    vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());                }                vals = vals.Substring(0, vals.Length - 1);            }            try            {                this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));            }            catch            {                returnCode = false;            }            return returnCode;        }    }}

 

3. 寫好dal, 這裡有個提示, id可以自增, 但是一定要插入null

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SQLite;namespace SQLiteDemo{    public class BookDAL    {        SQLiteDatabase sqlExcute = new SQLiteDatabase();        public bool Create(Book book)        {            try            {                var sql = "insert into Book values(@ID,@BookName,@Price);";                var cmdparams = new List<SQLiteParameter>()                {                    new SQLiteParameter("ID", null),                    new SQLiteParameter("BookName", book.BookName),                    new SQLiteParameter("Price", book.Price)                };                return sqlExcute.ExecuteNonQuery(sql, cmdparams);            }            catch (Exception e)            {                //Do any logging operation here if necessary                throw e;                return false;            }        }        public bool Update(Book book)        {            try            {                var sql = "update Book set BookName=@BookName,Price=@Price where ID=@ID;";                var cmdparams = new List<SQLiteParameter>()                {                    new SQLiteParameter("ID", book.ID),                    new SQLiteParameter("BookName", book.BookName),                    new SQLiteParameter("Price", book.Price)                };                return sqlExcute.ExecuteNonQuery(sql, cmdparams);            }            catch (Exception)            {                //Do any logging operation here if necessary                return false;            }        }        public bool Delete(int ID)        {            try            {                using (SQLiteConnection conn = new SQLiteConnection("Data Source=e:\\test.db3"))                {                    conn.Open();                    SQLiteCommand cmd = conn.CreateCommand();                    cmd.CommandText = "delete from Book where ID=@ID;";                    cmd.Parameters.Add(new SQLiteParameter("ID", ID));                    int i = cmd.ExecuteNonQuery();                    return i == 1;                }            }            catch (Exception)            {                //Do any logging operation here if necessary                return false;            }        }        public Book GetbyID(int ID)        {            try            {                var sql = "select * from Book where ID=@ID;";                var cmdparams = new List<SQLiteParameter>()                {                    new SQLiteParameter("ID", ID)                };                var dt = sqlExcute.GetDataTable(sql, cmdparams);                if (dt.Rows.Count > 0)                {                    Book book = new Book();                    book.ID = int.Parse(dt.Rows[0]["ID"].ToString());                    book.BookName = dt.Rows[0]["BookName"].ToString();                    book.Price = decimal.Parse(dt.Rows[0]["Price"].ToString());                    return book;                }                else                    return null;            }            catch (Exception)            {                //Do any logging operation here if necessary                return null;            }        }    }}

 

4. 在console裡寫調用

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SQLite;namespace SQLiteDemo{    class Program    {        static void Main(string[] args)        {            BookDAL BookDAL = new SQLiteDemo.BookDAL();            Book book = new Book();            book.BookName = "第一本書";            book.Price = 10.0m;            BookDAL.Create(book);            book.BookName = "第二本書";            book.Price = 13.0m;            BookDAL.Create(book);            book = BookDAL.GetbyID(2);            Console.WriteLine(book.ID + "　" + book.BookName + "　" + book.Price);            book.Price = 11.1m;            BookDAL.Update(book);            book = BookDAL.GetbyID(2);            Console.WriteLine(book.ID + "　" + book.BookName + "　" + book.Price);            book = BookDAL.GetbyID(1);            Console.WriteLine(book.ID + "　" + book.BookName + "　" + book.Price);            Console.Read();        }    }}

 

5.出來執行個體

 

http://system.data.sqlite.org/downloads/1.0.65.0/sqlite-netFx40-setup-x86-2010-1.0.65.0.exe

 

注意:

如果用戶端調用出錯，可能是資料庫的位置錯誤，因為這裡是相對bin/debug下的位置，最好放個固定的位置。

 

參考:

http://stackoverflow.com/questions/2605490/system-data-sqlite-net-4