Database Access from CLR Procedures


.NET Framework 2.0 has a set of classes that are specifically designed to accommodate access to database objects from CLR procedures. They are extensions of ADO.NET designed for in-process use and are based on the .NET Framework Data Provider for SQL Server, also known as SqlClient. This provider is also used from regular managed applications and components. Therefore, if you are familiar with SqlClient and

ADO.NET, you will be able to leverage this knowledge during development of CLR procedures. CLR procedures do not support the full functionality of ADO.NET, so we will focus on restrictions as well as in-process extensions in SQLCLR.

To start on a positive note, let's briefly review the main in-process ADO.NET extensions. The SqlContext class is used to provide access to the context of the caller from the CLR code running on the server. Its most important use is to provide access to the SqlPipe member (another extension of ADO.NET) that is used to send messages and results to the caller.

SqlPipe also contains the SqlTriggerContext member (another extension) that provides access to the trigger's virtual tables, a map of updated fields, and information about the operation that has fired the trigger.

SqlContext also provides access to the Windows identity of the caller that initiated the process. This is needed to implement impersonalization—to execute operations in the context of the caller, instead of the context of a service account.

Tip 

You can find more information about the SqlContext class in Microsoft Visual Studio 2005 documentation than you can in Books (Mine.

CLR Data Types Mapping

The System.Data.SqlTypes namespace is especially designed for CLR in SQL Server. BOL contains a table with SQL Server data types, their equivalents in the SystemData. SqlTypes namespace, and the native CLR equivalents in .NET Framework. You can find it in Books OnLine under "SQL Server Data Types and Their .NET Framework Equivalents."

I will point to a couple of interesting details. Non-Unicode strings (char, varchar, and varchar (max)) are not supported. You must use Unicode strings (Nchar, Nvarchar, and Nvarchar (max)) or actually their CLR for SQL Server equivalents (SqlChars and SqlString) or .NET equivalents (string or char []). There is a difference between SqlChars and SqlString. SqlChars is a stream-based type that will perform better in stream operations, such as data access and data transfer, while SQLString is tailored for String manipulations.

Old BLOB data types (text, Ntext, and image) are not supported. You must use the new LOB data types (Nvarchar (max) and varbinary (max)). The equivalents for the smalldatetime and datetime data types are SqlDateTime and DateTime. Instead of the numeric and decimal data types, you should use SqlDecimal or Decimal. Exotic Transact-SQL data types such as table, cursor, sql_variant, and timestamp do not have equivalents.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net