How to get Primary Key Constraint columns using SqlConnection.GetSchema ()

Question

I have ADO.NET code for dynamically detecting a database schema, I need how to get unique column constraints and primary key constraints using the GetSchema method on SqlConnection . This is the code I have:

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

Answer 1

There is nothing in the GetSchemaTable on SqlConnection call that will let you understand this.

It might seem that you can use the value of the IsKey column, which should return true for everything, which helps to uniquely identify the record in the table. However, from the documentation for the IsKey column (my selection):

true: a column is one of many columns in a set of rows that were taken together that uniquely identify a row. A set of columns with IsKey set to true should uniquely identify a row in a set of rows. There is no requirement that this set of columns is a minimum set of columns. This set of columns can be formed from a base table primary key, a unique constraint, or a unique index .

Because of this, you cannot guarantee that it contributes to the primary key per se.

Now, if you only need to uniquely identify the string, then IsKey will be fine, since the primary key is not always a way to uniquely identify the string (for example, you can have natural identifiers with a unique index), even if you have a primary key and a unique index with other columns, the values ​​in all of these columns in combination will always be unique.

However, if you need to look at the columns that make up the primary key, then GetSchemaTable will not give you the necessary information. Most likely, you can simply call the sp_pkeys system stored procedure to find the column names that contribute to the creation of the primary key.

Answer 2

If someone needs solutions, I created a function for this, Sqlcommand contains the operator that you want to get information about the scheme.

 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 

Answer 3

You can get primaryKeys , UniqueKeys and ForeignKeys and any other scheme specified in the dataTable returned by this command: "connection.GetSchema (" MetaDataCollections ")"

Below is the code that returns you primaryKeys and UniqueKeys (Key name and column name).

View all documentation here

  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 } 

Answer 4

How about calling GetSchema () on your SqlConnection? Using collectionName="IndexColumns" and a list of schema restrictions, you can request the information you need using GetSchema ().

Cm:

• GetSchema and Schema Collections
• Circuit limitations

Once I installed SqlConnection using the database name, the following worked for me:

 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"]); } }