Development, Deployment, and Usage of CLR Database Objects in Visual Studio 2005


I will demonstrate the development and deployment of database objects in Visual Studio 2005 first (upon creation of a stored procedure), and then in the next section I will show you how to do it without Visual Studio. Follow these steps:

  1. Launch Visual Studio 2005.

  2. Select File | New | Project.

  3. In the New Project window's Project Types pane, expand Visual C# or Visual Basic, then Database, and select the SQL Server Project template. Set Name (for example, CShrpSqlServerSp or VbSqlServerSp), Location, and Solution Name of the project (see Figure 11-2).

    image from book
    Figure 11-2: Creating a new database project in Visual Studio 2005

  4. Click OK.

  5. The program will prompt you to add a new database reference (connection) or to select an existing one. Create a reference to the Asset5 database.

  6. The program will prompt you to enable debugging for SQLCLR for this connection. Choose Yes.

  7. In the Solution Explorer pane (View | Solution Explorer), open the context-sensitive menu for the solution (top node). Choose Add | Stored Procedure. (Alternatively, you can execute Project | Add Stored procedure from the main menu.)

  8. The program will prompt you to select a template. Select Stored Procedure and change the name from the default name to ap_First (see Figure 11-3).

    image from book
    Figure 11-3: Templates of database objects

  9. When you click Add, the program will generate the following C# code:

     using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {     [Microsoft.SqlServer.Server.SqlProcedure]     public static void ap_First()     {         // Put your code here     } }; 

    If you are developing in Visual Basic, the project template will be very similar:

     Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class StoredProcedures    <Microsoft.SqlServer.Server.SqlProcedure()> _     Public Shared Sub  ap_First ()         ' Add your code here     End Sub End Class 

    The templates consist of references to namespaces that are required and to definitions of the class. The class contains a single static method that will become a CLR stored procedure.

    The class is defined as public to denote that other classes can instantiate objects of this type (and use them).

    In .NET Framework 2.0, the CLR class does not have to be defined in a single file. The idea is that the source code of each database object should be in a separate file (to be easier to manage). Therefore, Visual Studio names the file and the method using the same name. You just need to declare it as partial (as in public partial class in C# or as Partial Public Class in Visual Basic).

    The method is preceded by the [Microsoft. SqlServer. Server. SqlProcedure] attribute in C# (or <Microsoft. SqlServer. Server. SqlProcedure()> in Visual Basic) that notifies Visual Studio and the compiler how to treat the method and prepare it for deployment in SQL Server.

    Methods that will become database objects must be declared as public static in C#, or Public Shared in Visual Basic. The keyword public means that they are accessible from outside and static (or Shared) means that they behave like procedures (not like methods of objects that have state). The keyword void is in the position that declares an output value for the method (that is, for the stored procedure) and declares that the method does not return anything. If you want it to return 4-byte integers, you should declare it as int. In Visual Basic, subroutines (Sub) do not return values, while functions (Func) do.

  10. Add the following line (where it says "Add your code here"):

     SqlContext.Pipe.Send("Hello world!\n"}; 

    If you are using C#, the semicolon (;) at the end of the row denotes the end of the statement (but if you are using Visual Basic, you must omit it, since Visual Basic syntax does not require row delimiters).

  11. We will now create an assembly. Verify that the Solution Configurations combo box on the toolbar is set to Debug (and not Release). Choose Build | Build CShrpSqlServerSp from the menu. Visual Studio has created a CShrpSqlServerSp.dll file in the bin\debug subfolder of the project folder.

    Note 

    If there were errors during compilation, you can see them in the Error List pane (View | Error List).

  12. Deploy to stored procedures by choosing Build | Deploy project_name from the menu.

    Note 

    If there were errors during compilation, you can see them in the Output pane (View | Output).

  13. Open the Query window in Management Studio and execute against the Asset5 database:

     Exec ap_First Go 

    And that's all. You have to admit that it was not too difficult.

    Tip 

    If you want, you can put multiple static methods (future stored procedures, user-defined functions and procedures) in a single class and build and deploy them together (in a single operation) in Visual Studio.

As an alternative to Management Studio, you can execute scripts in the Test Scripts node of Solution Explorer. Scripts in this node are also entry points into the debugger. You can find more information about using them in Chapter 16.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

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