CS 3870/CS 5870: Note05

1. CS 3870/CS 5870: Note05 Lab 3 Web Application with Dataase

2. Master Page • All Web pages will be similar • Should be created before other web pages • Add New Items • Controls on the Master page ContentPlaceHolder Leave ContentPlaceHolder empty Add controls before ContentPlaceHolder • Adding text Title and Names • Adding navbar • External CSS file

3. CSS File body { padding-left: 12em; } .navbar { list-style-type: none; padding: 0; margin: 0; position: absolute; top: 4em; left: 1em; width: 11em }

4. Navbar <ul class="navbar"> <li> All Products </li> <li> Updating Products </li> <li> Shopping </li> </ul> No links yet!

5. Content Pages • Create the three pages using the master page • Complete the navbar

6. Navbar <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>

7. The Database • UWPCS3870 • SQL Server Express on Xray • User: jim (not case sensitive) • Password: UWPCS3870 (case sensitive)

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

9. Accessing Database • Data Source Controls • SqlDataSource • AccessDataSource • . . . • Code class • Connection • Command • DataAdpater • AdapterBuilder • Lab 3 • Use Code class

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

11. SQLDataClass • Code class in folder App_Code • No code module • All variables and procedures should be Shared • Otherwise, need to create object

12. Variables Public Class SQLDataClass Private Const ConStr As String = "Data Source=Xray\Sqlexpress;” & “Initial Catalog=UWPCS3870;Persist Security Info=True;” & “User ID=jim;Password=UWPCS3870" Private Shared prodAdapter As System.Data.SqlClient.SqlDataAdapter Private Shared prodBuilder As System.Data.SqlClient.SqlDataAdapter Private Shared prodCmd As New Data.SqlClient.SqlCommand Private Shared con As New Data.SqlClient.SqlConnection Public Shared tblProduct As New Data.DataTable("Product") . . . End Class The objects are available all the times.

13. Setup Command and Adapter ‘ Sets up the connection, command and adapter Public Shared Sub setupProdAdapter() con.ConnectionString = ConStr prodCmd.Connection = con prodCmd.CommandType = Data.CommandType.Text prodCmd.CommandText = "Select * from Product order by ProductID" prodAdapter = New System.Data.SqlClient.SqlDataAdapter(prodCmd) prodAdapter.FillSchema(tblProduct, Data.SchemaType.Source) End Sub

15. Retrieve Data Records ‘ Gets the table records and the table schema Public Shared Sub getAllProdcts() ‘ Need to reset the command prodCmd.CommandText = "Select * from Product order by ProductID" Try If Not tblProduct Is Nothing Then tblProduct.Clear() End If prodAdapter.Fill(tblProduct) Catch ex As Exception Throw ex Finally con.Close() End Try End Sub

16. Setting up the Adapter ‘ Global.asax Sub Application_Start(. . .) SQLDataClass.setupProdAdapter() End Sub Do it just once for the application for all sessions of all users.

17. Binding Gridview Protected Sub Page_Load(. . .) Handles Me.Load DataClass.getAllProducts() GridView1.DataSource = DataClass.tblProducts GridView1.DataBind() End Sub Refill the data table for each page request.

18. 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 “Lab3_”

19. Session Variables Sub Session_Start(. . .) Session(“Lab3_Index”) = 0 End Sub Protected Sub Page_Load(…) Handles Me.Load DisplayRow(Session(“Lab3_Index”)) End Sub

20. Display Record Private Sub DisplayRow(Index As Integer) Dim row As Data.DataRow row = SQLDataClass.tblProduct.Rows(index) ‘ May need formatting txtID.Text = row(0) txtName.Text = row(1) txtPrice.Text = row(2) txtDescription.Text = row(3) End Sub

21. Navigation Buttons Partial Class Lab3_Updating Protected Sub Button6_Click(…) Handles btnNext.Click Session(“Index”) += 1 DisplayRow(Session(“Index”)) End Sub Protected Sub Button6_Click(…) Handles btnPrevious.Click Session(“Index”) -= 1 DisplayRow(Session(“Index”)) End Sub

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

23. Navigation Buttons Partial Class Lab3_Updating Protected Sub Button6_Click(…) Handles btnNext.Click Session(“Index”) += 1 DisplayRow(Session(“Index”)) EnableDisableButtons() End Sub Protected Sub Button6_Click(…) Handles btnPrevious.Click Session(“Index”) -= 1 DisplayRow(Session(“Index”)) EnableDisableButtons() End Sub

24. 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’;

25. Button Update ‘ID not to be modified Protected Sub Button6_Click(…) Handles btnUpdate.Click Dim theID As String = txtID.Text Dim newName As String = txtName.Text Dim newPrice As Double = txtPrice.Text Dim newDesc As String = txtDesc.Text SQLDataClass.UpdateProduct(theID, newName, newPrice, newDesc) End Sub

