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