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