/// <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);
}
public static DataSet From(Transaction pTransaction, string pSelectQuery, DataFieldCollection pConditionalParameters)
{
bool lSplitQuery = false;
List<string> lQueries = new List<string>();
SqlQuery lSelect;
DataSet lReturn = new DataSet();
DataTable lTable;
if (pTransaction.DataBase.DataBaseType == 0 && pSelectQuery.Contains("-- table"))
{
lSplitQuery = true;
//divide queries que tem mais de uma tabela em várias queries diferentes,
//pra pegar no oracle. o indicador deve estar no formato:
// -- table XX
// com dois traços, um espaço, a palavra "table" em lower case, outro espaco,
// e o numero da tabela com pad de 1 zero. suporta até 99 tabelas, e a tabela zero não
// precisa de indicador.
byte lCounter = 1;
int lStartIndex = 0, lEndIndex = 0;
string lFormat = "-- table {0}";
string lTempQuery, lPreviousTable = "", lCurrentTable = "";
while (lCurrentTable == "" || pSelectQuery.Contains(lCurrentTable))
{
lCurrentTable = string.Format(lFormat, lCounter.ToString().PadLeft(2, '0'));
if (lPreviousTable != "")
lStartIndex = pSelectQuery.IndexOf(lPreviousTable);
lEndIndex = pSelectQuery.IndexOf(lCurrentTable);
if (lEndIndex == -1) lEndIndex = pSelectQuery.Length;
lTempQuery = pSelectQuery.Substring(lStartIndex, lEndIndex - lStartIndex);
lQueries.Add(lTempQuery);
lPreviousTable = lCurrentTable;
lCounter++;
}
}
else
{
lQueries.Add(pSelectQuery);
}
for (int a = 0; a < lQueries.Count; a++)
{
lSelect = new SqlQuery(pTransaction, lQueries[a]);
if (pConditionalParameters != null)
{
foreach (DataField lField in pConditionalParameters.Keys)
{
if (lSelect.Query.Contains(string.Format(">>{0}", lField.Name)))
lSelect.AddParameter(lField.Name, pConditionalParameters[lField]);
}
}
if (lSplitQuery)
{
lTable = lSelect.ExecuteDataTable().Copy();
lTable.TableName = string.Format("Table {0}", a);
lReturn.Tables.Add(lTable);
}
else
{
//não tem problema estar dentro do loop pq vai ser sempre 1 query só,
//mas retornando várias tabelas no dataset
lReturn = lSelect.ExecuteDataSet();
}
}
return lReturn;
}