Invoke Table-Valued Functions in SQL Server by Using the WCF Service Model

Article
02/02/2021

You can use the SQL adapter in a .NET application using the WCF service model to invoke table-valued functions in SQL Server. The adapter exposes the table-valued functions as methods that can be invoked directly on SQL Server. For more information about how the adapter supports scalar functions, see Execute Table-Valued Functions in SQL Server using the SQL adapter.

This topic demonstrates how to invoke the TVF_EMPLOYEE function in a SQL Server database. The TVF_EMPLOYEE function takes the designation of an employee in the Employee table and returns the record for the employee. The TVF_EMPLOYEE function and the Employee table are created by running the SQL script provided with the samples. For more information, see Adapter Samples.

About the Examples Used in this Topic

The example in this topic invoked the TVF_EMPLOYEE table-valued function on the Employee table. TVF_EMPLOYEE function and the Employee table are created by running the SQL script provided with the samples. A sample, TableFunction_ServiceModel, which is based on this topic, is also provided with the SQL adapter samples. For more information, see Adapter Samples.

The WCF Client Class

The name of the WCF client generated for invoking the scalar function in SQL Server using the SQL adapter is listed in the following table.

SQL Server Database Artifact	WCF Client Name
Table-valued function	TableValuedFunctions_[SCHEMA]Client

[SCHEMA] = Collection of SQL Server artifacts; for example, dbo.

Method Signature for Invoking Table-valued Functions

The following table shows the method signatures for the basic operations on a table. The signatures are the same for a view, except that the view namespace and name replace those of the table.

Operation	Method Signature
Table-valued function name	public [NAMESPACE][FUNCTION_NAME][] [FUNCTION_NAME](param1, param2, …)

[NAMESPACE] = The namespace, for example, schemas.microsoft.com.Sql._2008._05.Types.TableFunctionReturnTables.dbo.TVF_EMPLOYEE

[FUNCTION_NAME] = Name of the table-valued function.

As an example, the following code shows the method signatures for a WCF client class generated for the TVF_EMPLOYEE scalar functions, in the dbo schema, which takes the employee designation as a parameter and returns the employee record.

public partial class TableValuedFunctions_dboClient : System.ServiceModel.ClientBase<TableValuedFunctions_dbo>, TableValuedFunctions_dbo {      
    public schemas.microsoft.com.Sql._2008._05.Types.TableFunctionReturnTables.dbo.TVF_EMPLOYEE[] TVF_EMPLOYEE(string emp_desig);  
}

In this snippet, TableValuedFunctions_dboClient is the name of the WCF class in the SqlAdapterBindingClient.cs generated by the Add Adapter Service Reference Plug-in.

Parameters for Invoking Table-valued Functions

The parameters for the methods exposed by the SQL adapter to invoke a table-valued function are the same as the parameters defined in the function definition in SQL Server. For example, the parameter for invoking the TVF_EMPLOYEE table-valued function is emp_desig and takes an employee’s designation.

Again, the return value for a table-valued function is same as the return value defined in the function definition in SQL Server. For example, the return value for the TVF_EMPLOYEE function is an array of records of type schemas.microsoft.com.Sql._2008._05.Types.TableFunctionReturnTables.dbo.TVF_EMPLOYEE[].

Creating a WCF Client to Invoke Table-valued Functions

The generic set of actions required to perform an operation on SQL Server using a WCF client involves a set of tasks described in Overview of the WCF Service Model with the SQL Adapter. This section describes how to create a WCF client to invoke the TVF_EMPLOYEE table-valued function.

Create a Visual C# project in Visual Studio. For this topic, create a console application.
Generate the WCF client class for the TVF_EMPLOYEE scalar function. For more information about generating a WCF client class, see Generate a WCF Client or WCF Service Contract for SQL Server Artifacts.
In the Solution Explorer, add reference to Microsoft.Adapters.Sql and Microsoft.ServiceModel.Channels.
Open the Program.cs and create a client as described in the snippet below.
```
          TableValuedFunctions_dboClient client = new TableValuedFunctions_dboClient("SqlAdapterBinding_TableValuedFunctions_dbo");  
client.ClientCredentials.UserName.UserName = "<Enter user name here>";  
client.ClientCredentials.UserName.Password = "<Enter password here>";  
```
In this snippet, TableValuedFunctions_dboClient is the WCF client defined in SqlAdapterBindingClient.cs. This file is generated by the Add Adapter Service Reference Plug-in. SqlAdapterBinding_TableValuedFunctions_dbo is the name of the client endpoint configuration and is defined in the app.config. This file is also generated by the Add Adapter Service Reference Plug-in and contains the binding properties and other configuration settings.

Note

In this snippet, you use the binding and endpoint address from the configuration file. You can also explicitly specify these values in your code. For more information on the different ways of specifying then client binding, see Configure a Client Binding for the SQL Adapter.

Open the client as described in the snippet below:

try  
{  
   Console.WriteLine("Opening Client...");  
   client.Open();  
}  
catch (Exception ex)  
{  
   Console.WriteLine("Exception: " + ex.Message);  
   throw;  
}

Invoke the TVF_EMPLOYEE function to retrieve all the employee records having the “Manager” designation.

Console.WriteLine("Invoking the TVF_EMPLOYEE function");  
schemas.microsoft.com.Sql._2008._05.Types.TableFunctionReturnTables.dbo.TVF_EMPLOYEE[] emp_details;  
string emp_designation = "Manager";  

try  
{  
    emp_details = client.TVF_EMPLOYEE(emp_designation);  
}  
catch (Exception e)  
{  
    Console.WriteLine("Exception: " + e.Message);  
    throw;  
}  
Console.WriteLine("The details for the employee with the 'Manager' designation are:");  
Console.WriteLine("*******************************************************************");  
for (int i = 0; i < emp_details.Length; i++)  
{  
    Console.WriteLine("Employee ID        : " + emp_details[i].Employee_ID);  
    Console.WriteLine("Employee Name      : " + emp_details[i].Name);  
    Console.WriteLine("Employee Desigation: " + emp_details[i].Designation);  
    Console.WriteLine("Employee Salary    : " + emp_details[i].Salary);  
    Console.WriteLine();  
}

Close the client as described in the snippet below:

client.Close();  
Console.WriteLine("Press any key to exit...");  
Console.ReadLine();

Build the project and then run it. The application displays the employee ID, name, and salary of all the employees with a “Manager” designation.

Share via