ADO.NET Cookbook

10.3. 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

Use system stored procedures.

The sample code executes the system stored procedure sp_helpconstraint to get constraint information for the columns in the Orders table in the Northwind sample database. Column default values are identified and retrieved from the result set.

The C# code is shown in Example 10-3.

Example 10-3. File: ColumnDefaultsForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Text; using System.Data; using System.Data.SqlClient; // . . . StringBuilder result = new StringBuilder( ); // Fill the Orders table with schema and data. SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders", ConfigurationSettings.AppSettings["Sql_ConnectString"]); DataTable ordersTable = new DataTable(ORDERS_TABLE); da.FillSchema(ordersTable, SchemaType.Source); da.Fill(ordersTable); SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); // Command for system stored procedure returning constraints SqlCommand cmd = new SqlCommand("sp_helpconstraint", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@objname",SqlDbType.NVarChar,776); cmd.Parameters[0].Value ...