for RuBoard |
In the preceding hour, you saw how to create a set of screens that displays a list of products and enables you to click on a particular product to see the detailed product information. The next few sections of this hour reinforce the same three- tier style of development by implementing an administrative section.
The stored procedure that will actually update the product information in the database is straightforward and provided in Listing 22.1. As you can see, it accepts the product values as parameters and passes those parameters to an UPDATE query. When the query is done, the number of records updated by the query is passed back to the calling method. This can be used to ensure that the update had no errors. Because we're updating a single product at a time, the number of rows modified should always be "1" if the query is successful.
CREATE PROCEDURE Product_Update ( @ProductID int, @ProductName nvarchar(40), @QuantityPerUnit nvarchar(20), @UnitPrice money, @UnitsInStock smallint, @ImagePath varchar(256), @Discontinued bit, @RowsAffected int OUTPUT ) AS UPDATE Products SET ProductName = @ProductName, QuantityPerUnit = @QuantityPerUnit, UnitPrice = @UnitPrice, UnitsInStock = @UnitsInStock, ImagePath = @ImagePath, Discontinued = @Discontinued WHERE ProductID = @ProductID
With the stored procedure created, the next step is to create the necessary middle-tier method that will perform the update. Place the code from Listing 22.2 into the ProductsDB.vb file. For more details on creating a component layer, please see the relevant sections in the preceding hour.
Public Function UpdateProducts( ProductID as Int32, _ ProductName as string, _ QuantityPerUnit as string, _ UnitPrice as string, _ UnitsInStock as Int16, _ ImagePath as string, _ Discontinued as Boolean ) as Int32 'Create Instance of Connection and Command Objects Dim conn as New SqlConnection( _ ConfigurationSettings.AppSettings("ConnectionString")) Dim cmd as New SqlCommand("Product_Update", conn) 'Mark the Command as a Stored Procedure cmd.CommandType = CommandType.StoredProcedure ' Add Parameters to SPROC Dim parameterProductID As SqlParameter = _ New SqlParameter("@ProductID", SqlDbType.Int, 4) parameterProductID.Value = productID cmd.Parameters.Add(parameterProductID) Dim parameterProductName As SqlParameter = _ New SqlParameter("@ProductName", SqlDbType.NVarChar, 40) parameterProductName.Value = ProductName cmd.Parameters.Add(parameterProductName) Dim parameterQuantityPerUnit As SqlParameter = _ New SqlParameter("@QuantityPerUnit", SqlDbType.NVarChar, 20) parameterQuantityPerUnit.Value = QuantityPerUnit cmd.Parameters.Add(parameterQuantityPerUnit) Dim parameterUnitPrice As SqlParameter = _ New SqlParameter("@UnitPrice", SqlDbType.Money, 8) parameterUnitPrice.Value = Convert.ToDecimal(UnitPrice) cmd.Parameters.Add(parameterUnitPrice) Dim parameterUnitsInStock As SqlParameter = _ New SqlParameter("@UnitsInStock", SqlDbType.SmallInt, 2) parameterUnitsInStock.Value = Convert.ToInt16(UnitsInStock) cmd.Parameters.Add(parameterUnitsInStock) Dim parameterImagePath As SqlParameter = _ New SqlParameter("@ImagePath", SqlDbType.NVarChar, 50) parameterImagePath.Value = ImagePath cmd.Parameters.Add(parameterImagePath) Dim parameterDiscontinued As SqlParameter = _ New SqlParameter("@Discontinued", SqlDbType.Bit, 1) parameterDiscontinued.Value = Discontinued cmd.Parameters.Add(parameterDiscontinued) Dim parameterRowsAffected As SqlParameter = _ New SqlParameter("@RowsAffected", SqlDbType.Int, 4) parameterRowsAffected.Direction = ParameterDirection.Output cmd.Parameters.Add(parameterRowsAffected) conn.Open() cmd.ExecuteNonQuery() conn.Close() Return Convert.ToInt32(parameterRowsAffected.Value) End Function
After you've added the preceding code into the ProductsDB.vb file, don't forget that you must recompile the namespace. You must manually recompile your namespace after each change. |
As you can see, the UpdateProducts() method in Listing 22.2 accepts seven arguments that describe a product. Lines 9 “15 set up the Connection and Command objects. Then, lines 17 “51 create the set of stored procedure input parameters. The last parameter, found in lines 53 “56, sets up the output parameter that we'll use to ensure our query was successful.
With the middle tier and data layers firmly in place, you can easily work on the presentation layer. Rather than create an entirely new product list screen, you can use the Products.aspx Web form from the preceding hour in Listing 21.3 with a few changes. At the end of the DataList 's ItemTemplate , place the code in Listing 22.3. This will create a new link for each product on the product list screen. Clicking these links will navigate to the ProductEditor.aspx Web form that you will create next. You can see the additional link created in Figure 22.1. Notice that the hyperlink generated by the DataGrid passes the ProductID to the producteditor.aspx page in the querystring. This is the mechanism the product editor uses to identify which product is being modified.
<a href='producteditor.aspx?productID= <%# DataBinder.Eval(Container.DataItem, "ProductID") %>'> edit product </a>
The code for the ProductEditor.aspx Web form is provided in Listing 22.4. Notice that it is much like the ProductDetails.aspx screen. Instead of displaying the detailed product information in label Web controls, however, it uses editable text boxes. Note that the LoadData methods for these two screens are almost identical. When the ProductEditor.aspx Web form loads, it retrieves the product information using the exact same techniques as the ProductDetails.aspx uses.
<% @Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD> <LINK rel="stylesheet" type="text/css" href="Main.css"> <!-- End Style Sheet --> <script language="VB" runat="server" > Sub Page_Load(Source as Object, E as EventArgs) 'Get ProductID and store in Viewstate Dim ProductID As Integer = Int32.Parse(Request.Params("ProductID")) if ProductID <> "" then ViewState("ProductID") = ProductID end if if IsPostBack then UpdateData( Convert.ToInt32(ViewState("ProductID")) ) end if LoadData( ProductID ) End Sub Private Sub UpdateData( productID as Int32 ) Dim iResults as Int32 Dim product as new ADO24HRS.ProductsDB() iResults = product.UpdateProducts( _ productID, _ txtProductName.Text, _ txtQuantityPerUnit.Text, _ txtUnitPrice.Text, _ txtUnitsInStock.Text, _ txtImagePath.Text, _ chkDiscontinued.Checked ) if iResults = 1 then lblOutput.Text = "Record successfully updated!" else lblOutput.Text = "There was a problem updating the record" end if End Sub Private Sub LoadData( productID as Int32 ) 'Declare new instance of ProductsDB Dim products as New ADO24HRS.ProductsDB() 'Declare new instance of custom class Dim myProductDetails as ADO24HRS.ProductDetails 'Fill custom class with data myProductDetails = products.GetProductDetails( productID ) txtProductName.Text = myProductDetails.ProductName txtQuantityPerUnit.Text = myProductDetails.QuantityPerUnit txtUnitPrice.Text = myProductDetails.UnitPrice.ToString() txtUnitsInStock.Text = myProductDetails.UnitsInStock.ToString() ProductImage.Src = "/ADO24HOURS" + myProductDetails.ImagePath txtImagePath.Text = myProductDetails.ImagePath.ToString() chkDiscontinued.Checked = myProductDetails.Discontinued End Sub </script> </HEAD> <BODY> <h1>Edit Product Details</h1> <hr> <form runat="server" id=form1 name=form1> <asp:label id="lblOutput" runat="server" /> <table cellpadding=5> <tr> <td> <img id="ProductImage" height=144 width=144 runat="server"> </td> <td valign="center"> <table> <tr> <td> <strong>Product Name: </strong> </td> <td> <asp:TextBox id="txtProductName" runat="server" /> </td> </tr> <tr> <td> Quantity Per Unit: </td> <td> <asp:TextBox id="txtQuantityPerUnit" runat="server" /> </td> </tr> <tr> <td> Units In Stock: </td> <td> <asp:TextBox id="txtUnitsInStock" runat="server" /> </td> </tr> <tr> <td> Price Per Unit: </td> <td> <asp:TextBox id="txtUnitPrice" runat="server" /> </td> </tr> <tr> <td> Image Path: </td> <td> <asp:TextBox id="txtImagePath" runat="server" /> </td> </tr> <tr> <td> Product Discontinued: </td> <td> <asp:CheckBox id="chkDiscontinued" runat="server" /> </td> </tr> <tr> <td> </td> <td> <input type="submit" value="Submit Changes"> </td> </tr> </table> </td> </tr> </table> </form> <hr> </BODY> </HTML>
After you click on one of the new Edit Product links on the product listing screen, the ProductEditor.aspx Web form from Listing 22.4 loads. It appears much like the screen in Figure 22.2.
You can use the Product Editor to make changes to existing product information, such as the product name, quantity in stock, and price. After you make changes to the selected product and submit the form, the Web form gathers the product information and passes it to the UpdateProducts() method of the ProductsDB class, found in Listing 22.2. As you saw earlier, that method then passes that product information to the Product_Update stored procedure from Listing 22.1. The Product_Update method executes an UPDATE SQL query, which saves the product data. After this is completed, the stored procedure passes the number of rows affected by the query back to the UpdateProducts() method. The UpdateProducts() method passes this value back to the Web form, which uses the value to either confirm for the user that the product was updated successfully, or inform the user that something went wrong.
for RuBoard |