SqlConnection.GetSchema 方法
定义
返回此 SqlConnection 的数据源的架构信息。Returns schema information for the data source of this SqlConnection.
重载
| GetSchema() |
返回此 SqlConnection 的数据源的架构信息。Returns schema information for the data source of this SqlConnection. 有关架构的详细信息,请参阅 SQL Server 架构集合。For more information about scheme, see SQL Server Schema Collections. |
| GetSchema(String) |
通过使用架构名称的指定字符串,返回此 SqlConnection 的数据源的架构信息。Returns schema information for the data source of this SqlConnection using the specified string for the schema name. |
| GetSchema(String, String[]) |
通过使用指定字符串作为架构名称,指定字符串数组作为限制值,返回此 SqlConnection 的数据源的架构信息。Returns schema information for the data source of this SqlConnection using the specified string for the schema name and the specified string array for the restriction values. |
注解
如果尝试检索多个版本控制存储过程的架构信息,则仅返回最新版本的架构。If you attempt to retrieve schema information for more than one versioned stored procedure, the schema for the latest one only is returned.
GetSchema()
返回此 SqlConnection 的数据源的架构信息。Returns schema information for the data source of this SqlConnection. 有关架构的详细信息,请参阅 SQL Server 架构集合。For more information about scheme, see SQL Server Schema Collections.
public:
override System::Data::DataTable ^ GetSchema();
public override System.Data.DataTable GetSchema ();
override this.GetSchema : unit -> System.Data.DataTable
Public Overrides Function GetSchema () As DataTable
返回
一个包含架构信息的 DataTable。A DataTable that contains schema information.
适用于
GetSchema(String)
通过使用架构名称的指定字符串,返回此 SqlConnection 的数据源的架构信息。Returns schema information for the data source of this SqlConnection using the specified string for the schema name.
public:
override System::Data::DataTable ^ GetSchema(System::String ^ collectionName);
public override System.Data.DataTable GetSchema (string collectionName);
override this.GetSchema : string -> System.Data.DataTable
Public Overrides Function GetSchema (collectionName As String) As DataTable
参数
- collectionName
- String
指定要返回的架构的名称。Specifies the name of the schema to return.
返回
一个包含架构信息的 DataTable。A DataTable that contains schema information.
例外
将 collectionName 指定为 null。collectionName is specified as null.
注解
可能需要数据库、表或列的架构信息。You may need the schema information of the database, tables or columns. 此示例:This sample:
使用 GetSchema 获取架构信息。Uses GetSchema to get schema information.
使用架构限制获取指定的信息。Use schema restrictions to get the specified information.
获取数据库、表和某些列的架构信息。Gets schema information of the database, tables, and some columns.
在运行该示例之前,需要使用以下 Transact-sql 创建示例数据库:Before you run the sample, you need to create the sample database, using the following Transact-SQL:
USE [master]
GO
CREATE DATABASE [MySchool]
GO
USE [MySchool]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course]([CourseID] [nvarchar](10) NOT NULL,
[Year] [smallint] NOT NULL,
[Title] [nvarchar](100) NOT NULL,
[Credits] [int] NOT NULL,
[DepartmentID] [int] NOT NULL,
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
[CourseID] ASC,
[Year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department]([DepartmentID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Budget] [money] NOT NULL,
[StartDate] [datetime] NOT NULL,
[Administrator] [int] NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1045', 2012, N'Calculus', 4, 7)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1061', 2012, N'Physics', 4, 1)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2021', 2012, N'Composition', 3, 2)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2042', 2012, N'Literature', 4, 2)
SET IDENTITY_INSERT [dbo].[Department] ON
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (1, N'Engineering', 350000.0000, CAST(0x0000999C00000000 AS DateTime), 2)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (2, N'English', 120000.0000, CAST(0x0000999C00000000 AS DateTime), 6)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (4, N'Economics', 200000.0000, CAST(0x0000999C00000000 AS DateTime), 4)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (7, N'Mathematics', 250024.0000, CAST(0x0000999C00000000 AS DateTime), 3)
SET IDENTITY_INSERT [dbo].[Department] OFF
ALTER TABLE [dbo].[Course] WITH CHECK ADD CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Department] ([DepartmentID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
GO
在 Visual Studio 项目中,如何从数据库获取架构信息具有此代码示例的 c # 和 Visual Basic 版本。How to Get Schema Information from Database has C# and Visual Basic versions of this code sample in a Visual Studio project.
using System;
using System.Data;
using System.Data.SqlClient;
class Program {
static void Main(string[] args) {
using (SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=MySchool;Integrated Security=True;Asynchronous Processing=true;")) {
conn.Open();
// Get the Meta Data for Supported Schema Collections
DataTable metaDataTable = conn.GetSchema("MetaDataCollections");
Console.WriteLine("Meta Data for Supported Schema Collections:");
ShowDataTable(metaDataTable, 25);
Console.WriteLine();
// Get the schema information of Databases in your instance
DataTable databasesSchemaTable = conn.GetSchema("Databases");
Console.WriteLine("Schema Information of Databases:");
ShowDataTable(databasesSchemaTable, 25);
Console.WriteLine();
// First, get schema information of all the tables in current database;
DataTable allTablesSchemaTable = conn.GetSchema("Tables");
Console.WriteLine("Schema Information of All Tables:");
ShowDataTable(allTablesSchemaTable, 20);
Console.WriteLine();
// You can specify the Catalog, Schema, Table Name, Table Type to get
// the specified table(s).
// You can use four restrictions for Table, so you should create a 4 members array.
String[] tableRestrictions = new String[4];
// For the array, 0-member represents Catalog; 1-member represents Schema;
// 2-member represents Table Name; 3-member represents Table Type.
// Now we specify the Table Name of the table what we want to get schema information.
tableRestrictions[2] = "Course";
DataTable courseTableSchemaTable = conn.GetSchema("Tables", tableRestrictions);
Console.WriteLine("Schema Information of Course Tables:");
ShowDataTable(courseTableSchemaTable, 20);
Console.WriteLine();
// First, get schema information of all the columns in current database.
DataTable allColumnsSchemaTable = conn.GetSchema("Columns");
Console.WriteLine("Schema Information of All Columns:");
ShowColumns(allColumnsSchemaTable);
Console.WriteLine();
// You can specify the Catalog, Schema, Table Name, Column Name to get the specified column(s).
// You can use four restrictions for Column, so you should create a 4 members array.
String[] columnRestrictions = new String[4];
// For the array, 0-member represents Catalog; 1-member represents Schema;
// 2-member represents Table Name; 3-member represents Column Name.
// Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.
columnRestrictions[2] = "Course";
columnRestrictions[3] = "DepartmentID";
DataTable departmentIDSchemaTable = conn.GetSchema("Columns", columnRestrictions);
Console.WriteLine("Schema Information of DepartmentID Column in Course Table:");
ShowColumns(departmentIDSchemaTable);
Console.WriteLine();
// First, get schema information of all the IndexColumns in current database
DataTable allIndexColumnsSchemaTable = conn.GetSchema("IndexColumns");
Console.WriteLine("Schema Information of All IndexColumns:");
ShowIndexColumns(allIndexColumnsSchemaTable);
Console.WriteLine();
// You can specify the Catalog, Schema, Table Name, Constraint Name, Column Name to
// get the specified column(s).
// You can use five restrictions for Column, so you should create a 5 members array.
String[] indexColumnsRestrictions = new String[5];
// For the array, 0-member represents Catalog; 1-member represents Schema;
// 2-member represents Table Name; 3-member represents Constraint Name;4-member represents Column Name.
// Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.
indexColumnsRestrictions[2] = "Course";
indexColumnsRestrictions[4] = "CourseID";
DataTable courseIdIndexSchemaTable = conn.GetSchema("IndexColumns", indexColumnsRestrictions);
Console.WriteLine("Index Schema Information of CourseID Column in Course Table:");
ShowIndexColumns(courseIdIndexSchemaTable);
Console.WriteLine();
}
Console.WriteLine("Please press any key to exit...");
Console.ReadKey();
}
private static void ShowDataTable(DataTable table, Int32 length) {
foreach (DataColumn col in table.Columns) {
Console.Write("{0,-" + length + "}", col.ColumnName);
}
Console.WriteLine();
foreach (DataRow row in table.Rows) {
foreach (DataColumn col in table.Columns) {
if (col.DataType.Equals(typeof(DateTime)))
Console.Write("{0,-" + length + ":d}", row[col]);
else if (col.DataType.Equals(typeof(Decimal)))
Console.Write("{0,-" + length + ":C}", row[col]);
else
Console.Write("{0,-" + length + "}", row[col]);
}
Console.WriteLine();
}
}
private static void ShowDataTable(DataTable table) {
ShowDataTable(table, 14);
}
private static void ShowColumns(DataTable columnsTable) {
var selectedRows = from info in columnsTable.AsEnumerable()
select new {
TableCatalog = info["TABLE_CATALOG"],
TableSchema = info["TABLE_SCHEMA"],
TableName = info["TABLE_NAME"],
ColumnName = info["COLUMN_NAME"],
DataType = info["DATA_TYPE"]
};
Console.WriteLine("{0,-15}{1,-15}{2,-15}{3,-15}{4,-15}", "TableCatalog", "TABLE_SCHEMA",
"TABLE_NAME", "COLUMN_NAME", "DATA_TYPE");
foreach (var row in selectedRows) {
Console.WriteLine("{0,-15}{1,-15}{2,-15}{3,-15}{4,-15}", row.TableCatalog,
row.TableSchema, row.TableName, row.ColumnName, row.DataType);
}
}
private static void ShowIndexColumns(DataTable indexColumnsTable) {
var selectedRows = from info in indexColumnsTable.AsEnumerable()
select new {
TableSchema = info["table_schema"],
TableName = info["table_name"],
ColumnName = info["column_name"],
ConstraintSchema = info["constraint_schema"],
ConstraintName = info["constraint_name"],
KeyType = info["KeyType"]
};
Console.WriteLine("{0,-14}{1,-11}{2,-14}{3,-18}{4,-16}{5,-8}", "table_schema", "table_name", "column_name", "constraint_schema", "constraint_name", "KeyType");
foreach (var row in selectedRows) {
Console.WriteLine("{0,-14}{1,-11}{2,-14}{3,-18}{4,-16}{5,-8}", row.TableSchema,
row.TableName, row.ColumnName, row.ConstraintSchema, row.ConstraintName, row.KeyType);
}
}
}
适用于
GetSchema(String, String[])
通过使用指定字符串作为架构名称,指定字符串数组作为限制值,返回此 SqlConnection 的数据源的架构信息。Returns schema information for the data source of this SqlConnection using the specified string for the schema name and the specified string array for the restriction values.
public:
override System::Data::DataTable ^ GetSchema(System::String ^ collectionName, cli::array <System::String ^> ^ restrictionValues);
public override System.Data.DataTable GetSchema (string collectionName, string[] restrictionValues);
override this.GetSchema : string * string[] -> System.Data.DataTable
Public Overrides Function GetSchema (collectionName As String, restrictionValues As String()) As DataTable
参数
- collectionName
- String
指定要返回的架构的名称。Specifies the name of the schema to return.
- restrictionValues
- String[]
请求的架构的一组限制值。A set of restriction values for the requested schema.
返回
一个包含架构信息的 DataTable。A DataTable that contains schema information.
例外
将 collectionName 指定为 null。collectionName is specified as null.
注解
restrictionValues参数可以提供 n 个值深度,这些值由特定集合的限制集合指定。The restrictionValues parameter can supply n depth of values, which are specified by the restrictions collection for a specific collection. 为了设置给定限制的值,而不设置其他限制的值,你需要将前面的限制设置为, null 然后将相应的值放在中,以便为你要指定的值指定限制。In order to set values on a given restriction, and not set the values of other restrictions, you need to set the preceding restrictions to null and then put the appropriate value in for the restriction that you would like to specify a value for.
"Tables" 集合就是这样的一个示例。An example of this is the "Tables" collection. 如果 "表" 集合具有三个限制(数据库、所有者和表名称),并且您只想返回与 "Carl" 所有者关联的表,则需要传入以下值: null、"Carl"。If the "Tables" collection has three restrictions--database, owner, and table name--and you want to get back only the tables associated with the owner "Carl", you need to pass in the following values: null, "Carl". 如果未传入限制值,则将默认值用于该限制。If a restriction value is not passed in, the default values are used for that restriction. 这与传入的映射相同 null ,这不同于传入参数值的空字符串。This is the same mapping as passing in null, which is different from passing in an empty string for the parameter value. 在这种情况下,空字符串 ( "" ) 被视为指定参数的值。In that case, the empty string ("") is considered to be the value for the specified parameter.
有关演示的代码示例 GetSchema ,请参阅 GetSchema 。For a code sample demonstrating GetSchema, see GetSchema.