/// <summary>
/// 复杂SQL解析,解析出所有的列
/// </summary>
/// <param name="functionData"></param>
/// <returns></returns>
public DbFunctionData GetFunctionData(DbFunctionData functionData)
{
dataUtility.con.Open();
SelectStatementInfo selectStatement = new SelectStatementInfo(sqlInfo.sql);
ParseSQL(ref selectStatement);
functionData.fields = GetFields(dataUtility, selectStatement);
functionData.sqlVarParameters = new List <SqlVarParameter>();
GetParameters(dataUtility, selectStatement, ref functionData.sqlVarParameters);
dataUtility.con.Close();
//转换为可执行的SQL
functionData.sql = FormatSql(functionData.sql);
return(functionData);
}
/// <summary>
/// 分析代码
/// </summary>
/// <param name="csharpCode">代码</param>
/// <param name="index">代码开始的位置</param>
/// <returns></returns>
public List <DbFunctionData> Compile(string csharpCode)
{
List <DbFunctionData> dbFunctionDatas = new List <DbFunctionData>();
DbFunctionData data;
MatchCollection macDbFunctions = dbFuncitonRegex.Matches(csharpCode);
Match matDbFunction = null;
if (macDbFunctions.Count > 0)
{
for (int i = 0; i < macDbFunctions.Count; i++)
{
matDbFunction = macDbFunctions[i];
if (matDbFunction.Success)
{
data = new DbFunctionData();
data.expression = matDbFunction.Groups[0].Value;
data.type = matDbFunction.Groups[1].Value;
data.varName = matDbFunction.Groups[2].Value;
data.connectionName = matDbFunction.Groups[3].Value;
data.functionName = matDbFunction.Groups[4].Value;
data.sql = matDbFunction.Groups[5].Value;
if (matDbFunction.Groups[6].Success)
{
data.parameters = new string[2];
//select sql
data.parameters[0] = matDbFunction.Groups[5].Value;
//pageSize
data.parameters[1] = matDbFunction.Groups[6].Value;
}
else
{
data.parameters = new string[1];
data.parameters[0] = matDbFunction.Groups[5].Value;
}
data.convertMethodName = matDbFunction.Groups[7].Value;
data.index = matDbFunction.Index;
data.length = matDbFunction.Length;
dbFunctionDatas.Add(data);
}
}
}
return(dbFunctionDatas);
}
/// <summary>
/// 数据库函数替换类
/// </summary>
/// <param name="methodName">函数名</param>
/// <param name="dbFunctionData">函数信息</param>
/// <param name="appRoot">网站根目录</param>
/// <returns></returns>
public string SetMagicStruct(string methodName, DbFunctionData dbFunctionData, List <NFinal.Compile.StructField> structFieldList, string appRoot)
{
string result = null;
if (dbFunctionData.functionName == "QueryAll" || dbFunctionData.functionName == "QueryRow" ||
dbFunctionData.functionName == "Page" || dbFunctionData.functionName == "QueryRandom" ||
dbFunctionData.functionName == "QueryTop")
{
NFinal.DB.ConnectionString connectionString = null;
connectionString = GetConnectionString(dbFunctionData.connectionName);
string type = connectionString.type.ToString();
string fileName = appRoot + "NFinal\\SqlTemplate\\" +
type +
"\\Struct.txt";
VTemplate.Engine.TemplateDocument doc = new VTemplate.Engine.TemplateDocument(fileName, System.Text.Encoding.UTF8);
doc.SetValue("functionName", methodName);
doc.SetValue("varName", dbFunctionData.varName);
doc.SetValue("dbType", type);
doc.SetValue("fields", dbFunctionData.fields);
doc.SetValue("structFields", structFieldList);
result = doc.GetRenderText();
}
return(result);
}
/// <summary>
/// 把数据库信息填入从Csharp分析出的函数信息中
/// </summary>
/// <param name="DbStore">数据库表信息</param>
/// <param name="functionData">从Csharp中分析出的函数信息</param>
/// <returns>完整的函数信息</returns>
public DbFunctionData FillFunctionData(Dictionary <string, NFinal.DB.Coding.DataUtility> DbStore, DbFunctionData functionData)
{
//查找出sql语句中用的是哪个数据库
NFinal.DB.Coding.DataUtility dataUtility = NFinal.DB.Coding.DB.DbStore[functionData.connectionName];
SqlParser sqlParser = new SqlParser(dataUtility);
SqlStatement sqlSatement = new SqlStatement("", dataUtility.dbType);
string sql = sqlSatement.GetSql(functionData.parameters[0]);
//转换子查询
functionData.sql = sqlSatement.GetIdInSql(sql);
//所@符号统一转为?或:号
functionData.sql = sqlSatement.FormatSql(functionData.sql);
List <SqlInfo> sqlInfos = sqlParser.Parse(sql);
SqlInfo sqlInfo = null;
functionData.hasSqlError = false;
bool hasTable = false;
bool hasField = false;
if (sqlInfos.Count > 0)
{
sqlInfo = sqlInfos[0];
//分析表名,查看此表名是否存在
if (sqlInfo.Tables.Count > 0)
{
functionData.tables = new List <DB.Coding.Table>();
DB.Coding.Table table = new DB.Coding.Table();
//循环sql语句中的表名
for (int i = 0; i < sqlInfo.Tables.Count; i++)
{
hasTable = false;
//循环数据库中的表名
for (int j = 0; j < dataUtility.tables.Count; j++)
{
//如果表名存在,则添加表
if (sqlInfo.Tables[i].name == dataUtility.tables[j].name)
{
functionData.tables.Add(dataUtility.tables[j]);
hasTable = true;
break;
}
}
//如果表名不存在,则证明sql语句有错误.
if (!hasTable)
{
functionData.hasSqlError = true;
functionData.sqlError = "表" + sqlInfo.Tables[i].name + "不存在!";
return(functionData);
}
}
}
}
//分析sql中的参数信息
functionData.sqlVarParameters = FillParameter(dataUtility.csTypeDic, functionData.tables, sqlInfo.sqlVarParameters);
//分析列信息
if (sqlInfos.Count > 0)
{
sqlInfo = sqlInfos[0];
if (sqlInfo.Columns.Count > 0)
{
functionData.fields = new List <DB.Coding.Field>();
DB.Coding.Field field = new DB.Coding.Field();
DB.Coding.Table table = new DB.Coding.Table();
for (int i = 0; i < sqlInfo.Columns.Count; i++)
{
//如果列中有表名
if (sqlInfo.Columns[i].tableName != string.Empty)
{
hasTable = false;
//查看表名是否存在于sql语句的表中,如果不存在,则视为出错
for (int j = 0; j < functionData.tables.Count; j++)
{
if (functionData.tables[j].name == sqlInfo.Columns[i].tableName)
{
table = functionData.tables[j];
hasTable = true;
break;
}
}
if (!hasTable)
{
functionData.hasSqlError = true;
functionData.sqlError = "表" + sqlInfo.Columns[i].tableName + "不存在";
return(functionData);
}
else
{
//查看是否使用了table.*
if (sqlInfo.Columns[i].name == "*")
{
//functionData.fields.AddRange(table.fields);
for (int k = 0; k < table.fields.Count; k++)
{
table.fields[k].structFieldName = table.fields[k].name;
functionData.fields.Add(table.fields[k]);
}
}
//如果使用了table.field
else
{
hasField = false;
//查看该field是否存在
for (int k = 0; k < table.fields.Count; k++)
{
if (sqlInfo.Columns[i].name == table.fields[k].name)
{
//查看是否有AsName
if (string.IsNullOrEmpty(sqlInfo.Columns[i].asName))
{
table.fields[k].structFieldName = sqlInfo.Columns[i].name;
}
else
{
table.fields[k].structFieldName = sqlInfo.Columns[i].asName;
}
functionData.fields.Add(table.fields[k]);
hasField = true;
}
}
if (!hasField)
{
functionData.hasSqlError = true;
functionData.sqlError = "在表" + table.name + "中,列" + sqlInfo.Columns[i].name + "不存在";
return(functionData);
}
}
}
}
//如果列中没有表名
else
{
hasField = false;
if (sqlInfo.Columns[i].name == "*")
{
for (int j = 0; j < functionData.tables.Count; j++)
{
for (int k = 0; k < functionData.tables[j].fields.Count; k++)
{
functionData.tables[j].fields[k].structFieldName = functionData.tables[j].fields[k].name;
functionData.fields.Add(functionData.tables[j].fields[k]);
}
}
}
else
{
//循环sql语句中所有表的所有列
for (int j = 0; j < functionData.tables.Count; j++)
{
for (int k = 0; k < functionData.tables[j].fields.Count; k++)
{
if (sqlInfo.Columns[i].name == functionData.tables[j].fields[k].name)
{
//查看是否有AsName
if (string.IsNullOrEmpty(sqlInfo.Columns[i].asName))
{
functionData.tables[j].fields[k].structFieldName = sqlInfo.Columns[i].name;
}
else
{
functionData.tables[j].fields[k].structFieldName = sqlInfo.Columns[i].asName;
}
functionData.fields.Add(functionData.tables[j].fields[k]);
hasField = true;
}
}
}
if (!hasField)
{
functionData.hasSqlError = true;
functionData.sqlError = "列" + sqlInfo.Columns[i].name + "不存在";
return(functionData);
}
}
}
}
}
}
return(functionData);
}
