/// <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);
}
