Returning Data Using a Dataset

Team-Fly    

 
.NET and COM Interoperability Handbook, The
By Alan Gordon
Table of Contents
Chapter Ten.  XML Web Services


Another way to return tabular data from a Web service is to return it as an ADO.NET dataset. Just like ADO Recordsets, you can fabricate a dataset from in-memory data that does not come from a database. To understand how to do this, let's first look at the object model for ADO.NET datasets, which is shown in Figure 10-15.

Figure 10-15. The object model for ADO.NET datasets.

graphics/10fig15.gif

As you can see, a dataset is a collection of data tables. A data table is actually the equivalent of an ADO Recordset; it contains columns and rows. To create a fabricated Dataset, you first need to add a data table to the dataset using the Add method on the Tables collection, which is a property of the dataset. You then add the columns to the table by calling the Add method of the Columns collection, which is a property of the DataTable class. The following code shows an implementation of the Amortization table method that returns a fabricated dataset:

 1.  [WebMethod] 2.  public DataSet AmortizationTableDS( 3.      short numMonths,double interestRate, 4.      decimal  loanAmt) 5.  { 6.      DataTable dtPayments; 7.      DataRow rowPayment; 8.      short i, numLoops; 9.      double monthlyRate; 10.     decimal monthlyPmt, remainingBalance, 11.       principle, interest; 12.     DataSet ds=new DataSet(); 13.     dtPayments=ds.Tables.Add("Payments"); 14.     dtPayments.Columns.Add("Month", 15.       typeof(System.Int16)); 16.     dtPayments.Columns.Add("Payment", 17.       typeof(System.Decimal)); 18.     dtPayments.Columns.Add("Principle", 19.       typeof(System.Decimal)); 20.     dtPayments.Columns.Add("Interest", 21.       typeof(System.Decimal)); 22.     dtPayments.Columns.Add("Balance", 23.       typeof(System.Decimal)); 24.     monthlyRate=interestRate/1200; 25.     monthlyPmt=MonthlyPayment(numMonths, 26.             InterestRate,loanAmt); 27.     remainingBalance=loanAmt; 28.     numLoops=(short)(numMonths-1); 29.     for (i=0;i<numLoops;i++) 30.     { 31.       interest=decimal.Round( 32.         (decimal)(monthlyRate* 33.         (double)remainingBalance),2); 34.       principle=monthlyPmt-interest; 35.       remainingBalance= 36.         remainingBalance-principle; 37.       rowPayment=dtPayments.NewRow(); 38.       rowPayment["Month"]=i+1; 39.       rowPayment["Payment"]=monthlyPmt; 40.       rowPayment["Interest"]=interest; 41.       rowPayment["Principle"]=principle; 42.       rowPayment["Balance"]=remainingBalance; 43.       dtPayments.Rows.Add(rowPayment); 44.     } 45. // Calculate the final payment so the balance is zero; 46.     if (numMonths > 0) 47.     { 48.       interest=decimal.Round((decimal) 49.         (monthlyRate* 50.         (double)remainingBalance),2); 51.       principle=decimal.Round( 52.         remainingBalance,2); 53.       monthlyPmt=interest+principle; 54.       rowPayment=dtPayments.NewRow(); 55.       rowPayment["Month"]=numMonths; 56.                 rowPayment["Interest"]=interest; 57.                 rowPayment["Principle"]= 58.                   remainingBalance; 59.       rowPayment["Payment"]=monthlyPmt; 60.       rowPayment["Balance"]=(decimal)0.0; 61.       dtPayments.Rows.Add(rowPayment); 62.     } 63.     return ds; 64. } 

Line 12 creates an instance of the DataSet class. In order for this line to compile, you have to add a using statement for the System.Data namespace as shown here:

 using System.Data 

Line 13 adds a data table called Payments to the dataset. Lines 14 and 15 add a Month column to the Payments data table. Notice that we specify the name and type for the column. Lines 16 through 23 add Payment, Principle, Interest, and Balance columns to the data table. Lines 24 through 62 populate the data table with the amortization information for a loan. Line 37 calls the NewRow method on the Payments data table to get a new row. I then populate the row on lines 38 through 42, and line 43 calls the Add method on the Rows collection of the data table to add the new row to the table. Lines 45 through 62 repeat the same logic to calculate the final payment. Line 63 returns the data set normally. Here is the generated XML:

[View full width]
 
[View full width]
<?xml version="1.0" encoding="utf-8"?> <DataSet xmlns="http://tempuri.org/"> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns: graphics/ccc.gif msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="NewDataSet" msdata:IsDataSet="true"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element name="Payments"> <xs:complexType> <xs:sequence> <xs:element name="Month" type="xs:short" minOccurs="0" /> <xs:element name="Payment" type="xs:decimal" minOccurs="0" /> <xs:element name="Principle" type="xs:decimal" minOccurs="0" /> <xs:element name="Interest" type="xs:decimal" minOccurs="0" /> <xs:element name="Balance" type="xs:decimal" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema> <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn: graphics/ccc.gif schemas-microsoft-com:xml-diffgram-v1"> <NewDataSet xmlns=""> <Payments diffgr:id="Payments1" msdata:rowOrder="0" diffgr:hasChanges="inserted"> <Month>1</Month> <Payment>2308.94</Payment> <Principle>355.82</Principle> <Interest>1953.12</Interest> <Balance>374644.18</Balance> </Payments> <Payments diffgr:id="Payments2" msdata:rowOrder="1" diffgr:hasChanges="inserted"> <Month>2</Month> <Payment>2308.94</Payment> <Principle>357.67</Principle> <Interest>1951.27</Interest> <Balance>374286.51</Balance> </Payments> </Payments> </NewDataSet> </diffgr:diffgram> </DataSet>

Here I am using the default XML schema for data sets, but remember you can change this by calling one of the many overloads of the ReadXMLSchema method and by passing in an XSD schema. The data set will now be rendered in XML using the XSD schema that you specified.

In some (many) cases, your XML Web service will actually return data from a database. In these cases, you can use ADO.NET from your Web service methods . The following code shows an example of an XML Web service method that returns data from a database:

 1.  [WebMethod] 2.  public DataSet GetBookByTitle(string title) 3.  { 4.      DataSet ds; 5.      SqlCommand cmd; 6.      SqlDataAdapter dsAdapter; 7.      SqlParameter param; 8.      SqlConnection conn; 9.      ds=new DataSet(); 10.     conn=new SqlConnection( 11.         "user id=sa;password=;initial catalog=DevDotNet;data source=localhost"); 12.     cmd=new SqlCommand("GetBooksByTitle",conn); 13.     cmd.CommandType = CommandType.StoredProcedure; 14.     param=cmd.Parameters.Add(new 15.       SqlParameter("@Title",SqlDbType.NVarChar,255)); 16.     param.Value=title; 17.     param.Direction=ParameterDirection.Input; 18.     try 19.     { 20.     // Open the connection and execute the Command 21.       conn.Open(); 22.       dsAdapter =new SqlDataAdapter(); 23.       dsAdapter.SelectCommand =cmd; 24.       dsAdapter.Fill(ds,"Titles"); 25.       return ds; 26.     } 27.     catch (Exception err) 28.     { 29.     // An error occurred, pass the exception up 30.       throw err; 31.     } 32.     finally 33.     { 34.     // Close the Connection 35.       if (conn.State == ConnectionState.Open) 36.         conn.Close(); 37.     } 38. } 

This code is no different than any managed code that you would write to access a database using ADO.NET. You do not have to do anything special in order to write this code from within an XML Web service, so I won't walk through all the code. In this case, I am using the SQLClient managed provider. Therefore, I need to reference the System.Data.dll assembly and add the following using statement to the code:

 using System.Data.SqlClient; 

The most important section of this code is on lines 21 through 25. Line 21 opens a connection to the database. Line 22 creates a data adapter. Line 23 defines a Select command for the adapter. Line 24 calls the Fill method on the data adapter to execute the command and fill a data set with the results. Line 25 returns the data set to the client. As before, the ASP.NET runtime will serialize the data set into its XML form and encode it into the response message without any additional work from you.

You can do other things with this ability to return XML data sets from Web services. For instance, if you currently have a COM/COM+-based business object that returns ADO Recordsets, you can turn it into Web service that returns ADO.NET Datasets using the following code:

 1.  [WebMethod] 2.  public DataSet RetrieveByKeyword(string strKeyword) 3.  { 4.      PUBSBOSERVERLib.Book objBook=new 5.        PUBSBOSERVERLib.BookClass(); 6.      ADODB.Recordset rs; 7.      DataSet ds=new DataSet(); 8.      OleDbDataAdapter adapter=new 9.        OleDbDataAdapter(); 10.     rs=objBook.RetrieveByKeyword(strKeyword); 11.     adapter.Fill(ds,rs,"Books"); 12.     return ds; 13. } 

Lines 4 and 5 create an instance of the COM/COM+ business object. I used the Add Reference command (the COM tab) in Visual Studio .NET to create an Interop assembly for the business object. Because the business object uses ADO, the Interop assembly will reference the PIA for ADO, and it will be referenced automatically when you reference the COM/COM+ business object. Line 6 creates an instance of an ADO Recordset. Line 7 creates a data set. Lines 8 and 9 create an instance of the OleDbDataAdapter. Here I am using the managed provider for OleDb. In order to compile this code, I need to add the following using statement:

 using System.Data.OleDb 

Line 10 calls the RetrieveByKeyword method of the business object. This method returns an ADO Recordset that contains a list of books with titles that contain the specified keyword. Line 11 is the key line in this listing. This line uses a special Fill method in the OleDbDataAdapter, which fills a data set with the contents of an ADO Recordset. Line 12 returns the data set normally from the method.

Unfortunately, there is a problem with this approach. If you implement your XML Web Service using an ASP.NET "asmx" file, you will always incur a thread switch when you call an STA COM object because ASP.NET worker threads are setup to enter an MTA by default. Web forms ("aspx" files) support an "aspcompat" attribute that will cause the worker thread to use an STA, but this functionality is not currently supported with Web Services.

Fortunately, on some platforms, there is a better way to expose an existing COM+ application as a Web service. If you are using any of the Windows XP family of operating systems, which includes XP Professional or Windows .NET Server, you can expose an existing COM+ application as an XML Web service by clicking a single checkbox. Microsoft refers to XML Web services that you create in this manner as COM+ Web services. Because this functionality is built on the .NET remoting infrastructure, I will defer discussion of it until Chapter 11, when I talk in depth about .NET remoting.


Team-Fly    
Top
 


. Net and COM Interoperability Handbook
The .NET and COM Interoperability Handbook (Integrated .Net)
ISBN: 013046130X
EAN: 2147483647
Year: 2002
Pages: 119
Authors: Alan Gordon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net