Wednesday, September 28, 2011

Common Language Runtime (CLR) Integration Programming in .NET

When Microsoft introduced common language runtime (CLR) integration support with SQL Server an new window was opened for .NET application developers and SQL server users. They can write stored procedures, triggers, user-defined types, user defined function or user defined aggregate functions using any .NET language of their choice. Previous versions of Visual Studio.NET did not support CLR integration programing because .NET 2003 cannot use the .NET Framework 2.0 assemblies. .NET programming languages provide a richer programming environment for developers than Transact-SQL. All the managed code runs into the common language runtime environment which provides more security to the code besides the normal database engine’s stored procedures available in earlier versions of SQL Server. You should use Transact-SQL when the code will mostly perform data access with little or no procedural logic. Use managed code for CPU-intensive functions and procedures that feature complex logic, or when you want to make use of the BCL of the .NET Framework. The following libraries/namespaces are supported by CLR integration in SQL Server:

• CustomMarshalers
• Microsoft.VisualBasic
• Microsoft.VisualC
• mscorlib
• System
• System.Configuration
• System.Data
• System.Data.OracleClient
• System.Data.SqlXml
• System.Deployment
• System.Security
• System.Transactions
• System.Web.Services
• System.Xml
• System.Core.dll
• System.Xml.Linq.dll

CLR Stored Procedures

Stored procedures cannot be used in scalar expressions, unlike scalar expressions they are able to return tabular format data, and can invoke data definition language (DDL) and data manipulation language (DML) statements and return Output parameters. In CLR, stored procedures are created as public static methods in the .NET framework assembly. This static method can be of void or integer type.  I it returns an integer value, it is treated as return code of procedure as-

EXECUTE @return_status = your_procedure

Read more: .NET Zone
QR: common-language-runtime-clr

Posted via email from Jasper-Net