CS 3870: Note05

1. CS 3870: Note05 Prog3 Web Application with Database

2. Master Page • All Web pages will be similar • Should be created before other web pages • Add New Items • Master Page • Name: Prog3MasterPage.master • Check “Place code in separate file” • Uncheck “Select master page” • Could create a master page based on another master page • May be incomplete and need to come back later

3. Elements on the Master page • External CSS file <link href="StyleSheet.css" rel="stylesheet" type="text/css" /> • Form • All controls should be inside Form for dynamic pages • Two ContentPlaceHolder controls: could change id <asp:ContentPlaceHolder id="head" runat="server"> </asp:ContentPlaceHolder> <asp:ContentPlaceHolder id="ContentPlaceHolder1" runat="server"> </asp:ContentPlaceHolder>

4. Elements on the Master page • Leave ContentPlaceHolder empty • Add all common elements before/after ContentPlaceHolder • Title and Names • Adding navbar: incomplete

5. Content Pages • Create the three pages using the master page • Add New Item • Web Form • Place code in separate file • Select master page • Add • Select folder • Select Master page • The second web form • Add • Web Form (with master)

6. Content Pages • No Form control on content pages • The form control on the master page will be combined with the controls on the content page • Two Content controls <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server"> </asp:Content> <asp:Content ID="Content2" ContentPlaceHolderID="Prog3Body" Runat="Server"> </asp:Content> • Type it if not there

7. Complete the Navbar on Master Page <ul class="navbar"> <li> <a href="Default.aspx"> All Products </a></li> <li> <a href="Updating.aspx"> Updating Products </a></li> <li> <a href="Shopping.aspx"> Shopping </a></li> </ul> Type and select the pages.

8. Adding Elements • Type “All Products”, “Updating” and “Shopping” on the three pages • Run to see the pages

9. The Database • UWPCS3870 • SQL Server on Alpha • User: MSCS (not case sensitive) • Password: MasterInCS (case sensitive) • May not be able to access it from local Web site • Publish and check it using the Grader

10. Table Product Four Columns ProductID : nchar(4), primary key, not updatable ProductNmae: nvarchar(50) UnitPrice : smallmoney Description : nvarchar(MAX), allow nulls

11. Accessing Database • Data Source Controls • SqlDataSource • AccessDataSource • . . . • Code class • Connection • Command • DataAdpater • AdapterBuilder • Prog3 • Use Code class

12. ASP.NET Folders • Solution Explorer • Right click Web site • Add ASP.NET Folder • App_Code • (App_Data) • . . .

13. SQLDataClass • Code class in folder App_Code • All variables and procedures should be Static in C# (Shared in VB.NET) • Otherwise, need to create object

14. Variables public class SQLDataClass { private const string ConStr = "Data Source=Alpha;" + "Initial Catalog = UWPCS3870; Persist Security Info=True;" + "User ID = MSCS; Password=MasterInCS"; private static System.Data.SqlClient.SqlDataAdapter prodAdapter; private static System.Data.SqlClient.SqlCommand prodCmd = new System.Data.SqlClient.SqlCommand(); private static System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(); public static System.Data.DataTable tblProduct = new System.Data.DataTable("Product"); } The objects are accessible from any folders within the web site and available at all the times.

15. Setup Adapter public static void setupProdAdapter() { con.ConnectionString = ConStr; prodCmd.Connection = con; prodCmd.CommandType = System.Data.CommandType.Text; prodCmd.CommandText = "Select * from Product order by ProductID"; prodAdapter = new System.Data.SqlClient.SqlDataAdapter(prodCmd); prodAdapter.FillSchema(tblProduct, System.Data.SchemaType.Source); }

16. Retrieve Data Records public static void getAllProducts() { prodCmd.CommandText = "Select * from Product order by ProductID"; try { if (!(tblProduct == null)) tblProduct.Clear(); prodAdapter.Fill(tblProduct); } catch (Exception e) { throw e; } finally { con.Close(); } }

17. Setting up the Adapter // Global.asax void Application_Start(object sender, EventArgs e) { // Code that runs on application startup SQLDataClass.setupProdAdapter(); } Do it just once for the application for all sessions of all users.

18. Setting up the Adapter public static void getAllProducts() { if (prodAdapter == null) setupProdAdapter(); . . . }

19. Creating Gridview • Add a GridView on page Default.aspx • GridView: ToolBox – Data

20. Binding Gridview protected void Page_Load(object sender, EventArgs e) { SQLDataClass.getAllProducts(); ProductGrid.DataSource= SQLDataClass.tblProduct; ProductGrid.DataBind(); } Refill the data table for each page request.

