C#使用SQLite数据库详解

SQLite 是一个嵌入式的关系数据库系统，使用十分广泛。在一些数据量不大的应用程序中，如果使用SQLite可以极大的减少部署时的工作量。 要在C#中使用SQLite也很简单，只要找一个C#的wrapper就可以了，例如，我使用的就是来自

http://sqlite.phxsoftware.com/  的一个dll，System.Data.SQLite. 下载下来的文件是SQLite-1.0.65.0-setup.exe。只要安装一下就可以使用了，非常方便。该程序符合ADO.NET的规范，并且支持Visual Studio的可视化表设计器。

打开Visual Studio 2008，新建一个Console Application，为此项目添加System.Data.SQLite的引用。添加一个数据库连接，此时可以发现，新建连接中有了一个SQLite Database Connection，选择此类型的数据连接，并且新建一个文件，

test.db3. 接下来在新数据库中添加一张表，如下：

下面开始为此表建立一个Data Access类，以展示在C#中如何使用SQLite，可以想象，和操作其他数据库是几乎一样的，感谢ADO.NET的功劳。

首先是一个实体类 Book.cs：

<!--Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->public class Book    {        private int id;        private string bookName;        private decimal price;         public int ID        {            get { return id; }            set { id = value; }        }        public string BookName        {            get { return bookName; }            set { bookName = value; }        }        public decimal Price        {            get { return price; }            set { price = value; }        }    } 

编写DAL类：

<!--Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->using System;using System.Collections.Generic;using System.Text;using System.Data.SQLite; namespace ConsoleApplication1{    public class BookDAL    {        public static bool Create(Book book)        {            try            {                using (SQLiteConnection conn = new SQLiteConnection("Data Source=e:\\test.db3"))                {                    conn.Open();                    SQLiteCommand cmd = conn.CreateCommand();                    cmd.CommandText = "insert into Book values(@ID,@BookName,@Price);";                    cmd.Parameters.Add(new SQLiteParameter("ID", book.ID));                    cmd.Parameters.Add(new SQLiteParameter("BookName", book.BookName));                    cmd.Parameters.Add(new SQLiteParameter("Price", book.Price));                    int i = cmd.ExecuteNonQuery();                    return i == 1;                }            }            catch (Exception)            {                //Do any logging operation here if necessary                return false;            }        }         public static bool Update(Book book)        {            try            {                using (SQLiteConnection conn = new SQLiteConnection("Data Source=e:\\test.db3"))                {                    conn.Open();                    SQLiteCommand cmd = conn.CreateCommand();                    cmd.CommandText = "update Book set BookName=@BookName,Price=@Price where ID=@ID;";                    cmd.Parameters.Add(new SQLiteParameter("ID", book.ID));                    cmd.Parameters.Add(new SQLiteParameter("BookName", book.BookName));                    cmd.Parameters.Add(new SQLiteParameter("Price", book.Price));                    int i = cmd.ExecuteNonQuery();                    return i == 1;                }            }            catch (Exception)            {                //Do any logging operation here if necessary                return false;            }        }         public static 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 static Book GetbyID(int ID)        {            try            {                using (SQLiteConnection conn = new SQLiteConnection("Data Source=e:\\test.db3"))                {                    conn.Open();                    SQLiteCommand cmd = conn.CreateCommand();                    cmd.CommandText = "select * from Book where ID=@ID;";                    cmd.Parameters.Add(new SQLiteParameter("ID", ID));                    SQLiteDataReader dr = cmd.ExecuteReader();                    if (dr.Read())                    {                        Book book = new Book();                        book.ID = dr.GetInt32(0);                        book.BookName = dr.GetString(1);                        book.Price = dr.GetDecimal(2);                        return book;                    }                    else                        return null;                }            }            catch (Exception)            {                //Do any logging operation here if necessary                return null;            }        }    }} 

编写测试主程序：

<!--Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->using System; namespace ConsoleApplication1{    class Program    {        static void Main(string[] args)        {            Book book = new Book();            book.ID = 1;            book.BookName = "Book A";            book.Price = 10.0m;            BookDAL.Create(book);             book.ID = 2;            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);         }    }} 

最终结果：

2 第二本书 13
2 第二本书 11.1
1 Book A 10