private TableSchema GetTable(string tableName, NpgsqlConnection conn, NpgsqlConnection pkConn, Dictionary<string,ForeignKeyInfo> fkOneTable) { string[] restrictions = new string[4]; restrictions[0] = conn.Database; restrictions[1] = "public"; restrictions[2] = tableName; DataTable tt = conn.GetSchema("Tables", restrictions); if (tt.Rows.Count == 0) return null; TableSchema tableSchema = new PostgresTableSchema(); tableSchema.Name = tt.Rows[0]["TABLE_NAME"].ToString(); restrictions = new string[4]; restrictions[0] = conn.Database; restrictions[1] = "public"; restrictions[2] = tableSchema.Name; DataTable ff = conn.GetSchema("Columns", restrictions); restrictions[2] = tableSchema.Name; //if (pkConn.State == ConnectionState.Closed) //{ // pkConn.Open(); //} //List<string> pkColumns = new List<string>(); //string[] parameters = new string[] { conn.Database, "public", tableSchema.Name }; //DataTable pkTable = pkConn.GetSchema("Index", parameters); ////pkConn.Close(); //foreach (DataRow indexRow in pkTable.Rows) //{ // pkColumns.Add(indexRow["column_name"].ToString()); //} string sql = string.Format(@" select a.attname as column_name, (case when atttypmod-4>0 then atttypmod-4 else 0 end)CHARACTER_MAXIMUM_LENGTH, (case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as P, (case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as U, (case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as R, (case when a.attnotnull=true then 'Y' else 'N' end) as nullable, col_description(a.attrelid,a.attnum) as comment,'XEditText' as control, c.relname,a.attname as column_name, (case when a.attnotnull=true then 'Y' else 'N' end) as IS_NULLABLE,format_type(a.atttypid,a.atttypmod) as data_type,0 as NUMERIC_PRECISION,0 as NUMERIC_SCALE,0 as CHARACTER_OCTET_LENGTH, '' as Value from pg_attribute a inner join pg_class c on a.attrelid = c.oid where c.relname ='{0}' and a.attstattarget = -1", tableName); DataSet ds = PostgresSqlHelper.ExecuteDataset(conn, CommandType.Text, sql); Dictionary<string, bool> dict_IsIdentity = new Dictionary<string, bool>(); Dictionary<string,string> dict_Description = new Dictionary<string,string>(); List<string> pkColumns = new List<string>(); foreach (DataRow row in ds.Tables[0].Rows) { dict_IsIdentity.Add((string)row["COLUMN_NAME"], false); if (row["COMMENT"] != DBNull.Value) { dict_Description.Add((string)row["COLUMN_NAME"], (string)row["COMMENT"]); } if (row["P"] != DBNull.Value && row["P"].ToString() == "Y") { pkColumns.Add((string)row["COLUMN_NAME"]); } } foreach (DataRow fRow in ff.Rows) { string name = fRow["COLUMN_NAME"].ToString(); bool allowDBNull = fRow["IS_NULLABLE"].ToString().Equals("NO") ? false : true; string dataType = fRow["DATA_TYPE"].ToString(); string nativeType = fRow["DATA_TYPE"].ToString(); byte precision = 0; byte.TryParse(fRow["NUMERIC_PRECISION"].ToString(), out precision); int scale = 0; int.TryParse(fRow["NUMERIC_SCALE"].ToString(), out scale); int size = 0; int.TryParse(fRow["CHARACTER_MAXIMUM_LENGTH"].ToString(), out size); if (size == 0) { int.TryParse(fRow["CHARACTER_OCTET_LENGTH"].ToString(), out size); } bool isPk = pkColumns.Contains(name); PostgresColumnSchema columnSchema = new PostgresColumnSchema( isPk, name, dataType, nativeType, size, precision, scale, allowDBNull); tableSchema.Columns.Add(columnSchema); columnSchema.IsIdent = fRow["column_default"] != DBNull.Value ? fRow["column_default"].ToString().StartsWith("nextval('") : false; columnSchema.Description = dict_Description.ContainsKey(name) ? dict_Description[name] : ""; if (fkOneTable != null) { if (fkOneTable.ContainsKey(columnSchema.Name)) { ForeignKeyInfo fkInfo = fkOneTable[columnSchema.Name]; columnSchema.ForeignKeyTable = fkInfo.FK_Table; columnSchema.ForeignKeyColumn = fkInfo.FK_Column; } } } //获得外键 //DataTable fk = conn.GetSchema("ForeignKeys", restrictions); //foreach (DataRow fkRow in fk.Rows) //{ // fk.WriteXml("1.xml"); //} return tableSchema; }
private TableSchema GetTable(string tableName, NpgsqlConnection conn, NpgsqlConnection pkConn, Dictionary <string, ForeignKeyInfo> fkOneTable) { string[] restrictions = new string[4]; restrictions[0] = conn.Database; restrictions[1] = "public"; restrictions[2] = tableName; DataTable tt = conn.GetSchema("Tables", restrictions); if (tt.Rows.Count == 0) { return(null); } TableSchema tableSchema = new PostgresTableSchema(); tableSchema.Name = tt.Rows[0]["TABLE_NAME"].ToString(); restrictions = new string[4]; restrictions[0] = conn.Database; restrictions[1] = "public"; restrictions[2] = tableSchema.Name; DataTable ff = conn.GetSchema("Columns", restrictions); restrictions[2] = tableSchema.Name; //if (pkConn.State == ConnectionState.Closed) //{ // pkConn.Open(); //} //List<string> pkColumns = new List<string>(); //string[] parameters = new string[] { conn.Database, "public", tableSchema.Name }; //DataTable pkTable = pkConn.GetSchema("Index", parameters); ////pkConn.Close(); //foreach (DataRow indexRow in pkTable.Rows) //{ // pkColumns.Add(indexRow["column_name"].ToString()); //} string sql = string.Format(@" select a.attname as column_name, (case when atttypmod-4>0 then atttypmod-4 else 0 end)CHARACTER_MAXIMUM_LENGTH, (case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as P, (case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as U, (case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as R, (case when a.attnotnull=true then 'Y' else 'N' end) as nullable, col_description(a.attrelid,a.attnum) as comment,'XEditText' as control, c.relname,a.attname as column_name, (case when a.attnotnull=true then 'Y' else 'N' end) as IS_NULLABLE,format_type(a.atttypid,a.atttypmod) as data_type,0 as NUMERIC_PRECISION,0 as NUMERIC_SCALE,0 as CHARACTER_OCTET_LENGTH, '' as Value from pg_attribute a inner join pg_class c on a.attrelid = c.oid where c.relname ='{0}' and a.attstattarget = -1", tableName); DataSet ds = PostgresSqlHelper.ExecuteDataset(conn, CommandType.Text, sql); Dictionary <string, bool> dict_IsIdentity = new Dictionary <string, bool>(); Dictionary <string, string> dict_Description = new Dictionary <string, string>(); List <string> pkColumns = new List <string>(); foreach (DataRow row in ds.Tables[0].Rows) { dict_IsIdentity.Add((string)row["COLUMN_NAME"], false); if (row["COMMENT"] != DBNull.Value) { dict_Description.Add((string)row["COLUMN_NAME"], (string)row["COMMENT"]); } if (row["P"] != DBNull.Value && row["P"].ToString() == "Y") { pkColumns.Add((string)row["COLUMN_NAME"]); } } foreach (DataRow fRow in ff.Rows) { string name = fRow["COLUMN_NAME"].ToString(); bool allowDBNull = fRow["IS_NULLABLE"].ToString().Equals("NO") ? false : true; string dataType = fRow["DATA_TYPE"].ToString(); string nativeType = fRow["DATA_TYPE"].ToString(); byte precision = 0; byte.TryParse(fRow["NUMERIC_PRECISION"].ToString(), out precision); int scale = 0; int.TryParse(fRow["NUMERIC_SCALE"].ToString(), out scale); int size = 0; int.TryParse(fRow["CHARACTER_MAXIMUM_LENGTH"].ToString(), out size); if (size == 0) { int.TryParse(fRow["CHARACTER_OCTET_LENGTH"].ToString(), out size); } bool isPk = pkColumns.Contains(name); PostgresColumnSchema columnSchema = new PostgresColumnSchema( isPk, name, dataType, nativeType, size, precision, scale, allowDBNull); tableSchema.Columns.Add(columnSchema); columnSchema.IsIdent = fRow["column_default"] != DBNull.Value ? fRow["column_default"].ToString().StartsWith("nextval('") : false; columnSchema.Description = dict_Description.ContainsKey(name) ? dict_Description[name] : ""; if (fkOneTable != null) { if (fkOneTable.ContainsKey(columnSchema.Name)) { ForeignKeyInfo fkInfo = fkOneTable[columnSchema.Name]; columnSchema.ForeignKeyTable = fkInfo.FK_Table; columnSchema.ForeignKeyColumn = fkInfo.FK_Column; } } } //获得外键 //DataTable fk = conn.GetSchema("ForeignKeys", restrictions); //foreach (DataRow fkRow in fk.Rows) //{ // fk.WriteXml("1.xml"); //} return(tableSchema); }