對應 CLR 參數資料Mapping CLR Parameter Data

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

下表列出MicrosoftMicrosoftSQL ServerSQL Server資料類型、 common language runtime (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 NoneNone NoneNone
cursorcursor NoneNone NoneNone
datedate SqlDateTimeSqlDateTime 日期時間,可為 Null<日期時間 >DateTime, Nullable<DateTime>
datetimedatetime SqlDateTimeSqlDateTime 日期時間,可為 Null<日期時間 >DateTime, Nullable<DateTime>
datetime2datetime2 NoneNone 日期時間,可為 Null<日期時間 >DateTime, Nullable<DateTime>
DATETIMEOFFSETDATETIMEOFFSET None DateTimeOffset,可為 Null<DateTimeOffset >DateTimeOffset, Nullable<DateTimeOffset>
decimaldecimal SqlDecimalSqlDecimal Decimal,可為 Null<十進位 >Decimal, Nullable<Decimal>
floatfloat SqlDoubleSqlDouble Double,可為 Null<雙 >Double, Nullable<Double>
地理位置geography SqlGeographySqlGeography

SqlGeography定義於 Microsoft.SqlServer.Types.dll 中,它會隨 SQL Server,並可以從下載SQL Server 2019 (15.x)SQL Server 2019 (15.x)功能套件SqlGeography is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2019 (15.x)SQL Server 2019 (15.x) feature pack.
NoneNone
幾何geometry SqlGeometrySqlGeometry

SqlGeometry定義於 Microsoft.SqlServer.Types.dll 中,它會隨 SQL Server,並可以從下載SQL Server 2019 (15.x)SQL Server 2019 (15.x)功能套件SqlGeometry is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2019 (15.x)SQL Server 2019 (15.x) feature pack.
NoneNone
hierarchyidhierarchyid SqlHierarchyIdSqlHierarchyId

SqlHierarchyId定義於 Microsoft.SqlServer.Types.dll 中,它會隨 SQL Server,並可以從下載SQL Server 2019 (15.x)SQL Server 2019 (15.x)功能套件SqlHierarchyId is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2019 (15.x)SQL Server 2019 (15.x) feature pack.
NoneNone
imageimage NoneNone 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 NoneNone NoneNone
numericnumeric SqlDecimalSqlDecimal Decimal,可為 Null<十進位 >Decimal, Nullable<Decimal>
nvarcharnvarchar SqlChars SqlStringSqlChars, SqlString

SQLChars是較佳的相符項目,如資料傳輸和存取,並SQLString是比較適合執行 String 作業。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、 String,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 NoneNone Byte[]Byte[]
smallintsmallint SqlInt16SqlInt16 Int16,可為 Null<Int16 >Int16, Nullable<Int16>
smallmoneysmallmoney SqlMoneySqlMoney Decimal,可為 Null<十進位 >Decimal, Nullable<Decimal>
sql_variantsql_variant NoneNone 物件Object
tabletable NoneNone NoneNone
texttext NoneNone NoneNone
timetime NoneNone 時間範圍,可為 Null<TimeSpan >TimeSpan, Nullable<TimeSpan>
timestamptimestamp NoneNone NoneNone
tinyinttinyint SqlByteSqlByte 位元組,可為 Null<位元組 >Byte, Nullable<Byte>
uniqueidentifieruniqueidentifier SqlGuidSqlGuid Guid,可為 Null<Guid >Guid, Nullable<Guid>
使用者定義的 type(UDT)User-defined type(UDT) NoneNone 繫結到相同組件或相依組件中之使用者定義型別的相同類別。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、 Byte []、 Nullable<位元組 >byte, Byte[], Nullable<byte>
varcharvarchar NoneNone NoneNone
xmlxml SqlXmlSqlXml NoneNone

利用 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