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 ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.