c# - tipos - Cómo obtener columnas Restricciones de clave primaria usando SqlConnection.GetSchema()

la clave externa hace referencia a la tabla no válida

Question

Tengo un código de ADO.NET para detectar dinámicamente el esquema de la base de datos, lo que necesito es cómo obtener restricciones de columnas únicas y restricciones de clave primaria usando el método GetSchema en SqlConnection . Este es el código que tengo:

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

No hay nada en la llamada a GetSchemaTable en SqlConnection que le permita resolver esto.

Podría parecer que puede IsKey , utilizando el valor de la columna IsKey , que debería ser verdadero para cualquier cosa que contribuya a identificar de manera única el registro en la tabla. Sin embargo, de la documentación para la columna IsKey (énfasis mío):

true: la columna es uno de un conjunto de columnas en el conjunto de filas que, en conjunto, identifica de manera única la fila. El conjunto de columnas con IsKey establecido en verdadero debe identificar de manera única una fila en el conjunto de filas. No hay ningún requisito de que este conjunto de columnas sea un conjunto mínimo de columnas. Este conjunto de columnas se puede generar a partir de una clave primaria de tabla base, una restricción única o un índice único .

Debido a esto, no puede garantizar que contribuya a una clave primaria per se.

Ahora, si todo lo que necesita es algo para identificar de manera única la fila, entonces IsKey está bien, ya que la clave principal no siempre es la forma de identificar de forma única una fila (por ejemplo, puede tener identificadores naturales con un índice único). Incluso si tiene una clave principal y un índice único con otras columnas, los valores en todas las columnas en combinación siempre serán únicos.

Sin embargo, si específicamente necesita mirar las columnas que componen la clave principal, entonces GetSchemaTable no le dará la información que necesita. En su lugar, puede hacer una llamada al procedimiento almacenado del sistema sp_pkeys para encontrar los nombres de las columnas que contribuyen a crear la clave principal.

Answer 2

En caso de que todavía alguien necesite una solución, he creado una función para esto, el comando Sql contiene la declaración en la que desea obtener la información del esquema.

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

¿Qué hay de llamar a GetSchema () en su SqlConnection? Usando collectionName="IndexColumns" y una lista de restricciones de esquema, puede solicitar la información que desee utilizando GetSchema ().

Ver:

• Colecciones GetSchema y Schema
• Restricciones de esquema

Una vez que establecí SqlConnection usando el nombre de la base de datos, lo siguiente funcionó para mí:

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

Answer 4

Puede obtener primaryKeys , UniqueKeys y ForeignKeys y cualquier otro esquema enumerado en la dataTable devuelta por este comando: "connection.GetSchema (" MetaDataCollections ")"

Debajo de un código que le devuelve las keysKeys y UniqueKeys (nombre de clave y nombre de columna).

Mira toda la documentación aquí

    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
    }