C#操作SQLite数据库帮助类详解
作者:Only Version 时间:2024-01-22 03:52:37
本文实例讲述了C#操作SQLite数据库帮助类。分享给大家供大家参考,具体如下:
最近有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.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 > 0 && ds.Tables[0].Rows.Count > 0)
return ds.Tables[0].Rows[0];
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 < 0)
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 - 1) * 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 = 30;
cmd.ExecuteNonQuery();
}
}
希望本文所述对大家C#程序设计有所帮助。
标签:C#,SQLite
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Java基础开发之JDBC操作数据库增删改查,分页查询实例详解
2024-01-15 04:16:28
![](https://img.aspxhome.com/file/2023/3/71703_0s.png)
Sysbench对Mysql进行基准测试过程解析
2024-01-15 18:04:25
![](https://img.aspxhome.com/file/2023/8/124558_0s.png)
el-table嵌套el-popover处理卡顿的解决
2024-05-09 15:22:34
![](https://img.aspxhome.com/file/2023/6/126436_0s.jpg)
关于 Python opencv 使用中的 ValueError: too many values to unpack
2023-07-26 23:46:36
Python理解递归的方法总结
2022-06-10 03:31:08
python接口,继承,重载运算符详解
2023-03-08 05:51:38
![](https://img.aspxhome.com/file/2023/1/117851_0s.png)
返回首页的链接地址写法
2008-10-22 13:38:00
python 如何将office文件转换为PDF
2022-10-07 11:41:48
JS和JQuery实现雪花飘落效果
2024-04-18 09:51:42
![](https://img.aspxhome.com/file/2023/5/136155_0s.gif)
详解Python3 中hasattr()、getattr()、setattr()、delattr()函数及示例代码数
2023-08-21 15:36:40
简单易懂Pytorch实战实例VGG深度网络
2021-09-07 19:47:24
python中pop()函数的语法与实例
2023-12-26 13:05:31
v语言初体验小结
2022-09-22 01:02:51
![](https://img.aspxhome.com/file/2023/6/131846_0s.png)
中国,美国,英国3国时间同步动态显示js代码
2007-09-27 20:34:00
Python列表和集合的效率大比拼
2021-09-04 14:10:16
python日志模块logbook使用方法
2021-10-02 02:25:50
MySQL查询性能优化七种方式索引潜水
2024-01-20 01:11:35
![](https://img.aspxhome.com/file/2023/7/109787_0s.png)
Python中的sort()方法使用基础教程
2022-03-07 21:44:09
Spark SQL常见4种数据源详解
2024-01-18 09:07:21
Python使用pyecharts控件绘制图表
2023-11-08 17:59:54
![](https://img.aspxhome.com/file/2023/4/131184_0s.png)