Still Learning - { Paulus }

Calling WCF from SQLCLR

Sometimes I have to work with legacy database system, such as FoxPro, and I need to obtain data from FoxPro using WCF since I don’t want to directly query to FoxPro database.

But the main problem of this approach is I have to call the WCF from SQL Server in order to obtain data. For example, the customer data resides in FoxPro, and I have to obtain the customer name. I created WCF method GetCustomerName and I can successfully called it from my .NET application, but how can I call the method from SQL Server stored procedure?

After some research, I found a great blog which describes clearly how to solve this problem and I will summarize it in this post.

In order to properly call the WCF method from SQL Server, we need to create a CLR Stored Procedure. CLR Stored Procedure basically is a .NET assembly deployed in SQL Server. All you have to do is create a static method and decorate it with SqlMethodAttribute or SqlFunctionAttribute.

For example, we have created a WCF, hosted in localhost which have address such as: http://localhost/MyService/Customer.svc

As you may know, we have to create a proxy class using svcutil.exe:

svcutil http://localhost/MyService/Customer.svc /out:CustomerServiceClient.cs

The svcutil will produce auto-generated code in CustomerServiceClient.cs

Then, we can add this file to our SQL CLR project in Visual Studio and we can write a static method to use the proxy class:

   1:          [SqlFunction(DataAccess=DataAccessKind.Read)]

   2:          public static string GetCustomerName(string custCode)

   3:          {

   4:              string address = GetEndPointAddress(http://localhost/MyService/Customer.svc);

   5:              EndpointAddress ep = new EndpointAddress(address);

   6:              CustomerServiceClient custSvc = new CustomerServiceClient(

   7:                  new BasicHttpBinding(),

   8:                  ep);

   9:   

  10:              string customerName = String.Empty;

  11:   

  12:              try

  13:              {

  14:                  customerName = custSvc.GetCustomerName(custCode.ToUpper());

  15:              }

  16:              catch

  17:              {

  18:              }

  19:   

  20:              return customerName;

  21:          }

The second problem arises, the WCF URL is hard coded and we can’t deploy app.config in SQL CLR project. We have to query from database table if we don’t want to hard code the WCF URL. For now, let’s ignore this problem. Compile the SQL CLR project to produce an assembly (.dll).

Now, we have to deploy this assembly.

Before we can deploy the SQL CLR assembly, we have to deploy some .NET assemblies, they are:

1. %SystemRoot%\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll
2. %SystemRoot%\Microsoft.NET\Framework\v2.0.50727\System.Web.dll
3. %SystemRoot%\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll
4. %ProgramFiles%\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll

Before deploying those, we have to make sure our database option for TRUSTWORTHY is enabled, just issue this command:

   1:  ALTER DATABASE [MyDatabaseName] SET TRUSTWORTHY ON;

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

After that, we should able to deploy those .NET assemblies successfully, make sure we deploy it using UNSAFE permission set.

The next step is we have to deploy our SQL CLR assembly using UNSAFE permission set, just issue this command:

   1:  create assembly [AssemblyName]

   2:  from  'path_to_assembly'

   3:  with permission_set = unsafe

Then we can create the T-SQL function as usual:

   1:  create function dbo.wcf_GetCustomerName(@CustCode nvarchar(20))

   2:  returns nvarchar(50)

   3:  external name MyAssembly.[MyCompany.Customer].GetCustomerName

MyAssembly is the assembly filename.
MyCompany is C# the namespace
Customer is C# class name.
GetCustomerName is C# static method which returns string and ask single string parameter.

And then, we should able to call the WCF method using our T-SQL function as below:

select dbo.wcf_GetCustomerName('CA5290')

An error may occur like this one:

A .NET Framework error occurred during execution of user-defined routine 
or aggregate <your T-SQL function>:
System.Configuration.ConfigurationErrorsException:
The type 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior,
Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0,
Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
registered for extension 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior'
could not be loaded.
(C:\Windows\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 189)

Simply just disable the WCF debugging using this command in Visual Studio command prompt:

   1:  vsdiag_regwcf.exe -u

re-issue the select statement like above, we suppose to able to call the WCF method successfully.

Share this post: | | | |