Export (0) Print
Expand All

Invoking Scalar Functions in SQL Server by Using the WCF Service Model

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 Executing Scalar Functions in SQL Server.

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 Samples.

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 Samples.

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 ArtifactWCF 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.

OperationMethod 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.

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 Using the WCF Service Model with the Adapter. This section describes how to create a WCF client to invoke the GET_EMP_ID scalar function.

To create a WCF client

  1. Create a Visual C# project in Visual Studio. For this topic, create a console application.

  2. Generate the WCF client class for the GET_EMP_ID scalar function. For more information about generating a WCF client class, see Generating a WCF Client or WCF Service Contract for SQL Server Artifacts.

  3. In the Solution Explorer, add reference to Microsoft.Adapters.Sql and Microsoft.ServiceModel.Channels.

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

    Dd788164.note(en-us,BTS.70).gifNote
    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 Specifying a Client Binding for the SQL Adapter.

  5. 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;
    }
    
  6. 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;
    }
    
    Dd788164.note(en-us,BTS.70).gifNote
    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.

  7. Close the client as described in the snippet below:

    client.Close();
    Console.WriteLine("Press any key to exit...");
    Console.ReadLine();
    
  8. Build the project and then run it. The application displays the employee ID of the employee with the designation of “Manager”.

Show:
© 2014 Microsoft