getschema("Columns") + return DataType;

Question

i have this code

using (SqlConnection conn = new SqlConnection(ConnectionString))
                    {
                        conn.Open();
                        DataTable Databases = conn.GetSchema("Databases");
                        DataTable Tables = conn.GetSchema("Tables");
                        DataTable Columns = conn.GetSchema("Columns");
                        conn.close();
                     }

i need to return datattype by reading string value in column "DATA_TYPE"

 foreach (DataRow row in Columns.Rows)
                if (row["TABLE_NAME"].ToString() == tableName)
                {
                    if (fName == row["COLUMN_NAME"].ToString())
                    {
                      //return Datatype 
                      var x = row["DATA_TYPE"];
                    }
                }

////if(row["DATA_TYPE"] == "int") how i can set var x by DataType (Int) or how to get datatype by name that is found in row["DATA_TYPE"]??!!

Answer 1

a solution would be to create a dictionary mapping sql types to .net types:

Dictionary<string, Type> sqlToNetTypes;

and populate it with all possible types you can find in column "DATA_TYPE" and their .NET equivalent:

sqlToNetTypes.Add("int", typeof(int));
sqlToNetTypes.Add("varchar", typeof(sting));
sqlToNetTypes.Add("datetime", typeof(DateTime));
sqlToNetTypes.Add("bit", typeof(bool));
sqlToNetTypes.Add("numeric", typeof(float));//or double or decimal as you like...
...

then in a helper method:

Type GetNETType(string sqlType)
{
    if(sqlToNetTypes.ContainsKey(sqlType))
    {
        return sqlToNetTypes[sqlType];
    }else
    {
        return typeof(object); //generic type
    }
}

and use it like this:

foreach (DataRow row in Columns.Rows)
    if (row["TABLE_NAME"].ToString() == tableName)
    {
        if (fName == row["COLUMN_NAME"].ToString())
        {
            //return Datatype 
            var x = GetNETType(row["DATA_TYPE"]);
        }
    }