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

With a freshly built web service we can now have a bit of fun.

1. Create a new project utilizing the Visual Studio Tools for Office template for Excel 2007 Workbook:
   
   
2. Accept the default settings for Create a new document and click OK:
   
   Note: if you encounter errors while creating a new project it is most likely because the VSTO add-ins are not configured until first used.
   
   The quickest way to resolve the issue is to exit out of Visual Studio and use the right-click Run as administrator trick to launch with elevated permissions.
   
3. From the Solution Explorer, right-click on the References folder then choose Add Service Reference:
   
   
4. Type in the web address of the web service we just created:
   
   Hint: You may want to switch (Alt-Tab) over to the browser window where we tested the web service against IIS after deploying then copy the address from the address bar.
   
5. After pressing Go the deployed web service will be queried and the results displayed for confirmation:
   
   
6. With the Excel Workbook Designer displayed from within Visual Studio right-click anywhere on the worksheet and choose View Code:
   
   
7. Create a new method called DisplayCustomer containing the following code:

   public void DisplayCustomer(int customerKey)
   {
   // Call the Web service.
   using (var service = new
   Assignment3Service.WebServiceSoapClient())
   {
   var result = service.GetCustomerByKey(customerKey);
   if (result != null)
   {
   var items = result.Tables["Customer"]
   .Rows[0].ItemArray;
   var limit = items.GetLength(0);
   for (int i = 0; i < limit; i++)
   {
   var item = items[i];
   this.Cells[1, i + 1] = item;
   }
   }
   }
   }

   
   
8. Right-click on the Excel Workbook project and choose Add New Item:
   
   
9. Select Office under the Visual C# Installed Templates then choose Ribbon (Visual Designer) and click Add:
   
   
10. From the Toolbox drag an EditBox control then a Button control onto the designer surface:
    
    Resulting in the following:
    
    
11. Clean up the Labels to make the Ribbon more meaningful and useful:
    
    
    
12. The completed Ribbon should look similar to this:
    
    
13. Double-click on the Search button (text) on the Ribbon to jump to the event-handler section. Put the following code to call the add-in method DisplayCustomer previously created:
    

    private void btnSearch_Click(object sender, RibbonControlEventArgs e)
    {
    Globals.Sheet1.DisplayCustomer(int.Parse(CustomerKeyBox.Text));
    }

    
    
14. Notice as you start typing that Intellisense will provide filtered statement completion:
    
    
15. Here is the completed method:
    
    Pay attention to the name of the control (shown as “CustomerKeyBox” above) – this name must match the name you gave to the EditBox control immediately preceding this step.
    
16. Now press F5 to debug the solution. Doing so will launch Excel 2007 (or Excel 2010) and you’ll notice the Add-Ins tab of the Ribbon UI displays your custom fields:
    
    
17. Simply type in a customer number then click Search, the results should look something like this:
    

At this point we have used a custom add-in running on a client desktop to invoke a web service published and deployed in IIS which in turn connects to a SQL database to execute a query using the data supplied by the client (customer key value) and the results are returned and displayed in Excel.

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.

How To Build a 2-Tier ASP.NET 4.0 Web Site–Part 3

Now that IIS 7.5 is installed on Windows 7 let’s move on to deploying the website to IIS and testing it there.

1. From the Build menu choose Publish Web Site:
   
   
2. You can either accept the default location or change it to something else:
   
   Make note of the location chosen as you will need it shortly.
   
3. Now let’s configure the website in IIS 7. Launch IIS manager from Control Panel | Administrative Tools | Internet Information Services (IIS) Manager:
   
   
   Hint: IIS Manager is the configuration tool for IIS and you’ll be jumping into it often. You can use the Run dialog for faster access via the Windows + R then typing “inetmgr”:
   
   
4. Expand the tree control on the left, right-click on Default Web Site then right click and choose Add Application:
   
5. Type in an Alias name that will be used as the website name when browsing, Select the ASP.NET v4.0 Application pool and choose the same Physical path where you just published the website to previously. You should click the Test Settings button to verify that Authentication and Authorization are configured correctly:
   
   
6. If you received the above Authorization error it means that IIS does not have permission to access the folder where you published the website to. If this occurs, click Close then OK to complete the creation of the application. Back in the tree view control right-click on Edit Permissions to grant the IIS web server permission to the publish folder:
   
7. On the Security tab choose Edit, then Add, then Advanced, and finally Find Now:
   
8. From the list of users and groups use Control + click to select the follow three:
   
   • IIS_IUSRS
     
   • IUSR
     
   • NETWORK SERVICE
   Now you can click OK through until the dialogs are dismissed, right-click on the Website in the tree control again and choose Edit Application where you can Test Settings again.
