473,320 Members | 1,987 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Execute stored procedure via web method.

From a vb.net application I'm want to execute an Oracle Stored Procedure via
a web service. What would be the best way to approach this? I can't pass and
Oracle Command or the parameter objects object to the Webservice, cause
they're not serializable. How would I get the parameters to the web method?
Is there a dynamic P/SQL statement string that I can pass to the web method
and execute it as such? I've been searching for a method and or idea of how
to do this, I would appreciate some professional advice.
Best Regards,

Russ.
Nov 21 '05 #1
3 8148
Russell Verdun wrote:
From a vb.net application I'm want to execute an Oracle Stored
Procedure via a web service. What would be the best way to approach
this? I can't pass and Oracle Command or the parameter objects object
to the Webservice, cause they're not serializable. How would I get the
parameters to the web method? Is there a dynamic P/SQL statement
string that I can pass to the web method and execute it as such? I've
been searching for a method and or idea of how to do this, I would
appreciate some professional advice.


Did you read the answer I gave to this question the first time you posted it? There's no magic bullet solution for this. You can't use the built in types because they're not XML serializable, so you need to write your own. You then need to map back and forth from your types at the web service interop level to the real command object types internally. Best case scneario, if you don't need all the features of a command (e.g. parameters), you can just take and return a string, but I even suggested a nice little schema you can implement to accept parameters in my reply to your initial request for help with this.

HTH,
Drew
Nov 21 '05 #2
Yes, I did get your answer, but not sure where to start, I'm not versed in
XML, but it looks straight forward, where would XML code go?

Russ
"Drew Marsh" <dr****@hotmail.no.spamming.com> wrote in message
news:uW**************@TK2MSFTNGP12.phx.gbl...
Russell Verdun wrote:
From a vb.net application I'm want to execute an Oracle Stored
Procedure via a web service. What would be the best way to approach
this? I can't pass and Oracle Command or the parameter objects object
to the Webservice, cause they're not serializable. How would I get the
parameters to the web method? Is there a dynamic P/SQL statement
string that I can pass to the web method and execute it as such? I've
been searching for a method and or idea of how to do this, I would
appreciate some professional advice.
Did you read the answer I gave to this question the first time you posted

it? There's no magic bullet solution for this. You can't use the built in
types because they're not XML serializable, so you need to write your own.
You then need to map back and forth from your types at the web service
interop level to the real command object types internally. Best case
scneario, if you don't need all the features of a command (e.g. parameters),
you can just take and return a string, but I even suggested a nice little
schema you can implement to accept parameters in my reply to your initial
request for help with this.
HTH,
Drew

Nov 21 '05 #3
Russell Verdun wrote:
Yes, I did get your answer, but not sure where to start, I'm not
versed in XML, but it looks straight forward, where would XML code go?


Ahh, well we can help you out there. Unfortunately the answer to your question is a lot longer than could possibly be explained and understood in a newsgroup posting. I highly suggest reading up on XML, XML Schema and WSDL. However, I'll explain how I would do it from a pure .NET perspective and hopefully you can run with it from there.

I would suggest something like the following...

An schema like:

<xs:schema id="SimpleCommandExample" targetNamespace="uri:marsh-samples-SimpleCommand" elementFormDefault="qualified"
xmlns="uri:marsh-samples-SimpleCommand" xmlns:xs="http://www.w3.org/2001/XMLSchema"
attributeFormDefault="unqualified">
<xs:complexType name="SimpleCommand">
<xs:sequence>
<xs:element name="parameters" minOccurs="0" maxOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:sequence>
<xs:element name="parameter" type="SimpleCommandParameter" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="text" type="xs:string" />
</xs:complexType>
<xs:complexType name="SimpleCommandParameter">
<xs:attribute name="name" type="xs:string" />
<xs:attribute name="value" type="xs:string" />
<xs:attribute name="type" type="xs:QName" />
</xs:complexType>
<xs:element name="command" type="SimpleCommand"></xs:element>
</xs:schema>

Which would result in instance documents like:

