February 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 »

Implementation of User-Defined Function

For the purposes of this example, create a new table whose declaration looks like the following:

CREATE TABLE Users(ID int, Name Varchar(50))

In the user-defined function, you return the name of the user based on the user's supplied ID. To create this function, select Project->Add User-Defined Function from the menu and specify the name of the user-defined function file as GetUserNameByID.cs. Once the file is created, modify the code in the class to look like the following:

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
public partial class UserDefinedFunctions
    public static SqlString GetUserNameByID(int id)
        SqlCommand cmd    = SqlContext.GetCommand();
        cmd.CommandText   = "SELECT Name FROM Users 
            WHERE ID      = " + id.ToString() ;
        SqlDataRecord rec = cmd.ExecuteRow();
        string name       = rec.GetString(0);
        return name;

In the declaration section, the System.Data.SqlServer is imported so that you can access types in the in-proc provider. Next, the function is decorated with the [SqlFunction] custom attribute, which is found in the System.Data.Sql namespace. On the next line, the target method is declared as a public static method. The SqlContext class, located in the System.Data.SqlServer namespace, is then used to access the SqlCommand object with a connection to the SQL Server instance already set up.

The following two lines of code should look familiar to developers who have written client applications that use the types found in the System.Data.SqlClient namespace:

cmd.CommandText = "SELECT Name FROM Users
                   WHERE ID = " + id.ToString() ;
   SqlDataRecord rec = cmd.ExecuteRow();

The appropriate command text is specified by setting the CommandText property of the SqlCommand object returned by the call to SqlContext.GetCommand. Next, the ExecuteRow method of the SqlCommand object is called. This returns a value of type SqlDataRecord. SqlDataRecord is a new object that is introduced in ADO.NET 2.0. You can use this object to represent a specific record in the database. Once you have the record, you then can access the first string value column in the SqlDataRecord object by using the GetString method.

Now that you've created the user-defined function, you can build and deploy it using Visual Studio 2005. Once the deployment is completed, you can then test it, which the next section covers.

Testing the User-Defined Function Using a Windows Forms Application

In this example, you will test the function from a Windows forms client application that is created using Visual C#. (See the following screenshot.) Name the project UDFExamplesClientApp.

Click here for a larger image.

Next, add a label control, a textbox control, and a command button to the form. In the Click event of the command button, add the following lines of code:

private void btnInvoke_Click(object sender, EventArgs e)
   using (SqlConnection connection = new SqlConnection())
      using (SqlCommand command = new SqlCommand())
         connection.ConnectionString =
            @"Server=(local)\SQLExpress;Integrated Security=
         //Set the SqlCommand object's properties
         command.CommandType = CommandType.Text;
         string userID = txtUserID.Text;
         command.CommandText = "SELECT dbo.GetUserNameByID(" + 
            userID + ")";
         command.Connection = connection;
         DataSet userNameDataSet = new DataSet();
         SqlDataAdapter adapter = new SqlDataAdapter(command);
         string userName =
         lblResult.Text = userName; 

With the above code, you do the following:

  1. Create an instance of SqlConnection in a using block and then create the SqlCommand object.
  2. Set the ConnectionString property of the SqlConnection object to a valid connection string. (Because you are using the SQL Server Express that is supplied with Visual Studio 2005, you specify integrated authentication in the connection string.)
  3. Set the CommandType property of the SqlCommand object to CommandType.Text to indicate that you want to execute a SQL statement.
  4. Set the CommandText property to the name of the user-defined function. (To the user-defined function, you also supplied the value entered by the user in the textbox as an argument.)
  5. Create instances of the DataSet, and SqlDataAdapter objects. (To the constructor of the SqlDataAdapter object, you also supplied the previously created SqlCommand object as an argument.)
  6. Execute the user-defined function by invoking the Fill method of the SqlDataAdapter object.

Once the user-defined function is executed and the dataset populated with the results of the query execution, you then can retrieve the result by navigating through the DataTable that is contained in the DataSet object.

If you execute the above code and click the command button, you will see the following screen. The label control displays the user name returned by the user-defined function.

Choosing Between T-SQL and Managed Code

When writing stored procedures, triggers, and user-defined functions, programmers now will have to decide whether to use traditional T-SQL or a .NET language such as Visual Basic .NET or C#. The correct decision depends upon the particular situation. In some cases, you should use T-SQL; in others, you should use managed code.

T-SQL is best in situations where the code will mostly perform data access with little or no procedural logic. Managed code is best suited for CPU-intensive functions and procedures that feature complex logic, or where you want to leverage the .NET Framework's Base Class Library. Code placement is another important fact to consider. You can run both T-SQL and in-process managed code on the server. This functionality 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 machines 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. While T-SQL code cannot run on a client machine, the SQL Server in-process provider was designed to be as similar as possible to client-side managed ADO.NET, enhancing the portability of code between server and client.

From T-SQL to Managed Code

With the release of SQL Server 2005 Beta 2, database programmers can now take advantage of the rich functionality of the .NET Base Class Library and the CLR. By uing CLR integration, you can create your user-defined functions using the .NET language of your choice. This will allow you to utilize the .NET Framework, which provides thousands of classes and methods on the server-side. Many tasks that were awkward or difficult to perform in T-SQL now can be easily accomplished using managed code.

About the Author

Thiru Thangarathinam has six years of experience in architecting, designing, developing, and implementing applications using object-oriented application development methodologies. He also possesses a thorough understanding of the software life cycle (design, development, and testing). He holds several certifications, including MCAD for .NET, MCSD, and MCP. Thiru is an expert with ASP.NET, .NET Framework, Visual C# .NET, Visual Basic .NET, ADO.NET, XML Web services, and .NET Remoting. Thiru also has authored numerous books and articles. Contact him at thiruthangarathinam@yahoo.com.

Page 4 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