9. When testing from within Visual Studio we are running under the permissions of ourselves – the account we signed into Windows with. When browsing against IIS, it is IIS that is connecting to the database. Since the IIS web server will be acting as a proxy for the user when accessing the database, you must grant the IIS user permission to the database. Using SQL Server Management Studio, execute the following T-SQL statements:
   

   CREATE LOGIN [IIS APPPOOL\ASP.NET v4.0] 
     FROM WINDOWS 
     WITH DEFAULT_DATABASE=[master], 
     DEFAULT_LANGUAGE=[us_english]
   GO
   
   CREATE USER [AdventureWorksUser] 
     FOR LOGIN [IIS APPPOOL\ASP.NET v4.0]
   GO
   
   EXEC sp_addrolemember 'db_datareader', 'AdventureWorksUser'
   GO
   

   
   
10. Finally right-click on the website again, choose Browse:
    
11. Sit back and enjoy the fruits of your hard labor:
    

While testing the site in IIS is not absolutely required, it is helpful to go through the publishing process to help shake out potential problems such as the fact that IIS runs under a service account and not the you (the logged in user) the way Visual Studio’s build in web server does.

TD Bank website team is sloppy

Hmmm…Saturday before Christmas here in the U.S. of A., major banking site, error page like this:

I’ve been developing in ASP.NET since 2002 so I think I can safely say that somebody screwed up and they’ve got some sloppy practices in place!

Looks like ‘Banknorthct’ is a valid #bankid# token in case you’re trying to discover information about their site. Also, they like to use D:\websites to store their site files in so hackers shouldn’t have to go hunting for that configuration detail. Finally, they’re running .NET 2.0 which means it’s a lot easier for folks to attack because they can immediately try all known exploits.

Do I seem too harsh? Well, two simple steps would’ve prevented this:

1. Turn ‘RemoteOnly’ back on in your web.config – someone explicitly changed it from the default value.
2. Code a custom error page possibly with a redirect so the customer doesn’t see your dirty underwear!!

Tell me again how much banks got for a bailout in ‘09 and ‘08 and how much profit are they raking in now?

Install IIS 7.5 on Windows 7

Here are the quick steps for installing IIS 7.5 on Windows 7 Professional/Enterprise/Ultimate. While Microsoft’s TechNet does have an entry it’s not the most informative or person-friendly (person being different than a techie geek). This post is a companion to the How To series on building a 2-tier ASP.NET 4.0 web site (part 1, part 2, part 3).

1. Navigate to Control Panel | Programs | Turn Windows features on or off:
   
   
   
2. Check the box next to Internet Information Services and then expand the sub-tree by clicking the (+) plus sign next to it:
   
3. Continue expanding the sub-tree until you open Application Development Features then check the box for ASP.NET:
   
4. Further down, expand Security then enable Basic Authentication and Windows Authentication:
   
   
   Clicking OK to close the dialog out will cause the computer to install and pre-configure IIS, taking several minutes to complete.
5. After IIS is installed you’ll find a new set of folders has been created:
   
   C:\inetpub\wwwroot is the default “root” folder for IIS (a.k.a. world wide web server) so this path is the default location to place websites. The key fact here is that if you publish into this folder you won’t have to worry about folder (NTFS) permissions. Publishing to the default personal user location that Visual Studio selects – within your “My Documents” folder means you need to take an extra step of granting IIS permission to access those folders in your documents area. This screenshot from IIS Manager shows the default web site along with the path matching the path shown above:
   
6. Next we’ll configure Application Pools that will host and execute the ASP.NET applications. Launch IIS Manager from Control Panel | Administrative Tools | Internet Information Services (IIS) Manager:
   
   
   Hint: IIS Manager is the configuration tool for IIS and you’ll be jumping into it often. You can use the Run dialog for faster access via the Windows + R then typing “inetmgr”:
   
7. When you select the Application Pools entry in the tree control you should see the following:
   
   However, a standard configuration of IIS 7.x on Windows Server 2008 R2 looks like this:
   
   Notice that the server installation contains two additional application pools configured for ASP.NET v4.0.
8. Let’s configure our personal machine (desktop/laptop) in the same manner to enable more realistic local testing. Right-click on the Application Pools entry in the tree control and choose Add Application Pool:
   
   After adding the integrated mode, repeat the steps to add a classic mode pipeline:
   
9. Since IIS was not installed and ASP.NET was already present in Windows 7, we must manually register the “handler” for ASP.NET pages and content so IIS can correctly map web requests. To do so open a command prompt running as Administrator – necessary because this type of registration requires elevated permissions. To open a command prompt as Administrator click the Start button, type in “command” and wait a moment until it shows the Command Prompt under the Programs section then right-click and select Run as administrator as shown here:
   
10. With the command prompt open execute two commands by typing in each of the following and pressing Enter after each command:
    
    

    cd  %windir%\Microsoft.NET\Framework\v4.0.*
    aspnet_regiis.exe –i

    Note that the last four digits of the build number (30319 shown above) are less important. You may have a different version depending on the service pack level and patches installed in your computer.

That’s it – you’ve installed IIS, enabled common security features and created application pools designed to host ASP.NET 4.0 websites.