Has anyone got a C # function that maps an SQL column data type to its CLR equivalent?

Question

I am sitting to write a massive switch () statement to turn SQL data types into CLR data types in order to generate classes from MSSQL stored procedures. I am using this diagram as a reference. Before I delve into something that might take all day and be a huge pain to fully test, I would like to contact the SO community to find out if anyone else wrote anything in C # to accomplish this a seemingly common and certainly tedious task.

Answer 1

This is the one we use. You can configure it (e.g., types with a null / non-empty value, etc.), but it should save you most of the input.

public static Type GetClrType(SqlDbType sqlType) { switch (sqlType) { case SqlDbType.BigInt: return typeof(long?); case SqlDbType.Binary: case SqlDbType.Image: case SqlDbType.Timestamp: case SqlDbType.VarBinary: return typeof(byte[]); case SqlDbType.Bit: return typeof(bool?); case SqlDbType.Char: case SqlDbType.NChar: case SqlDbType.NText: case SqlDbType.NVarChar: case SqlDbType.Text: case SqlDbType.VarChar: case SqlDbType.Xml: return typeof(string); case SqlDbType.DateTime: case SqlDbType.SmallDateTime: case SqlDbType.Date: case SqlDbType.Time: case SqlDbType.DateTime2: return typeof(DateTime?); case SqlDbType.Decimal: case SqlDbType.Money: case SqlDbType.SmallMoney: return typeof(decimal?); case SqlDbType.Float: return typeof(double?); case SqlDbType.Int: return typeof(int?); case SqlDbType.Real: return typeof(float?); case SqlDbType.UniqueIdentifier: return typeof(Guid?); case SqlDbType.SmallInt: return typeof(short?); case SqlDbType.TinyInt: return typeof(byte?); case SqlDbType.Variant: case SqlDbType.Udt: return typeof(object); case SqlDbType.Structured: return typeof(DataTable); case SqlDbType.DateTimeOffset: return typeof(DateTimeOffset?); default: throw new ArgumentOutOfRangeException("sqlType"); } } 

