Edit

Share via

Receive Strongly-typed Polling-based Data-changed Messages from SQL Server Using WCF Service Model

  • Article

You can configure the SQL adapter to receive strongly-typed polling messages from SQL Server. You can specify a polling statement that the adapter executes to poll the database. The polling statement can be a SELECT statement or a stored procedure that returns a result set. You must use strongly-typed polling in a scenario where you want to receive a strongly-typed result set. For more information on how the adapter supports strongly-typed polling, see Support for Inbound Calls Using Polling.

Important

If you want to have more than one polling operation in a single application, you must specify an InboundID connection property as part of the connection URI to make it unique. The inbound ID you specify is added to the operation namespace to make it unique.

How This Topic Demonstrates Polling

In this topic, to demonstrate how the SQL adapter supports receiving strongly-typed data change messages, create a .NET application and generate the WCF service contract for the TypedPolling operation. Make sure you specify the following while generating the WCF service contract:

  • You must specify an InboundID as part of the connection URI.

  • You must specify a polling statement for the PollingStatement binding property.

    Additionally, if you want to specify other polling related binding properties while generating the proxy class, specify the PolledDataAvailableStatement as:

SELECT COUNT(*) FROM Employee

The PolledDataAvailableStatement must return a result set with the first cell containing a positive value. If the first cell does not contain a positive value, the adapter does not execute the polling statement.

As part of the polling statement, perform the following operations:

  1. Select all the rows from the Employee table.

  2. Execute a stored procedure (MOVE_EMP_DATA) to move all the records from the Employee table to an EmployeeHistory table.

  3. Execute a stored procedure (ADD_EMP_DETAILS) to add a new record to the Employee table. This procedure takes the employee name, designation, and salary as parameters.

    To perform these operations, you must specify the following for the PollingStatement binding property while generating the WCF service contract and helper classes:

SELECT * FROM Employee;EXEC MOVE_EMP_DATA;EXEC ADD_EMP_DETAILS John, Tester, 100000

After the polling statement is executed, all the records from the Employee table are selected and the message from SQL Server is received. After the MOVE_EMP_DATA stored procedure is executed by the adapter, all the records are moved to the EmployeeHistory table. Then, the ADD_EMP_DETAILS stored procedure is executed to add a new record to the Employee table. The next polling execution will only return a single record. This cycle continues until you close the service host.

Configuring Typed Polling with the SQL Adapter Binding Properties

The following table summarizes the SQL adapter binding properties that you use to configure the adapter to receive data-change messages. Other than the PollingStatement binding property, all the other binding properties listed in this section are required while running the .NET application. You must specify the PollingStatement binding property before generating the WCF service contract TypedPolling operation.

Binding Property Description
InboundOperationType Specifies whether you want to perform Polling, TypedPolling, or Notification inbound operation. Default is Polling. To receive strongly-typed polling messages, set this to TypedPolling.
PolledDataAvailableStatement Specifies the SQL statement that the adapter executes to determine whether any data is available for polling. The SQL statement must return a result set consisting of rows and columns. Only if a row is available, the SQL statement specified for the PollingStatement binding property will be executed.
PollingIntervalInSeconds Specifies the interval, in seconds, at which the SQL adapter executes the statement specified for the PolledDataAvailableStatement binding property. The default is 30 seconds. The polling interval determines the time interval between successive polls. If the statement is executed within the specified interval, the adapter waits for the remaining time in the interval.
PollingStatement Specifies the SQL statement to poll the SQL Server database table. You can specify a simple SELECT statement or a stored procedure for the polling statement. The default is null. You must specify a value for PollingStatement to enable polling. The polling statement is executed only if there is data available for polling, which is determined by the PolledDataAvailableStatement binding property. You can specify any number of SQL statements separated by a semi-colon. Important: For TypedPolling, you must specify this binding property before generating metadata.
PollWhileDataFound Specifies whether the SQL adapter ignores the polling interval and continuously executes the SQL statement specified for the PolledDataAvailableStatement binding property, if data is available in the table being polled. If no data is available in the table, the adapter reverts to execute the SQL statement at the specified polling interval. Default is false.

For a more complete description of these properties, see Read about the BizTalk Adapter for SQL Server adapter binding properties. For a complete description of how to use the SQL adapter to poll SQL Server, read further.

Configure Strongly-typed Polling in the WCF Service Model

