tailieunhanh - Retrieving Column Default Values from SQL Server

[ Tam LiB ] Recipe 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. | Team LiB Recipe 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 Namespaces variables and constants using System using using using using . . . StringBuilder result new StringBuilder Fill the Orders table with schema and data. SqlDataAdapter da new SqlDataAdapter SELECT FROM Orders Sql_ConnectString DataTable ordersTable new DataTable ORDERS_TABLE ordersTable ordersTable SqlConnection conn new SqlConnection Sql_ConnectString Command for system stored procedure returning constraints SqlCommand cmd new SqlCommand sp_helpconstraint conn @objname 776 0 .Value Orders @nomsg 5 1 .Value nomsg Create a DataReader from the stored procedure. SqlDataReader dr Iterate over the constraints records in the DataReader. while Select the default value constraints only. String constraintType dr constraint_type .ToString if DEFAULT String constraintKeys dr constraint_keys .ToString Only strips single quotes for numeric default types add necessary handling as required for nonnumeric defaults .