C# NpgsqlConnection.GetSchema方法代码示例

        static void Main(string[] args)
        {
            var b = new NpgsqlConnectionStringBuilder() { Host = "milkyway", Port = 5433, Username = "neocortex", Password = "masterboot", Database = "db" };

            using (DbConnection conn = new NpgsqlConnection(b.ToString()))
            {
                conn.Open();

                var tables = new List<string>();
                var all = conn.GetSchema("Tables");
                foreach (DataRow r in all.Rows)
                {
                    //table_catalog //table_schema //table_name //table_type
                    var schema = r["table_schema"];
                    var table = r["table_name"];
                    var type = r["table_type"];

                    if ("kernel".Equals(schema.ToString()))
                        tables.Add(table.ToString());
                }

                foreach (var table in tables)
                {
                    Console.WriteLine("Table: " + table);

                    var tableSchema = conn.GetSchema("Columns", new string[] { null, null, table });
                    foreach (DataRow row in tableSchema.Rows)
                    {
                        Console.WriteLine("Column = {0}. Type = {1}. Default = {2}. Nullable = {3}. Text lenght = {4}. Numeric precision = {5}.",
                            row["column_name"],
                            row["data_type"],
                            row["column_default"],
                            row["is_nullable"],
                            row["character_maximum_length"],
                            row["numeric_precision"]);
                    }
                }

                /*var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = @"SELECT * FROM kernel.users;";

                using (var reader = cmd.ExecuteReader())
                    while (reader.Read())
                        Console.WriteLine(string.Format("id = {0}, user = {1};", reader.GetString(0), reader.GetString(1)));
                */

            }

            Console.ReadKey();
        }

        public override TableSchema[] GetTables(string connectionString, DatabaseSchema database)
        {
            List<TableSchema> tableSchemas = new List<TableSchema>();
            var oleConn = new NpgsqlConnection();
            oleConn.ConnectionString =  connectionString;

            using (var conn = new NpgsqlConnection(connectionString))
            {
                this.ConnectionString = connectionString;

                string[] restrictions = new string[4];
                restrictions[0] = conn.Database;
                restrictions[1] = "public";
                conn.ConnectionString = connectionString;
                conn.Open();

                //var s = conn.GetSchema("Tables",new string[] { conn.Database, "public", null, null });
                List<string> tableNames = new List<string>();
                Dictionary<string, Dictionary<string, ForeignKeyInfo>> fkMap = null;

                DataTable tt = conn.GetSchema("Tables", restrictions);
                foreach (DataRow tRow in tt.Rows)
                {
                    tableNames.Add(tRow[2].ToString());
                }

                fkMap = this.GetForeignKeys(
                       conn, tableNames);

                foreach (DataRow tRow in tt.Rows)
                {
                    var tableName = tRow[2].ToString();
                    tableSchemas.Add(this.GetTable(tableName, conn, oleConn, fkMap.ContainsKey(tableName) ? fkMap[tableName] : null));
                }
                oleConn.Close();
                //tt.WriteXml("1.xml");
            }
            return tableSchemas.ToArray();
        }

 public void GetSchema()
 {
     using (NpgsqlConnection c = new NpgsqlConnection())
     {
         DataTable metaDataCollections = c.GetSchema();
         Assert.IsTrue(metaDataCollections.Rows.Count > 0, "There should be one or more metadatacollections returned. No connectionstring is required.");
     }
 }

        private void LoadDatabases()
        {
            string[] databaseList = new string[] { };
            NpgsqlConnection cn = null;

            try
            {
                if ((_connStrBuilder != null) && !String.IsNullOrEmpty(_connStrBuilder.Host))
                {
                    cn = new NpgsqlConnection(_connStrBuilder.ConnectionString);
                    cn.Open();

                    DataTable databases = cn.GetSchema("Databases", null);
                    databaseList = (from r in databases.AsEnumerable()
                                    let dbName = r.Field<string>("database_name")
                                    where !dbName.ToLower().StartsWith("template")
                                    select dbName).OrderBy(t => t).ToArray();
                }
            }
            catch (Exception ex)
            {
                if (ex is NpgsqlException)
                    MessageBox.Show(ex.Message, "PostgreSQL Error", MessageBoxButton.OK, MessageBoxImage.Error);
                else
                    MessageBox.Show(ex.Message, "HBIC Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if ((cn != null) && (cn.State != ConnectionState.Closed)) cn.Close();

                _databases = databaseList;
                OnPropertyChanged("Databases");

                if (_databases.Length == 1) _connStrBuilder.Database = _databases[0];
                OnPropertyChanged("Database");
            }
        }

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

//.........这里部分代码省略.........

        public static bool DatabaseHelperTableExists(string tableName)
        {
            NpgsqlConnection connection = new NpgsqlConnection(ConnectionString.GetWriteConnectionString());
            string[] restrictions = new string[4];
            restrictions[2] = tableName;
            connection.Open();
            DataTable table = connection.GetSchema("Tables", restrictions);
            connection.Close();
            if (table != null)
            {
                return (table.Rows.Count > 0);
            }

            return false;
        }