public static QueryResult Result(string conStr, QueryInstance ins,
int pageSize = 20,
int startPage = 1,
string rowIndex = "RowIndex",
bool inCludeRowIndex = false)
{
var command = GetSql(ins, rowIndex);
string sql = " ";
foreach (var col in ins.SelectedCols.OrderBy(p => p.SelectedIndex))
{
sql += string.Format("[{0}],", col.SelectedName);
}
if (inCludeRowIndex)
{
sql += string.Format("[{0}],", rowIndex);
}
sql = sql.Substring(0, sql.Length - 1);
command.CommandText = string.Format(@"SELECT COUNT(*) FROM ({0})A
SELECT {1} FROM ({0})A WHERE {2}>(@page-1) * @pagesize AND {2}<=@page*@pagesize ORDER BY {2}
", command.CommandText, sql, rowIndex);
command.CommandText = fmtSql(command.CommandText);
command.Parameters.AddWithValue("@page", startPage);
command.Parameters.AddWithValue("@pagesize", pageSize);
return(new QueryResult(conStr, command));
}
public void RefreshQueryInsReportParam(QueryInstance ins)
{
if (ins.BoolExp != null && ins.BoolExp.Exp != null)
{
ins.BoolExp.Exp.GetSqlPart(0);
}
}
public static System.Data.SqlClient.SqlCommand GetSql(QueryInstance ins, string rowIndex = "RowIndex")
{
var command = new System.Data.SqlClient.SqlCommand();
string sql = "SELECT distinct ";
foreach (var col in ins.SelectedCols.OrderBy(p => p.SelectedIndex))
{
sql += string.Format("{0},", GetSelectedColSlq(col));
}
// sql = sql.Substring(0, sql.Length - 1);
var cols = ins.SelectedCols.Where(p => p.OrderType != OrderType.NULL).OrderBy(q => q.SelectedIndex);
string rowCol = "ROW_NUMBER() OVER (ORDER BY ";
if (cols.Count() > 0)
{
foreach (var col in cols)
{
rowCol += string.Format("{0} {1},",
string.Format(col.SelectType.DBExp, col.SelectedTable.SelectedTableName, col.DataCol.DBName),
col.OrderType.ToString());
}
rowCol = rowCol.Substring(0, rowCol.Length - 1);
}
else
{
var col = ins.SelectedCols.First();
rowCol += string.Format("{0} {1}",
string.Format(col.SelectType.DBExp, col.SelectedTable.SelectedTableName, col.DataCol.DBName),
OrderType.ASC);
}
rowCol += string.Format(") AS [{0}]", rowIndex);
sql += rowCol;
sql += string.Format(" FROM {0}", GetTableSql(ins.SelectedTables));
if (ins.BoolExp != null && ins.BoolExp.Exp != null)
{
var sqlPart = ins.BoolExp.Exp.GetSqlPart(0);
if (sqlPart != null && string.IsNullOrEmpty(sqlPart.Stript) == false)
{
sql += " WHERE " + sqlPart.Stript;
if (sqlPart.Parameters != null)
{
foreach (var param in sqlPart.Parameters)
{
command.Parameters.Add(param.SqlParam);
}
}
if (ins.ReportParams != null)
{
for (int i = 0; i < ins.ReportParams.Count; i++)
{
string paramName = "@p" + i;
if (String.IsNullOrEmpty(ins.ReportParams[i].Exp) == false)
{
paramName = ins.ReportParams[i].Exp;
}
if (paramName[0] != '@')
{
paramName = "@" + paramName;
}
if (command.Parameters.IndexOf(paramName) < 0)
{
command.Parameters.AddWithValue(
paramName, ins.ReportParams[i].Value);
}
}
}
}
//sql += ins.BoolExp.Exp.GetSqlPart(0).Stript;
}
if (ins.SelectedCols.Count(p => p.SelectType.RequireGroupCol) > 0 &&
ins.SelectedCols.Count(p => p.SelectType.RequireGroupCol == false) > 0)
{
string groupStr = " GROUP BY ";
foreach (var col in ins.SelectedCols.Where(p => p.SelectType.RequireGroupCol == false).OrderBy(p => p.SelectedIndex))
{
groupStr +=
string.Format(col.SelectType.DBExp, col.SelectedTable.SelectedTableName, col.DataCol.DBName) + ",";
}
groupStr = groupStr.Substring(0, groupStr.Length - 1);
sql += groupStr;
}
command.CommandText = sql;
// // System.Diagnostics.Trace.WriteLine(sql);
return(command);
}