public bool QueryCount(Table table, WithEnums with, Dictionary <string, object> parms, out ulong count)
{
StringBuilder sb = new StringBuilder()
.Append("SELECT COUNT(*) AS '_ROW_COUNT_' FROM ")
.Append(ProcessTableName(table.SourceName, table.SourceSchema, with));
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append($" {table.WhereSQL}");
}
count = 0;
if (Query(sb.ToString(), parms, out IDataWrapper data))
{
try
{
if (data.Read())
{
count = ulong.Parse(data.GetValue(0).ToString());
}
return(true);
}
finally
{
data.Close();
}
}
else
{
return(false);
}
}
public bool QueryPage(Table table, uint fromRow, uint toRow, WithEnums with, Dictionary <string, object> parms, out IDataWrapper reader)
{
StringBuilder sb = new StringBuilder();
// 如果存在主键：
// SELECT <B.fieldsSQL> FROM <tableName> B JOIN (SELECT <keyFields> FROM
// (SELECT <keyFields>, ROW_NUMBER() OVER (ORDER BY <orderSQL>) AS "_RowNum_"
// FROM <tableName>
// {WHERE <whereSQL>}
// ) A WHERE "_RowNum_" BETWEEN <fromRow> AND <toRow>) A ON <B.keyFields> = <A.keyFields>
if (table.KeyFields.Length > 0)
{
string fieldsSQL = ProcessFieldNames(table.SourceFields, "B");
string tableName = ProcessTableName(table.SourceName, table.SourceSchema);
string tableNameWithB = ProcessTableName(table.SourceName, table.SourceSchema, "B");
string keyFields = ProcessFieldNames(table.KeyFields);
string keyFieldsWithAlias = ProcessFieldNames(table.KeyFields, ProcessTableName(table.SourceName, table.SourceSchema));
string keyField = ProcessFieldName(table.KeyFields[0]);
sb.Append($"SELECT {fieldsSQL} FROM {tableNameWithB} JOIN (SELECT {keyFields} FROM")
.Append($" (SELECT {keyFieldsWithAlias}, ROW_NUMBER() OVER (ORDER BY {table.OrderSQL})")
.Append($" AS \"_RowNum_\" FROM {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WEHRE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append($" {table.WhereSQL}");
}
sb.Append($") A WHERE A.\"_RowNum_\" BETWEEN {fromRow} AND {toRow}) A ON B.{keyField} = A.{keyField}");
for (int i = 1; i < table.KeyFields.Length; i++)
{
keyField = ProcessFieldName(table.KeyFields[i]);
sb.Append($" AND B.{keyField} = A.{keyField}");
}
return(Query(sb.ToString(), parms, out reader));
}
else
{
// 语法格式形如：
// SELECT <fieldsSQL> FROM (SELECT ROW_NUMBER() OVER (ORDER BY <orderSQL>)
// AS "_RowNum_", <fieldsSQL> FROM <tableName>
// {WHERE <whereSQL>}
// ) A WHERE A."_RowNum_" BETWEEN <fromRow> AND <toRow>
string fieldsSQL = ProcessFieldNames(table.SourceFields);
string fieldsWithAlias = ProcessFieldNames(table.SourceFields, ProcessTableName(table.SourceName, table.SourceSchema));
sb.Append($"SELECT {fieldsSQL} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {table.OrderSQL})")
.Append($" AS \"_RowNum_\", {fieldsWithAlias} FROM {ProcessTableName(table.SourceName, table.SourceSchema)}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append($" {table.WhereSQL}");
}
sb.Append($") A WHERE A.\"_RowNum_\" BETWEEN {fromRow} AND {toRow}");
return(Query(sb.ToString(), parms, out reader));
}
}
public bool QueryPage(Table table, uint fromRow, uint toRow, WithEnums with, Dictionary <string, object> parms, out IDataWrapper reader)
{
StringBuilder sb = new StringBuilder();
// 如果主键字段只有一个：
// SELECT <fieldsSQL> FROM <tableName> WHERE ROWNUM <= <toRow - fromRow + 1>
// { AND <keyField> > @LastMaxKey}{ AND {<whereSQL>}} ORDER BY <keyField> ASC
// 其中
// @LastMaxKey = SELECT MAX(<keyField>) AS "_MaxKey_" FROM (
// SELECT <keyField> FROM <tableName> WHERE ROWNUM <= <toRow - fromRow + 1>
// { AND <keyField> > @LastMaxKey}{ AND {<whereSQL>}} ORDER BY <keyField> ASC
if (table.KeyFields.Length == 1)
{
string tableName = ProcessTableName(table.SourceName, table.SourceSchema);
string keyField = ProcessFieldName(table.KeyFields[0]);
string keyFieldWithPrefix = ProcessFieldName(table.KeyFields[0], ProcessTableName(table.SourceName, table.SourceSchema));
// 查询最大键值
sb.Append($"SELECT MAX({keyField}) AS \"_MaxKey_\" FROM (")
.Append($"SELECT {keyFieldWithPrefix} FROM {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append(" ").Append(table.WhereSQL);
if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" AND {keyFieldWithPrefix} > @LastMaxKey");
}
sb.Append(" AND");
}
else if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" WHERE {keyFieldWithPrefix} > @LastMaxKey AND");
}
else
{
sb.Append(" WHERE");
}
sb.Append($" ROWNUM <= {toRow - fromRow + 1} ORDER BY {keyFieldWithPrefix} ASC) A");
if (QueryMaxKey(sb.ToString(), parms, out object maxValue))
{
string fieldsSQL = ProcessFieldNames(table.SourceFields, ProcessTableName(table.SourceName, table.SourceSchema));
sb.Length = 0;
sb.Append($"SELECT {fieldsSQL} FROM {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append(" ").Append(table.WhereSQL);
if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" AND {keyFieldWithPrefix} > @LastMaxKey");
}
sb.Append(" AND");
}
else if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" WHERE {keyFieldWithPrefix} > @LastMaxKey AND");
}
else
{
sb.Append(" WHERE");
}
sb.Append($" ROWNUM <= {toRow - fromRow + 1} ORDER BY {keyFieldWithPrefix} ASC");
bool rst = Query(sb.ToString(), parms, out reader);
parms["LastMaxKey"] = maxValue;
return(rst);
}
else
{
reader = null;
return(false);
}
}
// 如果存在复合主键：
// SELECT <B.fieldsSQL> FROM <tableName> B JOIN (SELECT <keyFields> FROM
// (SELECT <keyFields>, ROW_NUMBER() OVER (ORDER BY <orderSQL>) AS "_RowNum_"
// FROM <tableName>
// {WHERE <whereSQL>}
// ) A WHERE "_RowNum_" BETWEEN <fromRow> AND <toRow>) A ON <B.keyFields> = <A.keyFields>
else if (table.KeyFields.Length > 1)
{
string fieldsSQL = ProcessFieldNames(table.SourceFields, "B");
string tableName = ProcessTableName(table.SourceName, table.SourceSchema);
string tableNameWithB = ProcessTableName(table.SourceName, table.SourceSchema, "B");
string keyFields = ProcessFieldNames(table.KeyFields);
string keyFieldsWithAlias = ProcessFieldNames(table.KeyFields, ProcessTableName(table.SourceName, table.SourceSchema));
string keyField = ProcessFieldName(table.KeyFields[0]);
sb.Append($"SELECT {fieldsSQL} FROM {tableNameWithB} JOIN (SELECT {keyFields} FROM")
.Append($" (SELECT {keyFieldsWithAlias}, ROW_NUMBER() OVER (ORDER BY {table.OrderSQL})")
.Append($" AS \"_RowNum_\" FROM {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WEHRE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append($" {table.WhereSQL}");
}
sb.Append($") A WHERE A.\"_RowNum_\" BETWEEN {fromRow} AND {toRow}) A ON B.{keyField} = A.{keyField}");
for (int i = 1; i < table.KeyFields.Length; i++)
{
keyField = ProcessFieldName(table.KeyFields[i]);
sb.Append($" AND B.{keyField} = A.{keyField}");
}
return(Query(sb.ToString(), parms, out reader));
}
else
{
// 语法格式形如：
// SELECT <fieldsSQL> FROM (SELECT ROW_NUMBER() OVER (ORDER BY <orderSQL>)
// AS "_RowNum_", <fieldsSQL> FROM <tableName>
// {WHERE <whereSQL>}
// ) A WHERE A."_RowNum_" BETWEEN <fromRow> AND <toRow>
string fieldsSQL = ProcessFieldNames(table.SourceFields);
string fieldsWithAlias = ProcessFieldNames(table.SourceFields, ProcessTableName(table.SourceName, table.SourceSchema));
sb.Append($"SELECT {fieldsSQL} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {table.OrderSQL})")
.Append($" AS \"_RowNum_\", {fieldsWithAlias} FROM {ProcessTableName(table.SourceName, table.SourceSchema)}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append($" {table.WhereSQL}");
}
sb.Append($") A WHERE A.\"_RowNum_\" BETWEEN {fromRow} AND {toRow}");
return(Query(sb.ToString(), parms, out reader));
}
}
public bool QueryPage(Table table, uint fromRow, uint toRow, WithEnums with, Dictionary <string, object> parms,
out IDataWrapper reader)
{
StringBuilder sb = new StringBuilder();
string tableName = ProcessTableName(table.SourceName, table.SourceSchema);
// 语法格式形如：
// SELECT <fieldsSQL> FROM <tableName> {WHERE <whereSQL>}
// {ORDER BY <orderSQL>} LIMIT <fromRow - 1>, <toRow - fromRow + 1>
//
// 如果存在主键，可以优化为：
// SELECT <A.fieldsSQL> FROM <tableName> A JOIN (SELECT <keyFields> FROM <tableName> {WHERE <whereSQL>}
// {ORDER BY <orderSQL>} LIMIT <fromRow - 1>, <toRow - fromRow + 1>) B ON <A.keyFields> = <B.keyFields>
//
// 如果主键字段只有一个，可以进一步优化为：
// SELECT <fieldsSQL> FROM <tableName> {WHERE {<keyField> > @LastMaxKey} {AND {<whereSQL>}}}
// ORDER BY <keyField> ASC LIMIT <toRow - fromRow + 1>
// 其中
// @LastMaxKey = SELECT MAX(<keyField>) AS '_MaxKey_' FROM (SELECT <keyField> FROM <tableName>
// {WHERE {<keyField> > @LastMaxKey} {AND {<whereSQL>}}} ORDER BY <keyField> ASC
// LIMIT <toRow - fromRow + 1>) A
if (table.KeyFields.Length == 1)
{
string keyField = ProcessFieldName(table.KeyFields[0]);
string keyFieldWithPrefix = ProcessFieldName(table.KeyFields[0], tableName);
// 查询最大键值
sb.Append($"SELECT MAX({keyField}) AS '_MaxKey_' FROM (SELECT {keyFieldWithPrefix} FROM {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append(" ").Append(table.WhereSQL);
if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" AND {keyFieldWithPrefix} > @LastMaxKey");
}
}
else if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" WHERE {keyFieldWithPrefix} > @LastMaxKey");
}
sb.Append($" ORDER BY {keyFieldWithPrefix} LIMIT {toRow - fromRow + 1}) A");
if (QueryMaxKey(sb.ToString(), parms, out object maxValue))
{
string fieldsSQL = ProcessFieldNames(table.SourceFields, tableName);
sb.Length = 0;
sb.Append($"SELECT {fieldsSQL} FROM {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append(" ").Append(table.WhereSQL);
if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" AND {keyFieldWithPrefix} > @LastMaxKey");
}
}
else if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" WHERE {keyFieldWithPrefix} > @LastMaxKey");
}
sb.Append($" ORDER BY {keyFieldWithPrefix} LIMIT {toRow - fromRow + 1}");
bool rst = Query(sb.ToString(), parms, out reader);
parms["LastMaxKey"] = maxValue;
return(rst);
}
else
{
reader = null;
return(false);
}
}
else
{
if (table.KeyFields.Length > 0)
{
string fieldsSQL = ProcessFieldNames(table.SourceFields, "A");
string keyField = ProcessFieldName(table.KeyFields[0]);
string keyFields = ProcessFieldNames(table.KeyFields, tableName);
sb.Append($"SELECT {fieldsSQL} FROM {tableName} A JOIN (SELECT {keyFields} FROM {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append($" {table.WhereSQL}");
}
if (!string.IsNullOrEmpty(table.OrderSQL))
{
sb.Append(" ORDER BY ").Append(table.OrderSQL);
}
sb.Append($" LIMIT {fromRow - 1}, {toRow - fromRow + 1}) B ON A.{keyField} = B.{keyField}");
for (int i = 1; i < table.KeyFields.Length; i++)
{
keyField = ProcessFieldName(table.KeyFields[i]);
sb.Append($" AND A.{keyField} = B.{keyField}");
}
}
else
{
string fieldsSQL = ProcessFieldNames(table.SourceFields, tableName);
sb.Append($"SELECT {fieldsSQL} FROM {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append($" {table.WhereSQL}");
}
if (!string.IsNullOrEmpty(table.OrderSQL))
{
sb.Append(" ORDER BY ").Append(table.OrderSQL);
}
sb.Append($" LIMIT {fromRow - 1}, {toRow - fromRow + 1}");
}
return(Query(sb.ToString(), parms, out reader));
}
}
public bool QueryPage(Table table, uint fromRow, uint toRow, WithEnums with, Dictionary <string, object> parms,
out IDataWrapper reader)
{
StringBuilder sb = new StringBuilder();
// 如果主键字段只有一个：
// SELECT TOP <toRow - fromRow + 1> <fieldsSQL> FROM <tableName>
// {WHERE {<keyField> > @LastMaxKey} {AND {<whereSQL>}}} ORDER BY <keyField> ASC
// 其中
// @LastMaxKey = SELECT MAX(<keyField>) AS '_MaxKey_' FROM (
// SELECT TOP <toRow - fromRow + 1> <keyField> FROM <tableName>
// {WHERE {<keyField> > @LastMaxKey} {AND {<whereSQL>}}} ORDER BY <keyField> ASC
if (table.KeyFields.Length == 1)
{
string tableName = ProcessTableName(table.SourceName, table.SourceSchema, with);
string keyField = ProcessFieldName(table.KeyFields[0]);
string keyFieldWithPrefix = ProcessFieldName(table.KeyFields[0],
ProcessTableName(table.SourceName, table.SourceSchema));
// 查询最大键值
sb.Append($"SELECT MAX({keyField}) AS '_MaxKey_' FROM (")
.Append($"SELECT TOP {toRow - fromRow + 1} {keyFieldWithPrefix} FROM {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append(" ").Append(table.WhereSQL);
if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" AND {keyFieldWithPrefix} > @LastMaxKey");
}
}
else if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" WHERE {keyFieldWithPrefix} > @LastMaxKey");
}
sb.Append($" ORDER BY {keyFieldWithPrefix} ASC) A");
if (QueryMaxKey(sb.ToString(), parms, out object maxValue))
{
string fieldsSQL = ProcessFieldNames(table.SourceFields,
ProcessTableName(table.SourceName, table.SourceSchema));
sb.Length = 0;
sb.Append($"SELECT TOP {toRow - fromRow + 1} {fieldsSQL} FROM {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append(" ").Append(table.WhereSQL);
if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" AND {keyFieldWithPrefix} > @LastMaxKey");
}
}
else if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" WHERE {keyFieldWithPrefix} > @LastMaxKey");
}
sb.Append($" ORDER BY {keyFieldWithPrefix} ASC");
bool rst = Query(sb.ToString(), parms, out reader);
parms["LastMaxKey"] = maxValue;
return(rst);
}
else
{
reader = null;
return(false);
}
}
else if (Version.Parse(conn.ServerVersion).Major >= 10) // 2008 或更新版本
{
// 语法格式形如：
// SELECT <fieldsSQL> FROM (SELECT ROW_NUMBER() OVER (ORDER BY <orderSQL>)
// AS '_RowNum_', <fieldsSQL> FROM <tableName>
// {WHERE <whereSQL>}
// ) A WHERE A.[_RowNum_] BETWEEN <fromRow> AND <toRow>
//
// 如果存在主键，可以优化为：
// SELECT <B.fieldsSQL> FROM <tableName> B JOIN (SELECT <keyFields> FROM
// (SELECT <keyFields>, ROW_NUMBER() OVER (ORDER BY <orderSQL>) AS '_RowNum_'
// FROM <tableName>
// {WHERE <whereSQL>}
// ) A WHERE [_RowNum_] BETWEEN <fromRow> AND <toRow>) A ON <B.keyFields> = <A.keyFields>
if (table.KeyFields.Length > 1)
{
string fieldsSQL = ProcessFieldNames(table.SourceFields, "B");
string tableName = ProcessTableName(table.SourceName, table.SourceSchema, with);
string tableNameWithB = ProcessTableName(table.SourceName, table.SourceSchema, with, "B");
string keyFields = ProcessFieldNames(table.KeyFields);
string keyFieldsWithAlias = ProcessFieldNames(table.KeyFields,
ProcessTableName(table.SourceName, table.SourceSchema));
string keyField = ProcessFieldName(table.KeyFields[0]);
sb.Append($"SELECT {fieldsSQL} FROM {tableNameWithB} JOIN (SELECT {keyFields} FROM")
.Append($" (SELECT {keyFieldsWithAlias}, ROW_NUMBER() OVER (ORDER BY {table.OrderSQL})")
.Append($" AS '_RowNum_' FROM {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WEHRE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append($" {table.WhereSQL}");
}
sb.Append($") A WHERE A.[_RowNum_] BETWEEN {fromRow} AND {toRow}) A ON B.{keyField} = A.{keyField}");
for (int i = 1; i < table.KeyFields.Length; i++)
{
keyField = ProcessFieldName(table.KeyFields[i]);
sb.Append($" AND B.{keyField} = A.{keyField}");
}
}
else
{
string fieldsSQL = ProcessFieldNames(table.SourceFields);
string fieldsWithAlias = ProcessFieldNames(table.SourceFields,
ProcessTableName(table.SourceName, table.SourceSchema));
sb.Append($"SELECT {fieldsSQL} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {table.OrderSQL})")
.Append($" AS '_RowNum_', {fieldsWithAlias} FROM {ProcessTableName(table.SourceName, table.SourceSchema, with)}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append($" {table.WhereSQL}");
}
sb.Append($") A WHERE A.[_RowNum_] BETWEEN {fromRow} AND {toRow}");
}
}
else
{
// 此语法要求 whereSQL、orderSQL 包含表名前缀，如：MyTable.KeyField ASC
// SELECT TOP <toRow - fromRow + 1> <tableName.fieldsSQL> FROM <tableNameWith>
// LEFT JOIN (SELECT TOP <fromRow - 1> <keyFieldsSQL> FROM <tableNameWith>
// {WHERE <whereSQL>}
// ORDER BY <orderSQL>) B ON
// <tableName.keyFields> = <B.keyFields>
// WHERE <B.keyFields[0]> IS NULL
// {AND <whereSQL>}
string tableName = ProcessTableName(table.SourceName, table.SourceSchema);
string tableNameWith = ProcessTableName(table.SourceName, table.SourceSchema, with);
string fieldsSQL = ProcessFieldNames(table.SourceFields, tableName);
string keyFieldsSQL = ProcessFieldNames(table.KeyFields);
string keyField = ProcessFieldName(table.KeyFields[0]);
sb.Append($"SELECT TOP {toRow - fromRow + 1} {fieldsSQL} FROM {tableNameWith}")
.Append($" LEFT JOIN (SELECT TOP {fromRow - 1} {keyFieldsSQL} FROM {tableNameWith}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append($" {table.WhereSQL}");
}
sb.Append($" ORDER BY {table.OrderSQL}) B ON ").Append($"{tableName}.{keyField} = B.{keyField}");
for (int i = 1; i < table.KeyFields.Length; i++)
{
keyField = ProcessFieldName(table.KeyFields[i]);
sb.Append($" AND {tableName}.{keyField} = B.{keyField}");
}
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" WHERE");
}
sb.Append($" {table.WhereSQL} AND B.{keyField} IS NULL");
}
else
{
sb.Append($" WHERE B.{keyField} IS NULL");
}
}
return(Query(sb.ToString(), parms, out reader));
}
public bool QueryPage(Table table, uint fromRow, uint toRow, WithEnums with, Dictionary <string, object> parms,
out IDataWrapper reader)
{
StringBuilder sb = new StringBuilder();
string tableName = ProcessTableName(table.SourceName, table.SourceSchema);
// 语法格式形如：
// select <fieldsSQL> from <tableName> {where <whereSQL>}
// {order by <orderSQL>} limit <toRow - fromRow + 1> offset <fromRow - 1>
//
// 如果存在主键，可以优化为：
// select <A.fieldsSQL> from <tableName> A join (select <keyFields> from <tableName> {where <whereSQL>}
// {order by <orderSQL>} limit <toRow - fromRow + 1> offset <fromRow - 1>) B on <A.keyFields> = <B.keyFields>
//
// 如果主键字段只有一个，可以进一步优化为：
// select <fieldsSQL> from <tableName> {where {<keyField> > @LastMaxKey} {and {<whereSQL>}}
// order by <keyField> asc limit <toRow - fromRow + 1>
// 其中
// @LastMaxKey = select max(<keyField>) as "_MaxKey_" from (select <keyField> from <tableName>
// {where {<keyField> > @LastMaxKey} {and {<whereSQL>}}} order by <keyField> asc
// limit <toRow - fromRow + 1>) A
if (table.KeyFields.Length == 1)
{
string keyField = ProcessFieldName(table.KeyFields[0]);
string keyFieldWithPrefix = ProcessFieldName(table.KeyFields[0], tableName);
// 查询最大键值
sb.Append($"select max({keyField}) as \"_MaxKey_\" from (select {keyFieldWithPrefix} from {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" where ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" where");
}
sb.Append(" ").Append(table.WhereSQL);
if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" and {keyFieldWithPrefix} > @LastMaxKey");
}
}
else if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" where {keyFieldWithPrefix} > @LastMaxKey");
}
sb.Append($" order by {keyField} limit {toRow - fromRow + 1}) A");
if (QueryMaxKey(sb.ToString(), parms, out object maxValue))
{
string fieldsSQL = ProcessFieldNames(table.SourceFields, tableName);
sb.Length = 0;
sb.Append($"select {fieldsSQL} from {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" where ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" where");
}
sb.Append(" ").Append(table.WhereSQL);
if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" and {keyFieldWithPrefix} > @LastMaxKey");
}
}
else if (parms.ContainsKey("LastMaxKey"))
{
sb.Append($" where {keyFieldWithPrefix} > @LastMaxKey");
}
sb.Append($" order by {keyField} limit {toRow - fromRow + 1}");
bool rst = Query(sb.ToString(), parms, out reader);
parms["LastMaxKey"] = maxValue;
return(rst);
}
else
{
reader = null;
return(false);
}
}
else
{
if (table.KeyFields.Length > 0)
{
string fieldsSQL = ProcessFieldNames(table.SourceFields, "A");
string keyField = ProcessFieldName(table.KeyFields[0]);
string keyFields = ProcessFieldNames(table.KeyFields, tableName);
sb.Append($"select {fieldsSQL} from {tableName} A join (select {keyFields} from {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" where ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" where");
}
sb.Append($" {table.WhereSQL}");
}
if (!string.IsNullOrEmpty(table.OrderSQL))
{
sb.Append(" order by ").Append(table.OrderSQL);
}
sb.Append($" limit {toRow - fromRow + 1} offset {fromRow - 1}) B on A.{keyField} = B.{keyField}");
for (int i = 1; i < table.KeyFields.Length; i++)
{
keyField = ProcessFieldName(table.KeyFields[i]);
sb.Append($" and A.{keyField} = B.{keyField}");
}
}
else
{
string fieldsSQL = ProcessFieldNames(table.SourceFields, tableName);
sb.Append($"select {fieldsSQL} from {tableName}");
if (!string.IsNullOrEmpty(table.WhereSQL))
{
if (table.WhereSQL.IndexOf(" where ", StringComparison.OrdinalIgnoreCase) < 0)
{
sb.Append(" where");
}
sb.Append($" {table.WhereSQL}");
}
if (!string.IsNullOrEmpty(table.OrderSQL))
{
sb.Append(" order by ").Append(table.OrderSQL);
}
sb.Append($" limit {toRow - fromRow + 1} offset {fromRow - 1}");
}
return(Query(sb.ToString(), parms, out reader));
}
}