This is my first foray into writing stored procedures for SQL Server in managed code. I decided to check it out since I was already doing some other SQL Server work in support of a WPF-based Agile project management application and thought this would be a good opportunity to explore a little and see how it might apply.
Let me be clear, this is a post really to capture my thoughts and experience along the way. I’ll include links and quotes where it seems applicable.
Decision Points
From “Overview of CLR Integration” - http://msdn.microsoft.com/en-us/library/ms131045.aspx
Choosing Between Transact-SQL and Managed Code
When writing stored procedures, triggers, and user-defined functions, one decision you must make is whether to use traditional Transact-SQL, or a .NET Framework language such as Visual Basic .NET or Visual C#. Use Transact-SQL when the code will mostly perform data access with little or no procedural logic. Use managed code for CPU-intensive functions and procedures that feature complex logic, or when you want to make use of the BCL of the .NET Framework.
Choosing Between Execution in the Server and Execution in the Client
Another factor in your decision about whether to use Transact-SQL or managed code is where you would like your code to reside, the server computer or the client computer. Both Transact-SQL and managed code can be run on the server. This places code and data close together, and allows you to take advantage of the processing power of the server. On the other hand, you may wish to avoid placing processor intensive tasks on your database server. Most client computers today are very powerful, and you may wish to take advantage of this processing power by placing as much code as possible on the client. Managed code can run on a client computer, while Transact-SQL cannot.
* Note: I have some reservations about the suggestions made in the last section. While many business-class PCs today have more processing power, and while scenarios may exist where it would be nice to distribute the computational work to the client, it is likely unreasonable to place the burden of processing on the client PC due to the need to transfer the required data to the client. This seems like a corner case, which probably doesn’t happen often.
Advantages
While reading through the various articles, I came across this list of advantages for using managed code instead of Transact-SQL.
- Enhanced programming model
- Enhanced Safety and Security
- User-Defined Types and Aggregates
- Common Development Environment
- Better Performance (for computational sorts of logic; see the point above regarding straight data access)
- Language richness
- Reusability of code
- Extensibility
- Leverage existing skills
- Richer development experience
- Stability and reliability
Step-by-Step
1) Create the database project in your solution. Click the database category and choose the “SQL Server Project”. Give it a name and choose the directory where you want it created.
The wizard will create a .SQL script from which you can test the database objects that you create.
2) I decided to create a subfolder in which I’ll place my new stored procedures
3) After that, right-click the new folder and add a new stored procedure; give it a descriptive name. Use a naming convention that lets you know what the stored procedure does. I prefer to prefix my stored procedures with the name of the module of the app that it applies to; e.g., Products_InsertNewProduct or Products_SelectProductById
4) The stored procedure template will give you a basic outline for a stored procedure. The first thing you’ll notice is that it creates a static method on a partial class. Also, the method is marked up with the Microsoft.SqlServer.Server.SqlProcedure attribute.
5) Modify the signature of the method to include the parameters that you want
6) The implementation of this is pretty much plain vanilla ADO.NET; There are a couple of minor differences to make note of:
a. The connection string for the SqlConnection is based on the current context that the stored procedure is running under:
”context connection = true”b. When returning data, you use the SqlPipe, which is accessible from the SqlContext
7) Next, compile and deploy the project. Both of these commands are available from the “Build” menu.
That’s about it for now. You can use the Test.Sql script that is generated in the project in order to test your procedures. Going forward, I see lots of potential for code reuse. My next post on managed code for SQL Server will focus on triggers and user defined types.
Have fun!
Related Links
No comments:
Post a Comment