.NET CLR stored procedures in SQL Server 2005 DB: Good News for Programmers, Shock for DBAs - When to use .NET
(Page 5 of 5 )
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:
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub dsp_IncSalary()
Dim conn As New SqlConnection("context connection=true")
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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 14 
