映射 CLR 参数数据Mapping CLR Parameter Data

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

下表列出MicrosoftMicrosoftSQL ServerSQL Server数据类型,公共语言运行时 (CLR) 为中的等效项SQL ServerSQL ServerSystem.Data.SqlTypes命名空间,在及其本机CLR等效项MicrosoftMicrosoft .NET framework。The following table lists MicrosoftMicrosoft SQL ServerSQL Server data types, their equivalents in the common language runtime (CLR) for SQL ServerSQL Server in the System.Data.SqlTypes namespace, and their native CLR equivalents in the MicrosoftMicrosoft .NET Framework.

SQL Server 数据类型SQL Server data type 类型(在 System.Data.SqlTypes 或 Microsoft.SqlServer.Types 中)Type (in System.Data.SqlTypes or Microsoft.SqlServer.Types) CLR 数据类型 (.NET Framework)CLR data type (.NET Framework)
bigintbigint SqlInt64SqlInt64 Int64,可以为 Null<Int64 >Int64, Nullable<Int64>
binarybinary SqlBytes SqlBinarySqlBytes, SqlBinary Byte[]Byte[]
bitbit SqlBooleanSqlBoolean 布尔值、 可以为 Null<布尔 >Boolean, Nullable<Boolean>
charchar None None
cursorcursor None NoneNone
datedate SqlDateTimeSqlDateTime 日期时间,可以为 Null<日期时间 >DateTime, Nullable<DateTime>
datetimedatetime SqlDateTimeSqlDateTime 日期时间,可以为 Null<日期时间 >DateTime, Nullable<DateTime>
datetime2datetime2 NoneNone 日期时间,可以为 Null<日期时间 >DateTime, Nullable<DateTime>
DATETIMEOFFSET DATETIMEOFFSET None DateTimeOffset,可以为 Null<DateTimeOffset >DateTimeOffset, Nullable<DateTimeOffset>
decimaldecimal SqlDecimalSqlDecimal Decimal、 可以为 Null<十进制 >Decimal, Nullable<Decimal>
floatfloat SqlDoubleSqlDouble 双精度,可以为 Null<双精度 >Double, Nullable<Double>
地理geography SqlGeographySqlGeography

SqlGeography Microsoft.SqlServer.Types.dll 中定义,这与 SQL Server 安装,可以从下载SQL Server 2017SQL Server 2017功能包SqlGeography is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2017SQL Server 2017 feature pack.
None
geometrygeometry SqlGeometrySqlGeometry

SqlGeometry Microsoft.SqlServer.Types.dll 中定义,这与 SQL Server 安装,可以从下载SQL Server 2017SQL Server 2017功能包SqlGeometry is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2017SQL Server 2017 feature pack.
None
hierarchyidhierarchyid SqlHierarchyIdSqlHierarchyId

SqlHierarchyId Microsoft.SqlServer.Types.dll 中定义,这与 SQL Server 安装,可以从下载SQL Server 2017SQL Server 2017功能包SqlHierarchyId is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2017SQL Server 2017 feature pack.
NoneNone
imageimage None NoneNone
intint SqlInt32SqlInt32 Int32 类型,可以为 Null<Int32 >Int32, Nullable<Int32>
moneymoney SqlMoneySqlMoney Decimal、 可以为 Null<十进制 >Decimal, Nullable<Decimal>
ncharnchar SqlChars SqlStringSqlChars, SqlString String,Char]String, Char[]
ntextntext None None
numericnumeric SqlDecimalSqlDecimal Decimal、 可以为 Null<十进制 >Decimal, Nullable<Decimal>
nvarcharnvarchar SqlChars SqlStringSqlChars, SqlString

