tags:

views:

2275

answers:

9

I'm sitting down to write a massive switch() statement to turn SQL datatypes into CLR datatypes in order to generate classes from MSSQL stored procedures. I'm using this chart as a reference. Before I get too far into what will probably take all day and be a huge pain to fully test, I'd like to call out to the SO community to see if anyone else has already written or found something in C# to accomplish this seemingly common and assuredly tedious task.

A: 

If you simply do:

object MyObject = reader["MyColumn"];

The object MyObject will already be of the correct type.

EDIT: Ignore me, I miss-read the question.

Kragen
+1  A: 

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 fits, you'll need an appropriate IDbTypeMapper - either SqlServer2000TypeMapper or SqlServer2005TypeMapper.

Anton Gogolev
+19  A: 

This is the one we use. You may want to tweak it (e.g. nullable/non-nullable types etc.) but it should save you most of the typing.

    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");
        }
    }
Greg Beech
Thanks very much!
Chris McCall
A: 

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

Ahmed Said
A: 

Why not create a typed dataset and have the VS designer do the mapping for you? Unless the project has to adapt at runtime to different schemas, then you should use code generation techniques to create your classes, wether the built-in designers (ie. typed datasets) or custom ones (schema->XML->XSLT->.cs).

Remus Rusanu
It does have to adapt to different schemas. I'm writing a generator that takes in an SP name and gens a C# class from the input and output parameters using SQL-DMO (SQL 2000)
Chris McCall
That is also a valid approach. Greg already gave a good answer. Normally I'd recommend also considering using Stream oriented types for the large types (n/varchar/varbinary(max) and xml) but you say you're on SQL2K so it doesn't apply).
Remus Rusanu
+2  A: 

This doesn't directly answer the question as asked, but it does answer a common related one. Once you have an IDataReader you can call IDataRecord.GetFieldType(int) to "[get] the Type information corresponding to the type of Object that would be returned from GetValue."

Doug McClean
If you've got an object that was read from a field, you can also use the SqlMetaData.InferFromValue (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlmetadata.inferfromvalue.aspx)method to work out it's type.
adrianbanks
A: 

Normally I just use the Value property to convert a SqlType to a native .NET type. This does the job most of the time. If I have a corner case, I'll just write a quick helper function.

int i = dataReader.GetSqlInt32(0).Value;
Aaron Daniels
A: 

I understand that you're discussing writing a switch statement, but here's an alternate for Sql Server (similar concepts work for other DBs)

Consider using SysObjects to retrieve the full data types and generate 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're just enumerating a DataTable instead of the longer statement.

+1  A: 
    /****** 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'short', 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;
YordanGeorgiev