[ Team LiB ]Recipe 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, " +

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@objname", SqlDbType.NVarChar, 776);
cmd.Parameters[0].Value = dt.TableName;

conn.Open( );
// Create the DataReader from the command.
SqlDataReader dr = cmd.ExecuteReader( );
// Get the second result set containing column information.
dr.NextResult( );

Hashtable colInfo = new Hashtable( );
// Iterate over the second result to retrieve column information.
while(dr.Read( ))
{
colInfo.Add(dr["Column_name"].ToString( ),
"Length = " + dr["Length"] +
"; Precision = " + dr["Prec"] +
"; Scale = " + dr["Scale"]);
}
dr.Close( );
conn.Close( );

// Iterate over the column collection in the table.
foreach(DataColumn col in dt.Columns)
{
// Get column information.
schemaInfo.Append("\tCOLUMN: " + col.ColumnName +
Environment.NewLine);
schemaInfo.Append("\tAllowDBNull: " + col.AllowDBNull +
Environment.NewLine);

length, precision, and scale for each from the Hashtable. Information from the
FillSchema( ) method of the DataAdapter is also included. The data type and nullable
properties are available using both sp_help and FillSchema( ).
For more information about the sp_help system stored procedure, refer to Microsoft SQL
Server Books Online.
The GetSchemaTable( ) method of the DataReader also returns all column lengths. The
method returns a DataTable containing column metadata for a DataReader, where the
ColumnSize column contains the lengths. For more information about the
GetSchemaTable( ) method, see the discussion for Recipe 5.3
.
[ Team LiB ]