有人有一个 C# 函数可以将列的 SQL 数据类型映射到它的 CLR 等效项吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1058322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Anybody got a C# function that maps the SQL datatype of a column to its CLR equivalent?
提问by Chris McCall
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 chartas 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.
我坐下来编写大量的 switch() 语句，将 SQL 数据类型转换为 CLR 数据类型，以便从 MSSQL 存储过程生成类。我正在使用此图表作为参考。在我深入了解可能需要一整天并且完全测试的巨大痛苦之前，我想呼吁 SO 社区，看看是否有人已经在 C# 中编写或找到了一些东西来完成这个看似常见的事情和肯定乏味的任务。
采纳答案by Greg Beech
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");
}
}
回答by Anton Gogolev
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 SqlServer2000TypeMapperor SqlServer2005TypeMapper.
你可以试试Wizardby。但是，它将所谓的“本机”数据类型映射到DbType，然后很容易将其转换为 CLR 类型。如果这合适，您将需要一个合适的IDbTypeMapper-SqlServer2000TypeMapper或SqlServer2005TypeMapper.
回答by Ahmed Said
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
我认为没有内置的，但是您可以使用 VS 为您的表生成类，然后尝试编辑它们
回答by Remus Rusanu
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).
为什么不创建类型化数据集并让 VS 设计人员为您进行映射？除非项目必须在运行时适应不同的模式，否则你应该使用代码生成技术来创建你的类，无论是内置设计器（即类型化数据集）还是自定义设计器（模式->XML->XSLT->. CS）。
回答by Doug McClean
This doesn't directly answer the question as asked, but it does answer a common related one. Once you have an IDataReaderyou can call IDataRecord.GetFieldType(int)to "[get] the Typeinformation corresponding to the type of Objectthat would be returned from GetValue."
这并没有直接回答所问的问题，但它确实回答了一个常见的相关问题。一旦你有了一个，IDataReader你就可以调用IDataRecord.GetFieldType(int)“[获取]与将从 返回Type的类型相对应的信息。”ObjectGetValue
回答by Aaron Daniels
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.
通常我只是使用 Value 属性将 SqlType 转换为原生 .NET 类型。这在大多数情况下都可以完成。如果我有一个角落案例，我会写一个快速的帮助函数。
int i = dataReader.GetSqlInt32(0).Value;
回答by Aaron Daniels
I understand that you're discussing writing a switch statement, but here's an alternate for Sql Server (similar concepts work for other DBs)
我知道您正在讨论编写 switch 语句，但这是 Sql Server 的替代方案（类似的概念适用于其他数据库）
Consider using SysObjects to retrieve the full data types and generate your class:
考虑使用 SysObjects 来检索完整的数据类型并生成您的类：
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.
现在您只是枚举一个 DataTable 而不是更长的语句。
回答by Yordan Georgiev
/****** 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;
回答by jared.g
I include this extension (you could easily exchange the string key in the dictionary for SqlDbType as Greg has implemented - or even support both) in my model and expose a property that converts the CLR Type:
我在我的模型中包含了这个扩展（您可以轻松地将字典中的字符串键交换为 SqlDbType，因为 Greg 已实现 - 甚至支持两者）并公开一个转换 CLR 类型的属性：
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));
}
}
}
回答by Tarik H.
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;
}
