Building InputOutput Web Services

only for RuBoard

Building Input/Output Web Services

In the previous example, you built a very simple Web service. No input parameters were expected, and the output was a predefined string value. Web services are capable of much more than simply providing predefined string values, of course. In this section, I will show you how to build more complex Web services that take in and return a variety of data types.

The following is a list of data types supported in Web services:

  • boolean

  • byte

  • double

  • datatype

  • enumeration

  • float

  • int

  • long

  • short

  • string

  • unsignedByte

  • unsignedInt

  • unsignedLong

  • unsignedShort

Like any class method, Web services can take in any number of parameters, each using different data types. The types supported by the Web service are largely dependent on the protocol used to invoke the Web service. SOAP is the most extensible protocol because it is XML message based; however, it is also the heaviest on the wire. In other words, a SOAP message uses much more bandwidth than a simple Http-Get or Http-Post. The input and output values can be serialized into XML and passed between the consumer and provider in a SOAP message. Http-Get and Http-Post are more limited, as they only work with name /value pair passed in either the URL (Http-Get) or as part of the request header (Http-Post), but are much lighter on the wire (i.e. use less bandwidth).

In the Northwind sample application, available at http://www.samspublishing.com), we have built a Web service named ProductServices . The ProductServices Web service has three Web-callable methods ( WebMethods ): PlaceOrder() , GetProducts() , and GetOrderDetail() . Each of these WebMethods requires input parameters provided by the consumer.

The ProductServices ' Web service basic framework is shown in Listing 14.3. You will add the three WebMethods to the ProductServices class.

Listing 14.3 The ProductServices ' Web Service Framework
 [VB] 01: <%@ WebService Language="VB" Class="Northwind.ProductServices" %> 02: 03: Imports System.Web.Services 04: Imports System 05: Imports System.Data 06: Imports System.Data.SqlClient 07: 08: Namespace Northwind 09: 10:  <WebService(Namespace:="http://www.dotnetjunkies.com/northwind/")> _ 11:  Public Class ProductServices 12: 13:   Private ConString As String = "server=localhost;database=Northwind;uid=sa;pwd=;" 14:   ' WebMethods will be inserted here 15:  End Class 16: 17: End Namespace [C#] 01: <%@ WebService Language="C#" Class="Northwind.ProductServices" %> 02: 03: using System.Web.Services; 04: using System; 05: using System.Data; 06: using System.Data.SqlClient; 07: 08: namespace Northwind{ 09: 10:  [WebService(Namespace="http://www.dotnetjunkies.com/northwind/")] 11:  public class ProductServices{ 12: 13:   private string ConString = "server=localhost;database=Northwind;uid=sa;pwd=;"; 14:     // WebMethods will be inserted here 15:  } 16: 17: } 

In Listing 14.3, you simply set up the framework in which you will build the Web service. On line 1, you identify this file as a Web service with the @ WebService directive. On lines 3 through 6, you import the required namespaces. You will be using the ADO.NET data classes and the Sql Managed Provider, so the System.Data and System.Data.SqlClient namespaces are required. The System namespace is required for some DateTime manipulation you will be doing. On line 8, you declare the classes in this Web service as part of the Northwind namespace. On line 10, you use the WebService attribute to declare the namespace of the Web service, and on line 11 you declare the public class that is your Web service, ProductServices . On line 13, you create a private string, ConString , that you will use as the ConnectionString property for any data access you do.

Building the GetProducts() WebMethod

The GetProducts() WebMethod is intended to provide a Web-callable interface for a consumer to get an XML document (a DataSet ) of products and product information for a given category. The GetProducts() WebMethod takes one input parameter, the CategoryID . The category ID's are provided in the description of the Web service.

Getting products by a given category ID is a piece of application logic that you use in other parts of the Northwind Web site. You certainly do not want to duplicate the logic to do this. Rather, you want to encapsulate the logic in a class (part of the application DLL in the \bin directory), and invoke a method call on it. Web services enable this. Although the previous examples demonstrated building Web services with the application logic in the .asmx file, you are not limited to that. As with Web Forms or user controls, you can use the logic from custom classes in the Web service.

To build the GetProducts() WebMethod , first you build a Products class and then compile it into your application DLL in the bin directory. Listing 14.4 shows the code for the Products class.

