Mapping CLR Parameter Data

Mapping CLR Parameter Data

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

Mapping CLR Parameter Data

Updated: 31 October 2008

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	CLR data type (SQL Server)	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	SqlDateTime	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	TimeSpan	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

Other Resources

SQL Server Data Types in the .NET Framework

Help and Information

Getting SQL Server 2008 Assistance

Change History

Updated content
Added SqlGeography, SqlGeometry, and SqlHierarchyId types to the mapping table.