C# 使用sqlite 轻量级数据库

青野

　　一，准备工作：
　　sqlite3.exe 下载地址：http://www.sqlite.org/download.html 下载"sqlite-shell-win32-x86-
　　3070800.zip" 就OK了Precompiled Binaries For Windows
　　sqlite-shell-win32-x86-3070800.zip
(248.28 KiB)


　　
system.data.sqlite.dll 下载地址: http://www.dllzj.com/Down_System.Data.SQLite.DLL.html 这个 dll
　　用于Visual Studio 项目中引用
　　二，试用sqlite3.exe
　　解压sqlite-shell-win32-x86-3070800.zip 到 F:\jonse\DownLoads\sqlLite 目录下
　　开始-->运行-->cmd
　　>F:
　　>cd F:\jonse\DownLoads\sqlLite
　　>sqlite3 myDB.db (如果myDB.db不存在，则创建之；若存在，则打开它)
　　>create table test(id int,name varchar(20),remark varchar(200)); (创建test表，三列：
　　id,name,remark)
　　>insert into test select 1,'name1','remark1' union select 2,'name2','remark2'; (暂时插入2行数据)
　　>.mode column (显示列模式)
　　>.headers on (显示列头信息)
　　>select * from test; (查询所有的数据)
　　>select ifnull(max(id),0) as MaxID from test; (查询test表最大的ID值)
　　三, C# 使用sqlite
　　在VS2010的项目引用中添加 System.Data.SQLite.dll (这个在准备工作中已经下载好的)
　　(1),新建一个 SqlLiteHelper.cs 的类
　　using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
　　using System.Data;
using System.Data.SQLite;
using System.Data.Common;
　　namespace JonseTest
{
public abstract class SqlLiteHelper
{
public static string ConnSqlLiteDbPath = string.Empty;
public static string ConnString
{
get
{
return string.Format(@"Data Source={0}", ConnSqlLiteDbPath);
}
}
　　// 取datatable
public static DataTable GetDataTable(out string sError,string sSQL)
{
DataTable dt = null;
sError = string.Empty;
　　try
{
SQLiteConnection conn = new SQLiteConnection(ConnString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.CommandText = sSQL;
cmd.Connection = conn;
SQLiteDataAdapter dao = new SQLiteDataAdapter(cmd);
dt = new DataTable();
dao.Fill(dt);
}
catch (Exception ex)
{
sError = ex.Message;
}
　　return dt;
}
　　// 取某个单一的元素
public static object GetSingle(out string sError, string sSQL)
{
DataTable dt = GetDataTable(out sError, sSQL);
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0][0];
}
　　return null;
}
　　// 取最大的ID
public static Int32 GetMaxID(out string sError, string sKeyField,string sTableName)
{
DataTable dt = GetDataTable(out sError, "select ifnull(max([" + sKeyField + "]),0) as
　　MaxID from [" + sTableName + "]");
if (dt != null && dt.Rows.Count > 0)
{
return Convert.ToInt32(dt.Rows[0][0].ToString());
}
　　return 0;
}
　　// 执行 insert,update,delete 动作，也可以使用事务
public static bool UpdateData(out string sError, string sSQL,bool bUseTransaction=false)
{
int iResult = 0;
sError = string.Empty;
　　if (!bUseTransaction)
{
try
{
SQLiteConnection conn = new SQLiteConnection(ConnString);
conn.Open();
SQLiteCommand comm = new SQLiteCommand(conn);
comm.CommandText = sSQL;
iResult = comm.ExecuteNonQuery();
}
catch (Exception ex)
{
sError = ex.Message;
iResult = -1;
}
}
else // 使用事务
{
DbTransaction trans =null;
try
{
SQLiteConnection conn = new SQLiteConnection(ConnString);
conn.Open();
trans = conn.BeginTransaction();
SQLiteCommand comm = new SQLiteCommand(conn);
comm.CommandText = sSQL;
iResult = comm.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
sError = ex.Message;
iResult = -1;
trans.Rollback();
}
}
　　return iResult >0;
}
　　}
}
　　(2) 新建一个 frmSqlLite 的 form
　　public partial class frmSqlLite : Form
{
string sError = string.Empty;
public frmSqlLite()
{
InitializeComponent();
}
　　private void InitGrid()
{
SqlLiteHelper.ConnSqlLiteDbPath = @"F:\jonse\DownLoads\sqlLite\myDB.db";
　　sError = string.Empty;
string sSql = "select * from test";
DataTable dt = SqlLiteHelper.GetDataTable(out sError, sSql);
if (!string.IsNullOrEmpty(sError))
Common.DisplayMsg(this.Text, sError);
　　dataGridView1.DataSource = dt;
}
　　private void frmSqlLite_Load(object sender, EventArgs e)
{
InitGrid();
}
　　private void button1_Click(object sender, EventArgs e)
{
sError=string.Empty;
int iMaxID = SqlLiteHelper.GetMaxID(out sError, "id", "test") + 1;
string sSql = "insert into test select " + iMaxID + ",'name" + iMaxID + "','remark" +
　　iMaxID + "'";
sError=string.Empty;
bool bResult = SqlLiteHelper.UpdateData(out sError, sSql,true);
if (bResult)
Common.DisplayMsg(this.Text, "插入成功");
　　InitGrid();
}
　　private void button2_Click(object sender, EventArgs e)
{
sError = string.Empty;
int iMaxID = SqlLiteHelper.GetMaxID(out sError, "id", "test");
string sSql = "update test set name='name_jonse',remark='remark_jonse' where id=" +
　　iMaxID;
sError = string.Empty;
bool bResult = SqlLiteHelper.UpdateData(out sError, sSql, true);
if (bResult)
Common.DisplayMsg(this.Text, "修改成功");
　　InitGrid();
}
　　private void button3_Click(object sender, EventArgs e)
{
sError = string.Empty;
int iMaxID = SqlLiteHelper.GetMaxID(out sError, "id", "test");
string sSql = "delete from test where id=" + iMaxID;
sError = string.Empty;
bool bResult = SqlLiteHelper.UpdateData(out sError, sSql, true);
if (bResult)
Common.DisplayMsg(this.Text, "删除成功");
　　InitGrid();
}
}
　　(3). 公共类
　　public abstract class Common
{
public static void DisplayMsg(string sCaption, string sMsg)
{
sMsg = sMsg.TrimEnd('!').TrimEnd('！') + " !";
MessageBox.Show(sMsg, sCaption);
}
　　public static bool IsNullOrEmptyObject(object oSource)
{
if (oSource != null)
{
return string.IsNullOrEmpty(oSource.ToString());
}
　　return true;
}
　　}
　　http://hi.csdn.net/attachment/201110/4/0_1317741653EH5Q.gif
　　http://hi.csdn.net/attachment/201110/4/0_1317742503BHoh.gif