/// <summary>
/// 拼接分页的SQL语句
/// </summary>
/// <param name="selectColumn">需要查询的指定字段(多个之间用逗号隔开)</param>
/// <param name="selectTable">需要查询的表</param>
/// <param name="where">查询条件</param>
/// <param name="order">排序</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">一页显示条目</param>
/// <param name="dbType">数据库类型，默认MSSQLServer</param>
/// <param name="cmdParms">条件值</param>
/// <returns>分页查询结果</returns>
public static SqlQuery BuilderQueryPageSqlQuery(string selectColumn, string selectTable, string where, string order, int pageIndex, int pageSize, DatabaseType dbType = DatabaseType.MSSQLServer, object cmdParms = null)
{
SqlQuery query = new SqlQuery();
string sql = string.Empty;//select语句
if (pageIndex == 1)
{
switch (dbType)
{
case DatabaseType.PostgreSqlClient:
sql = string.Format(@"SELECT {0} FROM {1} {2} ORDER BY {3} limit 0," + GetSign(dbType) + "NUM;", string.IsNullOrWhiteSpace(selectColumn) ? "*" : selectColumn, selectTable, string.IsNullOrWhiteSpace(where) ? string.Empty : string.Format(" WHERE {0} ", where), order);
break;
case DatabaseType.MySql:
sql = string.Format(@"SELECT {0} FROM {1} {2} ORDER BY {3} limit 0," + GetSign(dbType) + "NUM;", string.IsNullOrWhiteSpace(selectColumn) ? "*" : selectColumn, selectTable, string.IsNullOrWhiteSpace(where) ? string.Empty : string.Format(" WHERE {0} ", where), order);
break;
default:
sql = string.Format(@"SELECT TOP(" + GetSign(dbType) + "NUM) {0} FROM {1} {2} ORDER BY {3};", string.IsNullOrWhiteSpace(selectColumn) ? "*" : selectColumn, selectTable, string.IsNullOrWhiteSpace(where) ? string.Empty : string.Format(" WHERE {0} ", where), order);
break;
}
query.AddParameter(GetSign(dbType) + "NUM", pageSize.ToString(), DbType.Int32, 4);
}
else
{
switch (dbType)
{
case DatabaseType.PostgreSqlClient:
sql = string.Format(@"SELECT * FROM ( SELECT {0},row_number() over(ORDER BY {3}) as [num] FROM {1} {2} ) as [tab] WHERE NUM BETWEEN " + GetSign(dbType) + "NumStart and " + GetSign(dbType) + "NumEnd;", string.IsNullOrWhiteSpace(selectColumn) ? "*" : selectColumn, selectTable, string.IsNullOrWhiteSpace(where) ? string.Empty : string.Format(" WHERE {0} ", where), order);
break;
case DatabaseType.MySql:
sql = string.Format("SELECT {0} FROM {1} {2} ORDER BY {3} LIMIT {4},{5};", string.IsNullOrWhiteSpace(selectColumn) ? "*" : selectColumn, selectTable, string.IsNullOrWhiteSpace(where) ? string.Empty : string.Format(" WHERE {0} ", where), order, GetSign(dbType) + "NumStart", GetSign(dbType) + "PageSize");
break;
default:
sql = string.Format(@"SELECT * FROM ( SELECT {0},row_number() over(ORDER BY {3}) as [num] FROM {1} {2} ) as [tab] WHERE NUM BETWEEN " + GetSign(dbType) + "NumStart and " + GetSign(dbType) + "NumEnd;", string.IsNullOrWhiteSpace(selectColumn) ? "*" : selectColumn, selectTable, string.IsNullOrWhiteSpace(where) ? string.Empty : string.Format(" WHERE {0} ", where), order);
break;
}
query.AddParameter(GetSign(dbType) + "NumStart", ((pageIndex - 1) * pageSize + 1), DbType.Int32, 4);
if (dbType == DatabaseType.MySql)
{
query.AddParameter(GetSign(dbType) + "PageSize", pageSize, DbType.Int32, 4);
}
else
{
query.AddParameter(GetSign(dbType) + "NumEnd", (pageIndex * pageSize).ToString(), DbType.Int32, 4);
}
}
if (cmdParms != null)
{
query.AddObjectParam(cmdParms);
}
query.CommandText = sql;
query.CommandType = CommandType.Text;
return(query);
}
/// <summary>
/// 拼接单个插入的SqlQuery
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="entity">实体值</param>
/// <param name="tableName">表名</param>
/// <param name="keyName">主键名字</param>
/// <param name="ignoreFields">需要忽略的字段</param>
/// <param name="isIdentity">是否自增</param>
/// <param name="dbType">数据库类型，默认MSSQLServer</param>
/// <returns>单个插入的SqlQuery</returns>
public static SqlQuery BuilderInsertOneSqlQuery <T>(T entity, string tableName, string keyName = null, string[] ignoreFields = null, bool isIdentity = true, DatabaseType dbType = DatabaseType.MSSQLServer) where T : new()
{
var propertyList = GetInsertPropertyList(entity, ignoreFields);
var proNameList = propertyList.Select(m => m.Name);
var columns = string.Join(",", proNameList);
var values = string.Join(",", proNameList.Select(p => GetSign(dbType) + p));
string commandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2});{3};", tableName, columns, values, isIdentity ? GetIdentityKeyScript(keyName, dbType) : string.Empty);
SqlQuery sqlQuery = new SqlQuery();
sqlQuery.CommandText = commandText;
sqlQuery.AddObjectParam(entity, ignoreFields: ignoreFields);
return(sqlQuery);
}
/// <summary>
/// 拼接批量插入的SqlQuery
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <exception cref="ArgumentNullException">entityList</exception>
/// <param name="entityList">实体列表，不能为Null或者为空列表</param>
/// <param name="tableName">表名</param>
/// <param name="keyName">主键名字</param>
/// <param name="ignoreFields">需要忽略的字段</param>
/// <param name="isIdentity">是否自增</param>
/// <param name="dbType">数据库类型，默认MSSQLServer</param>
/// <returns>批量插入的SqlQuery</returns>
public static SqlQuery BuilderInsertManySqlQuery <T>(List <T> entityList, string tableName, string keyName = null, string[] ignoreFields = null, bool isIdentity = true, DatabaseType dbType = DatabaseType.MSSQLServer) where T : new()
{
EnsureUtil.NotNull(entityList, "entityList");
var entity = entityList.FirstOrDefault();
if (entity == null)
{
throw new ArgumentNullException("entityList");
}
var propertyList = GetInsertPropertyList(entity, ignoreFields);
var proNameList = propertyList.Select(m => m.Name);
var columns = string.Join(",", proNameList);
StringBuilder builder = new StringBuilder();
SqlQuery sqlQuery = new SqlQuery();
for (int i = 0; i < entityList.Count; i++)
{
var values = string.Join(",", proNameList.Select(p => GetSign(dbType) + i.ToString() + p));
builder.AppendFormat("INSERT INTO {0} ({1}) VALUES ({2});{3};", tableName, columns, values, isIdentity ? GetIdentityKeyScript(keyName, dbType) : string.Empty);
sqlQuery.AddObjectParam(entityList[i], i.ToString(), ignoreFields: ignoreFields);
}
sqlQuery.CommandText = builder.ToString();
return(sqlQuery);
}
