How do I natively translate SqlType to underlying SQL type declaration?

Question

Is there a way in .NET SqlClient to translate SqlType to actual SQL type declaration, like

SqlInt32  -> "int"
SqlGuid   -> "uniqueidentifier"
SqlXml    -> "xml"
SqlString -> "nvarchar(??)"

without doing it manually?

CLARIFICATION: I am trying to automatically refactor a number of SQL select statements, and I need to know what types do they return. I'm planning to do it by running following code:

using (var connection = new SqlConnection(SqlConnectionString))
{
    connection.Open();
    var command = connection.CreateCommand();
    command.CommandText = sql;
    var reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
    for (int i = 0; i < reader.FieldCount; ++i)
    {
         var type = reader.GetProviderSpecificFieldType(i);
         ... generate some sql code ...
    }
}

So, once I got the type, I'm trying to generate some SQL code and use SqlTye returned in GetProviderSpecificFieldType, and I wanted to see if there's already a function I can use that will take SqlType and give me back the SQL declaration

Answer 1

Not that I know of. Since there is no 1:1 correspondence (for example, an SqlString can correspond to the SQL Server types char, nchar, text, ntext, nvarchar and varchar), such a function would have to guess what the original type was, since this information is no longer available once you got the SqlString.

If you know that an SqlString always corresponds to an nvarchar(255) in your database, you can write such a function yourself, using this list as a starting point.

What are you trying to achieve? Maybe there is better solution to your problem...

Answer 2

this could help you

Answer 3

Not quite a total duplicate but there is the opposite direction.

The problem of it being a Many <-> Many mapping is exactly the same though.

Any fully automatic translation would be, at best overzealous (for example mapping any string to a TEXT column), and at worst wrong in some subtle way.

At the very least it would need to be Version dependent, for example should it use DateTime or DateTime2?

Answer 4

I found a solution. I used GetSchemaTable in order to get the details of SQL type:

    private string __type(SqlDataReader reader, int i)
    {
        var schema = reader.GetSchemaTable();
        var row = (from DataRow r in schema.Rows 
                   where (int)r["ColumnOrdinal"] == i 
                   select r).First();

        var type = (string) row["DataTypeName"];
        if (type == "nvarchar" || type == "varchar" || 
            type == "nchar" || type == "char")
        {
            int maxLength = (int) row["ColumnSize"];
            if (maxLength == 2147483647) maxLength = -1;
            type += string.Format("({0})", (maxLength > 0) ? 
                        (object) maxLength : (object) "max");
        }
        return type;
    }