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#?

Proximate

Please prefer texts before images whenever possible. – Araby

What is the actual question? How to specify the size in a model or how to get schema metadata from the server? If the second, you don't need EF for this. That's provided by the ADO.NET classes EF6 itself calls. Check for example GetSchema and Schema Collections – Upandcoming

Why do you want to retrieve the column metadata? When working with EF6, this would only be useful while reverse-engineering a model from a database. Are you trying to write your own generator? – Upandcoming

It is quite difficult for me to explain this in English. But, I am communicating with tcp-ip and the data size is important for telegram. size is given in the database. so I need to get it from the database. For int to size 4 but not for string, sometimes 10 and sometimes 20 and may vary. – Eyewitness

Does this answer your question? Retrieve size of field having varchar datatype in SQL Server using C# – Demetricedemetris

you can check your answer from below link #40406187 – Punchboard

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

Scrub

thks for reply but dont want this. yes, i'm using ef6 code-first. I would like to bring the value "size" from the database. – Eyewitness

Answer 2

I am giving answer to this question supposing you are using it in a controller action, if not then just get the idea.

public class MyController : Controller
{
    private readonly MyDbContext _dbContext;

    public MyController(MyDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public ActionResult GetColumnSize(string TableName)
    {
        try
        {
            using (var connection = _dbContext.Database.Connection as SqlConnection)
            {
                if (connection == null)
                    return HttpNotFound(); // Handle appropriately

                connection.Open();

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = $"SELECT * FROM {TableName}"; // Replace with your table name
                    using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
                    {
                        DataTable schemaTable = reader.GetSchemaTable();
                        foreach (DataRow row in schemaTable.Rows)
                        {
                            string columnName = row.Field<string>("ColumnName");
                            int columnSize = row.Field<int>("ColumnSize"); // Get the column size
                            Console.WriteLine($"Column Name: {columnName}, Size: {columnSize}");
                        }
                    }
                }
            }

            // Handle the retrieved column sizes as needed
            // ...

            return View();
        }
        catch (Exception ex)
        {
            // Handle exceptions
            return View("Error", ex);
        }
    }
}

Fenton