Search code examples
c#sql-serverwpfentity-frameworksqldatatypes

Get the "sql datatype" of a column created in SQL server using C# and entity framework


I have a table tblDetails consisting of 2 columns

Id INT NOT NULL

Name VARCHAR(20)

By using EntityFramework 6 and C# for my WPF application im fetching the data to display it in the front end. Also I want to get the "sql datatype" of the columns as well.

This is the entity model used for database connectivity:

public class tblDetails
{
public int Id{get; set;}
public string Name{get;set;}
}

For this I'm using the GetProperty() method for it. Below is a sample of code:

private void GetDataType()
{
     var col = typeof(tblDetails).GetProperty("Name"); 
            string dataType = col.PropertyType.Name;
}

PROBLEM

dataType varibale is assigned with the value string but I want the exact Sql datatype of the column i.e. VARCHAR(20)

I've gone through a number of solutions like below but didn't get the solution to my problem.

How can I get data type of each column in a SQL Server table/view etc. using C# Entity Framework?

Anybody got a C# function that maps the SQL datatype of a column to its CLR equivalent?

Entity Framework Data Annotations Set StringLength VarChar

UPDATE

Below is the class in which I'm using the Dbcontext:

 public class DomainService : DbContext
   {
   public DomainService() : base("ConnectionString")
   {
     Configuration.LazyLoadingEnabled = true;
    #if DEBUG
       Database.Log = (s) => Debug.WriteLine(s);
    #endif
    }
  public DbSet<tblDetails> tblDetails { get; set; }
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
                base.OnModelCreating(modelBuilder);
                modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
  }

Solution

  • Got a solution: To get the datatype and length as well of variables of type VARCHAR, NCHAR & NVARCHAR

    SqlConnection connection = new SqlConnection("ConnectionString");
    connection.Open();
    
    string sqlcmnd = string.Format(
                            @"SELECT CASE UPPER(DATA_Type) 
                            WHEN 'NCHAR'    THEN  CONCAT(UPPER(DATA_Type),'(',ISNULL(CHARACTER_MAXIMUM_LENGTH,''),')') 
                            WHEN 'VARCHAR'  THEN  CONCAT(UPPER(DATA_Type),'(',ISNULL(CHARACTER_MAXIMUM_LENGTH,''),')') 
                            WHEN 'NVARCHAR' THEN  CONCAT(UPPER(DATA_Type),'(',ISNULL(CHARACTER_MAXIMUM_LENGTH,''),')') 
                            ELSE UPPER(DATA_Type) END AS DATA_TYPE
                            FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", tableName, ColumnName);
    
    SqlCommand cmd = new SqlCommand(sqlcmnd, connection);
    
    SqlDataReader reader = cmd.ExecuteReader();  
    while (reader.Read())
    string dataType = reader["DATA_TYPE"].ToString();