To receive the Polling operation when you use the WCF service model, you must:

  1. Generate a WCF service contract (interface) for the TypedPolling operation from the metadata exposed by the adapter. To do this, you could use the Add Adapter Service Reference Visual Studio Plug-in. While generating the WCF service contract for this example, make sure:

    • You specify the InboundID as Employee.

    • You specify a polling statement for the PollingStatement binding property. For this example, specify the polling statement as:

      SELECT * FROM Employee;EXEC MOVE_EMP_DATA;EXEC ADD_EMP_DETAILS John, Tester, 100000

  2. Implement a WCF service from this interface.

  3. Host this WCF service using a service host (System.ServiceModel.ServiceHost).

About the Examples Used in this Topic

The examples in this topic poll the Employee table. The example also uses the MOVE_EMP_DATA and ADD_EMP_DETAILS stored procedure. A script to generate these artifacts is supplied with the samples. For more information about the samples, see Samples for the SQL adapter. A sample, TypedPolling_ServiceModel, which is based on this topic, is also provided with the SQL adapter samples.

The WCF Service Contract and Class

You can use the Add Adapter Service Reference Plug-in to create a WCF service contract (interface) and supporting classes for the TypedPolling operation. For more information about generating a WCF service contract, see Generate a WCF Client or WCF Service Contract for SQL Server Artifacts.

The WCF Service Contract (Interface)

The following code shows the WCF service contract (interface) generated for the TypedPolling operation.

