Creating Stored Procedures with Managed Code

Introduction

SQL is by concept a language to manipulate sets of data; therefore Microsoft SQL Server 2005 database system uses T-SQL (Transact SQL) for writing structure code to control the data flow. Prior to Microsoft SQL Server 2005, the only way to write procedures and functions was by using T-SQL, but now, Microsoft SQL Server 2005 provides an integration with the Common Language Runtime (CLR), and consequently the procedures and functions can be written using managed code in any .NET language such as C#. This article is intended to illustrate how to implement a stored procedure with managed code.

The business scenario

As illustrative purposes, we're going to develop a stored procedure to return a list of products by its subcategory using the AdventureWorks database and Production.Product table shipped with the installation of Microsoft SQL Server 2005 (Listing 1). The main steps are to create a class and the underlying business logic to get a list of products, build this class into an assembly, register the assembly in the SQL Server engine, and then create a stored procedure in the database which is an interface to the corresponding method in the class hosted in the assembly.

select *
from Production.Product
where ProductSubcategoryID=@ProductSubcategoryID;

Listing 1

Developing the solution

The first step is to create a SQL Server project by opening Visual Studio.NET 2005 and select File | New | Project

Read more: C# Corner

Posted via email from jasper22's posterous