Listing 14.4 The Northwind.Products() Class
 [VB] 01: Imports System 02: Imports System.Data 03: Imports System.Data.SqlClient 04: 05: Namespace Northwind 06: 07:  Public Class Products 08: 09:   Private ConString As String = "server=localhost;database=Northwind;uid=sa;pwd=;" 10: 11:   Public Function GetProductsByCategory(CategoryID As Integer) As SqlDataReader 12:    Dim productsReader As SqlDataReader 13:    Dim myConnection As New SqlConnection(ConString) 14:    Dim getProductsCommand As SqlCommand 15:    Dim SqlStmnt As String 16: 17:    SqlStmnt = "SELECT Products.ProductID, Products.ProductName, " & _ 18:      "Suppliers.CompanyName, Categories.CategoryName, " & _ 19:      "Products.QuantityPerUnit, Products.UnitPrice, " & _ 20:      "Products.UnitsInStock " & _ 21:      "FROM Products INNER JOIN Suppliers ON " & _ 22:      "Products.SupplierID = Suppliers.SupplierID " & _ 23:      "INNER JOIN Categories ON " & _ 24:      "Products.CategoryID = Categories.CategoryID " & _ 25:      "WHERE Categories.CategoryID = " & CategoryID 26: 27:    getProductsCommand = New SqlCommand(SqlStmnt, myConnection) 28:    getProductsCommand.Connection.Open() 29:    productsReader = getProductsCommand.ExecuteReader() 30: 31:    Return productsReader 32:   End Function 33: 34:  End Class 35: 36: End Namespace [C#] 01: using System; 02: using System.Data; 03: using System.Data.SqlClient; 04: 05: namespace Northwind{ 06: 07:  public class Products{ 08: 09:   private string ConString = "server=localhost;database=Northwind;uid=sa;pwd=;"; 10: 11:   public SqlDataReader GetProductsByCategory(int CategoryID){ 12:    SqlDataReader productsReader; 13:    SqlConnection myConnection = new SqlConnection(ConString); 14:    SqlCommand getProductsCommand; 15:    string SqlStmnt; 16: 17:    SqlStmnt = "SELECT Products.ProductID, Products.ProductName, " + 18:      "Suppliers.CompanyName, Categories.CategoryName, " + 19:      "Products.QuantityPerUnit, Products.UnitPrice, " + 20:      "Products.UnitsInStock " + 21:      "FROM Products INNER JOIN Suppliers ON " + 22:      "Products.SupplierID = Suppliers.SupplierID " + 23:      "INNER JOIN Categories ON " + 24:      "Products.CategoryID = Categories.CategoryID " + 25:      "WHERE Categories.CategoryID = " + CategoryID; 26: 27:    getProductsCommand = new SqlCommand(SqlStmnt, myConnection); 28:    getProductsCommand.Connection.Open(); 29:    productsReader = getProductsCommand.ExecuteReader(); 30: 31:    return productsReader; 32:   } 33: 34:  } 35: 36: } 

The Products.GetProductsByCategory() method uses the Sql Managed Provider to retrieve product information from the Northwind database and return it as a SqlDataReader .

Note

In Listing 14.4, you open a connection to the database (line 28) using an instance of the SqlConnection class. You never explicitly close and destroy the connection object. This is because the DataReader needs the connection to remain open while it is being used. When the calling client is done with the DataReader the .NET Framework will manage closing and destroying the SqlConnection object.


The GetProducts() WebMethod will create an instance of the Products class, and use the GetProductsByCategory() method to get the data that will be returned to the Web service consumer. Listing 14.5 shows the code for the GetProducts() WebMethod . The code in Listing 14.5 should be inserted at line 14 of Listing 14.3.

Listing 14.5 The ProductServices.GetProducts() WebMethod
 [VB] 01: <WebMethod(Description:="Return a list of products available from the " & _ 02:  "Northwind Traders Company in a particular category.<br>" & _ 03:  "The Categories are:" & _ 04:  "<ol>" & _ 05:  "<li>Beverages</li>" & _ 06:  "<li>Condiments</li>" & _ 07:  "<li>Confections</li>" & _ 08:  "<li>Dairy Products</li>" & _ 09:  "<li>Grains/Cereals</li>" & _ 10:  "<li>Meat/Poultry</li>" & _ 11:  "<li>Produce</li>" & _ 12:  "<li>Seafood</li>" & _ 13:  "</ol>")> _ 14: Public Function GetProducts(CategoryID As Integer) As DataSet 15:   Dim ProductsDataSet As New DataSet() 16:   Dim myProducts As Products = New Products() 17:   Dim productsReader As SqlDataReader 18:   Dim productsTable As DataTable = New DataTable() 19:   Dim newRow As DataRow 20:   Dim i As Integer = 0 21: 22:   productsReader = myProducts.GetProductsByCategory(CategoryID) 23:   For i = 0 to productsReader.FieldCount-1 24:    productsTable.Columns.Add(New DataColumn(productsReader.GetName(i))) 25:   Next 26: 27:   While productsReader.Read() 28:    newRow = productsTable.NewRow() 29:    For i = 0 to productsReader.FieldCount-1 30:     newRow.Item(i) = productsReader.GetValue(i) 31:    Next 32:    productsTable.Rows.Add(newRow) 33:   End While 34: 35:   ProductsDataSet.Tables.Add(productsTable) 36:   ProductsDataSet.Tables(0).TableName = "Products" 37: 38:   Return ProductsDataSet 39:  End Function [C#] 01: [WebMethod(Description="Return a list of products available from the " + 02:  "Northwind Traders Company in a particular category.<br>" + 03:  "The Categories are:" + 04:  "<ol>" + 05:  "<li>Beverages</li>" + 06:  "<li>Condiments</li>" + 07:  "<li>Confections</li>" + 08:  "<li>Dairy Products</li>" + 09:  "<li>Grains/Cereals</li>" + 10:  "<li>Meat/Poultry</li>" + 11:  "<li>Produce</li>" + 12:  "<li>Seafood</li>" + 13:  "</ol>")] 14: public DataSet GetProducts(int CategoryID){ 15:   DataSet ProductsDataSet = new DataSet(); 16:   Products myProducts = new Products(); 17:   SqlDataReader productsReader; 18:   DataTable productsTable = new DataTable(); 19:   DataRow newRow; 20:   int i = 0; 21: 22:   productsReader = myProducts.GetProductsByCategory(CategoryID); 23:   for(i = 0; i < productsReader.FieldCount-1; i++){ 24:    productsTable.Columns.Add(new DataColumn(productsReader.GetName(i))); 25:   } 26: 27:   while(productsReader.Read()){ 28:    newRow = productsTable.NewRow(); 29:    for(i = 0; i < productsReader.FieldCount-1; i++){ 30:     newRow[i] = productsReader.GetValue(i); 31:    } 32:    productsTable.Rows.Add(newRow); 33:   } 34: 35:   ProductsDataSet.Tables.Add(productsTable); 36:   ProductsDataSet.Tables[0].TableName = "Products"; 37: 38:   return ProductsDataSet; 39:  } 

On line 1 of Listing 14.5, you declare the method as a WebMethod , and pass into it a description for the WebMethod (lines 1 through 13). The description will be rendered on the WebMethod description page generated by the .NET Framework (see Figure 14.3). The GetProducts() WebMethod returns an XML file (a DataSet ) of products for a given category. This is done by creating an instance of the Products class and calling the GetProductsBy Category() method, passing in the CategoryID passed into the WebMethod . Because the GetProductsByCategory() method returns a SqlDataReader , you have to programmatically create a DataTable , fill it with the records from the SqlDataReader , and drop the DataTable into a DataSet to be returned.

On line 18, you create an instance of the DataTable class, and an instance of the DataRow class on line 19. Although you know the names and number of columns that should be in the DataTable , based on the Products.GetProductsByCategory() method, this WebMethod should be built to allow that information to change without breaking the WebMethod . Using a For...Next loop, you loop through the first record in the SqlDataReader to create the columns in the DataTable . Using this design, the GetProductsByCategory() method can be changed to return different fields of data, and the GetProducts() WebMethod will adapt.

On line 27, you begin iterating through the data in the SqlDataReader using the Read() method. The Read() method advances the SqlDataReader one record at a time. With each record, you add a new DataRow to the DataTable . After all the rows in the SqlDataReader have been filled in the DataTable , the DataTable is added to an empty DataSet , and the DataSet is returned to the consumer.

Figure 14.6 shows the GetProducts() WebMethod description page.

Figure 14.6. The GetProducts() WebMethod page shows the WebMethod description, and an input box for entering a category ID for testing.
graphics/14fig06.gif

Building the PlaceOrder() WebMethod

The PlaceOrder() WebMethod is intended to enable customers to place orders to Northwind. Because it is a WebMethod , the client can use any type of application they would like, provided it can call a Web service. For example, a customer could have a Web site that allows his product managers to place orders online. The Web application then would make a call to the Web service to send the order. The customer also could use a desktop application. The application could include an order entry screen. The application, like the Web application, would make a call to the Web service to submit the order. This type of flexibility enables you to build one Web service that can be used by many possible clients , all using different systems.

The PlaceOrder() WebMethod takes in a series of parameters that identify the customer placing the order, and an XML document, in the form of a DataSet , containing the products to order, and the quantities for each product. This WebMethod does two things. First, it adds a new entry in the Orders table in the Northwind database. Then, using the newly generated order number, the PlaceOrder() WebMethod adds a record to the Order Details table for each order entered in the DataSet . The WebMethod returns the new order number to the consumer.

For this WebMethod , you will need to add two stored procedures to the Northwind database. The first, AddNewOrder , takes in parameters for all the order information and adds a new order record. The AddNewOrder stored procedure is shown in Listing 14.6.

Listing 14.6 The AddNewOrder SQL-Stored Procedure
 CREATE PROCEDURE [AddNewOrder]   @CustomerID nchar (5), @EmployeeID int, @OrderDate datetime,   @RequiredDate datetime, @ShipVia int, @Freight money,   @ShipName nvarchar (40), @ShipAddress varchar (60),   @ShipCity varchar (15), @ShipRegion varchar (15),   @ShipPostalCode varchar (10), @ShipCountry varchar (15),   @OrderID  int output AS INSERT INTO Orders (CustomerID, EmployeeID, OrderDate, RequiredDate, ShipVia,   Freight, ShipName, ShipAddress, ShipCity, ShipRegion,   ShipPostalCode, ShipCountry) VALUES (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShipVia,   @Freight, @ShipName, @ShipAddress, @ShipCity, @ShipRegion,   @ShipPostalCode, @ShipCountry) SELECT @OrderID = @@Identity 

The second stored procedure, AddNewOrderDetail , takes in parameters for an order detail line, such as the order ID, product ID, and quantity ordered. The AddNewOrderDetail stored procedure is shown in Listing 14.7.

Listing 14.7 The AddNewOrderDetail SQL-Stored Procedure
 CREATE PROCEDURE [AddNewOrderDetail]   @OrderID int, @ProductID int, @Quantity smallint AS DECLARE @UnitPrice money SELECT @UnitPrice = UnitPrice FROM Products WHERE ProductID = @ProductID INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (@OrderID, @ProductID, @UnitPrice, @Quantity, 0) 

After the two stored procedures have been created, you can build the PlaceOrder() WebMethod .

The PlaceOrder() WebMethod will take in all the necessary parameters, add a record to the Orders table, and then insert rows into the Order Details table. Listing 14.8 shows the PlaceOrder() WebMethod .

Listing 14.8 The PlaceOrder() WebMethod
 [VB] 01: <WebMethod(Description:="Submit an order for " & _ 02:  "processing.  You will be given your order number as the " & _ 03:  "return value.<br>" & _ 04:  "The ShipVia values are:" & _ 05:  "<ol>" & _ 06:  "<li>Speedy Express</li>" & _ 07:  "<li>United Package</li>" & _ 08:  "<li>Federal Shipping</li>" & _ 09:  "</ol>")> _ 10  Public Function PlaceOrder(CustomerID As String, RequiredDate As DateTime, ShipVia As graphics/ccc.gif Integer, ShipName As String, ShipAddress As String, ShipCity As String, ShipRegion As graphics/ccc.gif String, ShipPostalCode As String, ShipCountry As String, CustomerOrder As DataSet) As graphics/ccc.gif Integer 11:  Dim OrderNumber As Integer = 0 12:  Dim dt As DateTime = DateTime.Today 13:  Dim Freight As Double = 0 14:  Dim orderDetailCommand As SqlDataAdapter 15:  Dim myConnection As SqlConnection = New SqlConnection(ConString) 16:  Dim myCommand As SqlCommand 17:  Dim myParameter As SqlParameter = Nothing 18: 19:  myCommand = New SqlCommand("AddNewOrder", myConnection) 20:  myCommand.CommandType = CommandType.StoredProcedure 21:  myParameter = myCommand.Parameters.Add(new SqlParameter("@CustomerID", graphics/ccc.gif SqlDbType.NChar, 5)) 22:  myParameter.Value = CustomerID 23:  myParameter = myCommand.Parameters.Add(new SqlParameter("@EmployeeID", graphics/ccc.gif SqlDbType.Int)) 24:  myParameter.Value = 1 25:  myParameter = myCommand.Parameters.Add(new SqlParameter("@OrderDate", graphics/ccc.gif SqlDbType.DateTime)) 26:  myParameter.Value = dt 27:  myParameter = myCommand.Parameters.Add(new SqlParameter("@RequiredDate", graphics/ccc.gif SqlDbType.DateTime)) 28:  myParameter.Value = RequiredDate 29:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipVia", SqlDbType.Int)) 30:  myParameter.Value = ShipVia 31:  myParameter = myCommand.Parameters.Add(new SqlParameter("@Freight", SqlDbType.Money)) 32:  myParameter.Value = Freight 33:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipName", graphics/ccc.gif SqlDbType.NVarChar, 40)) 34:  myParameter.Value = ShipName 35:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipAddress", graphics/ccc.gif SqlDbType.NVarChar, 60)) 36:  myParameter.Value = ShipAddress 37:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipCity", graphics/ccc.gif SqlDbType.NVarChar, 15)) 38:  myParameter.Value = ShipCity 39:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipRegion", graphics/ccc.gif SqlDbType.NVarChar, 15)) 40:  myParameter.Value = ShipRegion 41:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipPostalCode", graphics/ccc.gif SqlDbType.NVarChar, 10)) 42:  myParameter.Value = ShipPostalCode 43:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipCountry", graphics/ccc.gif SqlDbType.NVarChar, 15)) 44:  myParameter.Value = ShipCountry 45:  myParameter = myCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.Int)) 46:  myParameter.Direction = ParameterDirection.Output 47: 48:  myConnection.Open() 49:  myCommand.ExecuteNonQuery() 50:  OrderNumber = CInt(myCommand.Parameters.Item("@OrderID").Value) 51: 52:  orderDetailCommand = New SqlDataAdapter() 53: 54:  ' Build the insert Command 55:  orderDetailCommand.InsertCommand = New SqlCommand("AddNewOrderDetail", myConnection) 56:  orderDetailCommand.InsertCommand.CommandType = CommandType.StoredProcedure 57: 58:  myParameter = orderDetailCommand.InsertCommand.Parameters.Add(new graphics/ccc.gif SqlParameter("@OrderID", SqlDbType.Int)) 59:  myParameter.Value = OrderNumber 60: 61:  myParameter = orderDetailCommand.InsertCommand.Parameters.Add(new graphics/ccc.gif SqlParameter("@ProductID", SqlDbType.Int)) 62:  myParameter.SourceVersion = DataRowVersion.Current 63:  myParameter.SourceColumn = "ProductID" 64: 65:  myParameter = orderDetailCommand.InsertCommand.Parameters.Add(new graphics/ccc.gif SqlParameter("@Quantity", SqlDbType.SmallInt)) 66:  myParameter.SourceVersion = DataRowVersion.Current 67:  myParameter.SourceColumn = "Quantity" 68: 69:  orderDetailCommand.Update(CustomerOrder, "Order") 70: 71:  Return OrderNumber 72: End Function [C#] 01: [WebMethod(Description="Submit an order for " + 02:  "processing. You will be given your order number as " + 03:  "the return value.<br>" + 04:  "The ShipVia values are:" + 05:  "<ol>" + 06:  "<li>Speedy Express</li>" + 07:  "<li>United Package</li>" + 08:  "<li>Federal Shipping</li>" + 09:  "</ol>")] 10: public int PlaceOrder(string CustomerID, DateTime RequiredDate, int ShipVia, string graphics/ccc.gif ShipName, string ShipAddress, string ShipCity, string ShipRegion, string ShipPostalCode, graphics/ccc.gif string ShipCountry, DataSet CustomerOrder){ 11:  int OrderNumber = 0; 12:  DateTime dt = DateTime.Today; 13:  Double Freight = 0; 14:  SqlDataAdapter orderDetailCommand; 15:  SqlConnection myConnection = new SqlConnection(ConString); 16:  SqlCommand myCommand; 17:  SqlParameter myParameter = null; 18: 19:  myCommand = new SqlCommand("AddNewOrder", myConnection); 20:  myCommand.CommandType = CommandType.StoredProcedure; 21:  myParameter = myCommand.Parameters.Add(new SqlParameter("@CustomerID", graphics/ccc.gif SqlDbType.NChar, 5)); 22:  myParameter.Value = CustomerID; 23:  myParameter = myCommand.Parameters.Add(new SqlParameter("@EmployeeID", graphics/ccc.gif SqlDbType.Int)); 24:  myParameter.Value = 1; 25:  myParameter = myCommand.Parameters.Add(new SqlParameter("@OrderDate", graphics/ccc.gif SqlDbType.DateTime)); 26:  myParameter.Value = dt; 27:  myParameter = myCommand.Parameters.Add(new SqlParameter("@RequiredDate", graphics/ccc.gif SqlDbType.DateTime)); 28:  myParameter.Value = RequiredDate; 29:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipVia", SqlDbType.Int)); 30:  myParameter.Value = ShipVia; 31:  myParameter = myCommand.Parameters.Add(new SqlParameter("@Freight", graphics/ccc.gif SqlDbType.Money)); 32:  myParameter.Value = Freight; 33:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipName", graphics/ccc.gif SqlDbType.NVarChar, 40)); 34:  myParameter.Value = ShipName; 35:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipAddress", graphics/ccc.gif SqlDbType.NVarChar, 60)); 36:  myParameter.Value = ShipAddress; 37:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipCity", graphics/ccc.gif SqlDbType.NVarChar, 15)); 38:  myParameter.Value = ShipCity; 39:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipRegion", graphics/ccc.gif SqlDbType.NVarChar, 15)); 40:  myParameter.Value = ShipRegion; 41:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipPostalCode", graphics/ccc.gif SqlDbType.NVarChar, 10)); 42:  myParameter.Value = ShipPostalCode; 43:  myParameter = myCommand.Parameters.Add(new SqlParameter("@ShipCountry", graphics/ccc.gif SqlDbType.NVarChar, 15)); 44:  myParameter.Value = ShipCountry; 45:  myParameter = myCommand.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.Int)); 46:  myParameter.Direction = ParameterDirection.Output; 47: 48:  myConnection.Open(); 49:  myCommand.ExecuteNonQuery(); 50:  OrderNumber = (int)myCommand.Parameters["@OrderID"].Value; 51: 52:  orderDetailCommand = new SqlDataAdapter(); 53: 54:  // Build the insert Command 55:  orderDetailCommand.InsertCommand = new SqlCommand("AddNewOrderDetail", myConnection); 56:  orderDetailCommand.InsertCommand.CommandType = CommandType.StoredProcedure; 57: 58:  myParameter = orderDetailCommand.InsertCommand.Parameters.Add(new graphics/ccc.gif SqlParameter("@OrderID", SqlDbType.Int)); 59:  myParameter.Value = OrderNumber; 60: 61:  myParameter = orderDetailCommand.InsertCommand.Parameters.Add(new graphics/ccc.gif SqlParameter("@ProductID", SqlDbType.Int)); 62:  myParameter.SourceVersion = DataRowVersion.Current; 63:  myParameter.SourceColumn = "ProductID"; 64: 65:  myParameter = orderDetailCommand.InsertCommand.Parameters.Add(new graphics/ccc.gif SqlParameter("@Quantity", SqlDbType.SmallInt)); 66:  myParameter.SourceVersion = DataRowVersion.Current; 67:  myParameter.SourceColumn = "Quantity"; 68: 69:  orderDetailCommand.Update(CustomerOrder, "Order"); 70: 71:  return OrderNumber; 72: } 

