.NET CLR stored procedures in SQL Server 2005 DB: Good News for Programmers, Shock for DBAs

Powerful databases for “extended” Stored Procedures

Every programmer would definitely agree that “Stored Procedures” are very important at the data access tier, as they boost the performance of applications to the optimum level.  Almost every relational database management system (RDBMS) existing today supports stored procedures in one way or another.

To work with stored procedures for a particular RDBMS, we need to study and work with the “native” database supported data access language (with its own syntax and semantics).  Examples include T-SQL (or Transact SQL) for Microsoft SQL Server 2000, PL/SQL for Oracle, and so on.  That is an old story now.  Day by day, major RDBMS products are trying to expand beyond their “nativeness,” providing flexibility and integration with other products, together with high security and performance.

The trend of “stored procedures” expanded even to the capability of supporting “external language” based stored procedures!  Even though this is good news for the programmers, it is quite a challenge for database administrators and database designers or architects from now on.  Just imagine how much time they will need to spend on learning and working with the “external languages.”  There is no doubt that from now on, all technical persons will have to work more closely than ever with their fellow technical persons.

As I am trying to focus on SQL Server 2005 database in this article, let us examine its capabilities.  Everyone knows that the SQL Server database supports native stored procedures with the help of Transact-SQL (or T-SQL).  SQL Server began supporting stored procedures in very early versions (officially from version 6.5). Now, it has expanded its capability, even to .NET based CLR stored procedures within SQL Server 2005! 

Now you can develop stored procedures in any .NET compliant languages (like VB.NET, C#, and so on) and deploy them in a SQL Server 2005 database, as if they were native stored procedures.  Best of all, the development and deployment has been made quite simple, as Microsoft tightly integrated SQL Server 2005 with Visual Studio 2005 IDE. Now the .NET CLR stored procedures can be developed with the “intellisense” features of Visual Studio 2005.

Another popular RDBMS (or even ORDBMS product), Oracle 10g (version 10.2) also began supporting “.NET CLR stored procedures” (along with PL/SQL stored procedures).  I contributed several articles on that topic as well.  One important issue to understand is that (at the time of this writing) Oracle-based CLR stored procedures work with .NET version 1.1.  On the other hand, SQL Server 2005 based CLR stored procedures work with .NET version 2.0.

Before proceeding further with this article, understand that it focuses on the following suite of products from Microsoft:

·         Microsoft SQL Server 2005 Enterprise Edition

·         Microsoft Visual Studio 2005 Professional Edition

Let us go through the following steps to create a new data base project using Visual Studio 2005:

·         Open Visual Studio 2005 IDE.

·         Go to menu File -> New -> Project.

·         Within the “New Project” dialog box, select “database” (under visual basic) as the project type and select “SQL Server Project” as the template.  Provide your own project name, choose your own location and finally click OK (Figure 1).

·         You will be prompted with the “New Database Reference” dialog, where you need to provide the instance name of your SQL Server 2005 installation along with your credentials and database to connect (Figure 2).  If only one SQL Server installation has taken place on your system, without any instance name, you can simply provide “.” as the server name.  You can test the details by clicking on the “Test Connection” button.  Once everything is tested, press OK.

·         When you are prompted for “SQL/CLR debugging” (Figure 3) press “YES”. 

 Once you complete all of these steps, you should be able to see the “server explorer” looking something like this image (Figure 4).

Once you are connected, you can play with all the items existing within the “Server Explorer” along with creating tables, inserting, modifying, deleting, dropping, designing relations, stored procedures (using T-SQL), and so on. Enjoy playing with it; finally, create a new table using “Server explorer” with structure and data as shown in the following figure (Figure 5).

Once you complete all of the steps in the previous section, proceed with the following steps to develop a .NET CLR based stored procedure using Visual Studio 2005.

·         Go to the Solution Explorer, right click on project and go to Add -> Stored Procedures (shown in Figure 6).

·         Select “Stored procedure” as in the template, provide the name of the stored procedure as “dsp_IncSalary.vb” and finally click “add” (shown in Figure 7).

·         Modify the code as follows:

Once you complete all of the steps in the previous section, proceed with the following steps to deploy the .NET CLR based stored procedure using Visual Studio 2005.

·         Press Ctrl+Alt+L to open “Solution Explorer”

·         Right click on and select “Deploy” (shown in Fig8)

·         The program starts building your solution and finally deploys it.  While the process is going on, you need to observe the status bar of Visual Studio IDE.  Once you complete deployment, the status bar should look something like the following image (Figure 9).

To execute the .NET CLR stored procedure from within Visual Studio 2005, open “Solution Explorer” from “View” menu, open “Test Scripts” and open “Test.sql”.  Go down to the last line of the same file and modify as shown in Figure 10.

Finally, press F5 to execute the solution.  The program rebuilds it, deploys it and executes your “test.sql”.  Now you can check your “emp” table in the database, which gets all values of “sal” column incremented by 100 as shown in the following Fig 11.

When to use .NET CLR stored procedures and Transact-SQL procedures

When writing stored procedures, triggers and user-defined functions, one decision you must make is whether to use traditional T-SQL, or a .NET Framework language such as Visual Basic .NET or Visual C# .NET. Use T-SQL when the code will mostly perform data access with little or no procedural logic. Use .NET CLR based stored procedures for CPU-intensive functions and procedures that feature complex logic, or when you want to make use of the BCL (Base Class Library) of the .NET Framework.

Another important issue to consider before comparing the two major RDBMS, MS SQL Server 2005 and Oracle 10g, is that Microsoft SQL Server 2005 supports CLR integration based on .NET version 2.0,  whereas Oracle 10.2 supports only .NET version 1.1 (at the time of this writing).

Coming to the above program, the most important lines are the following statements:

The method which resembles stored procedure (in this case “dsp_IncSalary”) should be declared “shared” and be preceded by “<Microsoft.SqlServer.Server.SqlProcedure()>”.  This is essential to treating the method as a stored procedure.

You can also observe that the connection string is quite different from traditional connection strings used in ADO.NET.  Here in this scenario, we are not working with any new connection opened for our application.  It connects using the SQL Server 2005 context itself (which is also called an in-process connection).  As the data access happens within the same process of SQL Server 2005, it is much faster in performance when compared with ordinary ADO.NET based data access.

Even though I examined only a stored procedure in the above scenario, you can develop user-defined functions, triggers, aggregates, user-defined types etc. as well.  Check this section regularly for new articles on the same concept.

I developed this entire solution (downloadable from the first page of this article) using Microsoft Visual Studio 2005 Professional Edition on Windows Server 2003 standard edition running Microsoft SQL Server 2005 enterprise edition.  I didn’t really test it on any other similar suite of products from Microsoft.  I leave it to the readers for further investigation on “support” issues.

I could not go very in-depth into the CLR details (or even ADO.NET) as it is beyond the scope of this article.  But, if you really need them, you can go through my list of articles related to that subject within this site.

I leave it to the programmers for further enhancements. Any doubts, comments, suggestions, bugs, errors or feedback are welcomed at jag_chat@yahoo.com