public string GetInsertIdentitySql <T>()
{
var table = SqlServerCache.GetTableEntity <T>();
CommonUtil.CheckTableKey(table);
return(table.InsertIdentitySql);
}
public string GetDeleteByIdsSql <T>()
{
var table = SqlServerCache.GetTableEntity <T>();
CommonUtil.CheckTableKey(table);
return(table.DeleteByIdsSql);
}
public string GetExistsKeySql <T>()
{
var table = SqlServerCache.GetTableEntity <T>();
CommonUtil.CheckTableKey(table);
return(string.Format("SELECT COUNT(1) FROM [{0}] WITH(NOLOCK) WHERE [{1}]=@{1}", table.TableName, table.KeyName));
}
public string GetBySkipTakeSql <T>(int skip, int take, string where, string returnFields, string orderBy)
{
var table = SqlServerCache.GetTableEntity <T>();
StringBuilder sb = new StringBuilder();
InitPage(sb, table, skip, take, where, returnFields, orderBy);
return(sb.ToString());
}
public string GetByWhereFirstSql <T>(string where, string returnFields)
{
var table = SqlServerCache.GetTableEntity <T>();
if (string.IsNullOrEmpty(returnFields))
{
returnFields = table.AllFields;
}
return(string.Format("SELECT TOP (1) {0} FROM [{1}] WITH(NOLOCK) {2}", returnFields, table.TableName, where));
}
public string GetByIdsWithFieldSql <T>(string field, string returnFields)
{
var table = SqlServerCache.GetTableEntity <T>();
if (string.IsNullOrEmpty(returnFields))
{
returnFields = table.AllFields;
}
return(string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}] IN @ids", returnFields, table.TableName, field));
}
public string GetUpdateSql <T>(string updateFields)
{
var table = SqlServerCache.GetTableEntity <T>();
CommonUtil.CheckTableKey(table);
if (string.IsNullOrEmpty(updateFields))
{
return(table.UpdateSql);
}
return(CommonUtil.CreateUpdateSql(table, updateFields, "[", "]"));
}
public string GetSchemaTableSql <T>(string returnFields)
{
var table = SqlServerCache.GetTableEntity <T>();
if (string.IsNullOrEmpty(returnFields))
{
return(string.Format("SELECT TOP 0 {0} FROM [{1}] WITH(NOLOCK)", table.AllFields, table.TableName));
}
else
{
return(string.Format("SELECT TOP 0 {0} FROM [{1}] WITH(NOLOCK)", returnFields, table.TableName));
}
}
public string GetAllSql <T>(string returnFields, string orderBy)
{
var table = SqlServerCache.GetTableEntity <T>();
if (string.IsNullOrEmpty(returnFields))
{
return(table.GetAllSql + orderBy);
}
else
{
return(string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) {2}", returnFields, table.TableName, orderBy));
}
}
public string GetByIdsSql <T>(string returnFields)
{
var table = SqlServerCache.GetTableEntity <T>();
CommonUtil.CheckTableKey(table);
if (string.IsNullOrEmpty(returnFields))
{
return(table.GetByIdsSql);
}
else
{
return(string.Format("SELECT {0} FROM [{1}] WITH(NOLOCK) WHERE [{2}] IN @ids", returnFields, table.TableName, table.KeyName));
}
}
public string GetPageSql <T>(int pageIndex, int pageSize, string where, string returnFields, string orderBy)
{
int skip = 0;
if (pageIndex > 0)
{
skip = (pageIndex - 1) * pageSize;
}
var table = SqlServerCache.GetTableEntity <T>();
StringBuilder sb = new StringBuilder();
sb.AppendFormat("DECLARE @total INT;SELECT @total = COUNT(1) FROM [{0}] WITH(NOLOCK) {1};SELECT @total;", table.TableName, where);
sb.Append("IF(@total>0) BEGIN ");
InitPage(sb, table, skip, pageSize, where, returnFields, orderBy);
sb.Append(" END");
return(sb.ToString());
}
public static string BulkCopy <T>(this IDbConnection conn, DataTable dt, string copyFields = null, bool insert_identity = false, int batchSize = 20000, int timeout = 100)
{
var table = SqlServerCache.GetTableEntity <T>();
return(BulkCopy(conn, dt, table.TableName, copyFields, insert_identity, batchSize, timeout));
}
public string GetUpdateByWhereSql <T>(string where, string updateFields)
{
var table = SqlServerCache.GetTableEntity <T>();
return(CommonUtil.CreateUpdateByWhereSql(table, where, updateFields, "[", "]"));
}
public string GetDeleteAllSql <T>()
{
return(SqlServerCache.GetTableEntity <T>().DeleteAllSql);
}
public string GetInsertReturnIdSql <T>(string sequence = null)
{
return(SqlServerCache.GetTableEntity <T>().InsertReturnIdSql);
}
public string GetInsertSql <T>()
{
return(SqlServerCache.GetTableEntity <T>().InsertSql);
}
public static string BulkUpdate <T>(this IDbConnection conn, DataTable dt, string column = "*", int batchSize = 20000, int timeOut = 100)
{
var table = SqlServerCache.GetTableEntity <T>();
return(BulkUpdate(conn, dt, table.TableName, column, batchSize, timeOut));
}
public string GetTotalSql <T>(string where)
{
var table = SqlServerCache.GetTableEntity <T>();
return(string.Format("SELECT COUNT(1) FROM [{0}] WITH(NOLOCK) {1}", table.TableName, where));
}