26. UpdateProduct Public Shared Sub UpdateProduct(theID As String, newName As String, newPrice As Double) ‘ Building SQL statement with variables prodCmd.CommandText = ". . ." Try con.Open() prodCmd.ExecuteNonQuery() Catch ex Throw ex Finally con.Close() End Try End Sub

27. UpdateProduct Public Shared Sub UpdateProduct(theID As String, newName As String, newPrice As Double, newDesc As String) prodCmd.CommandText= " Update Product" & _ " Set ProductName = ‘newName', " & _ " UnitPrice = newPrice, " & _ " Description = ‘newDesc'" & _ " Where ProductID = 'theID‘” Try . . . End Try End Sub

28. UpdateProduct Public Shared Sub UpdateProduct(theID As String, newName As String, newPrice As Double) ‘ Building SQL statement with variables prodCmd.CommandText= " Update Product " & _ " Set ProductName = " & newName & “, " & _ " UnitPrice = " & newPrice & ", " & _ " Description = " & newDesc & _ " Where ProductID = " & theID Try . . . End Try End Sub

29. UpdateProduct Public Shared Sub UpdateProduct(theID As String, newName As String, newPrice As Double) ‘ Building SQL statement with variables prodCmd.CommandText= " Update Product " & _ " Set ProductName = '" & newName & "'," & _ " UnitPrice = " & newPrice & ", " & _ " Description = '" & newDesc & "'" & _ " Where ProductID = '" & theID & "'“ Try . . . End Try End Sub

30. Try-Catch Public Shared Sub UpdateProduct(oldID As String, newID As String, newName As String, newPrice As Double) prodCmd.CommandText = ". . ." Try con.Open() prodCmd.ExecuteNonQuery() con.Close() Catch ex ‘ To see what is wrong Throw ex(prodCmd.CommandText) End Try End Sub

31. Try-Catch-Finally Public Shared Sub UpdateProduct(oldID As String, newID As String, newName As String, newPrice As Double) prodCmd.CommandText = ". . ." Try con.Open() prodCmd.ExecuteNonQuery() ‘ update database Catch ex Throw new Exception(ex.Message & myCmd.CommandText) Finally con.Close() ‘ always close it End Try End Sub

32. Button Update Protected Sub Button6_Click(…) Handles btnUpdate.Click Dim . . . Try DataClass.UpdateProduct(theID, newName, newPrice, newDesc) ‘ must update tblProducts SQLDataClass.getAllProduct() Catch ex Throw new Exception(ex.Message & myCmd.CommandText) Finally con.Close() ‘ always close it End Try End Sub

33. Updating Partial Class Lab3_Updating Inherits System.Web.UI.Page Protected Sub Page_Load(. . .) Handles Me.Load txtMsg.Text = "" DisplayRow(Session(“Lab3_Index")) End Sub Cannot Update Correctly!

34. Updating Partial Class Lab3_Updating Inherits System.Web.UI.Page Protected Sub Page_Load(. . .) Handles Me.Load txtMsg.Text = "" DisplayRow(Session(“Lab3_Index")) End Sub Cannot Update Correctly! Post Back!

35. PostBack Partial Class Lab3_Updating Inherits System.Web.UI.Page Protected Sub Page_Load(. . .) Handles Me.Load txtMsg.Text = “” If Not IsPostBack Then DisplayRow(Session("Index")) Else ‘ Do not do anything ‘ Textboxes keep their values from client End If End Sub

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

37. Page Shopping Property AutoPastBack of Textbox Must be True to fire Textchanged event

38. Textchanged Event of Textbox txtID ‘ Clear other textboxes id = txtID.Text.Trim Dim row as Data.DataRow row = SQLDataClass.tblProducts.Rows.Find(id) If row Is Nothing Then ‘ not found . . . txtID.Focus() Else ‘ Found . . . txtQuanity.Focus() End If

39. Textchanged Event of Textbox txtID If row Is Nothing Then ‘ not found Session(“Lab3_ID”) = “” txtID.Focus() Else ‘ Found txtName.Text = row(1) price = row(2) txtPrice.Text = FormatCurrency(price) Session("Lab3_Price") = price Session("Lab3_ID") = id txtQuanity.Focus() End If

40. Textchanged Event of txtQuantity If Session("Lab3_ID") = "" Then txtID.Focus() Exit Sub End If ‘ Otherwise, need to parse txtPrice or search database price = Session("Lab3_Price") subTotal = quanity * price tax = subTotal * 0.055 grandTotal = subTotal + tax ‘ Display result

41. Validating Input • txtID • No need to do it • Database will check it • Try-Catch-Finally • txtQuantity • Validator • No Validator • Your choice • Lab3 of CS3870 does not use Validator