Assignment 5 -- Working with SQL Server Express and Data Listing Controls

DataAccess Class: This week's exercises all work with database data. We will use a custom class named DataAccess that allows us to remove all the messy details of data access (connection objects, DataAdapers and Command objects) from our code and push them down into a class. The first step in this exercise is to add the DataAcess class to your application. Steps:

Use VWD to create a new class. Right click on the application root folder (c:\...) and select "Add new item..."  Click "class" and name it DataAccess.cs.

Cut & paste the code from DataAccess.cs.txt into this file and save the file. The dataAccess class is now accessible from throughout your application just as if it were part of the .NET class library.

web.config: The new DataAccess class looks in the web.config file for a database connection string. The web.config file may be in the same folder as your .aspx page or in any of its parent folders up to the root folder. The root folder is a convenient place to place the web.config file because connection strings defined at the top level are accessible from all the child folders. Copy the connection information from web.config.txt (view source) and put it into your web.config file. Note: The web.config files generated by Visual Web Developer 2008 will not work with .NET 2.0 (the version on Yorktown). I suggest using the simpler web.config text used in the link above.

Exercises:

1. GridView.aspx - In this exercise you will create a small database, use the DataAccess class to populate a dataTable and bind the dataTable to a GridView control. Steps:
   1. Create & populate database
      1. Create a new database by right clicking the root folder in VWD. Select "SQL Database" and name it "A05Customers"
      2. Open the database and add a new table "tblCustomers" by right clicking on the "Tables" folder and selecting "Add New Table." Add three fields with the following attributes:
         
         

         Column Name	Data Type	Allow Nulls
         CustID*	int	no
         NameF	varchar(50)	yes
         NameL	varchar(50)	no

         
         *CustID is the primary key and an auto-number field. To define it as primary key right click on the field and select "primary key." In the column properties for CustID select "Is Identity" "yes."
      3. Save the table as "tblCustomers" and close the table designer.
      4. Add some data to the table. In Server Explorer click on Tables to see your new table. Right-click on tblCustomer and select "Show Table Data."
      5. Add at least three rows of data.
      6. The database is now ready to be used.
   2. Web.Config
      1. Create a connection string named "A05CustomerData" in your web.config file pointing to your new database. (see instructions above).
   3. Populating a GridView Control
      1. Create a new web form named "GridView.aspx"
      2. Drag a GridView control from the tool box. Change its ID to "gvCustomers"
      3. Populate the GridView from the database with the following code:
         1.  void page_load()
            {
               //Initialize dataAccess class
               DataAccess myDA = new DataAccess("A05CustomerData");
            
               //Populate dataTable and bind to GridView Control
               string strSQL = "Select * from tblCustomers";
            
               gvCustomers.DataSource = myDA.FillDataTable(strSQL);
               gvCustomers.DataBind();
            }
   4. Formatting
      1. Add some formatting to make it pretty.
   5. Uploading to Yorktown
      1. All App_  folders must be located in the root directory of your Aspen account. This is the directory that you start in when you log in to Aspen. Create the folders App_Data for your database and App_Code for your C# class code.
         
         You can test that your root folder on Yorktown is configured as an Application by running the following code in a page_load subroutine. The .aspx page can be in any folder of your account.
         
            Response.Write("Application root: " + Request.ApplicationPath.ToString());
         
         This code should output something line: "Application root: /Smithj88" where Smithj88 is your Aspen root directory. If it doesn't please contact Professor Sandvig.
         
      2. When using Aspen to upload database files (.mdf) to the server you may get the error "File is locked or deleted."  Any of the following will usually fix this problem:
         1. Detach the database in Visual Studio right clicking on the database and selecting "detach."
         2. Close Visual Studio so Windows unlocks the file.
         3. Delete the .LDF transaction log file that has the same name as the database.
             
            
2. UserControl/default.aspx - This exercise is based on the User Control exercise in assignment 4. Copy UserControl.aspx, SearchBrowse.aspx and SearchMenu.ascx from A04 into your A05 folder. Test them to make sure they still work.
   
   In this exercise you will replace the static music styles menu used in A04 with a dynamic menu queried from the music store database. Steps:
   1. Replace the static links in SearchMenu.ascx with a DataList control.
   2. The DataList requires an ItemTemplate to define the layout. The sample uses regular HTML anchor tags. The syntax is:
      
      <ItemTemplate>
        <a class="menulink" href="SearchBrowse.aspx?style=<%# Eval("intStyleID") %>">
           <%# Eval("strStyleName") %>
        </a>
        <br />
      </ItemTemplate>

   3. Add the MusicStore database to your application as follows:

      1. Download MusicStoreSample.zip, unzip it and place the .mdf file in the App_Data folder located in the applications root folder.
      2. View the database in VWD by double clicking on the database icon. This will change the view from "Solution Explorer" to "Server Explorer." Viewing the database should display its tables, etc. Right clicking on the database folder displays options for creating queries, viewing properties, viewing table data, etc.
   4. web.config - add a new connection string to you web.config file pointing to the MusicStore database.
   5. Populating the DataList: Populate the datalist using code similar to the code shown in Exercise 1.
   6. SQL: VWD includes a query builder that will write SQL select statements.
      1. Right click on the Tables folder and select "New Query" Create a query that retrieves all the style names & style IDs and sorts by name.
      2. tblStyles includes styles for which the database has no items. List only styles that have associated ASINs by joining tblStyles and tblStyleASIN.
      3. Use the "distinct" keyword in the SQL statement to eliminate duplicates. Test the query in VWD by clicking the ! icon.
   7. Paste the SQL statement into your .ascx page and it should work perfectly.
       
      
