CODEWITHSUNDEEP
.netsql-servertypessqldatatypestype-conversion
user489041
user489041

Reputation: 28294

SQL Server to .Net type conversions

I have the following map that I use to convert betwen SQL Server types, SQLData types, and .NET types:

/// <summary>
    /// The map of types. THis maps all the corresponding types between sql server types, .net sql types, and .net types
    /// </summary>
    public static List<SqlTypeConversionHolder> TypeList = new List<SqlTypeConversionHolder>()
    {
        new SqlTypeConversionHolder("bigint", typeof(SqlInt64),typeof(Int64)),
        new SqlTypeConversionHolder("binary", typeof(SqlBytes),typeof(Byte[])),
        new SqlTypeConversionHolder("bit", typeof(SqlBoolean),typeof(Boolean)),
        new SqlTypeConversionHolder("char", typeof(SqlChars),typeof(char)), //this one may need work
        new SqlTypeConversionHolder("cursor", null,null),
        new SqlTypeConversionHolder("date", typeof(SqlDateTime),typeof(DateTime)),
        new SqlTypeConversionHolder("datetime", typeof(SqlDateTime),typeof(DateTime)),
        new SqlTypeConversionHolder("datetime2", null,typeof(DateTime)),
        new SqlTypeConversionHolder("DATETIMEOFFSET", null,typeof(DateTimeOffset)),
        new SqlTypeConversionHolder("decimal", typeof(SqlDecimal),typeof(Decimal)),
        new SqlTypeConversionHolder("float", typeof(SqlDouble),typeof(Double)),
        //new SqlTypeConversionHolder("geography", typeof(SqlGeography),typeof(null));
        //new SqlTypeConversionHolder("geometry", typeof(SqlGeometry),typeof(null));
        //new SqlTypeConversionHolder("hierarchyid", typeof(SqlHierarchyId),typeof(null));
        new SqlTypeConversionHolder("image", null,null),
        new SqlTypeConversionHolder("int", typeof(SqlInt32),typeof(Int32)),
        new SqlTypeConversionHolder("money", typeof(SqlMoney),typeof(Decimal)),
        new SqlTypeConversionHolder("nchar", typeof(SqlChars),typeof(String)),
        new SqlTypeConversionHolder("ntext", null,null),
        new SqlTypeConversionHolder("numeric", typeof(SqlDecimal),typeof(Decimal)),
        new SqlTypeConversionHolder("nvarchar", typeof(SqlChars),typeof(String)),
        new SqlTypeConversionHolder("nvarchar(1)", typeof(SqlChars),typeof(Char)),
        new SqlTypeConversionHolder("nchar(1)", typeof(SqlChars),typeof(Char)),
        new SqlTypeConversionHolder("real", typeof(SqlSingle),typeof(Single)),
        new SqlTypeConversionHolder("rowversion", null,typeof(Byte[])),
        new SqlTypeConversionHolder("smallint", typeof(SqlInt16),typeof(Int16)),
        new SqlTypeConversionHolder("smallmoney", typeof(SqlMoney),typeof(Decimal)),
        new SqlTypeConversionHolder("sql_variant", null,typeof(Object)),
        new SqlTypeConversionHolder("table", null,null),
        new SqlTypeConversionHolder("text", typeof(SqlString),typeof(string)), //this one may need work
        new SqlTypeConversionHolder("time", null,typeof(TimeSpan)),
        new SqlTypeConversionHolder("timestamp", null,null),
        new SqlTypeConversionHolder("tinyint", typeof(SqlByte),typeof(Byte)),
        new SqlTypeConversionHolder("uniqueidentifier", typeof(SqlGuid),typeof(Guid)),
        new SqlTypeConversionHolder("varbinary", typeof(SqlBytes),typeof(Byte[])),
        new SqlTypeConversionHolder("varbinary(1)", typeof(SqlBytes),typeof(byte)),
        new SqlTypeConversionHolder("binary(1)", typeof(SqlBytes),typeof(byte)),
        new SqlTypeConversionHolder("varchar", typeof(SqlString),typeof(string)), //this one may need work
        new SqlTypeConversionHolder("xml", typeof(SqlXml),typeof(string))
    };

The first parameter of a SqlTypeConversionHolder is the name of the sql server type. The second parameter is the .Net Sql type. The third is the .net type.

The ones that say This one may need work are the ones that I am not sure about. Could someone provide some help as to what the proper conversions would be? Looking on MSDN, it seems as if they dont have a proper conversion type. I find that hard to beleive. There has to be someone way to go from the SQL Server type to the .NET type.

Upvotes: 5

Views: 3329

Answers (3)

Jordan
Jordan

Reputation: 2758

Also to add one more for SQL 2008 anyway Table converts to DataTable

I give an example of how to use this type here SQL Server stored procedure convert varchar to int

[Edit]

        char is not the correct .NET data type for a SQL Server char. 
    It must be converted to either char[] or string,
 since a SQL char can hold more than one character.

Credit to @RedFilter for that one

Upvotes: 1

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171351

char is not the correct .NET data type for a SQL Server char. It must be converted to either char[] or string, since a SQL char can hold more than one character.

The Microsoft reference here agrees with me. The other ones look correct.

Upvotes: 2

Shawn H
Shawn H

Reputation: 600

According to MSDN you have all three (char, varchar, text) correct.

See: http://msdn.microsoft.com/en-us/library/system.data.sqltypes.aspx

Upvotes: 0

Related Questions