Winform [NetFramework 4.6]项目使用SugarSql连接Sqlite数据库读写操作 简单示例

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());
 }