Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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;
        }