For simplicity's sake, I have broken the PlaceOrder() WebMethod in Listing 14.8 into two parts. The first part, lines 1 through 51, adds a new order to the Orders table. The second part, lines 52 through 72, adds each order entry to the Order Details table and returns the order number to the consumer.

Note

For a real business application, this WebMethod should be built to use a transaction. You wouldn't want to commit the order entry unless the order detail entry was successful, and vice versa. Transactional processing is out of the scope of this chapter, and was omitted to simplify this example.


PlaceOrder() Part 1

Part 1 of the PlaceOrder() WebMethod works in the same fashion as many of the samples in previous chapters of this book. You have a series of input parameters that are passed to a stored procedure using a SqlCommand object. The SqlCommand has a parameters collection that includes a parameter for each input expected by the stored procedure. On lines 45 and 46, you define an output parameter to capture the OrderID that is returned from the stored procedure. The OrderID value is assigned to a variable that will be used in Part 2, and returned to the consumer.

You use the ExecuteNonQuery() method of the SqlCommand class to execute the stored procedure. The OrderID is captured by checking the SqlCOmmand.Parameters["@OrderID"].Value after the ExecuteNonQuery() method has been called.

PlaceOrder() Part 2

In Part 2 of the PlaceOrder() WebMethod , you use the SqlDataAdapter.Update() method to do a batch insert of all the order detail. In Chapter 11, "Editing and Filtering Data," you used the Update() method to update and insert records in an existing DataSet . Here you have a new DataSet being passed into the WebMethod . Because none of these records exist in the database (it is a new order after all), the Update() method will only be executing the SqlDataAdapter.InsertCommand .

