KeyLimeTie Blog

With SQL Server 2005, you can use .NET Framework languages to create database objects and retrieve and update data. You can create stored procedures, triggers, aggregates, user-defined functions, and user-defined types, using any one of the CLR-compliant languages. In this article, I will cover the steps involved in creating and debugging a stored procedure in C#.

To begin, it's worth noting that there are several advantages to using managed code over T-SQL:

Enhanced programming model    .NET Framework languages offer constructs and capabilities previously unavailable to SQL developers. Visual Basic, Visual C#, and Visual C++ provide capabilities that are not available in Transact-SQL, such as arrays, sophisticated exception handling, and reusability of code.

Reusability of Code    A library of managed assemblies can be created and distributed more easily than a Transact-SQL script can be distributed. 

Leverage Existing Skills    You can use and enhance your skills in the languages and development environment in which you are already experienced to create database objects.

Richer developer experience    When you develop database objects using the SQL Server project template, you have complete integration with the project system, including building, debugging, and deployment to multiple servers.

Security    When you use database objects created using Visual Basic, Visual C#, or Visual C++, the code-access security of those languages is combined with the user-based permissions in SQL Server.

Creating the Database Project

The first step in creating a stored procedure (or any database object) is to create a SQL Server project in Visual Studio:

1. From the File menu, create a new project.

2. In the New Project Dialog, select and expand a language node in the Project Types area.

3. Select the Database node.

4. Select the SQL Server Project template.

5. Click OK.

Next, you will prompted for the database connection you are using. Unless this database has had CLR-integration enabled previously, you will need to run the following in SQL Server:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Creating the Stored Procedure

At this point, you are ready to start creating database objects. To create a new stored procedure, do the following steps:
1. Open an existing SQL Server Project, or create a new one.  From the Project menu, select Add New Item.

2. Select Stored Procedure.

3. Type a Name for the new stored procedure.

4. Add code to run when the stored procedure is executed.

5. In the sample code below, I have created a stored procedure that accepts one parameter and queries the Product table of the Adventure Works database. The code should look very familiar, utilizing Connection, Command and Parameter objects. One object that may not be familiar is the SQLPipe. This allows managed stored procedures running in-process on a SQL Server database to return results back to the caller.

    [Microsoft.SqlServer.Server.SqlProcedure]
    publicstaticvoid SelectProductByProductID(int productID)
    {
        using (SqlConnection conn = 
new SqlConnection("context connection=true"))
        {
            try
            {
                SqlPipe pipe = null;

                SqlCommand SelectProductCommand = new SqlCommand();

                SqlParameter productIDParam = 
new SqlParameter("@ProductID", SqlDbType.Int);

                productIDParam.Value = productID;

                SelectProductCommand.Parameters.Add(productIDParam);
                SelectProductCommand.CommandText = 
"Select * from Production.Product where AProductID = @ProductID";

                conn.Open();

                SelectProductCommand.Connection = conn;

                pipe = SqlContext.Pipe;
                pipe.ExecuteAndSend(SelectProductCommand);
            }
            catch (SqlException sqlEx)
            {
                //code to handle or log the sql exception here 
                Console.WriteLine(sqlEx);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }
    }

Once the code is ready, you can build and deploy the stored procedure to the database by pressing 'F5'. To test the deployment, there is a file in your project called test.sql. In this file, you can add script calls to the obects you just created. For example, to call the stored procedure created above, enter:

EXEC [dbo].[SelectProductByProductID] 4

That's it. If the stored procedure was written correctly, you will now see this item in list of stored procedures in Enterprise Manager. However, you'll see the object has a lock icon next to it, as shown below. This is because the object is not editable outside of the Visual Studio project.





Conclusion

At this point, you might be asking 'so where are the benefits?'. Let's look at a few:

Exception Handling: if the stored procedure returns an error, say a constraint violation for example, this error gets converted into a SqlException that you can then handle in your managed code. To try this, change the column in the stored procedure text above to AProduct and hit 'F5'. You'll see that the catch block is entered. This gives a lot of flexibility in how you handle errors that don't readily exist in T-SQL.

Deployment: If you need to deploy the objects you created to another server, you simply change the database connection in the project properties and hit 'F5'. No more creating and managing scripted stored procedures.

Comments

Leave a Comment

Name:
Email:
URL:
Comment:
Security Code:
Type Security Code: