正文
sqlsugar入门(1)-初识sugar正确打开sugar的方式
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
1、实例化DB
public static SqlSugarClient GetDB(string s)
{
var ssc = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = s, //必填
DbType = SqlSugar.DbType.SqlServer, //必填
IsAutoCloseConnection = true
});
ssc.SetExpMethods();
ssc.SetEvents();
return ssc;
}
2、自定义扩展方法
public static void SetExpMethods(this SqlSugarClient ssc)
{
var expMethods = new List<SqlFuncExternal>();
expMethods.Add(new SqlFuncExternal()
{
UniqueMethodName = "CastToFloat",
MethodValue = (expInfo, dbType, expContext) =>
{
if (dbType == DbType.SqlServer)
return string.Format("CAST({0} AS float)", expInfo.Args[0].MemberName);
else
throw new Exception("未实现");
}
});
ssc.CurrentConnectionConfig.ConfigureExternalServices = new ConfigureExternalServices()
{
SqlFuncServices = expMethods //set ext method
};
}
3、AOP输出错误的sql
public static void SetEvents(this SqlSugarClient ssc)
{
ssc.Aop.OnError = error =>
{
Regex reg = new Regex(@"[^0-9]+");
var sql = error.Sql;
var pars = error.Parametres as SugarParameter[];
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
};
}
4、输出sql
public static string ToSqlString<T>(this ISugarQueryable<T> ISugarQueryable)
{
var tosql = ISugarQueryable.Clone().ToSql();
Regex reg = new Regex(@"[^0-9]+");
var sql = tosql.Key;
var pars = tosql.Value;
if (pars == null)
{
return sql;
}
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
return sql;
}
public static string ToSqlString<T>(this IUpdateable<T> IUpdateable) where T : class, new()
{
var tosql = IUpdateable.ToSql();
Regex reg = new Regex(@"[^0-9]+");
var sql = tosql.Key;
var pars = tosql.Value;
if (pars == null)
{
return sql;
}
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
return sql;
}
public static string ToSqlString<T>(this IDeleteable<T> IDeleteable) where T : class, new()
{
var tosql = IDeleteable.ToSql();
Regex reg = new Regex(@"[^0-9]+");
var sql = tosql.Key;
var pars = tosql.Value;
if (pars == null)
{
return sql;
}
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
return sql;
}
public static string ToSqlString<T>(this IInsertable<T> IInsertable) where T : class, new()
{
var tosql = IInsertable.ToSql();
Regex reg = new Regex(@"[^0-9]+");
var sql = tosql.Key;
var pars = tosql.Value;
if (pars == null)
{
return sql;
}
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
return sql;
}
5、Demo地址：https://gitee.com/xuanyun2018/sqlsugardemo.git
public static SqlSugarClient GetDB(string s)
{
var ssc = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = s, //必填
DbType = SqlSugar.DbType.SqlServer, //必填
IsAutoCloseConnection = true
});
ssc.SetExpMethods();
ssc.SetEvents();
return ssc;
}
public static void SetExpMethods(this SqlSugarClient ssc)
{
var expMethods = new List<SqlFuncExternal>();
expMethods.Add(new SqlFuncExternal()
{
UniqueMethodName = "CastToFloat",
MethodValue = (expInfo, dbType, expContext) =>
{
if (dbType == DbType.SqlServer)
return string.Format("CAST({0} AS float)", expInfo.Args[0].MemberName);
else
throw new Exception("未实现");
}
});
ssc.CurrentConnectionConfig.ConfigureExternalServices = new ConfigureExternalServices()
{
SqlFuncServices = expMethods //set ext method
};
}
3、AOP输出错误的sql
public static void SetEvents(this SqlSugarClient ssc)
{
ssc.Aop.OnError = error =>
{
Regex reg = new Regex(@"[^0-9]+");
var sql = error.Sql;
var pars = error.Parametres as SugarParameter[];
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
};
}
4、输出sql
public static string ToSqlString<T>(this ISugarQueryable<T> ISugarQueryable)
{
var tosql = ISugarQueryable.Clone().ToSql();
Regex reg = new Regex(@"[^0-9]+");
var sql = tosql.Key;
var pars = tosql.Value;
if (pars == null)
{
return sql;
}
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
return sql;
}
public static string ToSqlString<T>(this IUpdateable<T> IUpdateable) where T : class, new()
{
var tosql = IUpdateable.ToSql();
Regex reg = new Regex(@"[^0-9]+");
var sql = tosql.Key;
var pars = tosql.Value;
if (pars == null)
{
return sql;
}
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
return sql;
}
public static string ToSqlString<T>(this IDeleteable<T> IDeleteable) where T : class, new()
{
var tosql = IDeleteable.ToSql();
Regex reg = new Regex(@"[^0-9]+");
var sql = tosql.Key;
var pars = tosql.Value;
if (pars == null)
{
return sql;
}
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
return sql;
}
public static string ToSqlString<T>(this IInsertable<T> IInsertable) where T : class, new()
{
var tosql = IInsertable.ToSql();
Regex reg = new Regex(@"[^0-9]+");
var sql = tosql.Key;
var pars = tosql.Value;
if (pars == null)
{
return sql;
}
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
return sql;
}
5、Demo地址：https://gitee.com/xuanyun2018/sqlsugardemo.git
public static void SetEvents(this SqlSugarClient ssc)
{
ssc.Aop.OnError = error =>
{
Regex reg = new Regex(@"[^0-9]+");
var sql = error.Sql;
var pars = error.Parametres as SugarParameter[];
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value))); }; }
public static string ToSqlString<T>(this ISugarQueryable<T> ISugarQueryable)
{
var tosql = ISugarQueryable.Clone().ToSql();
Regex reg = new Regex(@"[^0-9]+");
var sql = tosql.Key;
var pars = tosql.Value;
if (pars == null)
{
return sql;
}
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
return sql;
} public static string ToSqlString<T>(this IUpdateable<T> IUpdateable) where T : class, new()
{
var tosql = IUpdateable.ToSql();
Regex reg = new Regex(@"[^0-9]+");
var sql = tosql.Key;
var pars = tosql.Value;
if (pars == null)
{
return sql;
}
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
return sql;
} public static string ToSqlString<T>(this IDeleteable<T> IDeleteable) where T : class, new()
{
var tosql = IDeleteable.ToSql();
Regex reg = new Regex(@"[^0-9]+");
var sql = tosql.Key;
var pars = tosql.Value;
if (pars == null)
{
return sql;
}
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
return sql;
} public static string ToSqlString<T>(this IInsertable<T> IInsertable) where T : class, new()
{
var tosql = IInsertable.ToSql();
Regex reg = new Regex(@"[^0-9]+");
var sql = tosql.Key;
var pars = tosql.Value;
if (pars == null)
{
return sql;
}
sql = pars.OrderByDescending(o => reg.Replace(o.ParameterName, "").ObjToInt()).ThenByDescending(o => o.ParameterName.Length).Aggregate(sql, (current, p) => current.Replace(p.ParameterName, (p.DbType == System.Data.DbType.Decimal ||
p.DbType == System.Data.DbType.Double ||
p.DbType == System.Data.DbType.Int16 ||
p.DbType == System.Data.DbType.Int32 ||
p.DbType == System.Data.DbType.Int64 ||
p.DbType == System.Data.DbType.Single ||
p.DbType == System.Data.DbType.VarNumeric ||
p.DbType == System.Data.DbType.UInt16 ||
p.DbType == System.Data.DbType.UInt32 ||
p.DbType == System.Data.DbType.UInt64
) ? (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("{0}", p.Value) : (p.Value == null || p.Value is System.DBNull) ? "null" : string.Format("'{0}'", p.Value)));
return sql;
}