On line 52, you instantiate a new SqlDataAdapter . There is no need to construct it with a SQL statement or SqlCommand because you will not be using the SelectCommand , only the InsertCommand . On line 54, you assign values to the InsertCommand property of the SqlData Adapter ; you assign a stored procedure name, AddNewOrderDetail , and a SqlConnection . On line 58, you create one of the three SqlParameters that the AddNewOrderDetail requires, the @OrderID parameter. This parameter uses the order number returned from the SqlCommand in Part 1. The OrderID is used to map the record in the Order Details table to the order record in the Order table.

On lines 61 through 67, you create the other two parameters. These two parameters are a bit different from the previous one; these parameters have to dynamically grab a value from the DataSet and pass it to the stored procedure. Each of these parameters has a line to set the SqlParameter.SourceVersion property. This property is used to determine which version of the DataTable column to pass into the parameter. In this case, because the DataSet / DataTable are new, and none of the records exist in the database, you want to use the DataRow Version.Current property. This property specifies that the current version of the data should be used because that is the only version that exists in the DataSet / DataTable . You follow that by setting the SqlParameter.SourceColumn property, which specifies which column of the DataTable will be used for this parameter.

Finally, you call the SqlDataAdapter.Update() method and pass in the DataSet and the name of the table to update. The Update() method executes the InsertCommand for any records that are in the DataTable , but not in the database. It also executes the UpdateCommand for any records that are in the DataTable or the database, that have changes because the DataTable was created. All the records in the DataTable are new, and not yet in the database; only the InsertCommand is called.

