Web Services and SQL Server Stored Procedures

  • Thread starter Thread starter Chris Rose
  • Start date Start date
C

Chris Rose

I've written a very basic web service that is used by my PocketPC to add two
numbers together. It works fine. So next I'm writing a WebService which
accesses my SQL Server
and returns rows appertaining to a specific OrderId.

I have written a stored procedure called spGetConfirmedOrder to which I pass
the OrderId and it works okay until I need to reference it in my code then
it doesn't recognise
it. Is there something that I'm missing out here do I need to get involde
with SOAP and WDSL files
or can I just use ADO.NET?

Regards

Chris
 
Chris:

What you say it doesn't recognize "it", what It are you referring to, the
parameter, the web service, the method or the proc itself. The way it
essentially works from the PPC is that the web service is in charge or
calling the proc. So if the WS calls a stored procedure that needs a
parameter(s), then it ultimately has to build the SqlCommand (since you are
using Sql Server) and add the paramters to it then make the call and return
the results. All the PPC needs to do is send values to the WS so it can use
those values to populate the params with in order to successfully fire the
proc.

Where do you think your problem is? Namely, are you confident that you can
invoke a web service (therefore the likely problem is getting the data to
and from it). Have you created the web reference already and built the
proxy? The only reason I ask is your statement about the WSDL and ADO.NET.
Technically, ADO.NET is only involved client side. You dont have a
dataadapter or anything like that. All you do is pass your dataset to the WS
and it handles that, and you pass params to the WS and it returns a dataset.
So IT takes care of all the datahandling...technically, if you use a WS for
all of your dataaceess, you'll have a datatable or a dataset client side,
but you won't have any command objects or connections.

As far as do you need a WSDL, the answer is that you need something.. I
don't know the web service but something has to be used to build the proxy.
Hvae you gotten this far? have you added your web reference? Basically,
can you tell me what all you've done and where specifically the problem is?

I've done more with web services than anything else and am writing an in the
process of writing an article on Invoking Google's web service with
SmartPhone 2003.... definitely have some experience with invoking WS via
devices and will do my best to help .

Let me know and I'll do what I can.

Cheers,

bill
 
Hi Bill,

When you want you can see the thread about this where Chris and I where
involved in the language.vb group.

I gave him the advice to ask it here because I thought this was your stuff.
In that thread I have set your name in the last subject, however when you
answer it do it here, I am also intrested.

I get the idea that Chris does not want to use what we call webservice at
all, however want a service over the Internet where he can directly connect
to the storedprocedure on his SQL server and fill his clients with data.

Cor
 
Bill

Thanks for your reply. Some late night coding sorted out most of the
problem for me. I had not defined the connection object or the
SQLDAdapter properly and when I did I created a global one. Now I've
sorted that out I've just got a table error as the service is not
expecting a table to be returned.

I tried to be clever in creating a class with all my SQL connection
objects so that connections would only be open as long as a call was in
place and I can reuse the classes when I create a more integrated
solution.

I'm re-reading my ADO.NET books and brushing up on my XML as well. I
have had a twelve month break in writing code and it shows.

I have still yet to write the PocketPC part of it where I'll display the
data in a very basic datagrid.

I wrote a very basic web service which adds and multiplies etc and that
works on the PPC. I'm hooked on Windows Services as it is the way
forward. My next project will be to write a basic password
authentication service using SQL Server to store my user base.

Kind Regards

Chris Rose
 
Hi Chris:

Glad you got it worked out. From the sounds of it, try to keep all of the
DB logic within the web service, have it return a datatable or a dataset,
but don't require the consumer to do anything but consume the web service.
You can build multiple methods for all your data access needs. This way you
can encapsulate everything and won't have to worry about anything but
manipulating the returned objects.. let the WS do everything else. If you
find you have a compelling reason to mix the two approaches, ok, but make
sure it's benefits outweigh the costs. This will save you a whole lot of
maintenance and security issues.

As far as the connections... close them as soon as you are done with them.
If you are only running one query for instance, as soon as it's done close
that connection. DataAdapters open and close them automatically and unless
you have a really strong reason to do otherwise, don't change it. Don't
feel compelled to leave the connection open for the entire lifespan of the
object. If you have connection pooling on, then close the connection so it
will be returned to the pool ASAP. It's a common misconception that leaving
a connection open for a specified period of time is efficient, but it's not
true. Basically, if the connection is open, make sure it's processing some
SQL Statement. If it sits idle for any more than the time it takes to call
the next sql statement, it probably needs closed. Most of the time, as soon
as your query finihsed it should close. the main exception will be if you
are firing 100 different queries for instance back to back with no
processing in between them. Leaving it open will save some overhead and in
this case, closing them, when the veyr next statement is opening it back up
doesn't do anything for you.

If you need any help, let me know.

Bill
 
Back
Top