O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required