Creating a Stored Procedure by Using Visual Studio

 

Creating a Stored Procedure by Using Visual Studio

If you have been using Notepad up to this point as I have, you'll certainly be ready to move over to Visual Studio .NET before continuing. Visual Studio .NET Professional Edition offers a Database project template that you can use to create assemblies that run in SQL Server. For example, Figure 9-3 shows the Database project template that is available for Visual Basic projects.

image from book
Figure 9-3: Visual Studio .NET has Database project templates for each language.

Using Visual Studio means that you use the same robust environment that you normally write code in, complete with IntelliSense. The Database project template also gives you the ability to easily deploy and debug your projects.

Create a Database project called SqlServerVb or SqlServerCs, depending on your language. You are prompted to select the database to connect to. If the database isn't attached, you can opt to attach a new database. For these examples, we'll select the Northwind database.

You can add stored procedures, user-defined functions, aggregates, triggers, and user-defined types to the database project. All of these are covered in this chapter, but at this time, add a stored procedure called HelloVb.vb or HelloCs.cs. The following code is included in the stored procedure template when it is added.

image from book

Visual Basic

Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class StoredProcedures     <Microsoft.SqlServer.Server.SqlProcedure()> _     Public Shared Sub HelloVb ()         ' Add your code here     End Sub End Class 
image from book

image from book

C#

using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {    [Microsoft.SqlServer.Server.SqlProcedure]    public static void HelloCs()    {       // Put your code here    } }; 
image from book

The Imports (Visual Basic) or using (C#) statement saves you typing. No namespace has been defined because there is little benefit to doing so. Also, Visual Studio has created a partial class called StoredProcedures because there really isn't any benefit to creating a separate class for each stored procedure. Notice the use of the SqlProcedure attribute, which tells Visual Studio how to register this method in SQL Server. Modify the method to display a simple message as follows.

image from book

Visual Basic

<Microsoft.SqlServer.Server.SqlProcedure(Name:="HiVb")> _    Public Shared Sub HelloVb()    SqlContext.Pipe.Send("Hello from VB!") End Sub 
image from book

image from book

C#

[Microsoft.SqlServer.Server.SqlProcedure(Name="HiCs")] public static void HelloCs() {    SqlContext.Pipe.Send("Hello from C#!"); } 
image from book

The SqlProcedure attribute has a property called Name that has been assigned to create the stored procedure with a different name. If you try to build and run the project by pressing F5, the project builds but it doesn't appear to run because what actually runs is a test script in the Test Scripts folder. Notice that there is only one test script in the folder, called Test.sql. You can add script files, and you can modify the existing test script. Open the existing Test.sql file, and add the following SQL to the end of the file.

image from book

Visual Basic

EXEC HiVb 
image from book

image from book

C#

EXEC HiCs 
image from book

Note that the SQLCLR needs to be enabled first. If you don't enable the SQLCLR, you get an error message that states that the SQLCLR must be enabled first.

The Name property that was assigned in the SqlProcedure attribute is used to execute the stored procedure. Press F5 and the result is sent to the output window. The main benefit is that you can make a change to the stored procedure and press F5 to recompile and refresh the assembly. You can also add breakpoints in your code to debug your stored procedures.

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net