CODEWITHSUNDEEP
c#database-schemadatacolumn
Rick
Rick

Reputation: 1913

How to get DefaultValue of column from database schema in C#?

I'm trying to read information from my database schema in C# (.NET 4.5, SQL Server 2014). I was having trouble with some fields such as MaxLength/ColumnLength until I found a forum that mentioned setting the DataAdapter.MissingSchemaAction to MissingSchemaAction.AddWithKey. Unfortunately the DefaultValue field is still blank even for columns that have a default set in the "Default Value or Binding" in the Column Properties in SQL Server.

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

How can I read the default value from a column in my table using .NET (preferably without resorting to querying SQL's sys.* tables).

Clarification

When creating or altering a table, you can set a default value of a column if none is provided. I'm trying to get the default value.

Example:

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

Now if I say, INSERT INTO Person (Id, LastName, FirstName) VALUES (1, 'Doe', 'John') the HomeState will be 'NY' even though I didn't set it.

Upvotes: 1

Views: 2136

Answers (1)

JamieS
JamieS

Reputation: 307

Try using the Microsoft.SqlServer.Smo library as below:

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

EDIT

Thanks to @Crowcoder who suggested the below simplification:

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

Upvotes: 2

Related Questions