最近有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 = 30; if (data!=null&&data.Count >= 1) { 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.E