public List <DB.Coding.Field> GetFields(DB.Coding.DataUtility dataUtility, SelectStatementInfo selectStatement)
{
List <DB.Coding.Field> fields = new List <DB.Coding.Field>();
string selectFiledsSql = string.Format("select {0} from {1} where 1>2", selectStatement.selectClause, selectStatement.fromClause);
System.Data.Common.DbCommand cmd = dataUtility.GetDbCommand(FormatSql(selectFiledsSql), dataUtility.con);
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
dt = reader.GetSchemaTable();
reader.Read();
Regex nameReg = new Regex(@"^[_0-9a-zA-Z]+$");
for (int i = 0; i < dt.Rows.Count; i++)
{
DB.Coding.Field field = new DB.Coding.Field();
field.name = dt.Rows[i]["BaseColumnName"].ToString();
field.structFieldName = dt.Rows[i]["ColumnName"].ToString();
//查看名称是否合法,不合法则重命名.
if (!nameReg.IsMatch(field.name))
{
field.name = "_column" + i.ToString();
}
if (!nameReg.IsMatch(field.structFieldName))
{
field.structFieldName = "_column" + i.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
{
switch (field.localType)
{
case "System.Int16": field.simpleType = "Number"; break;
case "System.Int32": field.simpleType = "Number"; break;
case "System.Int64": field.simpleType = "Number"; break;
case "System.UInt16": field.simpleType = "Number"; break;
case "System.UInt32": field.simpleType = "Number"; break;
case "System.UInt64": field.simpleType = "Number"; break;
case "System.Byte": field.simpleType = "Number"; break;
case "System.SByte": field.simpleType = "Number"; break;
case "System.Single": field.simpleType = "Number"; break;
case "System.Decimal": field.simpleType = "Number"; break;
case "System.Double": field.simpleType = "Number"; break;
case "System.TimeSpan": field.simpleType = "Time"; break;
case "System.DateTime:": field.simpleType = "Time"; break;
default: field.simpleType = field.simpleType = "String"; break;
}
}
}
else
{
switch (field.localType)
{
case "System.Int16": field.simpleType = "Number"; break;
case "System.Int32": field.simpleType = "Number"; break;
case "System.Int64": field.simpleType = "Number"; break;
case "System.UInt16": field.simpleType = "Number"; break;
case "System.UInt32": field.simpleType = "Number"; break;
case "System.UInt64": field.simpleType = "Number"; break;
case "System.Byte": field.simpleType = "Number"; break;
case "System.SByte": field.simpleType = "Number"; break;
case "System.Single": field.simpleType = "Number"; break;
case "System.Decimal": field.simpleType = "Number"; break;
case "System.Double": field.simpleType = "Number"; break;
case "System.TimeSpan": field.simpleType = "Time"; break;
case "System.DateTime:": field.simpleType = "Time"; break;
default: field.simpleType = field.simpleType = "String"; break;
}
}
fields.Add(field);
}
reader.Close();
return(fields);
}
/// <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);
}
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);
}
