根据 SQL 列大小动态设置 TextBox.MaxLength

引言

你有没有尝试过更新 SQL Server 表中的 varchar 列，当字符串的长度超过列大小时？这是行不通的…… 通常如何解决？ 你指定

myEditBox.MaxLength = 100;

如果你的 SQL 开发者更改了列大小但没有通知你怎么办？

解决方案

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);
    }
}

用法

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