Lessons from Simple Web Service Accessing SQL
1. WebMethod signature of void(string, out double, out double) turned into double(string, out double) on the client. Why?
2. “finally” works fine after a return and/or a catch, use it to close sql connection.
3. Testing a web service via Internet Explorer displays 500 errors if the service throws an exception.
4. Use FrontPage mode to copy the web service project to a public web server if different account credentials will be needed for access.
5. Overloaded methods need to use the MessageName property in the WebMethodAttribute i.e. [WebMethod(MessageName=”foo2”)].
6. Simplest SQL data retrieval requires at least these steps:
a. Create and open a connection object with a connection string.
b. Create a command object with a comman string and connection object.
c. Execute the command, returning a reader object.
d. Issue a read method call on the reader object to advance to the first result row.
e. Use the Get* methods to retrieve column values from the current row.
f. Close the connection.
7. Setting up a SQL database for use by a web service:
a. Add the host\ASPNET Windows User to the SQL->Security->Logins. “host” is the name of the web service server.
b. Giving localhost/ASPNET db_datareader access is sufficient for SELECT commands returning data.
c. Only a single ASPNET user can be given db_datareader access to an individual database. KIZMET\ASPNET and PELL\ASPNET can’t both have access simultaneously since both would be user “ASPNET”.
So it’s a “good thing” that a web server (YAM) accessing a remote sql server (PELL) comes in as user KIZMET\YAM$.
d. Install MDAC on web server if needed. http://www.microsoft.com/data/download_270RTM.htm
8. Setting up a NUnit test project to test a web service:
a. Name the project “Test”.
b. Project properties settings:
i. assembly & startup object names to
ii. Debug Mode to program
iii. Start Application to C:\Program Files\NUnit V2.0\bin\nunit-gui.exe
iv. Command Line Arguments to /assembly:
c. Add a reference to nunit.framework
d. Add a web reference to http://..../foo.asmx?wsdl
e. Add a “using localhost” or “using org.kizmet.www”