April 22, 2019
Hot Topics:

Implement User-defined Functions in SQL Server 2005 with Managed Code

  • August 26, 2004
  • By Thiru Thangarathinam
  • Send Email »
  • More Articles »

Implementing User-Defined Functions Using Visual Studio 2005

So far, you have seen the steps involved in creating and deploying a SQL Server user-defined function using the manual steps. This section shows you how to use Visual Studio 2005 to automate the compilation and deployment of the user-defined functions.

To start, create a new SQL Server Project using the File->New Project menu in Visual Studio 2005 and specify the project name as UDFExamples. (See the following screenshot.)

Click here for a larger image.

Because you are creating a database project, you need to associate a data source with the project. To this end, Visual Studio automatically prompts you to either select an existing database reference or add a new database reference. For the purposes of this example, use a database called Test. (See the following screenshot.)

Once you've created the project, select Project->Add User-Defined Function from the menu. In the Add New Item dialog box, enter Add.cs and click the Add button. (See the following screenshot.)

Click here for a larger image.

After you've created the class, modify the code in the class to look like the following:

using System;
using System.Data.Sql;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
   public static SqlInt32 Add(int x, int y)
      return x + y;

The above code starts by importing the required namespaces. It then declares a partial class named UserDefinedFunctions. (The partial class is a new feature in .NET Framework 2.0. It gives you the ability to split a single class into more than one source code file. This feature is very helpful when more than one user works on the same class. Without this concept, you would have to use source code management features such as check-in/check-out and merge changes for multiple programmers to work on a single class. With partial types, each team member can work on a separate part of the class, and the system will handle merging the separate code files back into a single class at compile time.)

After that, the code declares a static method named Add that takes in two parameters. The Add method is decorated with the SqlFunction attribute, which specifies that the Add method be exposed as a user-defined function to the consumers. Inside the Add method, it simply returns the result of the addition of the two numbers to the caller.

Now that the user-defined function is created, deploying it is very simple and straightforward. Before deploying it, you need to build the project. Select Build->Build UDFExamples from the menu, which compiles all the classes in the project. Any compilation errors will be displayed in the Error List pane.

Once the project is built, you then can deploy it on SQL Server by selecting Build->Deploy UDFExamples from the menu. This will not only register the assembly but also deploy the user-defined function in SQL Server.

Testing the Function

To test the function, bring up Server Explorer by selecting View->Server Explorer from the menu. In the Data Connections node, open the data connection you specified earlier. Then, open the Functions node. Right-click on this node and select Execute from the context menu. This will bring up the following dialog box, which you can use to enter values for the Add method's parameters.

Enter the values for the @x and @y parameters as shown above and click OK. You will see the following output in the Output dialog box.

Click here for a larger image.

So far, you learned how to create a simple user-defined function and test it using Visual Studio 2005. The next section looks at an advanced user-defined function that performs data access from within a user-defined function.

Creating a User-Defined Function that Performs Data Access

This section examines one more scalar user-defined function example that retrieves data from a SQL Server table and returns it to the caller. First, consider the important namespaces related to accessing data in the SQL Server 2005 database.

Understanding the In-process Managed Provider

A .NET routine can easily access data stored in the instance of SQL Server in which it runs. The data the routine can access is determined by the user context in which the code is running. The in-proc provider is optimized for working with data inside the SQL Server process. By using the classes and methods of the in-process managed provider, you can easily submit queries to the database, execute DML and DDL statements, and return result sets and messages to client applications. The System.Data.SqlServer namespace groups the types that make up the in-proc provider. This namespace shares many similarities and interfaces with ADO.NET's SqlClient namespace, which is used by developers accessing SQL Server data from managed client and middle-tier applications. Because of this similarity, you can easily migrate code from client applications to server libraries and back again.

Three important classes in the System.Data.SqlServer namespace are specific to the in-proc provider:

  • SqlContext—This class encapsulates the other extensions. It also provides the transaction and database connection, which are part of the environment in which the routine executes.
  • SqlPipe—This class enables routines to send tabular results and messages to the client. This class is conceptually similar to the Response class found in ASP.NET in that it can be used to send messages to the callers.
  • SqlTriggerContext—This class provides information on the context in which a trigger is run, including the inserted and deleted tables accessible from T-SQL triggers.

Page 3 of 4

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date