1.定义Sqlite表
CREATE TABLE [TestTable](
[Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL DEFAULT 0,
[Name] NVARCHAR NOT NULL);
2.项目引入DLL包
SqlSugar
Newtonsoft.Json
System.Data.SQLite
3.SQLiteHelper
数据库放到Debug文件夹下,而不是放到项目解决方案下
using System.Collections.Generic;
using System;
using SqlSugar;
namespace YourNamespace
{
public class SQLiteHelper<T> where T : class, new()
{
private static SqlSugarClient _db;
static SQLiteHelper()
{
// 这里的连接字符串根据你的数据库实际路径进行修改
string connectionString = "Data Source=TestSL.db;Version=3;";
_db = new SqlSugarClient(new ConnectionConfig
{
ConnectionString = connectionString,
DbType = SqlSugar.DbType.Sqlite, // 指定数据库类型为 SQLite
IsAutoCloseConnection = true, // 自动关闭连接
InitKeyType = InitKeyType.Attribute // 根据特性初始化主键
});
}
// 获取 SqlSugarClient 实例
public static SqlSugarClient GetInstance()
{
return _db;
}
// 添加记录
public static bool Insert(T entity)
{
try
{
return _db.Insertable(entity).ExecuteCommand() > 0;
}
catch (Exception ex)
{
// 处理异常
Console.WriteLine($"Insert failed: {ex.Message}");
return false;
}
}
// 更新记录
public static bool Update(T entity)
{
try
{
return _db.Updateable(entity).ExecuteCommand() > 0;
}
catch (Exception ex)
{
// 处理异常
Console.WriteLine($"Update failed: {ex.Message}");
return false;
}
}
// 删除记录
public static bool Delete(object id)
{
try
{
return _db.Deleteable<T>(id).ExecuteCommand() > 0;
}
catch (Exception ex)
{
// 处理异常
Console.WriteLine($"Delete failed: {ex.Message}");
return false;
}
}
// 根据 ID 查询记录
public static T GetById(object id)
{
try
{
return _db.Queryable<T>().InSingle(id);
}
catch (Exception ex)
{
// 处理异常
Console.WriteLine($"GetById failed: {ex.Message}");
return null;
}
}
// 查询所有记录
public static List<T> GetAll()
{
try
{
return _db.Queryable<T>().ToList();
}
catch (Exception ex)
{
// 处理异常
Console.WriteLine($"GetAll failed: {ex.Message}");
return null;
}
}
}
}
4.创建Model
备忘:如果Id为自增长 加上[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
using SqlSugar;
namespace YourNamespace.Models
{
public class TestTable
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Id { get; set; } = -1;
public string Name { get; set; }
}
}
5.创建BLL
using RfidRWTest.Models;
using System.Collections.Generic;
namespace YourNamespace .BLL
{
public class TestTableBll
{
public List<TestTable> GetAll()
{
return SQLiteHelper<TestTable>.GetAll();
}
public bool AddRecord(TestTable info)
{
var entity = new TestTable
{
Name = info.Name
};
bool result = SQLiteHelper<TestTable>.Insert(entity);
return result;
}
}
}
6.调用
读取
TestTableBll testTableBll = new TestTableBll();
/// <summary>
/// 读取
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
var list=testTableBll.GetAll();
MessageBox.Show(list.Count.ToString());
}
写入
/// <summary>
/// 写入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
var entity = new TestTable { Name = "TEST VAULE" };
var result = testTableBll.AddRecord(entity);
MessageBox.Show(result.ToString());
}