21. Formatting GridView <asp:GridView runat="server" ID="GridView1" AutoGenerateColumns="False" style="z-index: 1; position: relative; width: 50%; margin-left:25%; align-items: center; height: 176px" > <Columns> <asp:BoundField DataField="ProductID" HeaderText="Product ID" > <ItemStyle HorizontalAlign="Center" Width="10%"></ItemStyle></asp:BoundField> <asp:BoundField DataField="ProductName" HeaderText="Product Name" > <ItemStyle HorizontalAlign="Left" Width="20%"></ItemStyle></asp:BoundField> <asp:BoundField DataField="UnitPrice" HeaderText="Unit Price" DataFormatString="{0:c}" HtmlEncode="False" > <ItemStyle HorizontalAlign="Right" Width="10%"></ItemStyle></asp:BoundField> <asp:BoundField DataField="Description" HeaderText="Description"> <ItemStyle HorizontalAlign="right" Width="10%"></ItemStyle></asp:BoundField> </Columns> </asp:GridView>

22. Page Updating • Display record one at a time • Display the first record for the first visit • Display the same record for return visit • Need Session variable • Begin with “Prog3_”

23. Session Variables void Session_Start(object sender, EventArgs e) { //Prog2 Session["Prog2_ProductID"] = ""; . . . //Prog3 Session["Prog3_Index"] = 0; Session["Prog3_ID"] = ""; }

24. Page_Load protected void Page_Load(object sender, EventArgs e) { DisplayRow((int)Session["Prog3_Index"]); }

25. Display Record private void DisplayRow(int index) { System.Data.DataRow row = SQLDataClass.tblProduct.Rows[index]; txtID.Text = row[0].ToString(); txtName.Text = row[1].ToString(); txtPrice.Text = string.Format("{0:C}", row[2]); txtDescription.Text = row[3].ToString(); }

26. Navigation Buttons protected void btnNext_Click(object sender, EventArgs e) { int index = (int)Session["Prog3_Index"] + 1; if (index > SQLDataClass.tblProduct.Rows.Count - 1) index = SQLDataClass.tblProduct.Rows.Count - 1; Session["Prog3_Index"] = index; DisplayRow(index); } protected void btnPrevious_Click(object sender, EventArgs e) { int index = (int)Session["Prog3_Index"] - 1; if (index < 0) index = 0; Session["Prog3_Index"] = index; DisplayRow(index); }

27. Enable/Disable Buttons Could make a private Sub. Your choice.

28. Enable/Disable Buttons private void EnableDisableButtons() { int curIndex = (int)Session["Prog3_Index"]; btnFirst.Enabled = (curIndex > 0); . . . }

29. Display Record private void DisplayRow(int index) { System.Data.DataRow row = SQLDataClass.tblProduct.Rows[index]; txtID.Text = row[0].ToString(); txtName.Text = row[1].ToString(); txtPrice.Text = string.Format("{0:C}", row[2]); txtDescription.Text = row[3].ToString(); EnableDisableButtons() }

30. SQL Statements Update Product Set ProductName = ‘NewName’, UnitPrice = newPrice Description = ‘NewDescription’, Where ProductID = ‘theID’; Insert Into Product Values(‘ID’, ‘Name’, Price, ‘Description’); Delete From Product Where ProductID = ‘theID’;

31. Incorrect! // SQLDataClass // Building SQL statement with variables prodCmd.CommandText = "Update Product " + "Set ProductName = " + newName + ", " + "UnitPrice = " + newPrice + ", " + "Description = " + newDesc + " " + "Where ProductID = " + theID;

32. // SQLDataClass public static void UpdateProduct(string theID, string newName, double newPrice, string newDesc) { prodCmd.CommandText = "Update Product " + "Set ProductName = '" + newName + "', " + "UnitPrice = " + newPrice + ", " + "Description = '" + newDesc + "' " + "Where ProductID = '" + theID + "'"; try { con.Open(); prodCmd.ExecuteNonQuery(); } catch(Exception ex) { throw new Exception(ex.Message); } finally { con.Close(); } }

33. Button Update //Attempts to update the currently displayed record in the databse. protected void btnUpdate_Click(object sender, EventArgs e) { try { string theID = txtID.Text; string newName = txtName.Text; double newPrice = double.Parse(txtPrice.Text.Replace("$", "")); string newDesc = txtDescription.Text; SQLDataClass.UpdateProduct(theID, newName, newPrice, newDesc); txtMessage.Text = "Record updated."; SQLDataClass.getAllProducts(); } catch (Exception ex) { txtMessage.Text = "Product Not Updated: " + ex.Message; } }

34. Page_Load protected void Page_Load(object sender, EventArgs e) { txtMessage.Text = ""; DisplayRow((int)Session["Prog3_Index"]); } Cannot Update Correctly!

35. Page_Load protected void Page_Load(object sender, EventArgs e) { txtMessage.Text= ""; if (!IsPostBack) { DisplayRow((int)Session["Prog3_Index"]); } }

36. Buttons • btnNew • New • Save New • btnDelete • Delete • Cancel • Enabled/Disabled