<command xmlns="uri:marsh-samples-SimpleCommand" xmlns:xsd="http://www.w3.org/2001/XMLSchema" text="SELECT * FROM MyTable WHERE myField = @myParameter">
<parameters>
<parameter name="@myParameter" type="xsd:int" value="1" />
</parameters>
</command>

And a web method that handles such commands like:

<codeSnippet language="C#">
[WebMethod]
[SoapDocumentMethod(ParameterStyle=SoapParameterSty le.Bare, Use=SoapBindingUse.Literal)]
[return:XmlElement("results", Namespace="uri:marsh-samples-MyServiceTypes")]
public XmlDataDocument ExecuteCommand([XmlElement("command", Namespace="uri:marsh-samples-SimpleCommand")] XmlElement commandElement)
{
// TODO: run a validating reader over the element if you're concerned someone might pass you invalid instances

SqlCommand command = new SqlCommand(commandElement.GetAttribute("text"));

// NOTE: assumes schema is in default namespace which it probably wouldn't be, so make sure to use overload where
// XmlNamespaceManager is passed and use namespace prefix on XPath elements
XmlNodeList parameterElements = commandElement.SelectNodes("parameters/parameter");

if(parameterElements.Count > 0)
{
SqlParameterCollection commandParameters = command.Parameters;

foreach(XmlElement parameterElement in parameterElements)
{
string xsdNamespacePrefix = commandElement.OwnerDocument.GetPrefixOfNamespace( "http://www.w3.org/2001/XMLSchema");

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = parameterElement.GetAttribute("name");

string parameterElementValue = parameterElement.GetAttribute("value");

string parameterXsdType = parameterElement.GetAttribute("type");
string[] qNameParts = parameterXsdType.Split(':');

if(qNameParts[0] != xsdNamespacePrefix)
{
throw new NotSupportedException("Expecting parameter types only from the Xml Schema Definition namespace.");
}

object parameterValue;

switch(qNameParts[1])
{
case "int":
case "nonNegativeInteger":
parameterValue = XmlConvert.ToInt32(parameterElementValue);

break;

case "string":
parameterValue = parameterElementValue;

break;

// TODO: handle all the XSD types you want support

default:
throw new NotSupportedException(string.Format("Unsupported XSD type specified: {0}.", parameterXsdType));
}

parameter.Value = parameterValue;
}
}

// TODO: execute the command and get back a real, filled dataset
DataSet resultDataSet = new DataSet();

XmlDataDocument result = new XmlDataDocument(resultDataSet);

return result;
}
</codeSnippet>

HTH,
Drew
Nov 21 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Thomas Scheiderich | last post by:
Why would you use the Recordset object over the Execute method of getting data from your Sql database. For example, I have the following: Execute Method...
2
by: Matt | last post by:
I want to exexute stored procedure in ASP, but it has error "Microsoft VBScript compilation (0x800A0401) Expected end of statement" on line (1). The stored procedure "sp_emp" contain "select *...
0
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft...
2
by: Noloader | last post by:
Hello, Access XP, SQL Server 2000 Is it possible to hide a SP under Queries in Access, yet still be able to Execute it from Access? (Similar to hiding Tables, then using Views) We hooked...
2
by: Eli | last post by:
Hi all We currently have a strange problem with calling a Stored Procedure (SQL Database) in our C# Project. The only error I get is "System error" which says a lot :) Background: We have...
8
by: Jiggaz | last post by:
Hi, In my ASPX Page, i have a form for signup. And whene user click on the button, the event Button1_Click must use a stored procedure. But instead of use stored proc, i get this exception :...
7
by: JIM.H. | last post by:
Hello, Is there any difference to between SLQ string in the code and call execute query and call a stored procedure and execute the query that way concerning speed, effectiveness, reliability,...
0
by: franjorge | last post by:
Hi, I have created two stored procedures via VB using this code: sql = "CREATE PROC " & nombre_proc & " AS SELECT *" & _ " From MBM_PUNTOS_SCE_SIN_COINCIDIR_SIEGE_FALTA_PM_NE_" & mes & _ "...
1
by: shsandeep | last post by:
I 'recreated' a stored procedure. Earlier, i had given privileges to a particular group but now (after rebuilding) when I try to grant execute privilege for the SP to the same user group, it...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.