Using VS 2005 to Create UDFs
I've explained the manual steps you can follow to create and deploy a SQL Server UDF. Now, I'll show you how to use VS 2005 to automate the compilation and deployment of a UDF.
To begin, open VS 2005, select New Project from the File menu, and specify the project name as UserDefinedFunctions, as Figure 2 shows. Because you're creating a database project,VS 2005 will automatically prompt you to either select an existing database reference or add a new database reference. For the purposes of this example, we'll use the Northwind database.
Next, select Add User-Defined Function from VS 2005's Project menu. In the Add New Item dialog box, specify the name of the class as Category.cs and click Add. After creating the class, rename the created class to Category and modify it to look like the code in Listing 2.
Category.cs contains the code required to access data from within a UDF. The code first imports the System.Data.SqlClient namespace so that it can access the types in the new in-process SQL Server .NET data provider. A .NET routine can easily access data stored in the instance of SQL Server in which it runs.The data that the routine can access is determined by the user context in which the code is running. The in-process provider is optimized for working with data inside the SQL Server process. Using the classes and methods of the in-process provider, you can easily submit queries to the database, execute Data Manipulation Language (DML) and Data Definition Language (DDL) statements, and return result sets and messages to client applications.The System.Data.Sql namespace groups the types that make up the in-process 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.
Next, at callout A in Listing 2, the UDF is decorated with the SqlFunction custom attribute,which I explained earlier and which is found in the Microsoft.SqlServer.Server namespace. If a function is performing any type of data access,it must set the named parameter DataAccess to DataAccessKind.Read. (The enumeration DataAccessKind accepts only two values—None or Read—because you can't perform updates to a database from a CLR-based UDF.) On the next line, the function is declared as a public static method.
Next, Category.cs establishes a connection to the database by creating an instance of the SqlConnection object, passing in the appropriate connection string. Note that the connection string passed to the constructor of the SqlConnection object is "context connection=true", which indicates that the code will use the context of the logged-on user to open the connection to the database.
The code then opens the connection to the database using the Open() method, creates an instance of the SqlCommand object, and sets its properties appropriately.The UDF also creates a SqlParameter named paramCategoryID to pass the values to the @ CategoryID parameter, sets paramCategoryID's properties, and adds it to the SqlParameter-Collection of the SqlCommand object. Finally, Category.cs executes the SQL statement by calling the ExecuteScalar method of the SqlCommand object and returns the output of the SQL statement directly to the caller.
Before deploying your new UDF,you need to compile, or build, the project. To do so, select Build UserDefinedFunctions from VS 2005's Build menu.VS 2005 will compile all the classes in the project and will report any compilation errors in the Error List pane. After the project is built, you can deploy it on the SQL Server system by selecting Deploy UserDefinedFunctions from VS 2005's Build menu. This action not only registers the assembly on the SQL Server system but also deploys the UDF in SQL Server.
To test the UDF, open Server Explorer by selecting Server Explorer from VS 2005 Studio's View menu. Under the Data Connections node in Server Explorer, open the data connection we specified earlier. Then right-click the Functions node and select Execute from the context menu to open the Run Function dialog box that Figure 3 shows. Enter "1" as the value for the Add method's @CategoryID parameter as shown in Figure 3, and click OK.The function will be executed, and you'll see the output that Figure 4 shows. Note that you can also use VS 2005 to debug your UDF by stepping into the UDF code.
With the upcoming release of SQL Server 2005, you can take advantage of the CLR to create UDFs by using the .NET language of your choice. The .NET languages give you access to the .NET Framework, which provides a rich object model made up of thousands of classes and methods on the server side. Many tasks that were awkward or difficult to perform in TSQL can now be easily accomplished by using managed code.
Thiru Thangarathinam (thiruthangarathinam@yahoo.com) specializes in architecting, designing, and developing distributed enterprise-class applications by using .NET-related technologies. He is a frequent contributor to leading technology-related online publications, and his most recent book is Professional ASP.NET 2.0 XML (Wrox Press).
End of Article
Prev. page
1
[2]
next page -->
