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

@3615 i think u didn't read the question completely and properly — Tk1993
– Tk1993, Commented Feb 6, 2017 at 8:20

Shahar Gvirtz · Accepted Answer · 2017-02-06 09:15:34Z

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.

Tk1993 · Accepted Answer · 2017-02-22 08:17:46Z

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

kgzdev · Accepted Answer · 2017-02-06 09:56:41Z

0

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

edited Feb 6, 2017 at 9:56

answered Feb 6, 2017 at 8:28

kgzdev

2,9312 gold badges21 silver badges37 bronze badges

2 Comments

kgzdev Over a year ago

Are you using DBContext object of database? If you are using, please share your dbcontext object and table name, then I can give you example on your code

Tk1993 Over a year ago

It shows error: 'context' is a variable but is used like a type

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related