ADO.NET 3.5 Cookbook, 2nd Edition

11.4. Retrieving Column Default Values from SQL Server

Problem

The DataColumn object exposes a Default property. While the FillSchema() method of the DataAdapter returns schema information, it does not include the default values for columns. You want to retrieve the default values of columns in a SQL Server table.

Solution

Retrieve default values using catalog views or the system stored procedure sp_ helpconstraint. The solution demonstrates both approaches.

For each approach, the solution retrieves and outputs default values for the columns in the HumanResources.Employee table in AdventureWorks.

The C# code in Program.cs in the project RetrieveColumnDefaultValueSqlServer is shown in Example 11-4.

Example 11-4. File: Program.cs for RetrieveColumnDefaultValueSqlServer solution

using System; using System.Data; using System.Data.SqlClient; namespace RetrieveColumnDefaultValueSqlServer { class Program { private static string schemaName = "HumanResources"; private static string tableName = "Employee"; static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT * FROM " + schemaName + "." + tableName; SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); DataTable dt = new DataTable( ); da.FillSchema(dt, SchemaType.Source); // output column names and default values from the DataTable Console.WriteLine("---Column names and default values using " + "DataAdapter.FillSchema( ...