[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
[System.ServiceModel.ServiceContractAttribute(Namespace="http://schemas.microsoft.com/Sql/2008/05/", ConfigurationName="TypedPolling_Employee")]
public interface TypedPolling_Employee {

    // CODEGEN: Generating message contract since the wrapper namespace (https://schemas.microsoft.com/Sql/2008/05/TypedPolling/Employee) of message TypedPolling
    // does not match the default value (https://schemas.microsoft.com/Sql/2008/05/)
    [System.ServiceModel.OperationContractAttribute(IsOneWay=true, Action="TypedPolling")]
    void TypedPolling(TypedPolling request);
}

The Message Contracts

The message contract namespace is modified by the InboundID parameter in the connection URI, if specified. In this example, you specified the inbound ID as Employee. The request message returns a strongly-typed result set.

[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
[System.ServiceModel.MessageContractAttribute(WrapperName="TypedPolling", WrapperNamespace="http://schemas.microsoft.com/Sql/2008/05/TypedPolling/Employee", IsWrapped=true)]
public partial class TypedPolling {

[System.ServiceModel.MessageBodyMemberAttribute(Namespace="http://schemas.microsoft.com/Sql/2008/05/TypedPolling/Employee", Order=0)]
    public schemas.microsoft.com.Sql._2008._05.TypedPolling.Employee.TypedPollingResultSet0[] TypedPollingResultSet0;

[System.ServiceModel.MessageBodyMemberAttribute(Namespace="http://schemas.microsoft.com/Sql/2008/05/TypedPolling/Employee", Order=1)]
    public schemas.microsoft.com.Sql._2008._05.TypedPolling.Employee.TypedPollingResultSet1[] TypedPollingResultSet1;

    public TypedPolling() {
    }

    public TypedPolling(schemas.microsoft.com.Sql._2008._05.TypedPolling.Employee.TypedPollingResultSet0[] TypedPollingResultSet0, schemas.microsoft.com.Sql._2008._05.TypedPolling.Employee.TypedPollingResultSet1[] TypedPollingResultSet1) {
        this.TypedPollingResultSet0 = TypedPollingResultSet0;
        this.TypedPollingResultSet1 = TypedPollingResultSet1;
    }
}

WCF Service Class

The Add Adapter Service Reference Plug-in also generates a file that has a stub for the WCF service class implemented from the service contract (interface). The name of the file is SqlAdapterBindingService.cs. You can insert the logic to process the TypedPolling operation directly into this class. The following code shows the WCF service class generated by the Add Adapter Service Reference Plug-in.

namespace SqlAdapterBindingNamespace {

    public class SqlAdapterBindingService : TypedPolling_Employee {

        // CODEGEN: Generating message contract since the wrapper namespace (https://schemas.microsoft.com/Sql/2008/05/TypedPolling/Employee) of message TypedPolling
        // does not match the default value (https://schemas.microsoft.com/Sql/2008/05/)
        public virtual void TypedPolling(TypedPolling request) {
            throw new System.NotImplementedException("The method or operation is not implemented.");
        }
    }
}

Receive Strongly-typed Inbound Messages for Polling Operation

This section provides instructions on how to write a .NET application to receive strongly-typed inbound polling messages using the SQL adapter.

  1. Use the Add Adapter Service Reference Plug-in to generate a WCF service contract (interface) and helper classes for the TypedPolling operation. Make sure you specify the following while generating the WCF service contract for this example:

    • You must specify the InboundID as Employee.

    • You must specify a polling statement for the PollingStatement binding property. For this example, specify the polling statement as:

      SELECT * FROM Employee;EXEC MOVE_EMP_DATA;EXEC ADD_EMP_DETAILS John, Tester, 100000

      For more information, see Generate a WCF Client or WCF Service Contract for SQL Server Artifacts. You can optionally specify the binding properties while generating the service contract and helper classes. This guarantees that they are properly set in the generated configuration file.

  2. Implement a WCF service from the interface and helper classes generated in step 1. The TypedPolling method of this class can throw an exception to abort the polling transaction, if an error is encountered processing the data received from the TypedPolling operation; otherwise the method does not return anything. You must attribute the WCF service class as follows:

    [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]

    Within the TypedPolling method, you can implement your application logic directly. This class can be found in SqlAdapterBindingService.cs. This code in this example sub-classes the SqlAdapterBindingService class. In this code, the polling message received as a strongly-typed result set is written to the console.

    [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]

public class PollingService : SqlAdapterBindingNamespace.SqlAdapterBindingService
{
    public override void TypedPolling(TypedPolling request)
    {
        Console.WriteLine("\nNew Polling Records Received");
        Console.WriteLine("*************************************************");
        Console.WriteLine("Employee ID\tName\tDesignation\tSalary");

        for (int i = 0; i < request.TypedPollingResultSet0.Length; i++)
        {
            Console.WriteLine("{0}\t{1}\t{2}\t{3}",
            request.TypedPollingResultSet0[i].Employee_ID,
            request.TypedPollingResultSet0[i].Name,
            request.TypedPollingResultSet0[i].Designation,
            request.TypedPollingResultSet0[i].Salary);
        }
        Console.WriteLine("*************************************************");
        Console.WriteLine("\nHit <RETURN> to stop polling");
    }
}

  3. Because the SQL adapter does not accept credentials as part of the connection URI, you must implement the following class to pass credentials for the SQL Server database. In the latter part of the application, you will instantiate this class to pass on the SQL Server credentials.

    class PollingCredentials : ClientCredentials, IServiceBehavior
{
    public void AddBindingParameters(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase, Collection<ServiceEndpoint> endpoints, BindingParameterCollection bindingParameters)
    {
        bindingParameters.Add(this);
    }

    public void ApplyDispatchBehavior(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
    { }

    public void Validate(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
    { }

    protected override ClientCredentials CloneCore()
    {
        ClientCredentials clone = new PollingCredentials();
        clone.UserName.UserName = this.UserName.UserName;
        clone.UserName.Password = this.UserName.Password;
        return clone;
    }
}

  4. Create a SqlAdapterBinding and configure the polling operation by specifying the binding properties. You can do this either explicitly in code or declaratively in configuration. At a minimum, you must specify the InboundOperationType, PolledDataAvailableStatement, and PollingStatement.

    SqlAdapterBinding binding = new SqlAdapterBinding();
binding.InboundOperationType = InboundOperation.TypedPolling;
binding.PolledDataAvailableStatement = "SELECT COUNT (*) FROM EMPLOYEE";
binding.PollingStatement = "SELECT * FROM Employee;EXEC MOVE_EMP_DATA;EXEC ADD_EMP_DETAILS John, Tester, 100000";

  5. Specify SQL Server database credentials by instantiating the PollingCredentials class you created in Step 3.

    PollingCredentials credentials = new PollingCredentials();
credentials.UserName.UserName = "<Enter user name here>";
credentials.UserName.Password = "<Enter password here>";

  6. Create an instance of the WCF service created in step 2.

    // create service instance
PollingService service = new PollingService();

  7. Create an instance of System.ServiceModel.ServiceHost by using the WCF service and a base connection URI. The base connection URI cannot contain the inbound ID. You must also specify the credentials here.

    // Enable service host
Uri[] baseUri = new Uri[] { new Uri("mssql://mysqlserver//mydatabase") };
ServiceHost serviceHost = new ServiceHost(service, baseUri);
serviceHost.Description.Behaviors.Add(credentials);

  8. Add a service endpoint to the service host. To do this:

    • Use the binding created in step 4.

    • Specify a connection URI that contains credentials and, if required, an inbound ID.

    • Specify the contract as "TypedPolling_Employee".

    // Add service endpoint: be sure to specify TypedPolling_Employee as the contract
Uri ConnectionUri = new Uri("mssql://mysqlserver//mydatabase?InboundID=Employee");
serviceHost.AddServiceEndpoint("TypedPolling_Employee", binding, ConnectionUri);

  9. To receive polling data, open the service host. The adapter will return data whenever the query returns a result set.

    // Open the service host to begin polling
serviceHost.Open();

  10. To terminate polling, close the service host.

    Important

    The adapter will continue to poll until the service host is closed.

    serviceHost.Close();

Example

The following example shows a polling query that executes the Employee table. The polling statement performs the following tasks:

  1. Selects all the records from the Employee table.

  2. Executes the MOVE_EMP_DATA stored procedure to move all records from Employee table to EmployeeHistory table.

  3. Executes the ADD_EMP_DETAILS stored procedure to add a single record to the Employee table.

    The first polling message will contain all the records from the Employee table. The subsequent polling messages will contain only the last record inserted by the ADD_EMP_DETAILS stored procedure. The adapter will continue to poll until you close the service host by pressing <RETURN>.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Adapters.Sql;
using Microsoft.ServiceModel.Channels;
using System.ServiceModel;
using System.ServiceModel.Description;
using System.ServiceModel.Channels;
using System.Collections.ObjectModel;

namespace TypedPolling_ServiceModel
{
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]

    public class PollingService : SqlAdapterBindingNamespace.SqlAdapterBindingService
    {
        public override void TypedPolling(TypedPolling request)
        {
            Console.WriteLine("\nNew Polling Records Received");
            Console.WriteLine("*************************************************");
            Console.WriteLine("Employee ID\tName\tDesignation\tSalary");

            for (int i = 0; i < request.TypedPollingResultSet0.Length; i++)
            {
                Console.WriteLine("{0}\t{1}\t{2}\t{3}",
                request.TypedPollingResultSet0[i].Employee_ID,
                request.TypedPollingResultSet0[i].Name,
                request.TypedPollingResultSet0[i].Designation,
                request.TypedPollingResultSet0[i].Salary);
            }
            Console.WriteLine("*************************************************");
            Console.WriteLine("\nHit <RETURN> to stop polling");
        }
    }

    class PollingCredentials : ClientCredentials, IServiceBehavior
    {
        public void AddBindingParameters(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase, Collection<ServiceEndpoint> endpoints, BindingParameterCollection bindingParameters)
        {
            bindingParameters.Add(this);
        }

        public void ApplyDispatchBehavior(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
        { }

        public void Validate(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
        { }

        protected override ClientCredentials CloneCore()
        {
            ClientCredentials clone = new PollingCredentials();
            clone.UserName.UserName = this.UserName.UserName;
            clone.UserName.Password = this.UserName.Password;
            return clone;
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            ServiceHost serviceHost = null;
            try
            {
                Console.WriteLine("Sample started...");
                Console.WriteLine("Press any key to start polling...");
                Console.ReadLine();

                SqlAdapterBinding binding = new SqlAdapterBinding();
                binding.InboundOperationType = InboundOperation.TypedPolling;
                binding.PolledDataAvailableStatement = "SELECT COUNT (*) FROM EMPLOYEE";
                binding.PollingStatement = "SELECT * FROM Employee;EXEC MOVE_EMP_DATA;EXEC ADD_EMP_DETAILS John, Tester, 100000";
                Console.WriteLine("Binding properties assigned...");

                // This URI is used to specify the address for the ServiceEndpoint
                // It must contain the InboundId that was used to generate
                // the WCF service callback interface
                Uri ConnectionUri = new Uri("mssql://mysqlserver//mydatabase?InboundId=Employee");

                // This URI is used to initialize the ServiceHost. It cannot contain
                // the InboundID; otherwise,an exception is thrown when
                // the ServiceHost is initialized.
                Uri[] baseUri = new Uri[] { new Uri("mssql://mysqlserver//mydatabase") };

                PollingCredentials credentials = new PollingCredentials();
                credentials.UserName.UserName = "<Enter user name here>";
                credentials.UserName.Password = "<Enter password here>";

                Console.WriteLine("Opening service host...");
                PollingService service = new PollingService();
                serviceHost = new ServiceHost(service, baseUri);
                serviceHost.Description.Behaviors.Add(credentials);
                serviceHost.AddServiceEndpoint("TypedPolling_Employee", binding, ConnectionUri);
                serviceHost.Open();
                Console.WriteLine("Service host opened...");
                Console.WriteLine("Polling started...");
                Console.ReadLine();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception :" + e.Message);
                Console.ReadLine();

                /* If there is an error it will be specified in the inner exception */
                if (e.InnerException != null)
                {
                    Console.WriteLine("InnerException: " + e.InnerException.Message);
                    Console.ReadLine();
                }
            }
            finally
            {
                // IMPORTANT: you must close the ServiceHost to stop polling
                if (serviceHost.State == CommunicationState.Opened)
                    serviceHost.Close();
                else
                    serviceHost.Abort();
            }
        }
    }
}

See Also

Poll SQL Server using the SQL Adapter with WCF Service Model