Get column sizes of a table in SQL Server using C#

Question

How can I get column sizes of a table using Entity Framework?

Let's say we have modeled SomeTable in our app like so:

public class SomeTable
{
    [Key]
    public long TicketID { get; set; }
    public string SourceData { get; set; }
}

And it's inside our SomeDbContext like so:

public class SomeDbContext : DbContext
{
    public DbSet<SomeTable> SomeTables { get; set; }
}

This table in Db has SourceData column as varchar(16) like so:

How can I get the size of this column, which is 16 in this case using C#?

Answer 1

Assuming you use entity framework code-first.

DbContext has a virtual function OnModelCreating that you can override in your derived class. In this function you inform the DbContext about the model you intend to represent in your DbContext.

Suppose you have a DbSet<MyClass>:

class MyClass
{
    public int Id {get; set;}
    public string MyText {get; set;}
    ...
}

And you want to specify that MyText has a maximum length of 10 characters.

protected override void OnModelCreating (System.Data.Entity.DbModelBuilder modelBuilder)
{
     var myClassEntity = modelBuilder.Entity<MyClass>();

     // MyClass has a property MyText with a maximum length of 10
     myClassEntity.Property(entity => entity.MyText
                  .HasMaxLength(10);

     ... configure other MyClass properties.
}

Answer 2

The below method gets the column size of all the char columns (char, varchar) of a table directly from the database.

You just need to provide the tableName and the connectionString.

(This doesn't even need Entity Framework.)

// I took HUGE help from this Microsoft docs website: - AshishK
// https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.getschema?view=netframework-4.7.2#System_Data_SqlClient_SqlConnection_GetSchema_System_String_System_String___
public static Dictionary<string, int> GetColumnSizesOfTableFromDatabase(string tableName, string connectionString)
{
    var columnSizes = new Dictionary<string, int>();
            
    using (var connection = new SqlConnection(connectionString))
    {
        // Connect to the database then retrieve the schema information.  
        connection.Open();

        // You can specify the Catalog, Schema, Table Name, Column Name to get the specified column(s).
        // You can use four restrictions for Column, so you should create a 4 members array.
        String[] columnRestrictions = new String[4];

        // For the array, 0-member represents Catalog; 1-member represents Schema;
        // 2-member represents Table Name; 3-member represents Column Name.
        // Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.
        columnRestrictions[2] = tableName;

        DataTable allColumnsSchemaTable = connection.GetSchema("Columns", columnRestrictions);

        foreach (DataRow row in allColumnsSchemaTable.Rows)
        {
            var columnName = row.Field<string>("COLUMN_NAME");
            var dataType = row.Field<string>("DATA_TYPE");
            var characterMaxLength = row.Field<int?>("CHARACTER_MAXIMUM_LENGTH");

            // I'm only capturing columns whose Datatype is "varchar" or "char", i.e. their CHARACTER_MAXIMUM_LENGTH won't be null.
            if(characterMaxLength != null)
            {
                columnSizes.Add(columnName, characterMaxLength.Value);
            }
        }

        connection.Close();
    }

    return columnSizes;
}

---

PS: If you'd like to capture other information about the columns this way, the following fields are also available: