Exercise 2: Reading data from a SQL Server database

In this exercise you will learn how to properly handle the results of a Transact-SQL SELECT statement when one of the source columns allows database null values. We will revisit the proper handling of database null values for other types of operations in subsequent exercises.

Task 1 – Creating a Data Access Class

1. Start Microsoft Visual Web Developer 2010 Express Edition or Visual Studio 2010 from Start | All Programs.
2. In the File menu, choose Open | Project/Solution. In the Open Project dialog, browse to Ex2-ReadingData\Begin in the Source folder of this lab, select Begin.sln and click Open.
3. Create a Product class that contains the properties that you are going to show in the page. To do this, right-click the ListProducts project in Solution Explorer, point to Add, and then click Class. Type Product.cs in the Name text box and click Add.
4. Add the public access modifier to the class if necessary.

5. Add the following properties to the Product class.

   (Code Snippet – First SQL ASP.NET Application Lab – Ex02 – Product class properties)

   C#

   namespace ListProducts

   FakePre-a258e3ada4954cff8870a74e1ba6fb67-4c96e88dc3304a27af460c03365ca269FakePre-a20eb9db0d4e42989a1953ff4ec4e770-5cc0777917d346629413a292b0164811FakePre-4f711058d83348a282d239747d85f334-ddd8c9704f5b4540b3d8bcfa46bb8b45 public int ProductId { get; set; } public string Name { get; set; } public decimal ListPrice { get; set; } public string Size { get; set; } public string OriginalName { get; set; } public decimal OriginalListPrice { get; set; } public string OriginalSize { get; set; }FakePre-6acd015c9a9a4e4789e17b3197489bc9-2f7ddd7c0bbf4adca860582f2902b85fFakePre-eace2d72438844f28d4f834cfbe151b4-edc9e96354104e6087d084a63d09abb4FakePre-a68f31a0a4114698b750ea24d1b64e82-325b980ec9264984a7d5fb46507d5b95FakePre-bb12d1979d9c4f3db15f38bb90cabd36-32c7a7bd2aa04d84a699a4616e7128fdFakePre-42a39c63863546ecbc76d9902abd4f2e-a97f6bfc9f874dae8f34ae4a82b9a621FakePre-a770482791d9478db71ed903cd73879e-6bae3378177f49c78ddd2397d587e94fFakePre-8b11fcd3e8db41d6bb339641bb8fbb8a-f0a015587ae1453ca1d81bc2bee72ac6FakePre-61e41fef74234eec8674389f0442b706-3001e9a502ec4b328616897e6a18f908
   Note:

   Best Practice: When designing your application, create a class to work with your data as a business object in ASP.NET. This will make it easier to leverage powerful web server controls that support data binding. Classes will also make it easier to work with your data in code because of Visual Studio features like IntelliSense and AutoComplete.

   While there are many columns in the Products table, we are only going to work with four of them in this hands-on lab (ProductId, Name, ListPrice and Size). Notice that the class contains one property for the primary key (ProductId) and two properties for each of the data values.

   Note:

   Best Practice: When designing a class to be used for data binding, consider adding two properties for each data field that can be updated. One property is used to store the current value in the application, and the other property is used to store the value that was originally read from the database. This will make it easier to implement optimistic concurrency, which we will examine in an exercise later in this hands-on lab.

   If you examine the structure of the Products table, you will find that of the four columns we intend to use, only the Size column allows database null values. Since Size is a string, and strings are a nullable type, we can use a null reference for the Product.Size property to imply a database null value in the source table.

   Note:

   Best Practice: When designing a class to be used for data binding, use nullable types for properties whose bound column allows database null values. This will allow you to implement logic in your application that implies that a null reference is equivalent to a database null value. Remember that primitive value types such as System.Int32 and System.Boolean must be explicitly declared as nullable since value types do not support null references by default.

6. Create a Data Access class that will perform common operations against the database. To do this, right-click the ListProducts project in Solution Explorer, point to Add, and then click Class. Type ProductsDataAccess.cs in the Name text box and click Add.

7. Add the following using statements at the top of the file.

   C#

   using System.Configuration; using System.Data.SqlClient;

8. Modify the class signature to make it static and public.

   C#

   public static class ProductsDataAccess { }