Answer 2

  /****** Object: Table [dbo].[DbVsCSharpTypes] Script Date: 03/20/2010 03:07:56 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DbVsCSharpTypes]') AND type in (N'U')) DROP TABLE [dbo].[DbVsCSharpTypes] GO /****** Object: Table [dbo].[DbVsCSharpTypes] Script Date: 03/20/2010 03:07:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DbVsCSharpTypes]( [DbVsCSharpTypesId] [int] IDENTITY(1,1) NOT NULL, [Sql2008DataType] [varchar](200) NULL, [CSharpDataType] [varchar](200) NULL, [CLRDataType] [varchar](200) NULL, [CLRDataTypeSqlServer] [varchar](2000) NULL, CONSTRAINT [PK_DbVsCSharpTypes] PRIMARY KEY CLUSTERED ( [DbVsCSharpTypesId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET NOCOUNT ON; SET XACT_ABORT ON; GO SET IDENTITY_INSERT [dbo].[DbVsCSharpTypes] ON; BEGIN TRANSACTION; INSERT INTO [dbo].[DbVsCSharpTypes]([DbVsCSharpTypesId], [Sql2008DataType], [CSharpDataType], [CLRDataType], [CLRDataTypeSqlServer]) SELECT 1, N'bigint', N'long', N'Int64, Nullable<Int64>', N'SqlInt64' UNION ALL SELECT 2, N'binary', N'byte[]', N'Byte[]', N'SqlBytes, SqlBinary' UNION ALL SELECT 3, N'bit', N'bool', N'Boolean, Nullable<Boolean>', N'SqlBoolean' UNION ALL SELECT 4, N'char', N'char', NULL, NULL UNION ALL SELECT 5, N'cursor', NULL, NULL, NULL UNION ALL SELECT 6, N'date', N'DateTime', N'DateTime, Nullable<DateTime>', N'SqlDateTime' UNION ALL SELECT 7, N'datetime', N'DateTime', N'DateTime, Nullable<DateTime>', N'SqlDateTime' UNION ALL SELECT 8, N'datetime2', N'DateTime', N'DateTime, Nullable<DateTime>', N'SqlDateTime' UNION ALL SELECT 9, N'DATETIMEOFFSET', N'DateTimeOffset', N'DateTimeOffset', N'DateTimeOffset, Nullable<DateTimeOffset>' UNION ALL SELECT 10, N'decimal', N'decimal', N'Decimal, Nullable<Decimal>', N'SqlDecimal' UNION ALL SELECT 11, N'float', N'double', N'Double, Nullable<Double>', N'SqlDouble' UNION ALL SELECT 12, N'geography', NULL, NULL, N'SqlGeography is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack.' UNION ALL SELECT 13, N'geometry', NULL, NULL, N'SqlGeometry is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack.' UNION ALL SELECT 14, N'hierarchyid', NULL, NULL, N'SqlHierarchyId is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack.' UNION ALL SELECT 15, N'image', NULL, NULL, NULL UNION ALL SELECT 16, N'int', N'int', N'Int32, Nullable<Int32>', N'SqlInt32' UNION ALL SELECT 17, N'money', N'decimal', N'Decimal, Nullable<Decimal>', N'SqlMoney' UNION ALL SELECT 18, N'nchar', N'string', N'String, Char[]', N'SqlChars, SqlString' UNION ALL SELECT 19, N'ntext', NULL, NULL, NULL UNION ALL SELECT 20, N'numeric', N'decimal', N'Decimal, Nullable<Decimal>', N'SqlDecimal' UNION ALL SELECT 21, N'nvarchar', N'string', N'String, Char[]', N'SqlChars, SqlStrinG SQLChars is a better match for data transfer and access, and SQLString is a better match for performing String operations.' UNION ALL SELECT 22, N'nvarchar(1), nchar(1)', N'string', N'Char, String, Char[], Nullable<char>', N'SqlChars, SqlString' UNION ALL SELECT 23, N'real', N'single', N'Single, Nullable<Single>', N'SqlSingle' UNION ALL SELECT 24, N'rowversion', N'byte[]', N'Byte[]', NULL UNION ALL SELECT 25, N'smallint', N'smallint', N'Int16, Nullable<Int16>', N'SqlInt16' UNION ALL SELECT 26, N'smallmoney', N'decimal', N'Decimal, Nullable<Decimal>', N'SqlMoney' UNION ALL SELECT 27, N'sql_variant', N'object', N'Object', NULL UNION ALL SELECT 28, N'table', NULL, NULL, NULL UNION ALL SELECT 29, N'text', N'string', NULL, NULL UNION ALL SELECT 30, N'time', N'TimeSpan', N'TimeSpan, Nullable<TimeSpan>', N'TimeSpan' UNION ALL SELECT 31, N'timestamp', NULL, NULL, NULL UNION ALL SELECT 32, N'tinyint', N'byte', N'Byte, Nullable<Byte>', N'SqlByte' UNION ALL SELECT 33, N'uniqueidentifier', N'Guid', N'Guid, Nullable<Guid>', N'SqlGuidUser-defined type(UDT)The same class that is bound to the user-defined type in the same assembly or a dependent assembly.' UNION ALL SELECT 34, N'varbinary ', N'byte[]', N'Byte[]', N'SqlBytes, SqlBinary' UNION ALL SELECT 35, N'varbinary(1), binary(1)', N'byte', N'byte, Byte[], Nullable<byte>', N'SqlBytes, SqlBinary' UNION ALL SELECT 36, N'varchar', NULL, NULL, NULL UNION ALL SELECT 37, N'xml', NULL, NULL, N'SqlXml' COMMIT; RAISERROR (N'[dbo].[DbVsCSharpTypes]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT; GO SET IDENTITY_INSERT [dbo].[DbVsCSharpTypes] OFF; 

Answer 3

  internal Type type(SqlDbType sqltype) { Type resulttype = null; Dictionary<SqlDbType, Type> Types = new Dictionary<SqlDbType, Type>(); Types.Add(SqlDbType.BigInt, typeof(Int64)); Types.Add(SqlDbType.Binary, typeof(Byte[])); Types.Add(SqlDbType.Bit, typeof(Boolean)); Types.Add(SqlDbType.Char, typeof(String)); Types.Add(SqlDbType.Date, typeof(DateTime)); Types.Add(SqlDbType.DateTime, typeof(DateTime)); Types.Add(SqlDbType.DateTime2, typeof(DateTime)); Types.Add(SqlDbType.DateTimeOffset, typeof(DateTimeOffset)); Types.Add(SqlDbType.Decimal, typeof(Decimal)); Types.Add(SqlDbType.Float, typeof(Double)); Types.Add(SqlDbType.Image, typeof(Byte[])); Types.Add(SqlDbType.Int, typeof(Int32)); Types.Add(SqlDbType.Money, typeof(Decimal)); Types.Add(SqlDbType.NChar, typeof(String)); Types.Add(SqlDbType.NText, typeof(String)); Types.Add(SqlDbType.NVarChar, typeof(String)); Types.Add(SqlDbType.Real, typeof(Single)); Types.Add(SqlDbType.SmallDateTime, typeof(DateTime)); Types.Add(SqlDbType.SmallInt, typeof(Int16)); Types.Add(SqlDbType.SmallMoney, typeof(Decimal)); Types.Add(SqlDbType.Text, typeof(String)); Types.Add(SqlDbType.Time, typeof(TimeSpan)); Types.Add(SqlDbType.Timestamp, typeof(Byte[])); Types.Add(SqlDbType.TinyInt, typeof(Byte)); Types.Add(SqlDbType.UniqueIdentifier, typeof(Guid)); Types.Add(SqlDbType.VarBinary, typeof(Byte[])); Types.Add(SqlDbType.VarChar, typeof(String)); Types.TryGetValue(sqltype, out resulttype); return resulttype; } internal SqlDbType type(Type systype) { SqlDbType resulttype = SqlDbType.NVarChar; Dictionary<Type, SqlDbType> Types = new Dictionary<Type, SqlDbType>(); Types.Add(typeof(Boolean), SqlDbType.Bit); Types.Add(typeof(String), SqlDbType.NVarChar); Types.Add(typeof(DateTime), SqlDbType.DateTime); Types.Add(typeof(Int16), SqlDbType.Int); Types.Add(typeof(Int32), SqlDbType.Int); Types.Add(typeof(Int64), SqlDbType.Int); Types.Add(typeof(Decimal), SqlDbType.Float); Types.Add(typeof(Double), SqlDbType.Float); Types.TryGetValue(systype, out resulttype); return resulttype; } 

Answer 4

You do not need a function. I think you can search

 dt.Columns[i].DataType.UnderlyingSystemType 

dt - dataTable

This will return the CLR type for the corresponding column. Hope this helps, and BTW is my first stack overflow response

Answer 5

This does not directly answer the question asked, but answers the general related question. When you have an IDataReader , you can call IDataRecord.GetFieldType(int) to "[get] Type information corresponding to the type of Object to be returned from GetValue ."

Answer 6

I enable this extension (you can easily exchange the string key in the dictionary for SqlDbType, since Greg has implemented or even supports both) in my model and sets a property that converts the CLR type:

  namespace X.Domain.Model { using System; using System.Collections.Generic; using System.Linq; using System.Text; public class StoredProcedureParameter : DomainObject { public StoredProcedureParameter() { } public string StoredProcedure { get; set; } public string ProcedureSchema { get; set; } public string ProcedureName { get; set; } public string ParameterName { get; set; } public string ParameterOrder { get; set; } public string ParameterMode { get; set; } public string SqlDataType { get; set; } public Type DataType { get { return this.SqlDataType.ToClrType(); } } } static class StoredProcedureParameterExtensions { private static Dictionary<string, Type> Mappings; public static StoredProcedureParameterExtensions() { Mappings = new Dictionary<string, Type>(); Mappings.Add("bigint", typeof(Int64)); Mappings.Add("binary", typeof(Byte[])); Mappings.Add("bit", typeof(Boolean)); Mappings.Add("char", typeof(String)); Mappings.Add("date", typeof(DateTime)); Mappings.Add("datetime", typeof(DateTime)); Mappings.Add("datetime2", typeof(DateTime)); Mappings.Add("datetimeoffset", typeof(DateTimeOffset)); Mappings.Add("decimal", typeof(Decimal)); Mappings.Add("float", typeof(Double)); Mappings.Add("image", typeof(Byte[])); Mappings.Add("int", typeof(Int32)); Mappings.Add("money", typeof(Decimal)); Mappings.Add("nchar", typeof(String)); Mappings.Add("ntext", typeof(String)); Mappings.Add("numeric", typeof(Decimal)); Mappings.Add("nvarchar", typeof(String)); Mappings.Add("real", typeof(Single)); Mappings.Add("rowversion", typeof(Byte[])); Mappings.Add("smalldatetime", typeof(DateTime)); Mappings.Add("smallint", typeof(Int16)); Mappings.Add("smallmoney", typeof(Decimal)); Mappings.Add("text", typeof(String)); Mappings.Add("time", typeof(TimeSpan)); Mappings.Add("timestamp", typeof(Byte[])); Mappings.Add("tinyint", typeof(Byte)); Mappings.Add("uniqueidentifier", typeof(Guid)); Mappings.Add("varbinary", typeof(Byte[])); Mappings.Add("varchar", typeof(String)); } public static Type ToClrType(this string sqlType) { Type datatype = null; if (Mappings.TryGetValue(sqlType, out datatype)) return datatype; throw new TypeLoadException(string.Format("Can not load CLR Type from {0}", sqlType)); } } } 

Answer 7

You can try Wizardby . However, it maps from so-called “native” data types to DbType , which are then trivial to convert to CLR types. If this works, you will need the appropriate IDbTypeMapper - either SqlServer2000TypeMapper or SqlServer2005TypeMapper .

Answer 8

Here is a revision that takes a NULL value.

  public static Type GetClrType(SqlDbType sqlType, bool isNullable) { switch (sqlType) { case SqlDbType.BigInt: return isNullable ? typeof(long?) : typeof(long); case SqlDbType.Binary: case SqlDbType.Image: case SqlDbType.Timestamp: case SqlDbType.VarBinary: return typeof(byte[]); case SqlDbType.Bit: return isNullable ? typeof(bool?) : typeof(bool); case SqlDbType.Char: case SqlDbType.NChar: case SqlDbType.NText: case SqlDbType.NVarChar: case SqlDbType.Text: case SqlDbType.VarChar: case SqlDbType.Xml: return typeof(string); case SqlDbType.DateTime: case SqlDbType.SmallDateTime: case SqlDbType.Date: case SqlDbType.Time: case SqlDbType.DateTime2: return isNullable ? typeof(DateTime?) : typeof(DateTime); case SqlDbType.Decimal: case SqlDbType.Money: case SqlDbType.SmallMoney: return isNullable ? typeof(decimal?) : typeof(decimal); case SqlDbType.Float: return isNullable ? typeof(double?) : typeof(double); case SqlDbType.Int: return isNullable ? typeof(int?) : typeof(int); case SqlDbType.Real: return isNullable ? typeof(float?) : typeof(float); case SqlDbType.UniqueIdentifier: return isNullable ? typeof(Guid?) : typeof(Guid); case SqlDbType.SmallInt: return isNullable ? typeof(short?) : typeof(short); case SqlDbType.TinyInt: return isNullable ? typeof(byte?) : typeof(byte); case SqlDbType.Variant: case SqlDbType.Udt: return typeof(object); case SqlDbType.Structured: return typeof(DataTable); case SqlDbType.DateTimeOffset: return isNullable ? typeof(DateTimeOffset?) : typeof(DateTimeOffset); default: throw new ArgumentOutOfRangeException("sqlType"); } } 

Answer 9

I think there is no built-in for this, but you can use VS to create classes for your tables, and then try to edit them

Answer 10

Why not create a typed dataset and create a VS constructor for you? If the project does not have to adapt at runtime to various schemes, then you should use code generation methods to create your classes, use built-in constructors (for example, typed datasets) or custom ones (schema-> XML-> XSLT->. CS).

Answer 11

Usually I just use the Value property to convert SqlType to my own .NET type. This does most of the work. If I have a corner case, I just write a quick helper function.

 int i = dataReader.GetSqlInt32(0).Value; 

Answer 12

I understand that you are discussing the switch statement, but here is an alternative for Sql Server (similar concepts work for other databases)

Consider using SysObjects to get complete data types and create your class:

 declare @ProcName varchar(255) select @ProcName='Table, View, or Proc' SELECT --DISTINCT b.name , c.name Type , b.xtype , b.length , b.isoutparam FROM sysObjects a INNER JOIN sysCOLUMNs b ON a.id=b.id INNER JOIN systypes c ON b.xtype=c.xtype WHERE a.name=@ProcName order by b.colorder 

Now you just list the DataTable instead of the longer statement.