.NET CLR stored procedures in SQL Server 2005 DB: Good News for Programmers, Shock for DBAs
(Page 1 of 5 )
In this article, I shall introduce you to developing, deploying and testing a .NET based CLR stored procedure with SQL Server 2005 using Visual Studio 2005.
A downloadble file for this article is available
here.
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.
Next: Creating a database project using Visual Studio 2005 >>
More MS SQL Server Articles
More By Jagadish Chaterjee
