Accessing a web service using clr in SQL 2005

I need to access a billing webservice from SQL. I createde a new c# class project and made a web refrence to the web service "ProdBilling".

Here is the code of my assembly

using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
namespace PaymentProc
{
public class PaymentProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ChargeCard(int account, int amount)
{
string Response;
ProdBilling.Service serv = new ProdBilling.Service();
Response = serv.ChargeCard(account, amount);
SqlContext.Pipe.Send(Response);
}
}
}

I then ran WSDL

wsdl /oaymentProc.cs /naymentProc http://ProdWeb1/PaymentProc/PaymentProc.asmx

Then compliled

csc /target:library PaymentProc.cs

and added the assembly
CREATE ASSEMBLY PaymentProc from 'D:\ProdCode\PaymentProc.dll' WITH
PERMISSION_SET = UNSAFE

I cannot figure out how to refrence the chargecard method

I have tried

CREATE PROCEDURE PaymentProc
@.Account int,
@.Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard

It seems wsdl.exe put all this serialization code

namespace PaymentProc {
using System.Diagnostics;
using System.Web.Services;
using System.ComponentModel;
using System.Web.Services.Protocols;
using System;
using System.Xml.Serialization;

///
[System.CodeDom.Compiler.GeneratedCodeAttribute("wsdl", "2.0.50727.42")]
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.ComponentModel.DesignerCategoryAttribute("code")]
[System.Web.Services.WebServiceBindingAttribute(Name="ServiceSoap", Namespace="http://ProdWeb1/PaymentProc")]
public partial class PaymentProc : System.Web.Services.Protocols.SoapHttpClientProtocol {

private System.Threading.SendOrPostCallback ChargeCardOperationCompleted;

///
public PaymentProc()
{
this.Url = "http://ProdWeb1/PaymentProc/PaymentProc.asmx";
}

///
public event ChargeCardCompletedEventHandler ChargeCardCompleted;

///
[System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://ProdWeb1/PaymentProc/ChargeCard", RequestNamespace="http://ProdWeb1/PaymentProc", ResponseNamespace="http://ProdWeb1/PaymentProc", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]
public string ChargeCard(int account, int amount) {
object[] results = this.Invoke("ChargeCard", new object[] {
account,
amount});
return ((string)(results[0]));
}
.................

When I run

CREATE PROCEDURE PaymentProc
@.Account int,
@.Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard

I get error

Method, property or field 'ChargeCard' of class 'PaymentProc.PaymentProc' in assembly 'PaymentProc' is not static.

Any ideas? This seemsed so straitforward in the beginning.

Change your ChargeCard CLR method (the one you are marking as a proc) to some other name, and then change the CREATE PROCEDURE statement to use that changed name. That should hopefully do it.

Niels
|||

I think my problem originates from the fact that you cannot complie an assembly using a web refrence using csc. I redesigned my assembly with a web refrence to http://ProdWeb1/PaymentProc/PaymentProc.asmx called ProdBilling which I can test operation ChargeCard successfully.

using System;

using System.Collections.Generic;

using System.Text;

namespace PayProcAssembly

{

public class PaymentProcessing

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static string ChargeCard(int Account, float Amount)

{

ProdBilling.PayProcessing serv = new ProdBilling.PayProcessing();

string result = serv.ChargeCard(Account, Amount);

SqlContext.Pipe.Send(result);

}

}

}

When I run

csc /target:library PaymentProcessing.cs

I get

Error: The type or namespace name 'ProdBilling could not be found (are you missing a using directive or an assembly reference?)

I was told I needed to create a proxy using WSDL.exe but it seems when I ran

wsdl /o PaymentProc.cs /n PaymentProc http://ProdWeb1/PaymentProc/PaymentProc.asmx

It messed up my code. I cant even find the web refrence anymore. Is this correct that I have to use wsdl or is there an easier way?