9. Add a public static method for retrieving all products. To do this, add the following code (shown in bold) to the ProductsDataAccess class. Add code to open the connection and execute a query to return all the products.

   (Code Snippet – First SQL ASP.NET Application Lab – Ex02 – RetrieveAllProducts method)

   C#

   public static ICollection<Product> RetrieveAllProducts(int CurrentOffset, int OffsetIncrement) { List<Product> products = new List<Product>(); // When using { ... } statement ends, it closes the connection // automatically. using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString)) { connection.Open(); var commandText = "SELECT ProductID, Name, ListPrice, Size FROM Production.Product ORDER BY ProductID DESC OFFSET @current_offset ROWS FETCH NEXT @offset_increment ROWS ONLY"; var command = new SqlCommand(commandText, connection); command.Parameters.AddWithValue("@current_offset", CurrentOffset); command.Parameters.AddWithValue("@offset_increment", OffsetIncrement); // When using { ... } statement ends, it closes the // reader automatically. using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { products.Add(new Product { ProductId = reader.GetInt32(0), Name = reader.GetString(1), ListPrice = reader.GetDecimal(2), Size = !reader.IsDBNull(3) ? reader.GetString(3) : null, OriginalName = reader.GetString(1), OriginalListPrice = reader.GetDecimal(2), OriginalSize = !reader.IsDBNull(3) ? reader.GetString(3) : null }); } } } return products; }

   FakePre-ce13c58cbf1f4ed4baa20f1162d2598b-b1b41d1bb0d74fbf8ed339aa4d6876e9FakePre-635ffb3f81e6480c88e7f86ee1912ba6-9738b22684894973ac4fb326a1d51f2dFakePre-ca261da02f38480698ffb2bdb53fc242-b06b512c64fd439b97f43d6d7af7a8bcFakePre-9c75926f27f24936a7d365942ef26e50-dca2e14208d3488fbc95ae4711281ee8
   

   Let us look at this code in a bit more detail. The SELECT command is executed using the ExecuteReader method and if it succeeds a SqlDataReader object is returned to the application which can be used to read the results a row at a time. The results are only those specified in the requested page, with the OFFSET/FETCH syntax in the SELECT command, via the CurrentOffset and OffsetIncrement parameters received by the method. CurrentOffset indicates how many rows should the SELECT skip before fetching the amount of records the OffsetIncrement states. While the query is being executed, SQL Server obtains a shared lock on the data. Shared locks permit other users to read the same data, but prevent users from changing the data while the lock is held. In this case, the lock is released as soon as query execution completes because we are not using a transaction. We will talk more about transactions and locks in a future exercise.

   Next, the result set for the current page is read one row at a time in a loop for each page, and a new instance of the Product class is created and added to a collection after each iteration. The properties of each new Product instance are set using the appropriate column in the SqlDataReader object.

   After the last row is read, the SqlDataReader and SqlConnection objects are automatically closed because of the using syntax and a collection of products is returned to the caller. At this point the application has a copy of the results stored in memory. This means that other users can now change the source data. It also means that the data stored in the collection may become out of date. We will discuss this more in an upcoming exercise.

10. Press CTRL+SHIFT+B to build the solution.

Task 2 – Binding the Data to the UI

1. Open the Default.aspx page in Design view. To do this, double-click the file in the Solution Explorer, and then click Design at the bottom-left corner of the designer.
2. From the Toolbox within the Data group, drag a ListView control into the designer surface.
3. Go to the Source view, by clicking Source. In the <asp:ListView> tag, change the ID property from ListView1 to ProductsListView.

4. Go to the Design view, by clicking Design. Click the Choose Data Source drop down list, and then click New Data Source.

   Note:

   If you do not find the Chose Data Source dropdown list beside the ListView control, right-click the control in the designer surface and click Show Smart Tag in the menu.
   Figure 1

   Choosing the Data Source for the ListView control

5. In the Data Source Configuration Wizard, select the Object data source type, enter ProductsDataSource as the Data Source ID, and then click OK.

   Figure 2

   Choosing the data source type

6. Choose the ListProducts.ProductsDataAccess as the business object, and then click Next.

   Figure 3

   The Configure Data Source Wizard

7. In the SELECT tab, choose the RetrieveAllProducts method and click Next.

   Figure 4

   Choosing the select method

8. Select the CurrentOffset parameter from the list, and write a DefaultValue of 0 for it.

   Figure 8

   Setting CurrentOffset default value parameters

9. Select the OffsetIncrement parameter from the list, and write a DefaultValue of 10 for it. Then click Finish.

   Figure 9

   Setting OffsetIncrement default value parameters

10. Open the Default.aspx page in Source view. To do this, click Source at the bottom-left corner of the designer.

11. Paste the following code in bold between the <asp:ListView> tags. This code adds an ItemTemplate for the rows returned from the Data Source to specify how they will be displayed.

    HTML

    <asp:ListView ID="ProductsListView" runat="server" DataSourceID="ProductsDataSource">

    <LayoutTemplate> <asp:PlaceHolder ID="itemPlaceholder" runat="server" /> </LayoutTemplate> <ItemTemplate> <div> <h3> <b>Name:&nbsp;</b><%# Eval("Name") %></h3> <p> <b>Product ID:&nbsp;</b><%# Eval("ProductId")%><br /> <b>List price:&nbsp;</b><%# Eval("ListPrice") %><br /> <b>Size:&nbsp;</b><%# Eval("Size") %> </p> </div> </ItemTemplate>FakePre-850486214d8240febf144890c04625d5-ce58217bbd8f42ccaa5cf6498e05c10c

    The ListView web server control allows you to create templates to define the layout of its content. Some of the templates are: AlternatingItemTemplate, EditItemTemplate, InsertItemTemplate, ItemTemplate and SelectedItemTemplate. In this case we are defining the ItemTemplate layout, which controls the default way an instance of the underlying business object will be displayed in the list.

12. Paste the following code in bold above the <asp:ListView> tag. This code adds two buttons, Previous and Next, which will allow pagination for the query.

    HTML

    <asp:Button ID="PrevBtn" runat="server" Text="Previous" onclick="PrevBtn_Click" /> <asp:Button ID="NextBtn" runat="server" Text="Next" onclick="NextBtn_Click" /> <br />

13. Open the Default.aspx page in Design view. To do this, click Design at the bottom-left corner of the designer.
14. Double-click the Previous button you just added to open the code-behind window.

15. Add the following code in bold in the PrevBtn_Click method just created.(Code Snippet – First SQL ASP.NET Application Lab – Ex02 – PrevBtn_Click method)

    C#

    protected void PrevBtn_Click(object sender, EventArgs e)

    FakePre-3cf5718134e34fc6a44371a047ead251-e5f110d5691543ffadb83f1e15f9b67dvar offset = int.Parse(ViewState["CurrentOffset"].ToString()); var increment = int.Parse(ViewState["OffsetIncrement"].ToString()); if (offset > 0) { offset = offset - increment; if (offset < 0) { offset = 0; } else { ViewState["CurrentOffset"] = offset; ProductsDataSource.Select(); } } ShowSQLQueryMessage();FakePre-d28776555be9486291f39353e884094e-c447aa3a979348f89b95c7d790978f7bFakePre-a0a95258f976412c91ad14362eec7822-b095c00d90de4911aaea9e76553adef5FakePre-646fbb54502040528c5e1344f82c2abf-cb70d76d973a4a70a2c18c3ae940de3a

16. Go back to the Default.aspx page in Design view. To do this, double-click the file in the Solution Explorer, and then click Design at the bottom-left corner of the designer.
17. Double-click the Next button.

18. Add the following code in bold in the NextBtn_Click method.(Code Snippet – First SQL ASP. Application Lab – Ex02 – NextBtn_Click method)

    C#

    protected void NextBtn_Click(object sender, EventArgs e)

    FakePre-1a1d57123cf546f3be80d159ee47a08b-6e84ef54ef414e938054afd79fc611b0var offset = int.Parse(ViewState["CurrentOffset"].ToString()); var increment = int.Parse(ViewState["OffsetIncrement"].ToString()); ViewState["CurrentOffset"] = offset + increment; ProductsDataSource.Select(); ShowSQLQueryMessage();FakePre-04f2631d26a54b4585d6263d28b04bcb-c98d5dfddac94d8099e826f699149e43FakePre-ac91c17439c4463fad32f0f2d1c37605-57573b4bd04944fe8161802d0886552c

19. Paste the following code below the NextBtn_Click method.(Code Snippet – First SQL ASP. Application Lab – Ex02 – DataSourceSelectingSelected method)

    C#

    protected void ShowSQLQueryMessage() { string query = String.Format("Executed SQL Query: SELECT ProductID, Name, ListPrice, Size FROM Production.Product ORDER BY ProductID DESC OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", ViewState["CurrentOffset"], ViewState["OffsetIncrement"]); Debug.WriteLine(query); } protected void ProductsDataSource_Selecting(object sender, System.Web.UI.WebControls.ObjectDataSourceSelectingEventArgs e) { e.InputParameters["CurrentOffset"] = ViewState["CurrentOffset"]; e.InputParameters["OffsetIncrement"] = ViewState["OffsetIncrement"]; } protected void ProductsDataSource_Selected(object sender, System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs e) { var productList = (List<Product>) e.ReturnValue; if (productList.Count > 0) { ProductsListView.DataBind(); } else { var offset = int.Parse(ViewState["CurrentOffset"].ToString()); var increment = int.Parse(ViewState["OffsetIncrement"].ToString()); offset = offset - increment; if (offset < 0) offset = 0; ViewState["CurrentOffset"] = offset; } }

    FakePre-96a3bd29474c45d0b9aa4b6c98adf891-33c12d64175d44ddac1dddc1e0b0e9cbFakePre-478d086fad764378af36a1c5a263a68f-3af8e74678e6487486071cf5e1368c01FakePre-b18c89be5a8e453e9a739c7dd5761f11-508985fdcda54d50b2e682e7022b44c8FakePre-dde89f21ec25436cb13a0e3d6e5d7b4b-f72dd54826e24456b6a020009a64a2fe
    

20. Add the following code in bold in the Page_Load method.(Code Snippet – First SQL ASP. Application Lab – Ex02 – PageLoad method)

    C#

    protected void Page_Load(object sender, EventArgs e)

    FakePre-9683f345195543adad047cc7c379f2e3-b7729ba1bab6450a8161ff316f9b2231if (ViewState["CurrentOffset"] == null) ViewState["CurrentOffset"] = ProductsDataSource.SelectParameters["CurrentOffset"].DefaultValue; if (ViewState["OffsetIncrement"] == null) ViewState["OffsetIncrement"] = ProductsDataSource.SelectParameters["OffsetIncrement"].DefaultValue;FakePre-82c3c9f8e74c4b73af013670f5774551-6f0e3b15369c426dbe04b5c6df71df80FakePre-0aa6d8ff776e4754a2a99bdb8729b601-45f5ca2e5f9940ab82109a9cefb14ffe

21. Add the following using statements in bold at the top of the file along with the rest that are already there. We will need the System.Collections.Generic to handle the list returned by the RetrieveAllProducts method, and also System.Diagnostics to output the SQL query to the console.

    C#

    using System;

    FakePre-6857dc0a539847ecb0631909e1aaa931-ba753af0628f45e3bc93c0e4304b66b8FakePre-0a9295faed754c79b699b683ad31a174-4fc85ec540ab4a32b6f220256163676ausing System.Collections.Generic; using System.Diagnostics;

22. Select File | Close and click Yes when asked about saving changes.
23. Go back to the Default.aspx page in Design view. To do this, double-click the file in the Solution Explorer, and then click Design at the bottom-left corner of the designer.
24. Click the ObjectDataSource control in the designer surface to select it, and press F4 to bring up the properties for the object.
25. Click the events icon () on the Properties pane to see the available event of the control.

26. Click on the drop-down list at the right of the Selecting event, and select the ProductsDataSource_Selecting method.

    Figure 10

    Setting the Selecting event to call the ProductsDataSource_Selecting method

27. Then click on the drop-down list at the right of the Selected event, and select the ProductsDataSource_Selected method.

    Figure 11

    Setting the Selected event to call the ProductsDataSource_Selected method

28. Save and close the files.

Task 3 – Adding a Handler to Display the Product Images

Lots of web applications need to display images that are stored in a database. ASP.NET supports the img tag for displaying images that are stored in the file system using a URL specified in the src attribute. In this task you will add code that replaces the URL with a call to a custom method that retrieves the image from the database and inserts the binary data associated with the image directly into the response stream so it can be rendered on the page.

1. Add the ProductImage.ashx handler file to the solution. To do this, right-click the ListProducts project, point to Add, and click Existing Item. Browse to Assets in the Source folder of this lab, select the ProductImage.ashx file and click Add.

   Note:

   The image handler intercepts the HTTP request and returns the image binary data of the corresponding product as the response.

2. Open the ProductImage.ashx handler. To do this, double-click the file in the Solution Explorer.
3. Locate and inspect the GetImageBytes method. This method contains the query that returns the first photo associated to the given product ID.
4. Open the Default.aspx page, by double-clicking the file in the Solution Explorer.

5. Add code to display the image corresponding to each product. To do this, add the following code, inside the ItemTemplate tags after the closing </p> tag.

   HTML

   <img alt="<%# Eval("Name")%>" src="<%# "ProductImage.ashx?ProductId=" + Eval("ProductId") %>" />
   Note:

   Note: The ProductId is passed as a parameter to the handler. The handler executes a SQL query and returns the corresponding image as a stream.

6. Save and close the files.

Exercise 2: Verification

In order to verify that you have correctly performed every steps of exercise two, proceed as follows.

1. Press F5 to run the solution. Each product should be accompanied by its image. You can click in the Previous and Next buttons to iterate through each page.

   Figure 12

   Retrieving the products with their images from the SQL Server

2. While running the solution, in Visual Studio you can observe the Output window. If not active, click View | Output. Each time the Previous or Next buttons are clicked, the executed query will be written to the debug console.

   Figure 13

   Executed SQL Query

3. Close the browser to end this verification.