/// <summary>
/// 解析SQL语句中的Select 字段信息，并返回 From 谓词在SQL语句中的位置索引
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="fromIndex">From 谓词在SQL语句中的位置索引</param>
/// <returns>Select 字段信息</returns>
public List <SqlField> ParseSelect(string sql, out int fromIndex)
{
Point ps = TextSearchUtil.SearchWordsIndex(sql, "select");
if (ps.A == -1)
{
throw new ArgumentException("未找到期望的谓词select ,不是合法的SQL:\r\n" + sql);
}
Point pf = TextSearchUtil.SearchWordsIndex(sql, "from");
if (pf.A < ps.A)
{
throw new ArgumentException("在select谓词之后未找到期望的from谓词，不支持此种类型的SQL分页：\r\n" + sql);
}
fromIndex = pf.A;
string selectFieldsBlock = sql.Substring(ps.B, pf.A - ps.B).Trim();
List <SqlField> sqlFields = new List <SqlField>();
if (selectFieldsBlock == "*")
{
sqlFields.Add(new SqlField()
{
Field = "*"
});
return(sqlFields);
}
else
{
string[] selectFieldsArr = selectFieldsBlock.Split(new string[] { ",", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
foreach (string fieldItem in selectFieldsArr)
{
int targetIndex = 0;
string field = TextSearchUtil.FindNearWords(fieldItem, 0, 100, out targetIndex);
if (targetIndex >= 0)
{
//寻找临近的单词，可能没有，可能是AS，也可能直接就是字段别名
string fieldAsName = TextSearchUtil.FindNearWords(fieldItem, targetIndex + field.Length, 50, out targetIndex);
if (fieldAsName.ToLower() == "as")
{
fieldAsName = TextSearchUtil.FindNearWords(fieldItem, targetIndex + 2, 50, out targetIndex);
}
sqlFields.Add(new SqlField()
{
Field = field, Alias = fieldAsName
});
}
}
}
return(sqlFields);
}
/// <summary>
/// 解析排序部分
/// </summary>
/// <param name="sql"></param>
/// <param name="orderBlockPoint">Order语句块位置信息</param>
/// <returns></returns>
public List <OrderField> ParseOrder(string sql, out Point orderBlockPoint)
{
Point p = TextSearchUtil.SearchWordsLastIndex(sql, "order by");
if (p.A == -1)
{
//没有 order by子句，无法分页，但是可能是取前面N条记录
orderBlockPoint = p;
return(null);
}
//ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number", //这种应该不予处理
//所以 如果还有PARTITION BY 子句，则认为是复杂的SQL，抛出异常语句
List <OrderField> OrderFields = new List <OrderField>();
int orderSep = 0;//排序信息分隔符（逗号）的位置
int startIndex = p.B;
do
{
int fieldIndex;
string field = TextSearchUtil.FindNearWords(sql, startIndex, 255, '[', ']', out fieldIndex);
startIndex = fieldIndex + field.Length;
int orderTypeIndex;
string orderType = TextSearchUtil.FindNearWords(sql, startIndex, 10, out orderTypeIndex);
OrderField of = new OrderField();
of.Field = field;
//of.Alias 需要判断是否是别名
if (orderTypeIndex == -1)
{
of.IsAsc = true;
}
else
{
of.IsAsc = orderType.ToUpper() == "ASC";
}
OrderFields.Add(of);
//寻找可能有多个排序的分隔逗号
int dIndex = orderTypeIndex == -1 ? startIndex : orderTypeIndex + orderType.Length;
orderSep = TextSearchUtil.FindPunctuationBeforWord(sql, dIndex, ',');
if (orderSep != -1)
{
startIndex = orderSep + 1;
}
else
{
startIndex = dIndex;
}
} while (orderSep != -1);
orderBlockPoint = new Point(p.A, startIndex);
return(OrderFields);
/*
*
* if (orderSep != -1)
* {
* //寻找第二组排序字段信息
* string word2 = TextSearchUtil.FindNearWords(sql, orderSep + 1, 10, out wordIndex);
* if (wordIndex != -1)
* {
* string orderType2 = TextSearchUtil.FindNearWords(sql, wordIndex + word2.Length, 10, out orderTypeIndex);
* }
* }
* //搜索排序字段在SELECT子句中的别名
* int selectFieldIndex = sql.IndexOf(word, 0, StringComparison.OrdinalIgnoreCase);
* if (selectFieldIndex > 0)
* {
* //寻找临近的单词，可能是AS，也可能直接就是字段别名
* string fieldAsName = TextSearchUtil.FindNearWords(sql, selectFieldIndex + word.Length, 50, out wordIndex);
* if (fieldAsName.ToLower() == "as")
* {
* fieldAsName = TextSearchUtil.FindNearWords(sql, wordIndex + 2, 50, out wordIndex);
* }
* }
*/
}
/// <summary>
/// 解析SQL语句中的Select 字段信息，并返回 From 谓词在SQL语句中的位置索引
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="fromIndex">From 谓词在SQL语句中的位置索引</param>
/// <returns>Select 字段信息</returns>
public List <SqlField> ParseSelect(string sql, out int fromIndex)
{
Point ps = TextSearchUtil.SearchWordsIndex(sql, "select");
if (ps.A == -1)
{
throw new ArgumentException("未找到期望的谓词select ,不是合法的SQL:\r\n" + sql);
}
Point pd = TextSearchUtil.SearchWordsIndex(sql, "DISTINCT");
if (pd.A != -1)
{
ps.B = pd.B;
}
Point pf = TextSearchUtil.SearchWordsIndex(sql, "from");
if (pf.A < ps.A)
{
throw new ArgumentException("在select谓词之后未找到期望的from谓词，不支持此种类型的SQL分页：\r\n" + sql);
}
fromIndex = pf.A;
string selectFieldsBlock = sql.Substring(ps.B, pf.A - ps.B).Trim();
List <SqlField> sqlFields = new List <SqlField>();
if (selectFieldsBlock == "*")
{
sqlFields.Add(new SqlField()
{
Field = "*"
});
return(sqlFields);
}
else
{
string[] selectFieldsArr = selectFieldsBlock.Split(new string[] { ",", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
foreach (string fieldItem in selectFieldsArr)
{
int targetIndex = 0;
string field = TextSearchUtil.FindNearWords(fieldItem, 0, 100, out targetIndex);
if (targetIndex >= 0)
{
//select 字段可能是函数调用，感谢网友 芜湖－大枕头 发现此Bug 2017.4.14
string fieldUp = field.ToUpper();
if (fieldUp == "SUM" || fieldUp == "AVG" || fieldUp == "MIN" || fieldUp == "MAX" || fieldUp == "COUNT" || fieldUp.IndexOf('(') > 0)
{
int atas = fieldItem.ToUpper().IndexOf(" AS ");
string fieldAsName = fieldItem.Substring(atas + 3);
sqlFields.Add(new SqlField()
{
Field = fieldItem.Substring(0, atas),
Alias = fieldAsName
});
}
else
{
//寻找临近的单词，可能没有，可能是AS，也可能直接就是字段别名
string fieldAsName = TextSearchUtil.FindNearWords(fieldItem, targetIndex + field.Length, 50, out targetIndex);
if (fieldAsName.ToLower() == "as")
{
fieldAsName = TextSearchUtil.FindNearWords(fieldItem, targetIndex + 2, 50, out targetIndex);
}
sqlFields.Add(new SqlField()
{
Field = field, Alias = fieldAsName
});
}
}
}
}
return(sqlFields);
}
/// <summary>
/// 构造可分页的排序SQL语句，如果topCount小于0，源SQL必须带排序语句
/// </summary>
/// <param name="topCount">如果指定大于0的值，将生成Top子句，如果小于0，则是限制逆向排序的记录数</param>
/// <returns></returns>
public string Build(int topCount)
{
int fromIndex;
Point orderBlockPoint;
bool isSelectStart = false;
List <SqlField> sqlFields = ParseSelect(this.sourceSql, out fromIndex);
this.OrderFields = ParseOrder(this.sourceSql, out orderBlockPoint);
if (sqlFields[0].Field == "*")
{
isSelectStart = true;
}
else if (this.OrderFields != null)
{
//检查参与排序的字段是否在SELECT中，如果不在，添加进去
foreach (OrderField orderItem in this.OrderFields)
{
var target = sqlFields.Find(p => string.Equals(p.Field, orderItem.Field, StringComparison.OrdinalIgnoreCase) ||
string.Equals(p.Alias, orderItem.Field, StringComparison.OrdinalIgnoreCase));
if (target == null)
{
orderItem.CreateAlias("");
var tempField = new SqlField()
{
Field = orderItem.Field, Alias = orderItem.Alias
};
sqlFields.Add(tempField);
orderItem.InSelect = false;
}
else
{
orderItem.Field = target.Field;
orderItem.Alias = target.Alias;
orderItem.InSelect = true;
}
}
}
this.SelectFields = sqlFields;
//重新构造Select语句块
System.Text.StringBuilder sb = new StringBuilder();
//DISTINCT 查询问题，感谢网友 @深圳-光头佬 发现此问题 2017.6.21
Point psd = TextSearchUtil.SearchWordsIndex(sourceSql, "select DISTINCT");
if (psd.A != -1)
{
sb.Append("SELECT DISTINCT ");
}
else
{
sb.Append("SELECT ");
}
if (topCount > 0)
{
sb.Append("Top " + topCount + "\r\n");
}
else if (topCount < 0)
{
sb.Append("Top " + (-topCount) + "\r\n");
}
else
{
sb.Append(" \r\n");
}
if (isSelectStart)
{
sb.Append(" * \r\n");
}
else
{
int count = this.SelectFields.Count;
int index = 0;
foreach (SqlField item in this.SelectFields)
{
index++;
sb.Append('\t');
sb.Append(item.Field);
if (!string.IsNullOrEmpty(item.Alias))
{
sb.Append(" AS ");
sb.Append(item.Alias);
}
if (index != count)
{
sb.Append(',');
}
sb.Append("\r\n");
}
}
if (topCount < 0)
{
//逆排序，用于获取最后几页的数据，包括最后一页
if (orderBlockPoint.A == -1)
{
throw new Exception("当参数 topCount小于0（逆排序），必须指定排序信息");
}
//改变Order by 子句，生成逆向排序查询
string beforOrder = this.sourceSql.Substring(fromIndex, orderBlockPoint.A - fromIndex);
string afterOrder = this.sourceSql.Substring(orderBlockPoint.B);
string InverseOrderString = "\r\n ORDER BY " + this.GetLastInverseOrderExpString() + "\r\n";
sb.Append(beforOrder);
sb.Append(InverseOrderString);
sb.Append(afterOrder);
}
else
{
sb.Append(this.sourceSql.Substring(fromIndex));
}
return(sb.ToString());
}