Dynamically set TextBox.MaxLength based on SQL column size

Introduction

Have you ever tried to update a varchar in a SQL Server table when the size of the string is longer than the column size? It does not work… How is it normally solved? You specify:

myEditBox.MaxLength = 100;

What if you SQL developer changes the column size but does not let you know?

Solution

public class SQLHelper
{
    static public int GetSize(string csConnectionString, string csTableName, 
                              string csColumnName, int iDefault)
    {
        int iSize = iDefault;
        String csCommand =
            @"select CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = @TABLE_NAME AND COLUMN_NAME = @COLUMN_NAME";
        try
        {
            SqlConnection pConn = new SqlConnection(csConnectionString);
            pConn.Open();

            SqlCommand pCommand = new SqlCommand(csCommand, pConn);
            pCommand.Parameters.AddWithValue("@TABLE_NAME", csTableName);
            pCommand.Parameters.AddWithValue("@COLUMN_NAME", csColumnName);
            SqlDataReader pReader = pCommand.ExecuteReader();
            if (pReader.Read())
            {
                object pSize = pReader.GetValue(0);
                if (pSize != DBNull.Value)
                {
                    iSize = Convert.ToInt32(pSize);
                }
            }
            pReader.Close();
            pConn.Close();
        }
        catch (Exception em)
        {
            MessageBox.Show(em.Message, "SQL Error");
        }
        return iSize;
    }


    static public int GetSize(string csConnectionString, 
                      string csTableName, string csColumnName)
    {
        return GetSize(csConnectionString, csTableName, csColumnName, -1);
    }
}

Usage

myTextBox.MaxLength = SQLHelper.GetSize(ConnectionString, TableName, ColumnName);

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)