Invoke Scalar 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 scalar functions in SQL Server. The adapter exposes the scalar functions as methods that can be invoked directly on SQL Server. For more information about how the adapter supports scalar functions, see Execute Scalar Functions in SQL Server using the SQL adapter.

How This Topic Demonstrates Invoking Scalar Functions Using the WCF Service Model

This topic demonstrates how to invoke the GET_EMP_ID function in a SQL Server database. The GET_EMP_ID function takes the designation of an employee in the Employee table and returns the corresponding employee ID. The GET_EMP_ID 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 GET_EMP_ID scalar function on the Employee table. The GET_EMP_ID function and the Employee table are created by running the SQL script provided with the samples. A sample, ScalarFunction_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
Scalar function	ScalarFunctions_[SCHEMA]Client

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

Method Signature for Invoking Scalar 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
Scalar function name	public <return_type><scalar_function_name>(param1, param2, …)

<retrun_type> = Return type defined in the function definition

<scalar_function_name> = Name of the scalar function.

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

public partial class ScalarFunctions_dboClient : System.ServiceModel.ClientBase<ScalarFunctions_dbo>, ScalarFunctions_dbo {      
    public System.Nullable<int> GET_EMP_ID(string emp_desig);  
}

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

Parameters for Invoking Scalar Functions

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

Again, the return value for a scalar function is same as the return value defined in the scalar function definition in SQL Server. For example, the return value for the GET_EMP_ID function is an employee’s ID of type integer.

Creating a WCF Client to Invoke Scalar 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 GET_EMP_ID scalar function.

To create a WCF client

Create a Visual C# project in Visual Studio. For this topic, create a console application.
Generate the WCF client class for the GET_EMP_ID 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.
```
          ScalarFunctions_dboClient client = new ScalarFunctions_dboClient("SqlAdapterBinding_ScalarFunctions_dbo");  
client.ClientCredentials.UserName.UserName = "<Enter user name here>";  
client.ClientCredentials.UserName.Password = "<Enter password here>";  
```
In this snippet, ScalarFunctions_dboClient is the WCF client defined in SqlAdapterBindingClient.cs. This file is generated by the Add Adapter Service Reference Plug-in. SqlAdapterBinding_ScalarFunctions_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 GET_EMP_ID function to retrieve the ID for an employee with the designation as “Manager”.

Console.WriteLine("Invoking the GET_EMP_ID function");  
string emp_designation = "Manager";  
try  
{  
      System.Nullable<int> emp_id = client.GET_EMP_ID(emp_designation);  
      Console.WriteLine("The Employee ID for the employee with 'Manager' designation is:" + emp_id);  
}  
catch (Exception e)  
{  
      Console.WriteLine("Exception: " + e.Message);  
      throw;  
}

Note

For the sake of simplicity, the Employee table has only one employee with “Manager” designation. If your target table has more employees with the same designation, you must define the function accordingly.

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 of the employee with the designation of “Manager”.

Share via