SQLChars更好的匹配的数据传输和访问权限,并SQLString是执行字符串操作的更好的匹配项。SQLChars is a better match for data transfer and access, and SQLString is a better match for performing String operations.
String,Char]String, Char[]
nvarchar(1)、 nchar(1)nvarchar(1), nchar(1) SqlChars SqlStringSqlChars, SqlString Char、 字符串、 Char [],可以为 Null<char >Char, String, Char[], Nullable<char>
realreal SqlSingle (的范围SqlSingle,但是,大于实际)SqlSingle (the range of SqlSingle, however, is larger than real) 单一的可以为 Null<单一 >Single, Nullable<Single>
rowversionrowversion None Byte[]Byte[]
smallintsmallint SqlInt16SqlInt16 Int16,可以为 Null<Int16 >Int16, Nullable<Int16>
smallmoneysmallmoney SqlMoneySqlMoney Decimal、 可以为 Null<十进制 >Decimal, Nullable<Decimal>
sql_variantsql_variant None ObjectObject
tabletable None NoneNone
texttext None NoneNone
timetime None TimeSpan,可以为 Null<TimeSpan >TimeSpan, Nullable<TimeSpan>
timestamptimestamp None NoneNone
tinyinttinyint SqlByte 字节,可以为 Null<字节 >Byte, Nullable<Byte>
uniqueidentifieruniqueidentifier SqlGuidSqlGuid Guid,可以为 Null<Guid >Guid, Nullable<Guid>
用户定义 type(UDT)User-defined type(UDT) None 绑定到相同程序集或依赖程序集中的用户定义类型的相同类。The same class that is bound to the user-defined type in the same assembly or a dependent assembly.
varbinaryvarbinary SqlBytes SqlBinarySqlBytes, SqlBinary Byte[]Byte[]
varbinary(1)、 binary(1)varbinary(1), binary(1) SqlBytes SqlBinarySqlBytes, SqlBinary 字节、 Byte [],可以为 Null<字节 >byte, Byte[], Nullable<byte>
varcharvarchar None None
xmlxml SqlXmlSqlXml None

使用 Out 参数的自动数据类型转换Automatic Data Type Conversion with Out Parameters

CLR 方法可以通过将标记与输入的参数向调用代码或程序返回的信息修饰符 (Microsoft Visual C#) 或 <out () > ByRef (Microsoft Visual Basic)如果输入的参数是中的 CLR 数据类型System.Data.SqlTypes命名空间,并且调用程序指定其等效SQL ServerSQL Server数据类型作为输入参数,自动发生类型转换当 CLR 方法返回的数据类型。A CLR method can return information to the calling code or program by marking an input parameter with the out modifier (Microsoft Visual C#) or <Out()> ByRef (Microsoft Visual Basic) If the input parameter is a CLR data type in the System.Data.SqlTypes namespace, and the calling program specifies its equivalent SQL ServerSQL Server data type as the input parameter, a type conversion occurs automatically when the CLR method returns the data type.

例如,以下 CLR 存储过程具有输入的参数的SqlInt32 CLR 数据类型将标有out (C#) 或 <out () > ByRef (Visual Basic 中):For example, the following CLR stored procedure has an input parameter of SqlInt32 CLR data type that is marked with out (C#) or <Out()> ByRef (Visual Basic):

[Microsoft.SqlServer.Server.SqlProcedure]  
public static void PriceSum(out SqlInt32 value)  
{ ... }  
\<Microsoft.SqlServer.Server.SqlProcedure> _  
Public Shared Sub PriceSum( \<Out()> ByRef value As SqlInt32)  
...  
End Sub  

生成并在数据库中创建程序集后,在创建此存储的过程SQL ServerSQL Server使用以下 TRANSACT-SQL,它指定SQL ServerSQL Server数据类型int作为输出参数:After the assembly is built and created in the database, the stored procedure is created in SQL ServerSQL Server with the following Transact-SQL, which specifies a SQL ServerSQL Server data type of int as an OUTPUT parameter:

CREATE PROCEDURE PriceSum (@sum int OUTPUT)  
AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum  

CLR 存储调用过程时, SqlInt32数据类型自动转换为int数据类型,并返回到调用程序。When the CLR stored procedure is called, the SqlInt32 data type is automatically converted to an int data type, and returned to the calling program.

但是,并非所有 CLR 数据类型都可以通过 out 参数自动转换到其等效的 SQL ServerSQL Server 数据类型。Not all CLR data types can be automatically converted to their equivalent SQL ServerSQL Server data types through an out parameter, however. 下表列出了这些例外类型。The following table lists these exceptions.

CLR 数据类型 (SQL Server)CLR data type (SQL Server) SQL Server 数据类型SQL Server data type
十进制Decimal SMALLMONEYsmallmoney
SqlMoneySqlMoney SMALLMONEYsmallmoney
十进制Decimal moneymoney
DateTimeDateTime smalldatetimesmalldatetime
SQLDateTimeSQLDateTime smalldatetimesmalldatetime

更改历史记录Change History

更新的内容Updated content
添加SqlGeographySqlGeometry,并SqlHierarchyId映射表的类型。Added SqlGeography, SqlGeometry, and SqlHierarchyId types to the mapping table.

请参阅See Also

.NET Framework 中的 SQL Server 数据类型SQL Server Data Types in the .NET Framework