After the Update() has executed, you return the OrderID to the calling client (line 71).

Figure 14.7 shows the PlaceOrder() WebMethod description page. Because one of the input parameters is an XML file (a DataSet ), the Http-Get and Http-Post protocols cannot be used. Only the SOAP protocol information is rendered to the page.

Figure 14.7. The PlaceOrder() WebMethod page shows the WebMethod description.
graphics/14fig07.gif

Building the GetOrderDetail() WebMethod

The last WebMethod in the Northwind ProductServices Web service is the GetOrderDetail() WebMethod . This WebMethod is used to return a DataSet of the order detail for a given order number. A consumer can pass in an order number and get back a DataSet of items ordered and their quantities.

Like the GetProducts() WebMethod , the GetOrderDetail() WebMethod uses functionality used in the rest of the Northwind application, so it will instantiate the Products class and invoke the Products.GetOrderDetails() method. The Products.GetOrderDetails() method returns a SqlDataReader , so the WebMethod will need to dynamically create a DataTable and a DataSet to return to the calling client. This WebMethod is nearly identical to the GetProducts() WebMethod .

The code for the Products.GetOrderDetails() method is shown in Listing 14.9. This code should be added to the Products class, and the class must be recompiled.

Listing 14.9 The GetOrderDetails() Class
 [VB] 01: Public Function GetOrderDetails(OrderID As Integer) As SqlDataReader 02:  Dim orderDetailReader As SqlDataReader 03:  Dim myConnection As SqlConnection = new SqlConnection(ConString) 04:  Dim orderDetailCommand As SqlCommand = new SqlCommand("CustOrdersDetail", graphics/ccc.gif myConnection) 05:  Dim myParameter As SqlParameter = Nothing 06: 07:  orderDetailCommand.CommandType = CommandType.StoredProcedure 08:  myParameter = orderDetailCommand.Parameters.Add(new SqlParameter("@OrderID", graphics/ccc.gif SqlDbType.Int)) 09:  myParameter.Value = OrderID 10: 11:  orderDetailCommand.Connection.Open() 12:  orderDetailReader = orderDetailCommand.ExecuteReader() 13: 14:  Return orderDetailReader 15: End Function [C#] 01: public SqlDataReader GetOrderDetails(int OrderID){ 02:  SqlDataReader orderDetailReader; 03:  SqlConnection myConnection = new SqlConnection(ConString); 04:  SqlCommand orderDetailCommand = new SqlCommand("CustOrdersDetail", myConnection); 05:  SqlParameter myParameter = null; 06: 07:  orderDetailCommand.CommandType = CommandType.StoredProcedure; 08:  myParameter = orderDetailCommand.Parameters.Add(new SqlParameter("@OrderID", graphics/ccc.gif SqlDbType.Int)); 09:  myParameter.Value = OrderID; 10: 11:  orderDetailCommand.Connection.Open(); 12:  orderDetailReader = orderDetailCommand.ExecuteReader(); 13: 14:  return orderDetailReader; 15: } 

In Listing 14.9, you create a method that takes in one argument, the OrderID (line 1). You create a SqlDataReader on line 2, and execute a stored procedure, on line 12, that takes the OrderID as its only parameter. You do not need to add the CustOrderDetail -stored procedure, as it is included with the Northwind database when it is installed.

The Products.GetOrderDetails() method returns a SqlDataReader to the calling component. Listing 14.10 shows the code for the GetOrderDetail() WebMethod , which will invoke the Products.GetOrderDetails() method.

Listing 14.10 The GetOrderDetail() WebMethod
 [VB] 01: <WebMethod(Description:="Look up a customer order by OrderID.")> _ 02: Public Function GetOrderDetail(OrderID As Integer) As DataSet 03:  Dim OrderDataSet As New DataSet() 04:  Dim myProducts As Products = New Products() 05:  Dim orderDetailReader As SqlDataReader 06:  Dim orderDetailTable As DataTable = New DataTable() 07:  Dim newRow As DataRow 08:  Dim i As Integer = 0 09: 10:  orderDetailReader = myProducts.GetOrderDetails(OrderID) 11:  For i = 0 to orderDetailReader.FieldCount-1 12:   orderDetailTable.Columns.Add(New DataColumn(orderDetailReader.GetName(i))) 13:  Next 14: 15:  While orderDetailReader.Read() 16:   newRow = orderDetailTable.NewRow() 17:   For i = 0 to orderDetailReader.FieldCount-1 18:    newRow.Item(i) = orderDetailReader.GetValue(i) 19:   Next 20:   orderDetailTable.Rows.Add(newRow) 21:  End While 22: 23:  OrderDataSet.Tables.Add(orderDetailTable) 24:  OrderDataSet.Tables(0).TableName = "OrderDetail" 25: 26:  Return OrderDataSet 27: End Function [C#] 01: [WebMethod(Description="Look up a customer order by OrderID.")] 02: public DataSet GetOrderDetail(int OrderID){ 03:  DataSet OrderDataSet = new DataSet(); 04:  Products myProducts = new Products(); 05:  SqlDataReader orderDetailReader = null; 06:  DataTable orderDetailTable = new DataTable(); 07:  DataRow newRow; 08:  int i = 0; 09: 10:  orderDetailReader = myProducts.GetOrderDetails(OrderID); 11:  for(i = 0; i < orderDetailReader.FieldCount-1; i++){ 12:   orderDetailTable.Columns.Add(new DataColumn(orderDetailReader.GetName(i))); 13:  } 14: 15:  while(orderDetailReader.Read()){ 16:   newRow = orderDetailTable.NewRow(); 17:   for(i = 0; i < orderDetailReader.FieldCount-1; i++){ 18    newRow[i] = orderDetailReader.GetValue(i); 19:   } 20:   orderDetailTable.Rows.Add(newRow); 21:  } 22: 23:  OrderDataSet.Tables.Add(orderDetailTable); 24:  OrderDataSet.Tables[0].TableName = "OrderDetail"; 25: 26:  return OrderDataSet; 27: } 

In Listing 14.10, you create the GetOrderDetail() WebMethod on lines 1 “2. This WebMethod works similar to the GetProducts() WebMethod . On line 2 the OrderID is passed into the WebMethod . The WebMethod invokes the Products.GetOrderDetails() method on line 10, and returns a DataSet to the consumer.

Figure 14.8 shows the WebMethod description page for GetOrderDetail() .

Figure 14.8. The GetOrderDetail() WebMethod page shows the WebMethod description.
graphics/14fig08.gif
only for RuBoard


Programming Data-Driven Web Applications with ASP. NET
Programming Data-Driven Web Applications with ASP.NET
ISBN: 0672321068
EAN: 2147483647
Year: 2000
Pages: 170

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