Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Creating a Managed Project
You don’t have to perform all of your setup within SQL Server Management Studio (SSMS) or use the T-SQL features of Visual Studio. Visual Studio 2005 provides a special project for creating essentials for your database applications such as stored procedures, triggers, aggregates, user-defined functions, user-defined types, and classes. To create this project type, select the SQL Server Project template (located in the Database folder of the New Project dialog box) instead of your normal template. This project template immediately displays a New Database Reference dialog box in which you can choose the database you want from the various form fields.
After defining the database you want to use, click Test Connection to make sure that you can connect to it. Click OK. At this point, you might wonder why you have used this template, because it hasn’t done anything you wouldn’t normally do anyway. However, it’s at this point that Visual Studio ensures that you have SQL/CLR debugging available. Generally, you want this feature available when working on a database application; when you see the dialog box asking whether you want to enable this feature, click Yes.
Now you have a basic project. Right-click the project entry and choose Add > New Item. A list of templates appears, such as the one shown in Figure 7. You’re ready to create the tools you need to work with the database—without having the leave the familiar Visual Studio IDE.
)
Figure 7 Add the functionality you need to the database, using the special SQL Server Project template.
For this example, I chose to create a stored procedure. It’s important to remember that this is a managed stored procedure, so you’ll still write your code using C# or Visual Basic as you always have. However, now you’ll use objects that interact directly with SQL Server, because the code resides within the SQL Server database that you chose when you created the project. Listing 2 shows an example of a stored procedure that uses two of the objects associated with a SQL context.
Listing 2 Creating a managed stored procedure.
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure(Name="TestSproc")]
public static void MyTestSproc(SqlString Title)
{
// Create a command for obtaining data.
SqlCommand Cmd =
new SqlCommand(
"SELECT * FROM Customers WHERE ContactTitle = ’"
+ Title.ToString() + "’");
// Execute the command and send the result to
// SQL Server for any additional processing.
SqlContext.Pipe.ExecuteAndSend(Cmd);
}
};
This very simple stored procedure has four features that I want to emphasize:
- Notice that I’ve provided the Name property for the SqlProcedure attribute. This particular property is helpful because it lets you define precisely how the name of your stored procedure should appear in SQL Server.
- Notice the argument for the MyTestSproc method. It’s not a String type, as you might expect, but uses SqlString instead, because you’ll receive the value from SQL Server, not from the client application (as you might suppose). This particular argument is input only. If you want to make an argument both input and output, you must use the Out attribute. In this case, the argument would appear as follows:
public static void MyTestSproc([Out] SqlString Title)
The Out attribute appears in the System.Runtime.InteropServices namespace. As an alternative, you can use the out keyword when working with C#.
- You can execute any command in C# or VB that you can when working with T-SQL. In this case, the example uses the same query used in Listing 1. The only difference is that you must also specify how to execute the query. I easily could have created a SQL connection to perform this task, but decided to use SqlContext to perform the work.
- You’ll often interact with SQL Server using the SqlContext.Pipe object. In this case, the code uses the ExecuteAndSend() method to execute a SQL command and send the result back to SQL Server. The result appears as it would for any other stored procedure when you execute it from SSMS, but there are differences when you work in Visual Studio. I’ll discuss these differences in the next section.
Managed stored procedures can do a lot more, but this is a good starting point. You can see more of what a managed stored procedure can do in the MSDN article "Building Database Objects with Common Language Runtime (CLR) Integration." In the next section, you’ll use drag-and-drop to add this stored procedure to an application. However, before you can perform this task, you must build the application. After the application builds successfully, choose Build > Deploy to place the resulting DLL on SQL Server.