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：
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类：
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(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("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;
}
}
}
}
编写测试主程序：
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
//---------------------------------------------------------------
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
// 创建数据库文件
File.Delete("test1.db3");
SQLiteConnection.CreateFile("test1.db3");
DbProviderFactory factory = SQLiteFactory.Instance;
using (DbConnection conn = factory.CreateConnection())
{
// 连接数据库
conn.ConnectionString = "Data Source=test1.db3";
conn.Open();
// 创建数据表
string sql = "create table [test1] ([id] INTEGER PRIMARY KEY, [s] TEXT COLLATE NOCASE)";
DbCommand cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
// 添加参数
cmd.Parameters.Add(cmd.CreateParameter());
// 开始计时
Stopwatch watch = new Stopwatch();
watch.Start();
// 连续插入1000条记录
for (int i = 0; i < 1000; i++)
{
cmd.CommandText = "insert into [test1] ([s]) values (?)";
cmd.Parameters[0].Value = i.ToString();
cmd.ExecuteNonQuery();
}
// 停止计时
watch.Stop();
Console.WriteLine(watch.Elapsed);
}
哎~~~~ 一个常识性的错误，我加几行代码 (新增代码标记 "// <-------------------")。
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
// 创建数据库文件
File.Delete("test1.db3");
SQLiteConnection.CreateFile("test1.db3");
DbProviderFactory factory = SQLiteFactory.Instance;
using (DbConnection conn = factory.CreateConnection())
{
// 连接数据库
conn.ConnectionString = "Data Source=test1.db3";
conn.Open();
// 创建数据表
string sql = "create table [test1] ([id] INTEGER PRIMARY KEY, [s] TEXT COLLATE NOCASE)";
DbCommand cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
// 添加参数
cmd.Parameters.Add(cmd.CreateParameter());
// 开始计时
Stopwatch watch = new Stopwatch();
watch.Start();
DbTransaction trans = conn.BeginTransaction(); // <-------------------
try
{
// 连续插入1000条记录
for (int i = 0; i < 1000; i++)
{
cmd.CommandText = "insert into [test1] ([s]) values (?)";
cmd.Parameters[0].Value = i.ToString();
cmd.ExecuteNonQuery();
}
trans.Commit(); // <-------------------
}
catch
{
trans.Rollback(); // <-------------------
throw; // <-------------------
}
// 停止计时
watch.Stop();
Console.WriteLine(watch.Elapsed);
}
执行一下，耗时 0.2 秒。
