1

We have a webservice with these methods :

  1. NOCR_Send(datatable personInfo, string code, string username, string password) returns GUID;
  2. NOCR_Get(GUID code, string code, string username, string password) returns DataTable;

I want to consume these methods in sql server. Is it a way to create datatable in TSQL?

IT School
  • 33
  • 4
  • 1
    You want to do what? Your question makes no sense, please clarify. – Rick S Apr 20 '15 at 17:44
  • My guess is you've been given a web service that has a DataTable parameter? You need to write some code to query the data out of a SQL database (using ADO.NET in C# or VB), then call the web service using a WCF Proxy class from your app/webpage. Add a service reference to your project in Visual Studio, and point the URL at the web service you're trying to call. – Jon Apr 20 '15 at 17:47
  • I create that project in C# but I need to create a CLR method or stored procedure to consume web service. – IT School Apr 20 '15 at 17:56
  • Duplicate of http://stackoverflow.com/questions/26213278/calling-a-soap-webservice-from-tsql-stored-procedure – Jon Apr 20 '15 at 18:04

1 Answers1

0

SQLCLR isn't really meant for this - it's ok for simple functions that are better done in CLR (like needing to run a RegEx on something for example), but even then it creates dependencies and management issues it shouldn't have to (your DBA shouldn't need to know .NET, your .NET developer shouldn't have to be a DBA, etc.).

What you should do here is create an ADO.NET application that consumes the webservice and then passes the relevant data to SQL server, or consumes data from SQL server and passes the relevant data to your web service. You want to keep your web service abstracted from SQL Server and vice versa - don't try to make SQL Server a WCF client (it will do poorly), just give it the data from a well written WCF client.

In other words: let SQL Server handle data and .NET handle WCF.

Dan Field
  • 20,885
  • 5
  • 55
  • 71