如何以编程方式将SQL数据类型转换为.Net数据类型？

Can anyone show me a way of converting SQL Server data-types (varchar for example) to .Net data-types (String for example). I'm assuming that automatic conversion is not possible? I have an 'EntityProperty' object and would like it to have an appropriate 'Type' property (string, decimal, int32 etc), at the moment this property is just a string - 'int32' for example.

任何人都可以告诉我一种将SQL Server数据类型（例如varchar）转换为.Net数据类型（例如String）的方法。我假设自动转换是不可能的？我有一个'EntityProperty'对象，并希望它有一个合适的'Type'属性（字符串，十进制，int32等），此时此属性只是一个字符串 - 例如'int32'。

A little background: I'm using SQL DMO in an internal code generation app to query a database and generate a stored procedure based DAL from the database. Being an internal app I can take quite a few shortcuts and make quite a few assumptions. To get the app working at the moment this data-type conversion is handled by a Select Case statement which just converts the types to strings and generates a set of properties based on these strings but I would prefer a little more flexibility in being able to handle the types (use of TypeOf etc).

一点背景：我在内部代码生成应用程序中使用SQL DMO来查询数据库并从数据库生成基于DAL的存储过程。作为一个内部应用程序，我可以采取相当多的快捷方式，并做出一些假设。为了使应用程序正常运行，此数据类型转换由Select Case语句处理，该语句只是将类型转换为字符串并根据这些字符串生成一组属性，但我希望能够更灵活地处理类型（使用TypeOf等）。

Anyone worked on something similar?

有人在做类似的事吗？

I know EF, nHibernate, Subsonic etc could do all this for me but in this case, for various reasons, I am having to roll my own. :)

我知道EF，nHibernate，Subsonic等可以为我做这一切，但在这种情况下，出于各种原因，我不得不自己动手。 :)

5 个解决方案

#1

I've done something like this in the other direction, using a Dictionary of System.Type objects to SQL type names.

我已经在另一个方向上做了类似的事情，使用System.Type对象的Dictionary到SQL类型名称。

#2

The reason hard coding is a Bad Thing is only because when you put things in code that change, it's annoying (and expensive) - there's no other reason. Things that don't change, like pi, or the list of weekdays, can be hard-coded to your heart's content, and you won't incur any extra development cost as a result.

硬编码是一件坏事的原因只是因为当你把代码放在代码中改变时，它很烦人（并且很昂贵） - 没有其他原因。不会发生变化的事情，如pi或工作日列表，可能会根据您的内容进行硬编码，因此您不会产生任何额外的开发成本。

So this problem isn't so much about not maintaining a manual mapping table - in code if necessary - as it is about only maintaining the mapping table in one place.

所以这个问题不是关于不维护手动映射表 - 如果需要的话在代码中 - 因为它只是在一个地方维护映射表。

We rolled our own data access class, several years ago now. And sure, we convert manually (in a VB.NET Select Case statement) from .NET types to SQL types. I think it changed once, when we had to add Enum types.

几年前，我们推出了自己的数据访问类。当然，我们手动（在VB.NET Select Case语句中）从.NET类型转换为SQL类型。当我们不得不添加Enum类型时，我认为它改变了一次。

That's once, in about four years. We do a release a week, on average - guess how worried we are about the 'overhead' of hard coding the .NET -> SQL type mapping?

这是一次，大约四年。我们平均每周发布一次 - 猜猜我们对于硬编码.NET - > SQL类型映射的'开销'有多担心？

Do it in one place. Ensure that everything uses it. And then forget about it. There are other, much tougher problems to solve.

在一个地方做。确保一切都使用它。然后忘记它。还有其他更棘手的问题需要解决。

#3

There is no way to "automatically" perform the type conversion. In fact, most ORM libraries rely on the actual property type used in the destination entity class in order to perform the mapping.

无法“自动”执行类型转换。实际上，大多数ORM库依赖于目标实体类中使用的实际属性类型来执行映射。

I would use the SQL-CLR Type Mapping from the Linq to SQL documentation as a starting point for building manual mapping code. In many cases there will be more than one valid mapping.

我将使用Linq到SQL文档中的SQL-CLR类型映射作为构建手动映射代码的起点。在许多情况下，将有多个有效映射。

#4

Or you could a table for your automatic translation and than use those values ( this is a preliminary one, not largely tested ... ) :

或者您可以使用自动翻译的表格，而不是使用这些值（这是初步的，未经过大量测试...）：

and USED IT DIRECTLY FOR CLASS GENERATION or even if you like generate the classes for the whole db

并直接用于CLASS GENERATION，或者即使你喜欢为整个数据库生成类

        /****** 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;

#5

I know EF, nHibernate, Subsonic etc could do all this for me but in this case, for various reasons, I am having to roll my own. :)

我知道EF，nHibernate，Subsonic等可以为我做这一切，但在这种情况下，出于各种原因，我不得不自己动手。 :)

Why don't you use SubSonic or one of the other ORM mapping tools to define working conversions between Sql datatypes and .Net datatypes - and then roll your own solution using this information on the conversions as a basis?

为什么不使用SubSonic或其他ORM映射工具来定义Sql数据类型和.Net数据类型之间的工作转换 - 然后使用转换的这些信息作为基础来推广您自己的解决方案？

I'm assuming that you can't use third-party software in the solution - but you can in coming to a solution.

我假设你不能在解决方案中使用第三方软件 - 但你可以找到解决方案。

#1