Bagikan melalui


Mapping CLR Parameter Data

The following table lists Microsoft SQL Server data types, their equivalents in the common language runtime (CLR) for SQL Server in the System.Data.SqlTypes namespace, and their native CLR equivalents in the Microsoft .NET Framework.

SQL Server data type

Type (in System.Data.SqlTypes or Microsoft.SqlServer.Types)

CLR data type (.NET Framework)

bigint

SqlInt64

Int64, Nullable<Int64>

binary

SqlBytes, SqlBinary

Byte[]

bit

SqlBoolean

Boolean, Nullable<Boolean>

char

None

None

cursor

None

None

date

SqlDateTime

DateTime, Nullable<DateTime>

datetime

SqlDateTime

DateTime, Nullable<DateTime>

datetime2

None

DateTime, Nullable<DateTime>

DATETIMEOFFSET

None

DateTimeOffset, Nullable<DateTimeOffset>

decimal

SqlDecimal

Decimal, Nullable<Decimal>

float

SqlDouble

Double, Nullable<Double>

geography

SqlGeography

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.

None

geometry

SqlGeometry

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.

None

hierarchyid

SqlHierarchyId

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.

None

image

None

None

int

SqlInt32

Int32, Nullable<Int32>

money

SqlMoney

Decimal, Nullable<Decimal>

nchar

SqlChars, SqlString

String, Char[]

ntext

None

None

numeric

SqlDecimal

Decimal, Nullable<Decimal>

nvarchar

SqlChars, SqlString

SQLChars is a better match for data transfer and access, and SQLString is a better match for performing String operations.

String, Char[]

nvarchar(1), nchar(1)

SqlChars, SqlString

Char, String, Char[], Nullable<char>

real

SqlSingle

Single, Nullable<Single>

rowversion

None

Byte[]

smallint

SqlInt16

Int16, Nullable<Int16>

smallmoney

SqlMoney

Decimal, Nullable<Decimal>

sql_variant

None

Object

table

None

None

text

None

None

time

None

TimeSpan, Nullable<TimeSpan>

timestamp

None

None

tinyint

SqlByte

Byte, Nullable<Byte>

uniqueidentifier

SqlGuid

Guid, Nullable<Guid>

User-defined type(UDT)

None

The same class that is bound to the user-defined type in the same assembly or a dependent assembly.

varbinary

SqlBytes, SqlBinary

Byte[]

varbinary(1), binary(1)

SqlBytes, SqlBinary

byte, Byte[], Nullable<byte>

varchar

None

None

xml

SqlXml

None

Automatic Data Type Conversion with Out Parameters

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 Server data type as the input parameter, a type conversion occurs automatically when the CLR method returns the data type.

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

After the assembly is built and created in the database, the stored procedure is created in SQL Server with the following Transact-SQL, which specifies a SQL Server data type of int as an OUTPUT parameter:

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

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.

Not all CLR data types can be automatically converted to their equivalent SQL Server data types through an out parameter, however. The following table lists these exceptions.

CLR data type (SQL Server)

SQL Server data type

Decimal

smallmoney

SqlMoney

smallmoney

Decimal

money

DateTime

smalldatetime

SQLDateTime

smalldatetime