转自:http://www.csharpwin.com/csharpspace/10437r5520.shtml
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:
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 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