C# DbConnection.GetSchema方法代码示例

    internal Table(string tableName, DbConnection connection, TableDesignerDoc owner)
    {
      _owner = owner;
      _oldname = tableName;
      _connection = connection;
      _name = tableName;
      _owner.Name = _name;
      _catalog = _connection.Database; // main

      ReloadDefinition();

      if (_key == null) _key = new PrimaryKey(_connection, this, null);

      if (_exists)
      {
        using (DataTable tbl = connection.GetSchema("ForeignKeys", new string[] { Catalog, null, Name }))
        {
          foreach (DataRow row in tbl.Rows)
          {
            _fkeys.Add(new ForeignKey(connection, this, row));
            _oldfkeys.Add(new ForeignKey(connection, this, row));
          }
        }
      }

      using (DataTable tbl = connection.GetSchema("Columns", new string[] { Catalog, null, Name }))
      {
        foreach (DataRow row in tbl.Rows)
        {
          _columns.Add(new Column(row, this));
        }
      }
    }

        private void BindGrid(DbConnection conn)
        {
            conn.Open();
                _schema = new DataSet();
                var schema = conn.GetSchema();

                foreach (DataRow dataRow in schema.Rows)
                {
                    var tableName = dataRow["CollectionName"].ToString();
                    if(!_schema.Tables.Contains(tableName))
                    {
                        var dt = conn.GetSchema(tableName);
                        dt.TableName = tableName;
                        _schema.Tables.Add(dt);
                    }
                }
                conn.Close();
                dgSchema.DataSource = _schema.Tables[0];

                cbTable.DataSource = _schema.Tables[0];
                cbTable.DisplayMember = "CollectionName";
                cbTable.ValueMember = "CollectionName";
                _previousWidth = dgSchema.Width;
                _previousState = WindowState;
        }

 protected override DataTable StoredProcedureArguments(string storedProcedureName, DbConnection connection)
 {
     //it's not reported in the MetaDataCollections, but it is there.
     var dt = connection.GetSchema("PROCEDUREPARAMETERS", new[] { null, null, storedProcedureName, null });
     dt.TableName = ProcedureParametersCollectionName;
     return dt;
 }

        // SchemaReader.ReadSchema
        public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
        {
            var result = new Tables();


            var cmd = factory.CreateCommand();
            cmd.Connection = connection;
            cmd.CommandText = TABLE_SQL;

            //pull the tables in a reader
            using (cmd)
            {
                using (var rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Table tbl = new Table();
                        tbl.Name = rdr["TABLE_NAME"].ToString();
                        tbl.Schema = rdr["TABLE_SCHEMA"].ToString();
                        tbl.IsView = String.Compare(rdr["TABLE_TYPE"].ToString(), "View", true) == 0;
                        tbl.CleanName = CleanUp(tbl.Name);
                        tbl.ClassName = Inflector.MakeSingular(tbl.CleanName);
                        result.Add(tbl);
                    }
                }
            }

            //this will return everything for the DB
            var schema = connection.GetSchema("COLUMNS");

            //loop again - but this time pull by table name
            foreach (var item in result)
            {
                item.Columns = new List<Column>();

                //pull the columns from the schema
                var columns = schema.Select("TABLE_NAME='" + item.Name + "'");
                foreach (var row in columns)
                {
                    var type = GetPropertyType(row);
                    Column col = new Column();
                    col.Name = row["COLUMN_NAME"].ToString();
                    col.PropertyName = CleanUp(col.Name);
                    col.PropertyType = type;
                    col.CustomType = type == null 
                        ? row["DATA_TYPE"].ToString().ToLowerInvariant() 
                        : null;
                    col.Size = GetDatatypeSize(row["DATA_TYPE"].ToString());
                    col.Precision = GetDatatypePrecision(row["DATA_TYPE"].ToString());
                    col.IsNullable = row["IS_NULLABLE"].ToString() == "YES";
                    col.IsPrimaryKey = row["COLUMN_KEY"].ToString() == "PRI";
                    col.IsAutoIncrement = row["extra"].ToString().ToLower().IndexOf("auto_increment") >= 0;

                    item.Columns.Add(col);
                }
            }

            return result;

        }

		ExamineDatabaseDialog(DbConnection dbConnection)
		{
			this.dbConnection = dbConnection;
			InitializeComponent();

			Collections = new ObservableCollection<string>(dbConnection.GetSchema().AsEnumerable().Select(row => row["CollectionName"]).Cast<string>());
		}

        protected override void LoadTables(DbConnection Connection, Dictionary<string, TableInfo> Tables)
        {
            base.LoadTables(Connection, Tables);

            var dt = Connection.GetSchema("IndexColumns");
            foreach (DataRow dr in dt.Rows)
                if (Convert.ToInt32(dr[8]) == 56)
                    Tables[dr[5] as string].Columns[dr[6] as string].IsKey = true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        public DAODatabaseHelper(DbConnection connection)
        {
            _connection = connection;
            DbTypesConverter.ProviderTypeMap = BuildProviderTypeMap();

            _tableDefs = new TableDefsHelper(connection);
            DataTable dbTables = _connection.GetSchema("Tables");
            foreach (DataRow r in dbTables.Rows)
            {
                TableDefHelper tableDef = new TableDefHelper(r["TABLE_NAME"].ToString(), r["TABLE_NAME"].ToString(), true);
                AddColumnsToTableDef(tableDef);
                AddIndexesToTableDef(tableDef);
                _tableDefs.Add(tableDef,false);
            }
        }

        public string GetSQLCreateColumnsInTable(DbConnection dbConnection, string tableName)
        {
            string sqlColumns = "";
            string convertedColumnName;

            DataTable dataTable = dbConnection.GetSchema("COLUMNS", new string[] { null, null, tableName, null });

            foreach (DataRow dataRow in dataTable.Rows)
            {
                convertedColumnName = convertName.Column(tableName, dataRow["COLUMN_NAME"].ToString());
                sqlColumns += GetSQLColumn(convertedColumnName, dataRow);
            }

            sqlColumns = sqlColumns.Remove(sqlColumns.Length - 1);

            return string.Format("({0})", sqlColumns);
        }

        private static string EnsureTableName(DbConnection connection, string tabName)
        {
            if (string.IsNullOrEmpty(tabName))
            {
                DataTable worksheets = connection.GetSchema("Tables");
                foreach (DataRow row in worksheets.Rows)
                {
                    tabName = (string)row["TABLE_NAME"];

                    if (tabName.EndsWith("$") || tabName.EndsWith("$'"))
                        return tabName;
                }
            }
            else if (!tabName.EndsWith("$"))
                tabName += "$";

            return tabName;
        }

        internal View(string viewName, DbConnection connection, ViewDesignerDoc parent)
        {
            _owner = parent;
              _name = viewName;
              _oldname = viewName;
              _catalog = connection.Database;
              _connection = connection;
              _owner.Name = _name;

              if (String.IsNullOrEmpty(viewName) == false)
              {
            using (DataTable tbl = connection.GetSchema("Views", new string[] { Catalog, null, Name }))
            {
              if (tbl.Rows.Count > 0)
              {
            _sql = tbl.Rows[0]["VIEW_DEFINITION"].ToString();

            StringBuilder builder = new StringBuilder();
            builder.Append(_sql);
            builder.AppendLine(";");

            _triggers.Clear();
            _oldtriggers.Clear();

            using (DataTable ttbl = _connection.GetSchema("Triggers", new string[] { Catalog, null, Name }))
            {
              foreach (DataRow row in ttbl.Rows)
              {
                ViewTrigger t = new ViewTrigger(this, row);
                _triggers.Add(t);
                _oldtriggers.Add(((ICloneable)t).Clone() as ViewTrigger);

                builder.AppendFormat("{0};\r\n", t.OriginalSql);
              }
            }
            _oldsql = builder.ToString();
              }
              else
              {
            _oldname = null;
              }
            }
              }
        }

 /// <summary>
 /// Extracts the command information from the command object and add specific information based on the factory being use.
 /// </summary>
 /// <param name="connection">The connection to extract the information from.</param>
 /// <param name="pivotCommand">Command to be processed.</param>
 private static void DeriveParametersFromProcedureCode(DbConnection connection, DbCommand pivotCommand)
 {
     DataTable dbObjects = connection.GetSchema("Procedures", new String[] { null, null, pivotCommand.CommandText });
     if (dbObjects.Rows.Count > 0)
     {
         String procText = dbObjects.Rows[0]["PROCEDURE_DEFINITION"].ToString();
         String[] procLines = procText.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
         if ((procLines.Length > 0) && procLines[0].StartsWith("PARAMETERS", StringComparison.InvariantCultureIgnoreCase))
         {
             procLines = procLines[0].ToUpper().Replace("PARAMETERS", "").Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
             foreach (String paraminfo in procLines)
             {
                 string[] param = paraminfo.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                 DbParameter parameter = pivotCommand.CreateParameter();
                 parameter.ParameterName = param[0];
                 parameter.DbType = getDbType(param[1]);
                 pivotCommand.Parameters.Add(parameter);
             }
         }
     }
 }

 /// <summary>
 /// Get all the stored procedures (owner required for Oracle- otherwise null).
 /// </summary>
 /// <param name="connection">The connection.</param>
 /// <returns></returns>
 protected virtual DataTable StoredProcedures(DbConnection connection)
 {
     string collectionName = ProceduresCollectionName;
     if (!SchemaCollectionExists(connection, collectionName)) return CreateDataTable(collectionName);
     string[] restrictions = SchemaRestrictions.ForOwner(connection, collectionName);
     return connection.GetSchema(collectionName, restrictions);
 }

		private static List<IndexSchema> GetIndexes(DbConnection con, TableSchema eTable)
		{
			var aStore = new List<IndexSchema>();

			string[] restrict3 = {null, null, eTable.Name};
			string[] restrict4 = {null, null, eTable.Name, null};

			// INDEX_TYPE = 0 - ascending, 1 - descending
			restrict4[0] = null;
			restrict4[1] = null;
			restrict4[2] = eTable.Name;
			restrict4[3] = null;
			var dtShema = con.GetSchema("Indexes", restrict3);
			aStore.Clear();
			for (var x = 0; x < dtShema.Rows.Count; x++)
			{
				var cRow = dtShema.Rows[x];
				var cName = cRow["INDEX_NAME"].ToString();
				if (eTable.IsKeyExist(cName, ConstraintType.Unique) ||
					eTable.IsKeyExist(cName, ConstraintType.KeyPrimary) ||
					eTable.IsKeyExist(cName, ConstraintType.KeyForeign))
					continue;

				var eIndex = new IndexSchema();
				var columns = "";
				eIndex.Name = cName;
				eIndex.Unique = Convert.ToBoolean(cRow["IS_UNIQUE"], CultureInfo.InvariantCulture);
				if (cRow["INDEX_TYPE"] == DBNull.Value)
					eIndex.Sort = SortOrder.Ascending;
				else
					eIndex.Sort = Convert.ToInt32(cRow["INDEX_TYPE"], CultureInfo.InvariantCulture) == 0
									? SortOrder.Ascending
									: SortOrder.Descending;
				eIndex.IsActive = !Convert.ToBoolean(cRow["IS_INACTIVE"], CultureInfo.InvariantCulture);

				restrict4[3] = cName;
				var dtShemaCols = con.GetSchema("IndexColumns", restrict4);
				var dtv = dtShemaCols.DefaultView;
				//dtv.RowFilter = "INDEX_NAME = '" + cName + "'";
				dtv.Sort = "ORDINAL_POSITION ASC";
				for (var y = 0; y < dtv.Count; y++)
					columns += (dtv[y]["COLUMN_NAME"] + ", ");
				columns = columns.Remove(columns.Length - 2, 2);
				eIndex.Columns = columns;
				aStore.Add(eIndex);
			}
			return aStore;
		}

        private void CreateRestrictions(DbConnection connection)
        {
            _restrictions = new DataTable("Restrictions");
            _restrictions.Locale = CultureInfo.InvariantCulture;
            _restrictions.Columns.Add("CollectionName", typeof(string));
            _restrictions.Columns.Add("RestrictionNumber", typeof(int));
            _restrictions.Columns.Add("RestrictionName", typeof(string));

            try
            {
                var collections = connection.GetSchema(DbMetaDataCollectionNames.MetaDataCollections);
                foreach (DataRow row in collections.Rows)
                {
                    //every collections has catalog/ owner/ table restrictions
                    _restrictions.Rows.Add(row["CollectionName"].ToString(), 0, "OWNER");
                    _restrictions.Rows.Add(row["CollectionName"].ToString(), 1, "NA");
                    _restrictions.Rows.Add(row["CollectionName"].ToString(), 2, "TABLE");
                }
            }
            catch (NullReferenceException)
            {
                Console.WriteLine("Could not load metadataCollections"); //NpgSql again
            }
        }

 private static DataTable MetadataCollections(DbConnection connection)
 {
     try
     {
         return connection.GetSchema(DbMetaDataCollectionNames.MetaDataCollections);
     }
     catch (Exception e)
     {
         //some versions of NpgSql may throw a NullReferenceException
         Console.WriteLine(e);
         var metadataCollections = CreateDataTable("Metadata");
         metadataCollections.Columns.Add("CollectionName", typeof(string));
         return metadataCollections;
     }
 }