在VS2008中如何使用SQLite（C#版）

友情提示：本文所述代码下载地址 http://download.youkuaiyun.com/source/2299805

 

SQLite 是一个嵌入式的关系数据库系统，运用十分广泛。在一些数据处理量不大的应用程序中，使用SQLite可以很大程度的降低部署时的工作量。要使VS2008支持SQLite十分简单，只需要下载 SQLite-1.0.66.0-setup.exe，安装即可。

打开Visual Studio 2008，新建一个Console Application，项目名称为SQLite，并为项目添加System.Data.SQLite的引用。再添加一个数据库连接，此时可以看到，更改数据源窗口中多了一个SQLite Database File的数据源选项，如图：

 

挑选此类型的数据源，并且New一个Database文件test.db，密码123，如图：

 

接下来再新数据库中添加一张表Book，如图： 

下面开始为此表建立一个Data Access类，以展示在VS2008中如何使用SQLite，可以想象，和操作其他数据库是几乎一样的。
首先，新建一个实体类 Book.cs using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace SQLite { 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类：BookDAL   using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SQLite; namespace SQLite { class BookDAL { private const string sConn = "Data Source=G://Exercise//Visual Studio 2008//SQLite//test.db;Version=3;Password=123;"; public static bool Create(Book book) { try { using (SQLiteConnection conn = new SQLiteConnection(sConn)) { conn.Open(); SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = "INSERT INTO book(ID,BookName,Price) 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(sConn)) { 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(sConn)) { 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(sConn)) { 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; } } } }

最后，编写测试主程序： using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace SQLite { class Program { static void Main(string[] args) { if (BookDAL.Delete(1) == false) Console.WriteLine("删除 ID=1 失败"); if (BookDAL.Delete(2) == false) Console.WriteLine("删除 ID=2 失败"); Book book = new Book(); book.ID = 1; book.BookName = "Book　A"; book.Price = 40.0m; BookDAL.Create(book); book.ID = 2; book.BookName = "第二本书"; book.Price = 42.0m; BookDAL.Create(book); book = BookDAL.GetbyID(2); Console.WriteLine(book.ID + "　" + book.BookName + "　" + book.Price); book.Price = 24.5m; 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　第二本书　42
2　第二本书　24.5
1　Book　A　40