public void Parse()
{
Regex reg = new Regex(sqlReg, RegexOptions.IgnoreCase);
Match mat = reg.Match(sqlInfo.sql);
Table table = null;
if (mat.Success)
{
table = GetTable(mat.Groups[1].Value);
this.sqlInfo.Tables.Add(table);
this.sqlInfo.ColumnsSql = mat.Groups[2].Value;
this.sqlInfo.Columns = ParseColumn(mat.Groups[2].Value);
string[] varNames = mat.Groups[3].Value.Split(',');
if (sqlInfo.Columns.Count == varNames.Length)
{
SqlVarParameter sqlVarParameter = null;
Regex varReg = new Regex(varNameReg);
Match varMat = null;
for (int i = 0; i < sqlInfo.Columns.Count; i++)
{
varMat = varReg.Match(varNames[i]);
if (varMat.Success)
{
sqlVarParameter = new SqlVarParameter();
sqlVarParameter.sql = varNames[i];
sqlVarParameter.name = varMat.Groups[1].Value;
sqlVarParameter.columnName = sqlInfo.Columns[i].name;
sqlInfo.sqlVarParameters.Add(sqlVarParameter);
}
}
}
}
}
/// <summary>
/// 解析SQL语句中的参数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns></returns>
public List <SqlVarParameter> ParseVarName(string sql)
{
List <SqlVarParameter> parameters = new List <SqlVarParameter>();
SqlVarParameter par = null;
Regex varNameReg = new Regex(varName);
string varNameString = string.Empty;
Match mat = null;
// (top|limit) @num
string topOrLimitField = @"\s+(?:top|limit)\s+@([_a-zA-Z][_a-zA-Z0-9]*)";
Regex topOrLimitFieldReg = new Regex(topOrLimitField, RegexOptions.IgnoreCase);
MatchCollection mac = topOrLimitFieldReg.Matches(sql);
if (mac.Count > 0)
{
for (int i = 0; i < mac.Count; i++)
{
mat = mac[i];
if (mat.Success)
{
par = new SqlVarParameter();
par.name = mat.Groups[1].Value;
par.csharpName = mat.Groups[1].Value;
par.csharpType = "int";
}
}
}
//参数 与 列名 比较
string parameterCompareField =
@"((?:([_a-zA-Z0-9]+)\s*\.\s*)?([_a-zA-Z0-9]+))\s*(?:>|<|=|>\s*=|<\s*=|<\s*>)\s*@([_a-zA-z][_a-zA-Z0-9]*)";
Regex parameterCompareFieldReg = new Regex(parameterCompareField);
mac = parameterCompareFieldReg.Matches(sql);
if (mac.Count > 0)
{
for (int i = 0; i < mac.Count; i++)
{
mat = mac[i];
if (mat.Success)
{
par = new SqlVarParameter();
par.sql = mat.Value;
par.fullName = mat.Groups[1].Value;
par.tableName = mat.Groups[2].Value;
par.columnName = mat.Groups[3].Value;
par.name = mat.Groups[4].Value;
parameters.Add(par);
}
}
}
//列名 与 参数 比较
string fieldCompareParameter =
@"@([_a-zA-z][_a-zA-Z0-9]*)\s*(?:>|<|=|>\s*=|<\s*=|<\s*>)\s*((?:([_a-zA-Z0-9]+)\s*\.\s*)?([_a-zA-Z0-9]+))";
Regex fieldCompareParameterReg = new Regex(fieldCompareParameter);
mac = fieldCompareParameterReg.Matches(sql);
if (mac.Count > 0)
{
for (int i = 0; i < mac.Count; i++)
{
mat = mac[i];
if (mat.Success)
{
par = new SqlVarParameter();
par.sql = mat.Value;
par.name = mat.Groups[1].Value;
par.fullName = mat.Groups[2].Value;
par.tableName = mat.Groups[3].Value;
par.columnName = mat.Groups[4].Value;
parameters.Add(par);
}
}
}
Match varNameMat;
//列名 like 参数1
string fieldLikeParameter =
@"((?:([_a-zA-Z0-9]+)\s*\.\s*)?([_a-zA-Z0-9]+))\s+like\s+@([_a-zA-z][_a-zA-Z0-9]*)";
Regex fieldLikeParameterReg = new Regex(fieldLikeParameter, RegexOptions.IgnoreCase);
mac = fieldLikeParameterReg.Matches(sql);
if (mac.Count > 0)
{
for (int i = 0; i < mac.Count; i++)
{
mat = mac[i];
if (mat.Success)
{
par = new SqlVarParameter();
par.sql = mat.Value;
par.fullName = mat.Groups[1].Value;
par.tableName = mat.Groups[2].Value;
par.columnName = mat.Groups[3].Value;
par.name = mat.Groups[4].Value;
parameters.Add(par);
}
}
}
//列名 between 参数1 and 参数2
string fieldBetweenParameter =
@"((?:([_a-zA-Z0-9]+)\s*\.\s*)?([_a-zA-Z0-9]+))\s+between\s+(\S+)\s+and\s+(\S+)";
Regex fieldBetweenParameterReg = new Regex(fieldBetweenParameter, RegexOptions.IgnoreCase);
mac = fieldBetweenParameterReg.Matches(sql);
mat = null;
if (mac.Count > 0)
{
for (int i = 0; i < mac.Count; i++)
{
mat = mac[i];
if (mat.Success)
{
varNameString = mat.Groups[4].Value;
varNameMat = varNameReg.Match(varNameString);
if (varNameMat.Success)
{
par = new SqlVarParameter();
par.fullName = mat.Groups[1].Value;
par.tableName = mat.Groups[2].Value;
par.columnName = mat.Groups[3].Value;
par.sql = mat.Value;
par.name = varNameMat.Groups[1].Value;
parameters.Add(par);
}
varNameString = mat.Groups[5].Value;
varNameMat = varNameReg.Match(varNameString);
if (varNameMat.Success)
{
par = new SqlVarParameter();
par.fullName = mat.Groups[1].Value;
par.tableName = mat.Groups[2].Value;
par.columnName = mat.Groups[3].Value;
par.sql = mat.Value;
par.name = varNameMat.Groups[1].Value;
parameters.Add(par);
}
}
}
}
return(parameters);
}
public List <SqlVarParameter> GetParameters(DB.Coding.DataUtility dataUtility, SelectStatementInfo selectStatement, ref List <SqlVarParameter> allSqlVarParameters)
{
List <SqlVarParameter> sqlVarParameters = ParseVarName(selectStatement.sqlWithOutSubSelect);
if (sqlVarParameters.Count > 0)
{
string sqlParametersSelect = "select ";
for (int i = 0; i < sqlVarParameters.Count; i++)
{
if (i != 0)
{
sqlParametersSelect += ",";
}
sqlParametersSelect += sqlVarParameters[i].fullName;
}
sqlParametersSelect += " from " + selectStatement.fromClause + " where 1>2 ";
Regex fromReg = new Regex(@"\s+from\s+", RegexOptions.IgnoreCase);
System.Data.Common.DbCommand cmd = dataUtility.GetDbCommand(FormatSql(sqlParametersSelect), dataUtility.con);
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
dt = reader.GetSchemaTable();
reader.Read();
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlVarParameter parameter = sqlVarParameters[i];
DB.Coding.Field field = new DB.Coding.Field();
field.name = dt.Rows[i]["BaseColumnName"].ToString();
field.structFieldName = dt.Rows[i]["ColumnName"].ToString();
field.allowNull = Convert.ToBoolean(dt.Rows[i]["AllowDBNull"]);
field.csTypeLink = dataUtility.csTypeDic[dt.Rows[i]["DataType"].ToString().Split('.')[1]];
if (dataUtility.dbType == DB.DBType.Sqlite)
{
field.sqlType = dt.Rows[i]["DataTypeName"].ToString();
}
else
{
field.sqlType = reader.GetDataTypeName(i);
}
field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
field.length = Convert.ToInt32(dt.Rows[i]["ColumnSize"]);
field.localType = dt.Rows[i]["DataType"].ToString();
field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]);
field.position = Convert.ToInt32(dt.Rows[i]["ColumnOrdinal"]);
field.dbType = dataUtility.GetDbType(Convert.ToInt32(dt.Rows[i]["ProviderType"]));
//field.hasDefault = dt.Rows[i]["DefaultValue"] != null;
//field.defautlValue = field.hasDefault ? dt.Rows[i]["DefaultValue"].ToString() : null;
if (!string.IsNullOrEmpty(field.sqlType))
{
if (dataUtility.typeDic.ContainsKey(field.sqlType.ToLower()))
{
field.simpleType = dataUtility.typeDic[field.sqlType.ToLower()].simpleType;
}
else
{
field.simpleType = "String";
}
}
else
{
field.simpleType = "";
}
parameter.field = field;
allSqlVarParameters.Add(parameter);
}
reader.Close();
}
//分析子查询里的参数
for (int i = 0; i < selectStatement.selects.Count; i++)
{
GetParameters(dataUtility, selectStatement.selects[i], ref allSqlVarParameters);
}
return(sqlVarParameters);
}