How To Build an ASP.NET 4.0 Web Service and Consume It With Excel 2007-Part 1

This short series builds upon the previous How to Build a 2-Tier ASP.NET 4.0 Web Site (Parts 1, 2, 3). If you’re jumping in here you’ll need to adjust accordingly.

1. Right-click on the web project in Solution Explorer, choose Add New Item:
   
   
2. Ensure that Visual C# is chosen under Installed Templates on the left, select Web Service:
   
   
3. Replace the default boilerplate code show here:
   
   With the following code (begin replacement on line 10 above):

   [WebService(Namespace = "http://pragmatic/")]
   [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
   public class WebService : 
         System.Web.Services.WebService {
   [WebMethod]
   public DataSet GetCustomerByKey(int CustomerKey)
   {
     DataSet resultSet = new DataSet();
     SqlConnection conn = new SqlConnection(
     ConfigurationManager.ConnectionStrings["AdventureWorksDW2008R2ConnectionString"]
        .ConnectionString);
     using (SqlCommand cmd = new SqlCommand(
   @"SELECT [LastName], [FirstName], [MiddleName], [BirthDate], [EmailAddress], [Phone] 
   FROM [DimCustomer] 
   WHERE ([CustomerKey] = @CustomerKey)"))
     {
     cmd.Parameters.AddWithValue(
               "@CustomerKey", CustomerKey);
     conn.Open();
     cmd.Connection = conn;
     SqlDataAdapter dataAdapter = 
           new SqlDataAdapter(cmd);
     dataAdapter.Fill(resultSet, "Customer");
     }
     return resultSet;
   }

4. Here is the resultant web service code-behind file:
   
   
5. Explanation of above lines:
   10: Replace default namespace with meaningful one.
   12: Define a web method called GetCustomerByKey which accepts an integer assigned to the variable “CustomerKey” and returns an ADO.NET DataSet object.
   17: Declare an ADO.NET DataSet that will be populated with the results of the Sql query.
   19-20: Declare an ADO.NET SqlConnection used to connect to the Sql database *AND* initialize the connection string using the same value previously stored in the configuration file from Part 3. Note that your connection string value (shown here as “AdventureWorksDW2008R2ConnectionString” may be different. Open web.config file to obtain the actual value to use from the “name=” attribute:
   
   22-24: Construct a SqlCommand using the same SELECT statement previously used in Part 3. Note the “@CustomerKey” parameter token.
   26: Add an entry for the @CustomerKey parameter token and set its value to be the variable that is passed into the web method.
   27: Open the connection to the database.
   28: Associate the open connection to the SqlCommand object so the command will use it.
   29: Declare an ADO.NET DataAdapter and associate it to the SqlCommand object. This “magic” object will take care of a TON of work executing the Sql statement, taking the query results returned from the database and filling the collection of .NET DataSet objects (tables, rows, columns, values) with the result of the query.
   30: Simple little statement calling the Fill method of the DataAdapter, giving it the command to use and the dataset to be filled. This is the truly “magic” part of high-level object oriented programming.
   32: Return the populated dataset to the caller of this method.
   
6. To test the web service, simply press “F5” or click the green arrowhead on the toolbar next to “Debug”. When the web browser comes up, type the name of the web service file in the address bar and hit enter:
   
   Remember that your port number may be different than the :1483 shown above – that’s okay.
   
7. You should see the sample web service test page shown here:
   
   
8. Click the “GetCustomerByKey” hyperlink and you’ll see the test page for the web method:
   
   
9. Input a value, such as 11000 and click invoke to see the results of the web method:
   
   
10. You can re-publish the website as shown in the first few steps of Part 3 – no need to go through all the one-time configuration steps however. Verify the web service is published by opening a browser and typing in the correct address:
    

Next time we’ll use Excel to quickly test consuming the published web service with very little effort.