最近有WPF做客户端，需要离线操作存储数据，在项目中考虑使用Sqlite嵌入式数据库，在网上找了不少资料，最终整理出一个公共的帮助类。
Sqlite是一个非常小巧的数据库，基本上具备关系型数据库操作的大多数功能，Sql语法也大同小异。下面是我整理的帮助类代码：
1.获取 SQLiteConnection 对象，传入数据库有地址即可。
/// <summary>
/// 获得连接对象
/// </summary>
/// <returns>SQLiteConnection</returns>
public static SQLiteConnection GetSQLiteConnection()
{
//Sqlite数据库地址
string str = AppDomain.CurrentDomain.BaseDirectory;
var con = new SQLiteConnection("Data Source=" + str + "DataBass\\InfoServiceDbB.db");
return con;
}
2.准备操作命令参数，构造SQLiteCommand 对象：
/// <summary>
/// 准备操作命令参数
/// </summary>
/// <param name="cmd">SQLiteCommand</param>
/// <param name="conn">SQLiteConnection</param>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">参数数组</param>
private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, Dictionary<String, String> data)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Parameters.Clear();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = ;
if (data!=null&&data.Count >= )
{
foreach (KeyValuePair<String, String> val in data)
{
cmd.Parameters.AddWithValue(val.Key, val.Value);
}
}
}
3.查询，返回DataSet
/// <summary>
/// 查询，返回DataSet
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">参数数组</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataset(string cmdText, Dictionary<string, string> data)
{
var ds = new DataSet();
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
var da = new SQLiteDataAdapter(command);
da.Fill(ds);
}
return ds;
}
4.查询，返回DataTable
/// <summary>
/// 查询，返回DataTable
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">参数数组</param>
/// <returns>DataTable</returns>
public static DataTable ExecuteDataTable(string cmdText, Dictionary<string, string> data)
{
var dt = new DataTable();
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
SQLiteDataReader reader = command.ExecuteReader();
dt.Load(reader);
}
return dt;
}
5.返回一行数据 DataRow
/// <summary>
/// 返回一行数据
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">参数数组</param>
/// <returns>DataRow</returns>
public static DataRow ExecuteDataRow(string cmdText, Dictionary<string, string> data)
{
DataSet ds = ExecuteDataset(cmdText, data);
if (ds != null && ds.Tables.Count > && ds.Tables[].Rows.Count > )
return ds.Tables[].Rows[];
return null;
}
6.执行数据库操作
/// <summary>
/// 执行数据库操作
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">传入的参数</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string cmdText, Dictionary<string, string> data)
{
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
return command.ExecuteNonQuery();
}
}
7.返回SqlDataReader对象
/// <summary>
/// 返回SqlDataReader对象
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">传入的参数</param>
/// <returns>SQLiteDataReader</returns>
public static SQLiteDataReader ExecuteReader(string cmdText, Dictionary<string, string> data)
{
var command = new SQLiteCommand();
SQLiteConnection connection = GetSQLiteConnection();
try
{
PrepareCommand(command, connection, cmdText, data);
SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch
{
connection.Close();
command.Dispose();
throw;
}
}
8.返回结果集中的第一行第一列，忽略其他行或列
/// <summary>
/// 返回结果集中的第一行第一列，忽略其他行或列
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">传入的参数</param>
/// <returns>object</returns>
public static object ExecuteScalar(string cmdText, Dictionary<string, string> data)
{
using (SQLiteConnection connection = GetSQLiteConnection())
{
var cmd = new SQLiteCommand();
PrepareCommand(cmd, connection, cmdText, data);
return cmd.ExecuteScalar();
}
}
9.分页查询
/// <summary>
/// 分页查询
/// </summary>
/// <param name="recordCount">总记录数</param>
/// <param name="pageIndex">页牵引</param>
/// <param name="pageSize">页大小</param>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="countText">查询总记录数的Sql文本</param>
/// <param name="data">命令参数</param>
/// <returns>DataSet</returns>
public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, Dictionary<string, string> data)
{
if (recordCount < )
recordCount = int.Parse(ExecuteScalar(countText, data).ToString());
var ds = new DataSet();
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
var da = new SQLiteDataAdapter(command);
da.Fill(ds, (pageIndex - ) * pageSize, pageSize, "result");
}
return ds;
}
10.重新组织数据库
当你从SQLite数据库中删除数据时， 未用的磁盘空间将会加入一个内部的“自由列表”中。
当你下次插入数据时，这部分空间可以重用。磁盘空间不会丢失， 但也不会返还给操作系统。
如果删除了大量数据，而又想缩小数据库文件占用的空间，执行 VACUUM 命令。 VACUUM 将会从头重新组织数据库 你可以在你的程序中约定一个时间间隔执行一次重新组织数据库的操作，节约空间
public void ResetDataBass()
{
using (SQLiteConnection conn = GetSQLiteConnection())
{
var cmd = new SQLiteCommand(); if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Parameters.Clear();
cmd.Connection = conn;
cmd.CommandText = "vacuum";
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = ;
cmd.ExecuteNonQuery();
}
}