/// <summary>
/// 根据主键批量删除数据
/// </summary>
public static int DeleteByIds <T>(this IDbConnection conn, object ids, IDbTransaction transaction = null, int?commandTimeout = null)
{
if (DapperExtCommon.ObjectIsEmpty(ids))
{
return(0);
}
DapperExtSqls sqls = GetDapperExtSqls(typeof(T));
if (sqls.HasKey)
{
DynamicParameters dpar = new DynamicParameters();
dpar.Add("@ids", ids);
return(conn.Execute(sqls.DeleteByIdsSql, dpar, transaction, commandTimeout));
}
else
{
throw new ArgumentException("表" + sqls.TableName + "没有主键，无法DeleteById。");
}
}
public static IEnumerable <dynamic> GetByInDynamic <T>(this IDbConnection conn, string field, object ids, string returnFields = null, IDbTransaction transaction = null, int?commandTimeout = null)
{
if (DapperExtCommon.ObjectIsEmpty(ids))
{
return(new List <dynamic>());
}
DapperExtSqls sqls = GetDapperExtSqls(typeof(T));
DynamicParameters dpar = new DynamicParameters();
dpar.Add("@ids", ids);
if (returnFields == null)
{
returnFields = sqls.AllFields;
}
string sql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}] IN @ids", returnFields, sqls.TableName, field);
return(conn.Query(sql, dpar, transaction, true, commandTimeout));
}
/// <summary>
/// 获取dynamic数据,使用skip 和take
/// </summary>
/// <returns></returns>
public static IEnumerable <dynamic> GetBySkipDynamic <T>(this IDbConnection conn, int skip, int take, string returnFields = null, string where = null, object param = null, string orderBy = null, IDbTransaction transaction = null, int?commandTimeout = null)
{
DapperExtSqls sqls = GetDapperExtSqls(typeof(T));
if (returnFields == null)
{
returnFields = sqls.AllFields;
}
if (orderBy == null)
{
if (sqls.HasKey)
{
orderBy = string.Format("ORDER BY [{0}] DESC", sqls.KeyName);
}
else
{
orderBy = string.Format("ORDER BY [{0}]", sqls.AllFieldList.First());
}
}
StringBuilder sb = new StringBuilder();
if (skip == 0) //第一页,使用Top语句
{
sb.AppendFormat("SELECT TOP ({0}) {1} FROM [{2}] WITH(NOLOCK) {3} {4}", take, returnFields, sqls.TableName, where, orderBy);
}
else //使用ROW_NUMBER()
{
sb.AppendFormat("WITH cte AS(SELECT ROW_NUMBER() OVER({0}) AS rownum,{1} FROM [{2}] WITH(NOLOCK) {3})", orderBy, returnFields, sqls.TableName, where);
if (returnFields.Contains(" AS") || returnFields.Contains(" as"))
{
sb.AppendFormat("SELECT * FROM cte WHERE cte.rownum BETWEEN {1} AND {2}", returnFields, skip + 1, skip + take);
}
else
{
sb.AppendFormat("SELECT {0} FROM cte WHERE cte.rownum BETWEEN {1} AND {2}", returnFields, skip + 1, skip + take);
}
}
return(conn.Query(sb.ToString(), param, transaction, true, commandTimeout));
}
public static int UpdateByIdBatch <T>(this IDbConnection conn, IEnumerable <T> entitys, string updateFields = null, IDbTransaction transaction = null, int?commandTimeout = null)
{
DapperExtSqls sqls = GetDapperExtSqls(typeof(T));
if (sqls.HasKey)
{
if (updateFields == null)
{
return(conn.Execute(sqls.UpdateByIdSql, entitys, transaction, commandTimeout));
}
else
{
string updateList = DapperExtCommon.GetFieldsEqStr(updateFields.Split(',').ToList(), "[", "]");
string sql = string.Format("UPDATE [{0}] SET {1} WHERE [{2}]=@{2}", sqls.TableName, updateList, sqls.KeyName);
return(conn.Execute(sql, entitys, transaction, commandTimeout));
}
}
else
{
throw new ArgumentException("表" + sqls.TableName + "没有主键，无法UpdateById。");
}
}
public static DapperExtSqls GetDapperExtSqls(Type t)
{
if (dapperExtsqlsDict.Keys.Contains(t.TypeHandle))
{
return(dapperExtsqlsDict[t.TypeHandle]);
}
else
{
DapperExtSqls sqls = DapperExtCommon.GetDapperExtSqls(t);
string Fields = DapperExtCommon.GetFieldsStr(sqls.AllFieldList, "[", "]");
string FieldsAt = DapperExtCommon.GetFieldsAtStr(sqls.AllFieldList);
string FieldsEq = DapperExtCommon.GetFieldsEqStr(sqls.AllFieldList, "[", "]");
string FieldsExtKey = DapperExtCommon.GetFieldsStr(sqls.ExceptKeyFieldList, "[", "]");
string FieldsAtExtKey = DapperExtCommon.GetFieldsAtStr(sqls.ExceptKeyFieldList);
string FieldsEqExtKey = DapperExtCommon.GetFieldsEqStr(sqls.ExceptKeyFieldList, "[", "]");
sqls.AllFields = Fields;
if (sqls.HasKey && sqls.IsIdentity) //有主键并且是自增
{
sqls.InsertSql = string.Format("INSERT INTO [{0}]({1})VALUES({2})", sqls.TableName, FieldsExtKey, FieldsAtExtKey);
sqls.InsertIdentitySql = string.Format("SET IDENTITY_INSERT [{0}] ON;INSERT INTO [{0}]({1})VALUES({2});SET IDENTITY_INSERT [{0}] OFF", sqls.TableName, Fields, FieldsAt);
}
else
{
sqls.InsertSql = string.Format("INSERT INTO [{0}]({1})VALUES({2})", sqls.TableName, Fields, FieldsAt);
}
if (sqls.HasKey) //含有主键
{
sqls.DeleteByIdSql = string.Format("DELETE FROM [{0}] WHERE [{1}]=@id", sqls.TableName, sqls.KeyName);
sqls.DeleteByIdsSql = string.Format("DELETE FROM [{0}] WHERE [{1}] IN @ids", sqls.TableName, sqls.KeyName);
sqls.GetByIdSql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}]=@id", Fields, sqls.TableName, sqls.KeyName);
sqls.GetByIdsSql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}] IN @ids", Fields, sqls.TableName, sqls.KeyName);
sqls.UpdateByIdSql = string.Format("UPDATE [{0}] SET {1} WHERE [{2}]=@{2}", sqls.TableName, FieldsEqExtKey, sqls.KeyName);
}
sqls.DeleteAllSql = string.Format("DELETE FROM [{0}]", sqls.TableName);
sqls.GetAllSql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK)", Fields, sqls.TableName);
dapperExtsqlsDict[t.TypeHandle] = sqls;
return(sqls);
}
}
public static dynamic Insert <T>(this IDbConnection conn, T entity, IDbTransaction transaction = null, int?commandTimeout = null)
{
DapperExtSqls sqls = GetDapperExtSqls(typeof(T));
if (sqls.HasKey && sqls.IsIdentity)
{
switch (sqls.KeyType)
{
case "Int32": return(conn.ExecuteScalar <int>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout)); //int
case "Int64": return(conn.ExecuteScalar <long>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout)); //long
case "Decimal": return(conn.ExecuteScalar <decimal>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout)); //decimal
case "UInt32": return(conn.ExecuteScalar <uint>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout)); //uint
case "UInt64": return(conn.ExecuteScalar <ulong>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout)); //ulong
case "Double": return(conn.ExecuteScalar <double>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout)); //double
case "Single": return(conn.ExecuteScalar <float>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout)); //float
case "Byte": return(conn.ExecuteScalar <byte>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout)); //byte
case "SByte": return(conn.ExecuteScalar <sbyte>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout)); //sbyte
case "Int16": return(conn.ExecuteScalar <short>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout)); //short
case "UInt16": return(conn.ExecuteScalar <ushort>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout)); //ushort
default: return(conn.ExecuteScalar <dynamic>(sqls.InsertSql + ";SELECT @@IDENTITY", entity, transaction, commandTimeout)); //dynamic
}
}
else
{
return(conn.Execute(sqls.InsertSql, entity, transaction, commandTimeout));
}
}
/// <summary>
/// 获取分页数据
/// </summary>
public static IEnumerable <dynamic> GetByPageDynamic <T>(this IDbConnection conn, int pageIndex, int pageSize, out long total, string returnFields = null, string where = null, object param = null, string orderBy = null, IDbTransaction transaction = null, int?commandTimeout = null)
{
DapperExtSqls sqls = GetDapperExtSqls(typeof(T));
if (returnFields == null)
{
returnFields = sqls.AllFields;
}
if (orderBy == null)
{
if (sqls.HasKey)
{
orderBy = string.Format("ORDER BY `{0}` DESC", sqls.KeyName);
}
else
{
orderBy = string.Format("ORDER BY `{0}`", sqls.AllFieldList.First());
}
}
int skip = 0;
if (pageIndex > 0)
{
skip = (pageIndex - 1) * pageSize;
}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT COUNT(1) FROM `{0}` {1};", sqls.TableName, where);
sb.AppendFormat("SELECT {0} FROM `{1}` {2} {3} LIMIT {4},{5}", returnFields, sqls.TableName, where, orderBy, skip, pageSize);
using (var reader = conn.QueryMultiple(sb.ToString(), param, transaction, commandTimeout))
{
total = reader.ReadFirst <long>();
return(reader.Read());
}
}
public static dynamic GetByIdDynamic <T>(this IDbConnection conn, dynamic id, string returnFields = null, IDbTransaction transaction = null, int?commandTimeout = null)
{
DapperExtSqls sqls = GetDapperExtSqls(typeof(T));
if (sqls.HasKey)
{
DynamicParameters dpar = new DynamicParameters();
dpar.Add("@id", id);
if (returnFields == null)
{
return(conn.QueryFirstOrDefault(sqls.GetByIdSql, dpar, transaction, commandTimeout));
}
else
{
string sql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}]=@id", returnFields, sqls.TableName, sqls.KeyName);
return(conn.QueryFirstOrDefault(sql, dpar, transaction, commandTimeout));
}
}
else
{
throw new ArgumentException("表" + sqls.TableName + "没有主键，无法GetByIdDynamic。");
}
}
public static IEnumerable <dynamic> GetBySkipDynamic <T>(this IDbConnection conn, int skip, int take, string returnFields = null, string where = null, object param = null, string orderBy = null, IDbTransaction transaction = null, int?commandTimeout = null)
{
DapperExtSqls sqls = GetDapperExtSqls(typeof(T));
if (returnFields == null)
{
returnFields = sqls.AllFields;
}
if (orderBy == null)
{
if (sqls.HasKey)
{
orderBy = string.Format("ORDER BY `{0}` DESC", sqls.KeyName);
}
else
{
orderBy = string.Format("ORDER BY `{0}`", sqls.AllFieldList.First());
}
}
string sql = string.Format("SELECT {0} FROM `{1}` {2} {3} LIMIT {4},{5}", returnFields, sqls.TableName, where, orderBy, skip, take);
return(conn.Query(sql, param, transaction, true, commandTimeout));
}
private static T GetByIdBase <T>(this IDbConnection conn, Type t, dynamic id, string returnFields = null, IDbTransaction transaction = null, int?commandTimeout = null)
{
DapperExtSqls sqls = GetDapperExtSqls(t);
if (sqls.HasKey)
{
DynamicParameters dpar = new DynamicParameters();
dpar.Add("@id", id);
if (returnFields == null)
{
return(conn.QueryFirstOrDefault <T>(sqls.GetByIdSql, dpar, transaction, commandTimeout));
}
else
{
string sql = string.Format("SELECT {0} FROM `{1}` WHERE `{2}`=@id", returnFields, sqls.TableName, sqls.KeyName);
return(conn.QueryFirstOrDefault <T>(sql, dpar, transaction, commandTimeout));
}
}
else
{
throw new ArgumentException("表" + sqls.TableName + "没有主键，无法GetById。");
}
}
public static int InsertBatch <T>(this IDbConnection conn, IEnumerable <T> entitys, IDbTransaction transaction = null, int?commandTimeout = null)
{
DapperExtSqls sqls = GetDapperExtSqls(typeof(T));
return(conn.Execute(sqls.InsertSql, entitys, transaction, commandTimeout));
}
public static int DeleteAll <T>(this IDbConnection conn, IDbTransaction transaction = null, int?commandTimeout = null)
{
DapperExtSqls sqls = GetDapperExtSqls(typeof(T));
return(conn.Execute(sqls.DeleteAllSql, null, transaction, commandTimeout));
}
/// <summary>
/// 获取分页数据
/// </summary>
public static IEnumerable <dynamic> GetByPageDynamic <T>(this IDbConnection conn, int pageIndex, int pageSize, out int total, string returnFields = null, string where = null, object param = null, string orderBy = null, IDbTransaction transaction = null, int?commandTimeout = null)
{
DapperExtSqls sqls = GetDapperExtSqls(typeof(T));
if (returnFields == null)
{
returnFields = sqls.AllFields;
}
if (orderBy == null)
{
if (sqls.HasKey)
{
orderBy = string.Format("ORDER BY [{0}] DESC", sqls.KeyName);
}
else
{
orderBy = string.Format("ORDER BY [{0}]", sqls.AllFieldList.First());
}
}
int skip = 0;
if (pageIndex > 0)
{
skip = (pageIndex - 1) * pageSize;
}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("DECLARE @total INT;SELECT @total = COUNT(1) FROM [{0}] WITH(NOLOCK) {1};SELECT @total;", sqls.TableName, where);
sb.Append("IF(@total>0) BEGIN ");
if (pageIndex == 1)
{
sb.AppendFormat("SELECT TOP ({0}) {1} FROM [{2}] WITH(NOLOCK) {3} {4}", pageSize, returnFields, sqls.TableName, where, orderBy);
}
else
{
sb.AppendFormat("WITH cte AS (SELECT ROW_NUMBER() OVER({0}) AS rownum,{1} FROM [{2}] WITH(NOLOCK) {3})", orderBy, returnFields, sqls.TableName, where);
if (returnFields.Contains(" AS") || returnFields.Contains(" as"))
{
sb.AppendFormat("SELECT * FROM cte WHERE cte.rownum BETWEEN {0} AND {1}", skip + 1, skip + pageSize);
}
else
{
sb.AppendFormat("SELECT {0} FROM cte WHERE cte.rownum BETWEEN {1} AND {2}", returnFields, skip + 1, skip + pageSize);
}
}
sb.Append(" END");
using (var reader = conn.QueryMultiple(sb.ToString(), param, transaction, commandTimeout))
{
total = reader.ReadFirst <int>();
if (total > 0)
{
return(reader.Read());
}
else
{
return(new List <dynamic>());
}
}
}