Download the video screen cam of this tip.
Datasets are a completely disconnected way of passing data between components, functions, or applications. What you may not realize is that the "killer app" for Datasets is Web services.
Datasets live a double life. When used within a .NET application, they can be accessed through ADO.NET object model in a manner similar to accessing recordsets using ADO in the past. But they also can be persisted into XML, complete with relations, and self-describing meta-data without any effort on your part. ADO.NET manages how the dataset is persisted between its in-memory representation and its XML persisted form based on a given situation. When passing a Dataset back from a Web service, ADO.NET serializes the Dataset into XML and sends it along its merry way to the Web service consumer.
To illustrate how easy Visual Studio.NET makes this entire process, I've built a small affiliate management Web service that allows an affiliate's Web site to check on how its affiliate program is going, and the commissions that it is owed by the company running the affiliate program. There are three database tables I created in SQL Server:
- An Affiliate table, which has information about all the affiliates in the program
- A Customer table, which keeps track of the customers who have made purchases, including a foreign key relationship with the Affiliate who referred this customer to the site, and finally...
- A Purchase table, which has the details about each purchase a particular customer has made on the site.
The Web site owner can integrate the data from this Web service into his existing customer management application, his accounting application, or into his Web site in whatever way he pleases. Again, the purpose of this is to illustrate how easy it is to create this Web service using Visual Studio.NET. I'll walk through these steps at a fairly high level, so I would encourage you to download the screen cam video showing how I created this project so you can witness the entire process from start to finish. The following are the basic steps:
- Start by creating the tables in SQL Server (or the MSDE that ships with Visual Studio.NET). Add some sample data to the tables.
- Create a new Visual Basic.NET Web Service project and rename the "service1.asmx" file to "AffiliateManagement.asmx".
- In the Server Explorer, create a Data Connection to the database in which you created the tables, then navigate to the Purchase table, and drag-and-drop the table onto the Web service Designer Surface. This creates a SQL Connection and a SQL Data Adapter on the Web service's Designer Surface.
- Select the SQL Data Adapter and select the "Configure Data Adapter..."link at the bottom of the Properties window. This opens the "Data Adapter Configuration Wizard."
- Use the Query Builder to create a SELECT statement that grabs all of the purchase information from the Purchase table and some customer information, (ESPECIALLY the affiliate_id) from the Customer table. The SELECT statement looks like this:
SELECT purchase.purchase_id, purchase.purchase_date, purchase.purchase_amount,
purchase.customer_id, purchase.commission_due, purchase.commission_paid_date,
customer.customer_name, customer.customer_email, customer.customer_id
FROM purchase
INNER JOIN customer ON purchase.customer_id = customer.customer_id WHERE
(customer.affiliate_id = @affiliate)
Notice that I've included a parameter (@affiliate), which allows me to pass in a specific affiliate_id for the affiliate I will search on. I allow the wizard to create the stored procedure for me.
- Next, select the SQL Data Adapter again, and this time, select the "Generate Dataset..." link under the Properties window. From here you can create a Data Set called dsAffiliatePurchase and add an instance of it to the Web service Designer Surface.
- Now, access the code behind for the Web service and create a Web Method that looks like this:
<WebMethod()> _
Public Function GetAffiliateDetail(ByVal AffiliateID As Integer) _
As dsAffiliatePurchase
SqlDataAdapter1.SelectCommand.Parameters.Item("@affiliate").Value =
AffiliateID
SqlDataAdapter1.Fill(DsAffiliatePurchase1, "purchase")
GetAffiliateDetail = DsAffiliatePurchase1
End Function
Notice that the GetAffiliateDetail Web Method returns an instance of dsAffiliatePurchase. How will it do this? We let ADO.NET worry about the details of serializing the Dataset into XML. The first line of code sets the @affiliate parameter to the value passed into the Web service by the consumer. The second line of code "fills" the instance of the dataset that was added to our Designer Surface into the "purchase" table (which is just a name... in this case it does not have the actual structure of the "purchase" table but was named that since we dragged-and-dropped originally from the Server Explorer in step 4 above). The third line of code then sets the results in the dataset to the return value for the Web Method.
When you compile and test using the Web service Help Page to enter the AffiliateID, you'll see the values of the dataset passed back as serialized into XML.
Again, due to space constraints, I went over the steps at a high level, and would encourage you to download the video screen cam that demonstrates all of these steps more easily than words can express. In a few basic steps, you can expose information from your database to your trading partners, affiliates or potential customers through Web services, ADO.NET and Visual Studio.NET.
But the REAL magic happens in the next several tips, which shows how the XML is then persisted back into an ADO.NET dataset, and how changes to Datasets by the client of the Web service are recorded into Datagrams and sent back to the Web service provider for processing. Stay tuned.
About the Author
Robert Tabor is a Microsoft Certified Professional in Visual Basic with over six years of experience developing n-tier Microsoft-centric applications for some of the world's most prestigious companies and consulting organizations, such as Ernst & Young, KPMG, Cambridge Technology Partners, Sprint, American Heart Association, and the Mary Kay Corporation. Bob is the author of Microsoft .NET XML Web services by Sams Publishing, and contributes to
SoapWebservices.com and LearnVisualStudio.NET. He is currently working on initiatives within Mary Kay, the second largest eCommerce site in retail volume on the net, of how to utilize .NET within their e-business group.
