public override IEnumerable <T> GetBySkipTake <T>(int skip, int take, string where = null, object param = null, string returnFields = null, string orderBy = null)
{
DapperSqls sqls = GetDapperSqls(typeof(T));
if (returnFields == null)
{
returnFields = sqls.AllFields;
}
if (orderBy == null)
{
if (!string.IsNullOrEmpty(sqls.KeyName))
{
orderBy = string.Format("ORDER BY [{0}]", 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(Query <T>(sql, param));
}
public override int InsertIfExistUpdate <T>(T model, string updateFields = null, bool allowUpdate = true)
{
DapperSqls sqls = GetDapperSqls(typeof(T));
if (string.IsNullOrEmpty(sqls.KeyName))
{
throw new Exception("表[" + sqls.TableName + "]没有主键");
}
string sqlTotal = string.Format("SELECT COUNT(1) FROM [{0}] WHERE [{1}]=@{1}", sqls.TableName, sqls.KeyName);
int total = ExecuteScalar <int>(sqlTotal, model);
if (total > 0) //Exists
{
if (allowUpdate)
{
if (string.IsNullOrEmpty(updateFields))
{
return(Update(model));
}
else
{
return(Update(model, updateFields));
}
}
return(0);
}
else
{
return(Insert(model));
}
}
public override int GetCount <T>(string where = null, object param = null)
{
DapperSqls sqls = GetDapperSqls(typeof(T));
string sql = string.Format("SELECT COUNT(1) FROM [{0}] {1}", sqls.TableName, where);
return(ExecuteScalar <int>(sql, param));
}
public static DapperSqls GetDapperSqls(Type t)
{
if (!DapperSqlsDict.Keys.Contains(t.TypeHandle))
{
lock (_locker)
{
if (!DapperSqlsDict.Keys.Contains(t.TypeHandle))
{
DapperSqls sqls = DapperCommon.GetDapperSqls(t);
string Fields = DapperCommon.GetFieldsStr(sqls.AllFieldList, "[", "]");
string FieldsAt = DapperCommon.GetFieldsAtStr(sqls.AllFieldList);
string FieldsEq = DapperCommon.GetFieldsEqStr(sqls.AllFieldList, "[", "]");
string FieldsExtKey = DapperCommon.GetFieldsStr(sqls.ExceptKeyFieldList, "[", "]");
string FieldsAtExtKey = DapperCommon.GetFieldsAtStr(sqls.ExceptKeyFieldList);
string FieldsEqExtKey = DapperCommon.GetFieldsEqStr(sqls.ExceptKeyFieldList, "[", "]");
sqls.AllFields = Fields;
sqls.AllFieldsAt = FieldsAt;
sqls.AllFieldsAtEq = FieldsEq;
sqls.AllFieldsExceptKey = FieldsExtKey;
sqls.AllFieldsAtExceptKey = FieldsAtExtKey;
sqls.AllFieldsAtEqExceptKey = FieldsEqExtKey;
if (!string.IsNullOrEmpty(sqls.KeyName) && sqls.IsIdentity) //有主键并且是自增
{
sqls.InsertSql = string.Format("INSERT INTO [{0}]({1})VALUES({2})", sqls.TableName, FieldsExtKey, FieldsAtExtKey);
sqls.InsertIdentitySql = string.Format("INSERT INTO [{0}]({1})VALUES({2})", sqls.TableName, Fields, FieldsAt);
}
else
{
sqls.InsertSql = string.Format("INSERT INTO [{0}]({1})VALUES({2})", sqls.TableName, Fields, FieldsAt);
}
if (!string.IsNullOrEmpty(sqls.KeyName)) //含有主键
{
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}] WHERE [{2}]=@id", Fields, sqls.TableName, sqls.KeyName);
sqls.GetByIdsSql = string.Format("SELECT {0} FROM [{1}] WHERE [{2}] IN @ids", Fields, sqls.TableName, sqls.KeyName);
sqls.UpdateSql = 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}]", Fields, sqls.TableName);
DapperSqlsDict[t.TypeHandle] = sqls;
}
}
}
return(DapperSqlsDict[t.TypeHandle]);
}
public override T GetByWhereFirst <T>(string where, object param = null, string returnFields = null)
{
DapperSqls sqls = GetDapperSqls(typeof(T));
if (string.IsNullOrEmpty(returnFields))
{
returnFields = sqls.AllFields;
}
string sql = string.Format("SELECT {0} FROM [{1}] {2}", returnFields, sqls.TableName, where);
return(QueryFirstOrDefault <T>(sql, param));
}
public override IEnumerable <T> GetByWhere <T>(string where, object param = null, string returnFields = null)
{
DapperSqls sqls = GetDapperSqls(typeof(T));
if (string.IsNullOrEmpty(returnFields))
{
returnFields = sqls.AllFields;
}
string sql = string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) {2}", returnFields, sqls.TableName, where);
return(Query <T>(sql, param));
}
public override DataTable GetSchemaTable <T>(string returnFields = null)
{
DapperSqls sqls = GetDapperSqls(typeof(T));
if (returnFields == null)
{
returnFields = sqls.AllFields;
}
string sql = string.Format("SELECT {0} FROM `{1}` LIMIT 0", returnFields, sqls.TableName);
return(QueryDataTable(sql));
}
public override IEnumerable <T> GetAll <T>(string returnFields = null, string orderBy = null)
{
DapperSqls sqls = GetDapperSqls(typeof(T));
if (string.IsNullOrEmpty(returnFields))
{
return(Query <T>(sqls.GetAllSql + " " + orderBy));
}
else
{
string sql = string.Format("SELECT {0} FROM [{1}] " + orderBy, returnFields, sqls.TableName);
return(Query <T>(sql));
}
}
public override T GetById <T>(object id, string returnFields = null)
{
DapperSqls sqls = GetDapperSqls(typeof(T));
if (string.IsNullOrEmpty(sqls.KeyName))
{
throw new Exception("表[" + sqls.TableName + "]没有主键");
}
if (string.IsNullOrEmpty(returnFields))
{
return(QueryFirstOrDefault <T>(sqls.GetByIdSql, new { id = id }));
}
else
{
string sql = string.Format("SELECT {0} FROM [{1}] WHERE [{2}]=@id", returnFields, sqls.TableName, sqls.KeyName);
return(QueryFirstOrDefault <T>(sql, new { id = id }));
}
}
public override IEnumerable <T> GetBySkipTake <T>(int skip, int take, string where = null, object param = null, string returnFields = null, string orderBy = null)
{
DapperSqls sqls = GetDapperSqls(typeof(T));
if (returnFields == null)
{
returnFields = sqls.AllFields;
}
if (orderBy == null)
{
if (!string.IsNullOrEmpty(sqls.KeyName))
{
orderBy = string.Format("ORDER BY [{0}]", 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(Query <T>(sb.ToString(), param));
}
public override IEnumerable <T> GetByPage <T>(int pageIndex, int pageSize, out int total, string where = null, object param = null, string returnFields = null, string orderBy = null)
{
DapperSqls sqls = GetDapperSqls(typeof(T));
if (returnFields == null)
{
returnFields = sqls.AllFields;
}
if (orderBy == null)
{
if (!string.IsNullOrEmpty(sqls.KeyName))
{
orderBy = string.Format("ORDER BY [{0}]", 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);
QueryMultiple(sb.ToString(), param);
total = reader.ReadFirstOrDefault <int>();
return(reader.Read <T>());
}
public override int DeleteByWhere <T>(string where, object param)
{
DapperSqls sqls = GetDapperSqls(typeof(T));
return(Execute(sqls.DeleteAllSql + " " + where, param));
}
public override IEnumerable <T> GetByPage <T>(int pageIndex, int pageSize, out int total, string where = null, object param = null, string returnFields = null, string orderBy = null)
{
DapperSqls sqls = GetDapperSqls(typeof(T));
if (returnFields == null)
{
returnFields = sqls.AllFields;
}
if (orderBy == null)
{
if (!string.IsNullOrEmpty(sqls.KeyName))
{
orderBy = string.Format("ORDER BY [{0}]", 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");
QueryMultiple(sb.ToString(), param);
total = reader.ReadFirstOrDefault <int>();
if (total > 0)
{
return(reader.Read <T>());
}
else
{
return(Enumerable.Empty <T>());
}
}
