using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq.Expressions;
namespace Demos.Helper
{
public class SqlSugarHelper
{
private readonly SqlSugarClient _db;
public SqlSugarHelper(string connectionString)
{
_db = new SqlSugarClient(new ConnectionConfig
{
ConnectionString = connectionString,
DbType = DbType.SqlServer, // 根据实际数据库类型修改
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute
});
}
/// <summary>
/// 事务处理多条执行语句
/// </summary>
/// <param name="action">事务操作委托</param>
/// <returns>事务是否执行成功</returns>
public bool UseTransaction(Action action)
{
try
{
_db.Ado.BeginTran();
action();
_db.Ado.CommitTran();
return true;
}
catch (Exception ex)
{
_db.Ado.RollbackTran();
Console.WriteLine($"事务执行出错: {ex.Message}");
return false;
}
}
/// <summary>
/// 插入单条记录
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="entity">实体对象</param>
/// <returns>插入是否成功</returns>
public bool Insert<T>(T entity) where T : class, new()
{
// 开启 IDENTITY_INSERT
//_db.Ado.ExecuteCommand("SET IDENTITY_INSERT Test ON");
return _db.Insertable(entity).ExecuteCommand() > 0;
// 关闭 IDENTITY_INSERT
//db.Ado.ExecuteCommand("SET IDENTITY_INSERT Test OFF");
}
/// <summary>
/// 插入多条记录
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="entities">实体对象集合</param>
/// <returns>插入成功的记录数</returns>
public int InsertRange<T>(List<T> entities) where T : class, new()
{
return _db.Insertable(entities).ExecuteCommand();
}
/// <summary>
/// 根据主键删除记录
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="id">主键值</param>
/// <returns>删除是否成功</returns>
public bool DeleteById<T>(object id) where T : class, new()
{
return _db.Deleteable<T>(id).ExecuteCommand() > 0;
}
/// <summary>
/// 根据条件删除记录
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="whereExpression">条件表达式</param>
/// <returns>删除成功的记录数</returns>
public int Delete<T>(Expression<Func<T, bool>> whereExpression) where T : class, new()
{
return _db.Deleteable<T>().Where(whereExpression).ExecuteCommand();
}
/// <summary>
/// 更新单条记录
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="entity">实体对象</param>
/// <returns>更新是否成功</returns>
public bool Update<T>(T entity) where T : class, new()
{
return _db.Updateable(entity).ExecuteCommand() > 0;
}
/// <summary>
/// 根据条件更新记录
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="columns">要更新的列表达式</param>
/// <param name="whereExpression">条件表达式</param>
/// <returns>更新成功的记录数</returns>
public int Update<T>(Expression<Func<T, T>> columns, Expression<Func<T, bool>> whereExpression)
where T : class, new()
{
return _db.Updateable<T>().SetColumns(columns).Where(whereExpression).ExecuteCommand();
}
/// <summary>
/// 根据主键查询单条记录
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="id">主键值</param>
/// <returns>实体对象</returns>
public T GetById<T>(object id) where T : class, new()
{
return _db.Queryable<T>().InSingle(id);
}
/// <summary>
/// 根据条件查询单条记录
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="whereExpression">条件表达式</param>
/// <returns>实体对象</returns>
public T GetSingle<T>(Expression<Func<T, bool>> whereExpression) where T : class, new()
{
return _db.Queryable<T>().Single(whereExpression);
}
/// <summary>
/// 根据条件查询多条记录
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="whereExpression">条件表达式</param>
/// <returns>实体对象集合</returns>
public List<T> GetList<T>(Expression<Func<T, bool>> whereExpression) where T : class, new()
{
return _db.Queryable<T>().Where(whereExpression).ToList();
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="whereExpression">条件表达式</param>
/// <param name="totalCount">总记录数</param>
/// <returns>实体对象集合</returns>
public List<T> GetPageList<T>(int pageIndex, int pageSize,
Expression<Func<T, bool>> whereExpression, ref int totalCount) where T : class, new()
{
return _db.Queryable<T>().Where(whereExpression).ToPageList(pageIndex, pageSize, ref totalCount);
}
}
//class Program
//{
// static void Main()
// {
// string connectionString = "YourConnectionString";
// SqlSugarHelper helper = new SqlSugarHelper(connectionString);
// // 示例插入操作
// var entity = new YourEntity { /* 初始化属性 */ };
// bool insertResult = helper.Insert(entity);
// // 示例查询操作
// var list = helper.GetList<YourEntity>(x => x.SomeProperty == "SomeValue");
// }
//}
//class Program
//{
// static void Main()
// {
// string connectionString = "YourConnectionString";
// SqlSugarHelper helper = new SqlSugarHelper(connectionString);
// bool transactionResult = helper.UseTransaction(() =>
// {
// // 示例插入操作
// var entity1 = new YourEntity { /* 初始化属性 */ };
// helper.Insert(entity1);
// // 示例更新操作
// var entity2 = helper.GetById<YourEntity>(1);
// if (entity2 != null)
// {
// // 修改实体属性
// helper.Update(entity2);
// }
// });
// if (transactionResult)
// {
// Console.WriteLine("事务执行成功");
// }
// else
// {
// Console.WriteLine("事务执行失败");
// }
// }
//}
}
//// 定义要更新的列表达式
//Expression<Func<YourEntity, YourEntity>> columns = entity => new YourEntity
//{
// Name = "UpdatedName",
// Age = 25
//};
//// 动态构建条件表达式
//var parameter = Expression.Parameter(typeof(YourEntity), "entity");
//// 第一个条件：Age < 20
//var ageCondition = Expression.LessThan(Expression.Property(parameter, "Age"), Expression.Constant(20));
//// 第二个条件：City == "New York"
//var cityCondition = Expression.Equal(Expression.Property(parameter, "City"), Expression.Constant("New York"));
//// 组合条件，这里使用逻辑与（&&）
//var combinedCondition = Expression.AndAlso(ageCondition, cityCondition);
//// 构建最终的 Lambda 表达式
//var whereExpression = Expression.Lambda<Func<YourEntity, bool>>(combinedCondition, parameter);
//// 调用 Update 方法
//int updatedCount = helper.Update(columns, whereExpression);
//if (updatedCount > 0)
//{
// Console.WriteLine($"成功更新 {updatedCount} 条记录。");
//}
//else
//{
// Console.WriteLine("没有记录被更新。");
//}
调用示例
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Demos.Model
{
public class Test
{
/// <summary>
/// Desc:-
/// Default:-
/// Nullable:False
/// </summary>
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int id { get; set; }
/// <summary>
/// Desc:-
/// Default:-
/// Nullable:True
/// </summary>
public string name { get; set; }
/// <summary>
/// Desc:-
/// Default:-
/// Nullable:True
/// </summary>
public string nickname { get; set; }
/// <summary>
/// Desc:-
/// Default:-
/// Nullable:True
/// </summary>
public string age { get; set; }
}
}
using Demos.Helper;
using Demos.Model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Demos
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
Control.CheckForIllegalCrossThreadCalls = false;
}
string connStr = ConfigurationManager.AppSettings["connStr"].ToString();
private void Form1_Load(object sender, EventArgs e)
{
SqlSugarHelper helper = new SqlSugarHelper(connStr);
// 示例查询操作
List<Test> list = helper.GetList<Test>(x => x.age == x.age);
StringBuilder value = new StringBuilder();
foreach (var item in list)
{
value.AppendLine(item.id + "," + item.name + "," + item.nickname + "," + item.age);
}
this.txtValue.Text = value.ToString();
}
private void btnAdd_Click(object sender, EventArgs e)
{
Test test = new Test();
//test.id = 4;
test.name = "测试 name";
test.nickname = "测试 nickname";
test.age = "18";
SqlSugarHelper helper = new SqlSugarHelper(connStr);
bool insertResult = helper.Insert(test);
this.btnQuery.PerformClick();
MessageBox.Show("添加==>" + insertResult);
}
private void btnDelete_Click(object sender, EventArgs e)
{
SqlSugarHelper helper = new SqlSugarHelper(connStr);
int insertResult = helper.Delete<Test>(o=>o.id== 4);
this.btnQuery.PerformClick();
MessageBox.Show("删除==>" + insertResult);
}
private void btndelbyid_Click(object sender, EventArgs e)
{
Test test = new Test();
test.name = "测试 name";
test.nickname = "测试 nickname";
test.age = "20";
SqlSugarHelper helper = new SqlSugarHelper(connStr);
var insertResult = helper.DeleteById<Test>(1);
this.btnQuery.PerformClick();
}
private void btnUpdate_Click(object sender, EventArgs e)
{
// 定义要更新的列表达式
Expression<Func<Test, Test>> columns = entity => new Test
{
name = "UpdatedName",
age = "19"
};
// 定义更新条件表达式
Expression<Func<Test, bool>> whereExpression = entity => entity.id == 4;
SqlSugarHelper helper = new SqlSugarHelper(connStr);
int insertResult = helper.Update(columns, whereExpression);
this.btnQuery.PerformClick();
MessageBox.Show("修改==>" + insertResult);
}
private void btnUpdate2_Click(object sender, EventArgs e)
{
SqlSugarHelper helper = new SqlSugarHelper(connStr);
//Test test = new Test();
//test.id = 4;
//test.name = "测试 name";
//test.nickname = "测试 nickname";
//test.age = "20";
Test aa= helper.GetById<Test>(4);
aa.name = "110";
//aa.id = 4;
var bb = helper.Update(aa);
this.btnQuery.PerformClick();
}
private void btnQuery_Click(object sender, EventArgs e)
{
SqlSugarHelper helper = new SqlSugarHelper(connStr);
// 示例查询操作
List<Test> list = helper.GetList<Test>(x => x.age == x.age);
StringBuilder value = new StringBuilder();
foreach (var item in list)
{
value.AppendLine(item.id + "," + item.name + "," + item.nickname + "," + item.age);
}
this.txtValue.Text = value.ToString();
}
}
}
App.config
1 <?xml version="1.0" encoding="utf-8"?> 2 <configuration> 3 <startup> 4 <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" /> 5 </startup> 6 <appSettings> 7 <!--数据库连接--> 8 <add key="connStr" value="server=.;uid=sa;pwd=123456;database=SYPT_DB" /> 9 10 </appSettings> 11 <runtime> 12 <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> 13 <dependentAssembly> 14 <assemblyIdentity name="Newtonsoft.Json" publicKeyToken="30ad4fe6b2a6aeed" culture="neutral" /> 15 <bindingRedirect oldVersion="0.0.0.0-12.0.0.0" newVersion="12.0.0.0" /> 16 </dependentAssembly> 17 </assemblyBinding> 18 </runtime> 19 </configuration>
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。