初试 SQLite

关于 SQLite 数据库，下面来自百度百科：

SQLite，是一款轻型的数据库，是遵守ACID的关联式数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源世界著名的数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至今已经有12个年头，SQLite也迎来了一个版本 SQLite 3已经发布。

下面介绍 .NET 下调用 SQLite 的步骤：

1. 去 SQLite 官网（http://www.sqlite.org/download.html）找到 “Precompiled Binaries for .NET” ，并点击它。

2. 在打开的页面（http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki）找到 “Setups for 32-bit Windows (.NET Framework 4.0)”，并下载“sqlite-netFx40-setup-bundle-x86-2010-1.0.85.0.exe”。

3. 安装 sqlite-netFx40-setup-bundle-x86-2010-1.0.85.0.exe。

4. 下载一个可视化的 SQLite 管理工具 SQLiteSpy，就像 SQL Server Management Studio 一样的管理工具。

5.  打开它，创建一个数据库，关于 SQLite 的语法，它和 MySQL 很相似。

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

select * from [Book] limit 0,1;

6. 创建一个 .NET Web 应用程序。代码如下（代码来自与：http://www.cnblogs.com/TivonStone/archive/2013/01/05/2846831.html）：

Book.cs

public class Book
{
    public int ID { get; set; }

    public string BookName { get; set; }

    public decimal Price { get; set; }
}

SQLiteDatabase.cs

public class SQLiteDatabase
{
    private static readonly string sqlite_db_path = ConfigurationManager.AppSettings["sqlite_db_path"];

    String dbConnection;
    SQLiteConnection cnn;
 
    #region ctor
    /// <summary>
    ///     Default Constructor for SQLiteDatabase Class.
    /// </summary>
    public SQLiteDatabase()
    {
        dbConnection = sqlite_db_path ;
        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)
            {
                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;
    }
}

BookDAL.cs

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;
        }
    }
    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;
        }
    }
}

测试代码：

protected void btnDo_Click(object sender, EventArgs e)
{
    BookDAL BookDAL = new 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);
}

 

6. 测试通过。

代码下载：https://files.cnblogs.com/Music/SqliteDemo.rar

参考自：http://www.cnblogs.com/TivonStone/archive/2013/01/05/2846831.html

谢谢浏览！