When I first starting developing web services, I just loved to use DataSets to return data from the service.  I mean, they are pretty easy to use, and I am all about the easy road.  Then I stumbled across Scott Hanselman's blog article where I learned that "returning DataSets from web services is the spawn of Satan and represents all that is truly evil in the world."  Oops.  While I found this and other articles which explained the problems with using DataSets as a container for returning data from a web service, I didn't really find that "for Dummies" walkthrough that I was looking for.  At least, not a walkthrough that met my particular needs and that my feeble brain could understand.  This article is intended to be that walkthrough I wish I could have found.

DOWNLOAD COMPLETE CODE FOR THIS ARTICLE HERE: WebServiceArticle.zip (119.38 kb)

First, a disclaimer.  I definitely don't know all there is to know about web services.  There is probably a better way than what I am going to show you.  Still, I have learned some valuable lessons about web services in the last several months, and I am certain that what I am going to show you is much better than what I was doing, ie. using DataSets.  If you stumble across this article and see an important point that I have missed or gotten wrong, please post it as a comment. 

I won't go into a length explanation of the problem with returning DataSets from a web service.  I think that was done VERY well in Aaron Skonnard's article on MSDN here: http://msdn.microsoft.com/en-us/magazine/cc188755.aspx.  There is also a pretty thorough explanation with code examples in Wangming Ye's article on IBM's website here: http://www.ibm.com/developerworks/xml/library/ws-tip-j2eenet2.html   

Frankly, you may not see much difference in the content of my article and Wangming Ye's.  His article is definitely more technically thorough.  My objective here is to be more Dummy-friendly for newbie programmers like myself.  In case you aren't going to read those two articles, here's my nutshell summary.  When you return DataSets, DataTables, ArrayLists or pretty much any of the other generically-typed collection objects in the .NET framework from your web service, the section in your .NET-generated WSDL file that describes the structure of your data gets pretty ambiguous.  Those Java, Perl, PHP, etc. programmers out there who try to bind to you service using your WSDL are going to curse you and Microsoft because their toolkits aren't going to be able to accurately decipher the data structure.  This means they'll have to drop down to the XML and write extra code to parse out the data.  Yuck.  

Ok, let's build a .NET web service that even a Perl-programming, Bill-Gates-hater will not complain about.  I'm going to keep it fairly simple for the sake of clarity.  Let's say I have a MySql database called "db_test".  It's got one table called "tbl_recipes" (see the table diagram). 

Our service is going to have one web method called "getRecipesByIngredient".  As the name suggests, it will take a string ingredient as an input parameter and return back a collection of all the recipes that have that ingredient in it.  The collection will include the recipe ids, names, ingredients, and contributor names.  

In Visual Studio 2008, choose File > New Web Site, and choose "ASP.NET Web Service" under the Visual Studio installed templates section (sorry, I haven't learned about WCF services yet).  I won't go into setting up the MySql database here.  If you aren't sure how to do that, check out the "Setting Up Connections to Your MySQL Database" section of this article. 

Right out of the box, Visual Studio gives you a basic "Hello World!" web service already started with the file "App_Code/Service.cs" already open.  For me, that file looked like this: 

Just for good measure, let's customize some things.  At line 8, I'm assuming our service is hosted on a fictious website called "recipes.org" and I'm changing the code to look like this:

Now, I can go back to my App_Code/Service.cs file and create my new getRecipesByIngredient web method.  I am going to be using some ADO.NET objects as well as objects from the .NET Connector to get my data from the database, so I need to add a couple of using statements to the top of my Service.cs file as follows:

A much simpler approach to the sql code at lines 34 - 44 would have been to just concatenate the passed in string ingredient to the string SqlCode like this: 

string SqlCode = "SELECT `recipe_id`, `recipe_name`, `ingredients`, `contributor` ";
SqlCode += "FROM `tbl_recipes` WHERE `ingredients` LIKE concat('%" + ingredient + "%');";

This is considered bad practice, though, from a security standpoint.  More information about that here. 

Another good thing to do which I honestly neglected was adding a description to the WebMethod attribute just above the declaration of the method.  As with the description of the service that we added earlier in the article, this method description will be displayed on the .asmx page that the .NET engine generates for us.  The code looks something like this:
[WebMethod(Description = "Takes an ingredient as a string and returns an array of matching recipes and their ids from the recipes.org database.")]

In regards to understanding the DataSet problem, if you are a visual person like me, you might find it interesting to stop at line 53 and just return the DataSet ds first before adding the code for the rest of the method.  If you do this, you'll need to temporarily change the declaration of the web method from:

to: