/// <summary> /// Scalar获取数据 /// </summary> /// <param></param> /// <param></param> /// <returns></returns> public static object ExecuteScalar(string sql, object param) { using (SqlConnection con = new SqlConnection(connectionString)) { return con.ExecuteScalar(sql, param); } }
SqlSugar获取首行首列
/// <summary> /// 获取首行首列 /// </summary> /// <param>查询的sql</param> /// <param>替换参数</param> /// <returns></returns> public static object ExecuteScalar(string sql, object param) { using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig)) { return con.Ado.GetScalar(sql, param); } }
6、获取存储进程
Dapper获取存储进程
/// <summary> /// 带参数的存储进程 /// </summary> /// <param></param> /// <param></param> /// <returns></returns> public static List<T> ExecutePro(string proc, object param) { using (SqlConnection con = new SqlConnection(connectionString)) { List<T> list = con.Query<T>(proc, param, null, true, null, CommandType.StoredProcedure).ToList(); return list; } }
SqlSugar获取存储进程
/// <summary> /// 获取存储进程 /// </summary> /// <param>查询的sql</param> /// <param>替换参数</param> /// <returns></returns> public static object ExecutePro<T>(string proc, object param) 8 { using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig)) { return con.Ado.UseStoredProcedure().SqlQuery<T>(proc, param);12 } }
7、事务执行多个Sql
Dapper事务执行多个Sql
/// <summary> /// 事务1 - 全SQL /// </summary> /// <param>多条SQL</param> /// <param>param</param> /// <returns></returns> public static int ExecuteTransaction(string[] sqlarr) { using (SqlConnection con = new SqlConnection(connectionString)) { using (var transaction = con.BeginTransaction()) { try { int result = 0; foreach (var sql in sqlarr) { result += con.Execute(sql, null, transaction); } transaction.Commit(); return result; } catch (Exception ex) { transaction.Rollback(); return 0; } } } }
Sugar事务执行多个Sql
/// <summary> /// 事务1 - 全SQL /// </summary> /// <param>多条SQL</param> /// <param>param</param> /// <returns></returns> public static int ExecuteTransaction(string[] sqlarr) { using (SqlSugarClient con = new SqlSugarClient(connectionConfig)) { con.Ado.BeginTran(); using (var transaction = con.Ado.Transaction) { try { int result = 0; foreach (var sql in sqlarr) { result += con.Ado.ExecuteCommand(sql); } transaction.Commit(); return result; } catch (Exception ex) { transaction.Rollback(); return 0; } } } }
SqlSugar参数详解
SqlSugar同样支持游标 、Output、 ReturnValue
var nameP=new SugarParameter("@name", "张三", typeof(string),ParameterDirection.ReturnValue);
移植成SqlSugar ORM有哪些利益
同样是写Sql可是SqlSugar提供了AOP、机能监控、返回多个功效集、SQL分页、一对多、一对一操纵、JSON范例 等
1、SqlSugar支持AOP等事件操纵
db.Aop.OnLogExecuted = (sql, pars) => //SQL执行完 { Console.Write("time:" + db.Ado.SqlExecutionTime.ToString());//输出SQL执行时间 }; db.Aop.OnLogExecuting = (sql, pars) => //SQL执行前 { }; db.Aop.OnError = (exp) =>//SQL报错 { //exp.sql 这样可以拿到错误SQL }; db.Aop.OnExecutingChangeSql = (sql, pars) => //可以修改SQL和参数的值 { return new KeyValuePair<string, SugarParameter[]>(sql,pars); };
除了这些事件外 SqlSugar还支持了机能监控，对付机能差的Sql可能存储进程都能很好的跟踪
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.SqlServer, ConnectionString = Config.ConnectionString, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true }); db.Aop.OnLogExecuted = (sql, p) => { //执行时间高出1秒 if (db.Ado.SqlExecutionTime.TotalSeconds > 1) { //代码CS文件名 var fileName= db.Ado.SqlStackTrace.FirstFileName; //代码行数 var fileLine = db.Ado.SqlStackTrace.FirstLine; //要领名 var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName; //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层要领的信息 } };
因为内容较量多一下子写不完 ,没存眷的点一波存眷
2、Json范例的查询
public class UnitJsonTest { [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int Id { get; set; } [IsJson = true)]//个中IsJson是必填 public Order Order { get; set; } public string Name{get;set;} } var list = Db.Ado.SqlQuery<UnitJsonTest>().ToList();//数据库是一个json字符串
3、Sql分页
var list= db.SqlQueryable<Student>("select * from student").Where("id=@id",new { id=1}).ToPageList(1, 2);