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

Question

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

Answer 1

Entity Framework (and actually any "generic ORM") are meant to be generic, and therefor you somehow lost the ability to know the exact datatype.

Even if you're looking at the attributes or mapping that sometimes are stored by different ORM's to enable some kind of validations prior to actually doing the work against the DB - you can't guarantee someone didn't change the schema on the SQL Server end - and didn't update the model.

In most cases it shouldn't really matter - working with the matching CLR data-types should give you enough info (for example, for user interface usage).

If you need to know the exact data type, as stored and known by the server, you have the following options:

• If you're interested in table or view schema, you can use SMO (SQL Server Management object) to connect to the server and query the actual schema of the table or view.
• If you want to know the schema returned by specific query, you can use sp_describe_first_result_set SP (https://msdn.microsoft.com/en-us/library/ff878602.aspx) that gets SQL query and return result-set describing each column you can expect in the result set of the query. You can probably run that once for the queries you use, and then cache the results or something.

Hope it helps.

Answer 2

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

Answer 3

Use DbContext object instead your own created model class in reflection

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