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 /o
aymentProc.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?
