-Hello, World!-

-你好，世界！-

I am working on a C# with ASP.NET project and I have run into a snag. The project is to dynamically load metadata and records from tables to edit them without statically defining what tables could be edited. As such I need to get the schema/metadata of different tables.

我正在做一个带有ASP.NET的C#项目，我遇到了一个障碍。该项目是从表动态加载元数据和记录来编辑它们，而不是静态地定义哪些表可以编辑。因此，我需要获取不同表的模式/元数据。

Here's what I have so far:

以下是我目前掌握的情况：

// initialize the connection
using (SqlConnection con = new SqlConnection(metadata.DatabaseString))
{
    // open the connection
    con.Open();

    // initialize a new SqlCommand to get the schema
    SqlCommand command = con.CreateCommand();
    command.CommandType = CommandType.Text;

    // 0 = 1 ensures it's always an empty data set
    command.CommandText = "SELECT * FROM " + metadata.TableName + " WHERE 0=1;";

    // set to SchemaOnly to improve performance (i think)
    SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly);

    // GetSchemaTable() gets the table's metadata
    DataTable dataTable = reader.GetSchemaTable();

    // loops through all the rows of the data table
    foreach (DataRow row in dataTable.Rows)
    {
        // field names found here: https://msdn.microsoft.com/en-us/library/system.data.datatablereader.getschematable(v=vs.110).aspx#Remarks
        metadata.ColumnMetadata.Add(new ColumnWrapper()
        {
            ColumnType = GetTypeFromSql(row.Field<string>("DataTypeName")),
            ColumnRawType = row.Field<string>("DataTypeName"),
            ColumnName = row.Field<string>("ColumnName"),
            ByteSize = row.Field<int>("ColumnSize"),
            IsKey = row.Field<bool?>("IsKey") ?? false
        });
    }
}

The issue is the IsKey field is always empty. My SQL Server Table was created using the following query:

问题是IsKey字段始终为空。我的SQL Server表是使用以下查询创建的：

CREATE TABLE [dbo].[Dtm_LKUP_Role] (
    [DtmRoleId] INT IDENTITY (1, 1) NOT NULL,
    [RoleName]  VARCHAR(32) NOT NULL,
    [IsActive]  BIT DEFAULT ((1)) NOT NULL,
    PRIMARY KEY CLUSTERED ([DtmRoleId] ASC)
);

Here's what I have tried so far:

以下是我到目前为止尝试过的：

• Use a different table, same results


• Access dataTable.Columns["IsKey"]

No matter where I look I can't find the information I need. Does anyone have any ideas on what could cause this? In case it is relevant, I am using an MDF file and the LocalDB for my database connection rather than a live server.

无论我在哪里寻找，我都找不到我需要的信息。有谁知道这可能是什么原因吗？如果相关，我使用MDF文件和LocalDB进行数据库连接，而不是使用活动服务器。

Houston, we have lift off!

休斯顿，我们起飞了！

Based on the help from mjwills, I managed to get it working by changing my code to the following:

在mjwills的帮助下，我将代码更改为以下代码，从而使其正常工作：

// initialize the connection
using (SqlConnection con = new SqlConnection(metadata.DatabaseString))
{
    // open the connection
    con.Open();

    // initialize a new SqlCommand to get the schema. 0 = 1 ensures an empty data set
    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM " + metadata.TableName + " WHERE 0=1", con);

    // GetSchemaTable() gets the table's metadata
    DataTable dataTable = new DataTable();

    // tell the adapater to fill in the missing schema
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

    // fill the datatable with the schema
    adapter.FillSchema(dataTable, SchemaType.Mapped);

    // loops through all the rows of the data table
    foreach (DataColumn column in dataTable.Columns)
    {
        // field names found here: https://msdn.microsoft.com/en-us/library/system.data.datatablereader.getschematable(v=vs.110).aspx#Remarks
        metadata.ColumnMetadata.Add(new ColumnWrapper()
        {
            ColumnType = column.DataType,
            ColumnName = column.ColumnName,
            ByteSize = column.MaxLength,
            IsKey = dataTable.PrimaryKey.Contains(column)
        });
    }
}

I appreciate all the help from those who commented on my original question :)

我感谢所有就我原来的问题发表意见的人的帮助：)

You just need to change :

你只需要改变：

  // set to SchemaOnly to improve performance (i think)
    SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly);

to

至

  // set to SchemaOnly to improve performance (i think)
    SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);

And it should work.

而且它应该会奏效。