CODEWITHSUNDEEP
.netsql-serverado.net
galets
galets

Reputation: 18472

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

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

Upvotes: 1

Views: 281

Answers (4)

galets
galets

Reputation: 18472

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

Upvotes: 1

ShuggyCoUk
ShuggyCoUk

Reputation: 36438

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?

Upvotes: 0

Fredou
Fredou

Reputation: 20100

this could help you

Upvotes: 1

Heinzi
Heinzi

Reputation: 172270

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...

Upvotes: 2

Related Questions