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 =
BookDAL.Create(book);
book.ID = 2;
book.BookName = "第二本书";
book.Price =
BookDAL.Create(book);
book = BookDAL.GetbyID(2);
Console.WriteLine(book.ID + " " + book.BookName + " " + book.Price);
book.Price =
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