Share via

Invoke WCF Service via SQL

  • Article

I was working on SQL SSIS and hit upon a limitation with the web service task in the SSIS package. The web service SSIS package has known limitations to call WCF web service.

To overcome this limitation and to call a WCF service from SQL I thought upon the alternatives available. I came across the SQL CLR functionality.

I found that to invoke a web service from SQL (through SQL statements and stored procedures), understanding the capabilities of SQL CLR and exposing a .NET method as a CLR Stored Procedure is very essential.

SQL Server 2005 integration with CLR

Microsoft SQL Server now features the integration of the common language runtime (CLR) component of the .NET Framework for Microsoft Windows. The CLR supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support. For SQL Server users and application developers, CLR integration means that developers can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued), and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.

Creating a WCF client

To consume a WCF service through SQL, we need to create a client proxy for the WCF service. The steps involved in creating a client are

  • Obtain the service contract, binding, and address information for a service endpoint.
  • Create a WCF client using that information.
  • Call operations.
  • Close the WCF client object.

The above steps are sufficient in order to call a WCF service from a .NET managed application. However to invoke the WCF from a SQL statement or Stored Procedure the following additional steps are required

  • Expose a public static method which in turn calls the WCF service
  • Decorate the static method with SqlFuntion attribute, as below

[SqlFunction(FillRowMethodName = "FillRow", DataAccess = DataAccessKind.Read)]

  • Provide a FillRow method. The FillRow method acts as a helper method which fills in a row of the result set returned by the table valued function. FillRow method is the place to handle Null values and gives the developer a chance to pass an appropriate value in case the actual result returned by the client is null.
  • At the command prompt CD to the output directory and execute the sgen command. This creates the <wcf_client_assembly_name>.XmlSerializers.dll library necessary to avoid dynamically generating the serializers used during the invocation of the web service.

Exposing a WCF client as a SQL CLR Function

To load the WCF client assembly into SQL, the below steps need to be followed

  • Enable CLR on SQL by executing the following statement

    EXEC
    sp_configure
    'clr enabled', 1

    RECONFIGURE
    WITH
    OVERRIDE;

  • Set the database Trustworthy to ON

    ALTER
    DATABASE <database name>

SET
TRUSTWORTHY
ON

  • Create the assembly in SQL by using the CREATE Assembly statement as below. Note the use of PERMISSION_SET = UNSAFE. This is required since the CLR based stored procedure bas to cross the database boundary and access external resources.

    CREATE
    ASSEMBLY <WCF_Client_Assembly_Name>

    FROM <Fully Qualified Path to the assembly file>

    WITH
    PERMISSION_SET
    =
    UNSAFE

  • Expose the static method in the assembly (uploaded in the previous step) as a SQL Function, like below

    CREATE
    FUNCTION <CLR Function Name>(<parameters to the WCF client function)

    RETURNS
    TABLE

    (

    <return values with data type>

    )

AS
EXTERNAL
NAME <WCF_Client_Assembly_Name>.<WCF_Client_Class>.<Method>;

Additional links

Introducing SQL Server 2005's CLR Integration - https://www.devsource.com/print_article2/0,1217,a=151093,00.asp

CLR Integration Programming Model Restrictions - https://msdn2.microsoft.com/en-us/library/ms403273.aspx

What is Windows Communication Foundation - https://msdn2.microsoft.com/en-us/library/ms731082.aspx

Comments