C#调用本地sqllite文件封装工具类
namespace Test.Utils
{
public class SQLiteHelper
{
private SQLiteConnection _connection;
private static readonly Lazy<SQLiteHelper> lazy =
new Lazy<SQLiteHelper>(() => new SQLiteHelper());
public static SQLiteHelper Instance { get { return lazy.Value; } }
public SQLiteHelper()
{
string currentDirectory = Directory.GetCurrentDirectory();
string relativePath = "DB\\cabinet.db";
string path = Path.Combine(currentDirectory, relativePath);
// string path = "C:\\CabinetData\\cabinet.db";
_connection = new SQLiteConnection($"Data Source={path};Version=3;BinaryGUID=False;");
}
public SQLiteConnection GetConnection()
{
return _connection;
}
public void OpenConnection()
{
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
}
}
public void CloseConnection()
{
if (_connection.State != ConnectionState.Closed)
{
_connection.Close();
}
}
public List<Dictionary<string, object>> query(string sql)
{
List<Dictionary<string, object>> result = new List<Dictionary<string, object>>();
this.OpenConnection();
SQLiteCommand command = new SQLiteCommand(sql, this._connection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Dictionary<string, object> kv = new Dictionary<string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
kv[reader.GetName(i)] = reader.GetValue(i);
}
result.Add(kv);
}
//command.Dispose();
//this._connection.Dispose();
return result;
}
// 根据ID查询数据的方法
public T GetById<T>(string tableName, string idColumnName, Int64 id, Func<DbDataReader, T> map)
{
this.OpenConnection();
using (var cmd = _connection.CreateCommand())
{
cmd.CommandText = $"SELECT * FROM {tableName} WHERE {idColumnName} = @id";
cmd.Parameters.AddWithValue("@id", id);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return map(reader);
}
}
}
return default(T);
}
// 根据Name查询数据的方法
public T GetByName<T>(string tableName, string idColumnName, string name, Func<DbDataReader, T> map, Boolean hasDeleteColumn)
{
this.OpenConnection();
using (var cmd = _connection.CreateCommand())
{
if (hasDeleteColumn)
{
cmd.CommandText = $"SELECT * FROM {tableName} WHERE {idColumnName} = @name and is_deleted=0";
}
else
{
cmd.CommandText = $"SELECT * FROM {tableName} WHERE {idColumnName} = @name";
}
cmd.Parameters.AddWithValue("@name", name);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return map(reader);
}
}
}
return default(T);
}
/// <summary>
/// 执行插入操作
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="key_values">键值对字典</param>
/// <returns>int</returns>
public int Insert(string tableName, List<SQLiteParameter> parameters)
{
this.OpenConnection();
using (var cmd = _connection.CreateCommand())
{
// 添加参数到命令对象
cmd.Parameters.AddRange(parameters.ToArray());
// 构建INSERT语句的列名部分和值部分
string columnNames = string.Join(",", parameters.Select(p => p.ParameterName));
string placeholders = string.Join(",", parameters.Select(p => "@" + p.ParameterName));
// 构建完整的INSERT语句
string query = $"INSERT INTO {tableName} ({columnNames}) VALUES ({placeholders});";
cmd.CommandText = query;
// 执行命令并返回受影响的行数
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 执行插入操作
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="key_values">键值对字典</param>
/// <returns>int</returns>
public int Insert(string tableName, Dictionary<string, object> key_values)
{
using (var cmd = _connection.CreateCommand())
{
List<string> columns = new List<string>();
List<SQLiteParameter> parameters = new List<SQLiteParameter>();
int index = 0;
foreach (var kvp in key_values)
{
columns.Add(kvp.Key);
parameters.Add(new SQLiteParameter($"{kvp.Key}", kvp.Value));
cmd.Parameters.Add(parameters[index]);
index++;
}
string query = $"INSERT INTO {tableName} ({string.Join(",", columns)}) VALUES ({string.Join(",", parameters.Select(p => "@" + p.ParameterName))});";
cmd.CommandText = query;
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 执行更新操作
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="setValues">新数据</param>
/// <param name="whereClause">条件</param>
/// <param name="parameters">条件数据</param>
/// <returns>int</returns>
public int Update(string tableName, Dictionary<string, object> setValues, string whereClause, List<SQLiteParameter> parameters)
{
using (var cmd = _connection.CreateCommand())
{
List<string> setColumns = new List<string>();
int index = 0;
foreach (var kvp in setValues)
{
setColumns.Add($"{kvp.Key} = @{kvp.Key}");
cmd.Parameters.Add(new SQLiteParameter($"@{kvp.Key}", kvp.Value));
index++;
}
string query = $"UPDATE {tableName} SET {string.Join(",", setColumns)} WHERE {whereClause}";
cmd.CommandText = query;
cmd.Parameters.AddRange(parameters.ToArray());
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 执行删除操作
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="whereClause">条件</param>
/// <param name="parameters">参数数据</param>
/// <returns>int</returns>
public int Delete(string tableName, string whereClause, List<SQLiteParameter> parameters)
{
try
{
//this.OpenConnection();
//if (_connection.State != ConnectionState.Open)
//{
// Console.WriteLine();
//}
using (var cmd = _connection.CreateCommand())
{
if (tableName == null)
{
throw new ArgumentNullException("table=null");
}
// 在使用 _connection 之前确保其状态正确
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
}
cmd.CommandText = $"DELETE FROM {tableName} WHERE {whereClause};";
cmd.Parameters.AddRange(parameters.ToArray());
return cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception occurred: {ex.Message}");
return 0;
// 可以根据具体情况处理异常
}
}
/// <summary>
/// 执行条件查询操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数数据</param>
/// <returns>DataTable</returns>
public DataTable Select(string sql, List<SQLiteParameter> parameters)
{
DataTable dt = new DataTable();
// 在使用 _connection 之前确保其状态正确
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
}
using (var cmd = _connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters.ToArray());
using (var reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
}
return dt;
}
/// <summary>
/// 执行自定义SQL语句，返回影响行数
/// </summary>
/// <param name="sql"></param>
/// <returns>int类型</returns>
public int ExecuteSQL(string sql)
{
using (var cmd = _connection.CreateCommand())
{
cmd.CommandText = sql;
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 执行自定义SQL语句，返回影响行数
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns>int类型</returns>
public int ExecuteSQL(string sql, List<SQLiteParameter> parameters)
{
using (var cmd = _connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters.ToArray());
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 执行自定义sql查询语句,如果你计划对返回的 DataTable 进行大量的后续操作（例如，添加或删除行，修改列值等），那么使用 SQLiteDataAdapter 可能会更有优势，因为它提供了更高级的数据绑定和更新功能
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns>DataTable</returns>
public DataTable ExecuteSelect(string sql, List<SQLiteParameter> parameters)
{
using (SQLiteCommand command = _connection.CreateCommand())
{
command.CommandText = sql;
command.Parameters.AddRange(parameters.ToArray());
using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command))
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable); // 填充数据表
return dataTable; // 返回查询结果的数据表
}
}
}
/// <summary>
/// 批量操作
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int BatchExecuteSQL(string sql, List<List<SQLiteParameter>> parameters)
{
int affectedRows = 0;
using (var transaction = _connection.BeginTransaction())
{
try
{
using (var cmd = _connection.CreateCommand())
{
cmd.Transaction = transaction;
foreach (var paramList in parameters)
{
cmd.CommandText = sql;
cmd.Parameters.Clear();
cmd.Parameters.AddRange(paramList.ToArray());
affectedRows += cmd.ExecuteNonQuery();
}
transaction.Commit(); // 提交事务
}
}
catch (Exception ex)
{
transaction.Rollback(); // 发生异常时回滚事务
throw; // 重新抛出异常，以便上层调用者处理
}
}
return affectedRows;
}
}
}举个例子展示具体调用方式
首先实体类：
namespace Test.Model
{
[Table(Name = "agency")]
public class Agency
{
public int Id { get; set; }
public string Name { get; set; }
public string Property { get; set; }
public string Address { get; set; }
public override string ToString()
{
return $"Name: {Name}, Property: {Property}, Address: {Address}";
}
}
}封装了一个service方法（我之前是java开发，习惯了）：
namespace Test.Service
{
public class AgencyService
{
private static SQLiteHelper helper = SQLiteHelper.Instance;
public static List<Agency> selectAgencyID(Agency agency)
{
List<Agency> entities = new List<Agency>();
String sql = "SELECT * FROM agency WHERE name = @Name AND property = @Property AND address = @Address";
//where条件
//where条件中对应的参数
List<SQLiteParameter> parameter = new List<SQLiteParameter>
{
new SQLiteParameter("@Name", agency.Name),
new SQLiteParameter("@Property", agency.Property),
new SQLiteParameter("@Address", agency.Address)
};
DataTable dataTable = helper.Select(sql, parameter);
entities = dataTable.AsEnumerable().Select(data => new Agency
{
Id = Convert.ToInt32(data["id"]),
Name = (string)data["name"],
Property = (string)data["property"],
Address = (string)data["address"]
}).ToList();
return entities;
}
public static int insertAgency(Agency agency)
{
try
{
// 打开数据库连接
helper.OpenConnection();
// 插入数据示例
Dictionary<string, object> valuesToInsert = new Dictionary<string, object>
{
{ "username", agency.Name },
{ "property", agency.Property },
{ "address", agency.Address }
};
int insert_count = helper.Insert("agency1111", valuesToInsert);
Console.WriteLine(insert_count);
return insert_count;
}
catch (Exception ex)
{
LoggerFactory.GetLog().Error(string.Format("An exception occurred during data saving and has been captured.{0}", ex.Message));
TipDialogUtils.TipDialogPromt(ErrorCodes.DeviceError + ":" + ResourceResxManager.Instance.GetString("NewOrganizationFailure"));
}
return 0;
}
}
}
扫一扫
6万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
