[ Team LiB ] Recipe Determining the Length of Columns in a SQL Server Table Problem The FillSchema( ) method of the DataAdapter returns the correct length in the MaxLength property for string columns in a SQL Server database, but it returns -1 for the length of all other fields. | Team LiB Recipe Determining the Length of Columns in a SQL Server Table Problem The FillSchema method of the DataAdapter returns the correct length in the MaxLength property for string columns in a SQL Server database but it returns -1 for the length of all other fields. You need to get the length of columns other than string type columns. Solution Use the system stored procedure sp_help. The sample code executes a batch query to return all rows from both the Orders and Order Details tables in the Northwind sample database. The extended stored procedure sp_help is used to get the length precision and scale of all columns in both tables. The C code is shown in Example 10-4. Example 10-4. File Namespaces variables and constants using System using using using using using . . . StringBuilder schemainfo new StringBuilder Create a batch query to retrieve order and details. String sqlText select OrderiD CustomeriD EmployeeiD OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode ShipCountry FROM Orders SELECT OrderiD ProductiD UnitPrice Quantity Discount FROM Order Details II Create the connection. SqlConnection conn new SqlConnection Sql_ConnectString II Create DataAdapter. SqlDataAdapter da new SqlDataAdapter sqlText conn II Add table mappings. Table Orders Table1 Order Details II Create the DataSet. DataSet ds new DataSet II Fill the schema and data. ds ds II Iterate over the table collection in the DataSet. foreach DataTable dt in TABLE II Create the command to retrieve column information. cmd new SqlCommand sp_help conn @objname 776 0 .