3. SearchBrowse.aspx - This page retrieves the search or browse query from the querystring and displays search results in a DataList. The query is concatenated into the appropriate SQL statement (search or browse), the DataAccess class is used to retrieve the data, and the data is bound to a DataList control.

Instructions for retrieving the data programmatically and binding to a DataList:

1. The DataList control is a good control for displaying the items retrieved from the database. The DataList used in the style menu from the previous exercise would be a good template.
2. Retrieve the parameters search & browse from the querystring and assign them to local variables.
3. Request.QueryString["..."] returns a null if the parameter is not found. Check if strSearch retrieved a string using the syntax: if (strSearch != null) ...
4. The sql statement for a search is:
   
   strSQL = "SELECT distinct tblStyleASIN.strASIN, tblDescription.strTitle, tblDescription.strArtist, tblDescription.dblPrice, tblDescription.strReview, dbo.tblDescription.strImageDir " +
   "FROM tblStyleASIN INNER JOIN tblDescription ON tblStyleASIN.strASIN = tblDescription.strASIN " +
   " WHERE (tblDescription.strArtist LIKE '%" + strSearch + "%') or (tblDescription.strTitle LIKE '%" + strSearch + "%') or (tblDescription.strReview LIKE '%" + strSearch + "%') " +
   " ORDER BY tblDescription.strTitle" ;
   
   
5. The SQL for browse retrieves the same fields but the "WHERE " checks only in tblStyleASIN for matching StyleIDs.
6. It is good practice to list the number of items found in a search. The dataAccess class has a count property for the number of records retrieved. Display a count of the number of items retrieved by the query.
   
7. Use the DataAccess class to retrieve the data and bind it to the DataList.
8. DataList ItemTemplate:
   1. Images: The URL for all the images is:
      http://yorktown.cbe.wwu.edu/sandvig/MusicStore/MusicImages/AAAAA.01.MZZZZZZZ.jpg
      where "AAAAA" is the ASIN.
   2. The price should be formatted as currency. This can be done using either of the following syntax options:
          <%# Eval("dblPrice","{0:c}") %>
          <%# String.Format("{0:c}", Eval("dblPrice"))%>
       
4. ListCustomers.aspx and EditCustomers.aspx - The editing features of the DataList and GridView have a good "wow" factor but not useful in most situations. This exercise removes the insert & edit features from the datalist and puts them into a separate page for adding and editing customer information.

   Steps:

1. Use VWD to create a new SQL Server database named "CustomerData" in your App_Data folder. Add a table named tblCustomers and the fields CustID (identify field & primary key), NameF and NameL. Add a couple of records of data to the database for testing.
2. Add a new connection string to your web config file that points to the "CustomerData" database.
3. ListCustomers.aspx:
   1. Use DataListEdit.aspx (source) as a template for both ListCustomers.aspx and EditCustomers.aspx. Before you modify ListCustomers.aspx it would be a good idea to get it working to make sure your database connection is OK.
   2. Remove unneeded editing subroutines (Insert, Update, Edit) from ListCustomers.aspx. In the DataList's ItemTemplate change the editing LinkButton to a Hyperlink control. Replace the "commandName" property with:
      NavigateUrl=' <%# "EditCustomers.aspx?CustID=" & Eval("CustID") %>'
   3. Add a new link "Add New Customer" above the DataList that points to EditCustomers.aspx. Test & debug the page.
4. EditCustomers.aspx:
   1. This page handles four scenarios:
      1. New customer: page initially loading
      2. New customer: button click
      3. Edit customer: page initially loading
      4. Edit customer: button click
   2. When the page loads it checks the querystring for a CustID. This determines if it is editing an exisitng customer or adding a new one. If a customer ID is found in the querystring it looks up the customer's data and populates the textboxes. Create a variable named strCustID. This variable needs to have page level scope (initialized outside any methods) so that it can be accessed from the button click handler.
   3. When the page loads it checks to see if the querystring contains a CustID. Do this in the Page_Load() method with:
      
      if (Request.QueryString["CustID"] != null) strCustID = Request.QueryString["CustID"];
1. The next step is to create a method that populates the textboxes with data from the database. Copy the "FillDataList(...)" method from DataListEdit.aspx and rename it "FillTextBoxes()."  Remove the method's parameters list since we are not passing any parameters.  
2. Use the DataAccess class to retrieve the customer data and populate a dataTable. The syntax to copy the data from the dataTable to a textbox is:
         tbNameF.Text = Convert.ToString(dt.Rows[0]["NameF"]);
3. The button should read "Add" or "Edit" depending upon the presence of a CustID.
4. The handler for the button_click event uses an if statement to determine which SQL statement to use (Insert or Update). Syntax:
       if (strCustID == null) ...
5. It then uses the ExecuteNonQuery method of DataAccess to execute the SQL statement.
6. After the SQL statement is executed redirect back to ListCustomers.aspx (Tip: error messages may be lost when the page redirects. Add the redirect after debugging the code).