如何使用SqlConnection.GetSchema()获取列主键约束
本文关键字：约束 获取 何使用 SqlConnection GetSchema | 更新日期: 2023-09-27 17:53:58
我有一些ADO.NET的代码来动态检测数据库模式，我需要的是如何在SqlConnection上使用GetSchema方法获得唯一列约束和主键约束。这是我的代码：
conn.Open();
SqlCommand mSqlCommand = new SqlCommand("sp_pkeys", conn);
mSqlCommand.CommandType = CommandType.StoredProcedure;
mSqlCommand.Parameters.Add(
"@table_name", SqlDbType.NVarChar).Value = tableName;
SqlDataReader mReader = mSqlCommand.ExecuteReader(
(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly));
//ExecuteReader();
DataTable schema = mReader.GetSchemaTable();
mReader.Close();
conn.Close();
在SqlConnection上对GetSchemaTable的调用中没有任何内容可以让您弄清楚这一点。
您可以使用IsKey列值，对于有助于唯一标识表中记录的任何内容，该值都应返回true。然而，从IsKey列的文档(强调矿(来看：
true：该列是一组行集中的列它们一起唯一地标识该行。IsKey设置为的列集true必须唯一标识中的行行集。没有要求这组列是最小的一组列。这组列可以从基表生成主键、唯一约束或唯一索引。
正因为如此，您不能保证它对主键本身有贡献。
现在，如果您只需要一些东西来唯一地标识行，那么IsKey就可以了，因为主键并不总是唯一地标识一行的方式(例如，您可以使用具有唯一索引的自然标识符(。即使您有主键和其他列的唯一索引，组合中所有这些列的值也将始终是唯一的。
但是，如果您特别需要查看构成主键的列，那么GetSchemaTable将不会为您提供所需的信息。相反，您可以直接调用sp_pkeys系统存储过程，以查找有助于生成主键的列的名称。
如果仍然有人需要解决方案，我已经为此创建了一个函数，Sql命令包含您想要获取架构信息的语句。
Public Shared Function TableFromCommand(ByVal Command As SqlCommand) As DataTable
Dim Cn As SqlConnection = Nothing
Dim Dt As DataTable
Dim Dr As SqlDataReader
Dim Column As DataColumn
Dim Answer As New DataTable
Try
Answer.TableName = "SearchTable"
Cn = New SqlConnection("Your connection string")
Cn.Open()
Command.Connection = Cn
For Each Prm As SqlParameter In Command.Parameters
If Prm.Direction = ParameterDirection.Input _
OrElse Prm.Direction = ParameterDirection.InputOutput Then
Prm.Value = DBNull.Value
End If
Next
Dr = Command.ExecuteReader(CommandBehavior.SchemaOnly Or CommandBehavior.KeyInfo)
Dt = Dr.GetSchemaTable
Dim Keys As New List(Of DataColumn)
Dim ColumnsDic As New SortedDictionary(Of Integer, DataColumn)
For Each Row As DataRow In Dt.Rows
Column = New DataColumn
With Column
.ColumnName = Row("ColumnName").ToString
.DataType = Type.GetType(Row("DataType").ToString)
.AllowDBNull = CBool(Row("AllowDBNull"))
.Unique = CBool(Row("IsUnique"))
.ReadOnly = CBool(Row("IsReadOnly"))
If Type.GetType(Row("DataType").ToString) Is GetType(String) Then
.MaxLength = CInt(Row("ColumnSize"))
End If
If CBool(Row("IsIdentity")) = True Then
.AutoIncrement = True
.AutoIncrementSeed = -1
.AutoIncrementStep = -1
End If
If CBool(Row("IsKey")) = True Then
Keys.Add(Column)
End If
End With
ColumnsDic.Add(CInt(Row("ColumnOrdinal")), Column)
Answer.Columns.Add(Column)
Next
If Keys.Count > 0 Then
Answer.Constraints.Add("PrimaryKey", Keys.ToArray, True)
End If
Catch ex As Exception
MyError.Show(ex)
Finally
If Cn IsNot Nothing AndAlso Not Cn.State = ConnectionState.Closed Then
Cn.Close()
End If
End Try
Return Answer
End Function
您可以获得primaryKeys、UniqueKeys和ForeignKeys以及此命令返回的数据表中列出的任何其他模式："connection.GetSchema (" MetaDataCollections ")"
下面的代码将返回primaryKeys和UniqueKeys(键名和列名(。
查看此处的所有文档
public void Dotransfer()
{
var sourceSchema = new TableSchema(SourceConnectionString);
}
public class TableSchema
{
public TableSchema(string connectionString)
{
this.TableList = new List<string>();
this.ColumnList = new List<Columns>();
this.PrimaryKeyList = new List<PrimaryKey>();
this.ForeignKeyList = new List<ForeignKey>();
this.UniqueKeyList = new List<UniqueKey>();
GetDataBaseSchema(connectionString);
}
public List<string> TableList { get; set; }
public List<Columns> ColumnList { get; set; }
public List<PrimaryKey> PrimaryKeyList { get; set; }
public List<UniqueKey> UniqueKeyList { get; set; }
public List<ForeignKey> ForeignKeyList { get; set; }
protected void GetDataBaseSchema(string ConnectionString)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder();
builder.ConnectionString = ConnectionString;
string server = builder.DataSource;
string database = builder.InitialCatalog;
connection.Open();
DataTable schemaTables = connection.GetSchema("Tables");
foreach (System.Data.DataRow rowTable in schemaTables.Rows)
{
String tableName = rowTable.ItemArray[2].ToString();
this.TableList.Add(tableName);
string[] restrictionsColumns = new string[4];
restrictionsColumns[2] = tableName;
DataTable schemaColumns = connection.GetSchema("Columns", restrictionsColumns);
foreach (System.Data.DataRow rowColumn in schemaColumns.Rows)
{
string ColumnName = rowColumn[3].ToString();
this.ColumnList.Add(new Columns(){TableName= tableName, FieldName = ColumnName});
}
string[] restrictionsPrimaryKey = new string[4];
restrictionsPrimaryKey[2] = tableName;
DataTable schemaPrimaryKey = connection.GetSchema("IndexColumns", restrictionsColumns);
foreach (System.Data.DataRow rowPrimaryKey in schemaPrimaryKey.Rows)
{
string indexName = rowPrimaryKey[2].ToString();
if (indexName.IndexOf("PK_") != -1)
{
this.PrimaryKeyList.Add(new PrimaryKey()
{
TableName = tableName,
FieldName = rowPrimaryKey[6].ToString(),
PrimaryKeyName = indexName
});
}
if (indexName.IndexOf("UQ_") != -1)
{
this.UniqueKeyList.Add(new UniqueKey()
{
TableName = tableName,
FieldName = rowPrimaryKey[6].ToString(),
UniqueKeyName = indexName
});
}
}
string[] restrictionsForeignKeys = new string[4];
restrictionsForeignKeys[2] = tableName;
DataTable schemaForeignKeys = connection.GetSchema("ForeignKeys", restrictionsColumns);
foreach (System.Data.DataRow rowFK in schemaForeignKeys.Rows)
{
this.ForeignKeyList.Add(new ForeignKey()
{
ForeignName = rowFK[2].ToString(),
TableName = tableName,
// FieldName = rowFK[6].ToString() //There is no information
});
}
}
}
}
}
public class Columns
{
public string TableName { get; set; }
public string FieldName { get; set; }
}
public class PrimaryKey
{
public string TableName { get; set; }
public string PrimaryKeyName { get; set; }
public string FieldName { get; set; }
}
public class UniqueKey
{
public string TableName { get; set; }
public string UniqueKeyName { get; set; }
public string FieldName { get; set; }
}
public class ForeignKey
{
public string TableName { get; set; }
public string ForeignName { get; set; }
// public string FieldName { get; set; } //There is no information
}
在SqlConnection上调用GetSchema((怎么样？使用collectionName="IndexColumns"和模式限制列表，您可以使用GetSchema((请求所需的信息。
参见：
- GetSchema和Schema集合
- 架构限制
一旦我使用数据库名称建立了SqlConnection，以下对我有效：
var connectionString =
string.Format("Server=.''SQLEXPRESS;Database={0};Trusted_Connection=true", dbName);
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
DataTable tables = sqlConnection.GetSchema("Tables");
foreach (DataRow tablesRow in tables.Rows)
{
string tableName = tablesRow["table_name"].ToString();
Console.WriteLine(tableName);
var indexCols = sqlConnection.GetSchema("IndexColumns",
new string[] {dbName, null, tableName, "PK_" + tableName, null});
foreach (DataRow indexColsRow in indexCols.Rows)
Console.WriteLine(" PK: {0}", indexColsRow["column_name"]);
}
}