private TableDef GetTD_MySQL(string table) { string query = "SELECT COLUMN_NAME As field,ORDINAL_POSITION as ordinal, IS_NULLABLE, DATA_TYPE as DataType, CHARACTER_MAXIMUM_LENGTH as data_length,COLUMN_KEY as is_index,COLUMN_COMMENT as description,EXTRA\r\n\t\t\t\t\t\t\tFROM INFORMATION_SCHEMA.COLUMNS\r\n\t\t\t\t\t\t\twhere table_name = @name and table_schema = @schema group by COLUMN_NAME"; DbCommand cmd = (DbCommand)null; DbDataReader reader = (DbDataReader)null; TableDef tableDef = new TableDef(); tableDef.Name = table; List <FieldDef> fieldDefList = new List <FieldDef>(); try { cmd = DBConnectionFactory.GetMySqlCommand(query, this._dbconn); DBTools.AddDbParameter(ref cmd, "@name", (object)table); DBTools.AddDbParameter(ref cmd, "@schema", (object)this._dbconn.Database); reader = cmd.ExecuteReader(); while (reader.Read()) { FieldDef fieldDef = new FieldDef(); fieldDef.Name = DBTools.GetDBStringValue(reader, "field"); fieldDef.FieldLength = DBTools.GetDBIntValue(reader, "data_length"); string dbStringValue = DBTools.GetDBStringValue(reader, "description"); fieldDef.Description = string.IsNullOrEmpty(dbStringValue) ? DBHandler.NormalizeFieldName("_" + fieldDef.Name) : dbStringValue; try { fieldDef.DataType = DBTools.GetCSTypeFromDBType(DBTools.GetDBStringValue(reader, "DataType"), (double)fieldDef.FieldLength); } catch (Exception ex) { string message = ex.Message; } fieldDef.CanBeNull = DBTools.GetDBStringValue(reader, "is_nullable") == "YES"; fieldDef.IsPrimaryKey = DBTools.GetDBStringValue(reader, "is_index", (string)null) == "PRI"; fieldDefList.Add(fieldDef); } } catch (DbException ex) { throw ex; } finally { reader?.Close(); cmd?.Dispose(); } tableDef.Fields = fieldDefList.ToArray(); return(tableDef); }
private TableDef GetTD_PgSQL(string table) { string query = "SELECT\tpg_attribute.attnum as ordinal,\tpg_attribute.attname AS Field,\tpg_type.typname AS DataType,\tpg_attribute.atttypmod AS data_length,\tpg_attribute.attnotnull AS is_nullable,\tpg_index.indkey is_index\r\n\t\t\t\t\t\t\tFROM\tpg_class\r\n\t\t\t\t\t\t\tINNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid\r\n\t\t\t\t\t\t\tLEFT JOIN pg_index ON pg_index.indrelid = pg_attribute.attrelid and pg_index.indnatts = pg_attribute.attnum\r\n\t\t\t\t\t\t\tINNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid\r\n\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tpg_class.relname = @name\r\n\t\t\t\t\t\t\t\tand pg_attribute.attnum > 0\r\n\t\t\t\t\t\t\tORDER BY pg_attribute.attnum;"; DbCommand cmd = (DbCommand)null; DbDataReader reader = (DbDataReader)null; TableDef tableDef = new TableDef(); tableDef.Name = table; List <FieldDef> fieldDefList = new List <FieldDef>(); try { cmd = DBConnectionFactory.GetPgSqlCommand(query, this._dbconn); DBTools.AddDbParameter(ref cmd, "@name", (object)table); reader = cmd.ExecuteReader(); while (reader.Read()) { FieldDef fieldDef = new FieldDef(); fieldDef.Name = DBTools.GetDBStringValue(reader, "field"); fieldDef.FieldLength = DBTools.GetDBIntValue(reader, "data_length"); fieldDef.DataType = DBTools.GetCSTypeFromDBType(DBTools.GetDBStringValue(reader, "DataType"), (double)fieldDef.FieldLength); fieldDef.CanBeNull = DBTools.GetDBBoolValue(reader, "is_nullable"); fieldDef.IsPrimaryKey = DBTools.GetDBStringValue(reader, "is_index", (string)null) != null; fieldDefList.Add(fieldDef); } } catch (DbException ex) { throw ex; } finally { reader?.Close(); cmd?.Dispose(); } tableDef.Fields = fieldDefList.ToArray(); return(tableDef); }