ADO.NET 3.5 Cookbook, 2nd Edition

11.5. 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

Retrieve table schema information using DataReader.GetSchematable( ) method, catalog views, or the system stored procedure sp_help. The solution demonstrates all three approaches.

For each approach, the solution retrieves size, precision, and scale for each column in the HumanResources.Employee table in the AdventureWorks sample database and outputs those values to the console.

The C# code in Program.cs in the project RetrieveColumnLengthSqlServer is shown in Example 11-5.

Example 11-5. File: Program.cs for RetrieveColumnLengthSqlServer solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace RetrieveColumnLengthSqlServer
{
    class Program
    {
        private static string schemaName = "HumanResources";
        private static string tableName = "Employee";

        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT * FROM " + schemaName + "." + tableName;

            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable(  );
            da.FillSchema(dt, SchemaType.Source);

            // output column max length using DataAdapter.FillSchema( ...