如何从数据库模式中获取列的DefaultValue

Question

我正试图从我的数据库模式在c#中读取信息。. NET 4.5, SQL Server 2014)。我有一些字段，如MaxLength/ColumnLength的麻烦，直到我发现一个论坛提到设置DataAdapter.MissingSchemaAction到MissingSchemaAction.AddWithKey。不幸的是，即使在SQL Server列属性中的"默认值或绑定"中有默认设置的列，DefaultValue字段仍然是空白的。

SqlDataAdapter dbadapter = new SqlDataAdapter(SELECT_STRING, CONN_STRING);
dbadapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable tbl = new DataTable();
dbadapter.Fill(tbl);
// I actually looped through all rows/columns, but the net effect here is...
tbl.Columns[0].DefaultValue; // blank for all columns
// Also tried accessing the schema table available through DataReader
IDataReader reader = tbl.CreateDataReader();
DataTable schemaTbl = reader.GetSchemaTable();
/*
 * There are different schema fields here than in DataColumn,
 * but DefaultValue still blank. I looped through them all but...
 */
schemaTbl.Rows[0]["DefaultValue"]; // blank for all columns

如何使用。net从表中的列读取默认值(最好不诉诸于查询SQL的sys.*表)。

在创建或修改表时，如果没有提供列的默认值，则可以设置列的默认值。我正在尝试获取默认值

的例子:

CREATE TABLE Person
(
    Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    HomeState varchar(2) DEFAULT 'NY'
)

现在，如果我输入INSERT INTO Person (Id, LastName, FirstName) VALUES (1, 'Doe', 'John')，即使我没有设置它，homeestate也会是'NY'。

Answer 1

尝试如下使用Microsoft.SqlServer.Smo库:

using (var connection = new SqlConnection("connectionString"))
{
    var serverConnection = new ServerConnection(connection);
    var server = new Server(serverConnection);
    var database = server.Databases["databaseName"];
    var table = database.Tables["tableName"];
    foreach (Column column in table.Columns)
    {
        Console.WriteLine($"{column.Name} - default constraint: {column.DefaultConstraint?.Text ?? "None"}");
    }
    Console.ReadLine();
}

编辑

感谢@Crowcoder，他建议以下简化:

var serverConnection = new ServerConnection("serverName", "username", "password");
var server = new Server(serverConnection);
var database = server.Databases["databaseName"];
var table = database.Tables["tableName"];
foreach (Column column in table.Columns)
{
    Console.WriteLine($"{column.Name} - default constraint: {column.DefaultConstraint?.Text ?? "None"}");
}
Console.ReadLine();