ADO.NET Cookbook

10.4. 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: ColumnSchemaForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Collections; using System.Text; using System.Data; using System.Data.SqlClient; // . . . 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];"; // Create the connection. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); // Create DataAdapter. SqlDataAdapter da = new SqlDataAdapter(sqlText, conn); ...