1 / 73

Chapter Objectives

Chapter Objectives. Using Data Sources in Visual Studio .NET. The process of binding the data is the same regardless of the data source DataReader - provides a read-only, direct connection to the data source DataSet - accesses data as multiple DataTables

donnan
Download Presentation

Chapter Objectives

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter Objectives Introduction to ASP.NET, Second Edition

  2. Using Data Sources in Visual Studio .NET • The process of binding the data is the same regardless of the data source • DataReader - provides a read-only, direct connection to the data source • DataSet - accesses data as multiple DataTables • DataTables - can have relationships defined between the DataTable Objects • DataView - subset of one of the DataTables within the DataSet Introduction to ASP.NET, Second Edition

  3. The DataReader Object • Retrieve a read-only, non-buffered stream of data from a database • One record at any one time is stored in memory • When the new record is read, the old record is removed from memory first • Stream of data, retrieved sequentially • SqlDataReader - SQL Server • OledbDataReader - other OLE DB databases Introduction to ASP.NET, Second Edition

  4. Using a DataReader Object with an Access Database • Create connection string, Connection object, open Connection • Create a variable - SQL command or stored procedure • Create Command object - SQL and Connection object • Declare variable - DataReader Object • ExecuteReader of Command object - retrieve data and place data in DataReader • Read data from DataReader object, and write column values to the Web page • Close the DataReader object and connection Introduction to ASP.NET, Second Edition

  5. Using the DataReader to Retrieve Data from a Database DataReader.aspx (Page 382) Dim CS As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Inetpub\wwwroot\Chapter8\data\ TaraStore.mdb;" Dim objCN As New OleDb.OleDbConnection(CS) objCN.Open() Dim mySQL As String = _ "SELECT CategoryName FROM Categories" Dim objCM As New OleDb.OleDbCommand(mySQL, objCN) Dim objDR As OleDb.OleDbDataReader objDR = objCM.ExecuteReader() Dim MyCat As String While objDR.Read() MyCat = MyCat + (objDR("CategoryName") & "<br />") End While objDR.Close() objCN.Close() lblCat.Text = MyCat Introduction to ASP.NET, Second Edition

  6. DataReader.aspx (continued) Introduction to ASP.NET, Second Edition

  7. Building Advanced Queries Using SQL • Retrieve a select group of records using a criterion that is used to filter the records • A search condition evaluates to True or False • Strings are case-sensitive • WHERE keyword identify search condition • “AND” - conditions must resolve to True • “OR” - one condition needs to resolve to True WHERE user = 'katie' ANDS pwd = 'pass' WHERE lastVisit>#11/12/2003# OR member='new' Introduction to ASP.NET, Second Edition

  8. Building Advanced Queries Using SQL (continued) • Search condition expression • Keyword NULL to search for empty fields • Valid comparison operators include +, <, >, <>, IS, and ISNOT SELECT CategoryName, CategoryID, CatImage, Thumbnail, Description FROM Categories WHERE (CategoryName='Jewelry') OR (CategoryID>3) ORDER BY CategoryName ASC Introduction to ASP.NET, Second Edition

  9. Building SQL Queries to Insert, Modify, and Delete Data • Insert record INSERT INTO Products(name, price) VALUES('chair',153.00) • Concatenate the statement into a String SQL = "UPDATE Products SET " & _ " ProductName='Claddagh Ring'," & _ " UnitCost='25.45' WHERE ProductID=353" Introduction to ASP.NET, Second Edition

  10. Securing SQL Databases • SQL injection – attach SQL statements to an existing SQL query to run additional commands • Use stored procedures • Verify data entry • Strong passwords – combination of numbers and special characters to make it more difficult to guess Introduction to ASP.NET, Second Edition

  11. Using the DataReader Object with a SQL Server Database • Use different objects - SqlConnection, SqlCommand, and SqlDataReader • Steps same for both the OleDb and SqlClient • DataReader.aspx • Rewritten to support a SQL Server database using the SqlDataReader Introduction to ASP.NET, Second Edition

  12. Upsizing an Access Database to SQL Server Ch8TaraStoreSQL (Page 391) • Better security, performance, user management • Print or save Upsize Report TSUpsizeReport.html • Tools menu, Database Utilities • IUSR_MachineName – read permission to display data • User account – write permission to upsize • SA user have CREATE DATABASE privileges • Create a page to display your data Introduction to ASP.NET, Second Edition

  13. Using the DataReader Object to Display Data DataReaderSQL.aspx (Page 394) • Add Connection object SqlConnection1.Open() Dim mySQL As String = _ "SELECT CategoryName FROM Categories" Dim objCM As New _ SqlClient.SqlCommand(mySQL, SqlConnection1) Dim objDR As SqlClient.SqlDataReader objDR = objCM.ExecuteReader() Dim MyCat As String While objDR.Read() MyCat = MyCat + (objDR("CategoryName") & "<br>") End While objDR.Close() SqlConnection1.Close() lblCat.Text = MyCat Introduction to ASP.NET, Second Edition

  14. Using the DataReader Object with Stored Procedures DataReaderStoredProc.aspx (Page 396) • Create stored procedure CREATE PROCEDURE dbo.CategoryList AS SELECT * FROM Categories ORDER BY CategoryID ASC RETURN • Create Connection object Introduction to ASP.NET, Second Edition

  15. DataReaderStoredProc.aspx (continued) SqlConnection1.Open() Dim objCM As SqlClient.SqlCommand objCM = New _ SqlClient.SqlCommand("CategoryList", _ SqlConnection1) objCM.CommandType = CommandType.StoredProcedure Dim objDR As SqlClient.SqlDataReader objDR = _ objCM.ExecuteReader(CommandBehavior.CloseConnection) MyList.DataSource = objDR MyList.DataBind() Introduction to ASP.NET, Second Edition

  16. Using the DataReader Object with Parameters DataReaderDisplay.aspx (Page 398) • Delete Connection object and replace with new • Create stored procedures CREATE PROCEDURE dbo.SubCategoryByCategory @CategoryID int AS SELECT * FROM SubCategories WHERE CategoryID = @CategoryID ORDER BY SubCategoryID RETURN Introduction to ASP.NET, Second Edition

  17. DataReaderDisplay.aspx (continued) CREATE PROCEDURE dbo.ProductsBySubCategory @SubCategoryID int AS SELECT * FROM Products WHERE SubCategoryID = @SubCategoryID ORDER BY ModelName RETURN CREATE PROCEDURE dbo.SingleProductByProductID @ProductID int AS SELECT * FROM Products WHERE ProductID = @ProductID ORDER BY ModelName RETURN Introduction to ASP.NET, Second Edition

  18. How the QueryString Passes Data from the Database • DataReaderProducts.aspx • 4 functions - data bound to DataList control • Pass parameters • Show - set ImageUrl • selItem - selected item • CatID, SubCatID, ProdID • Creates Categegory List • GetCat() -> CategoryList Introduction to ASP.NET, Second Edition

  19. DataReaderDisplay.aspx (continued) • Click on category • DataReaderProducts.aspx?CatID=21&selItem=0&Show=cat • GetSubCats(CatID) • SubCategoryByCategory • SubCategories list bound to MySubCatList • LargeImage Show = cat • ImageUrl = "images/CatPics/" & CatID & ".jpg" Introduction to ASP.NET, Second Edition

  20. DataReaderDisplay.aspx (continued) • Click on subcategory • ?CatID=21&SubCatID=1&selItem=0&Show=prodlist • GetProducts(SubCatID) • ProductsBySubCategory • Products list bound to MyProdList • LargeImage Show = prodlist • "images/SubCatPics/" & SubCatID & ".jpg" Introduction to ASP.NET, Second Edition

  21. DataReaderDisplay.aspx (continued) • Click on subcategory • ?ProdID=548&CatID=21&SubCatID=1&selItem=0&Show=prod • GetProduct (ProdID ) • SingleProductByProductID • Products list bound to MyProduct • LargeImage Show = prod • "images/ProductPics/" & ProdID & ".jpg" Introduction to ASP.NET, Second Edition

  22. DataReaderProducts.aspx (Page 401) • Delete Connection and replace with new • Insert code to pass parameter to stored procedure Dim paramCatID As SqlParameter paramCatID = New SqlParameter("@CategoryID", SqlDbType.Int, 4) paramCatID.Value = CatID objCM2.Parameters.Add(paramCatID) Introduction to ASP.NET, Second Edition

  23. The DataAdapter, DataSet, and DataView Objects • Summary of Steps • DataAdapter (Connection and SQL) • Generate DataSet from the Data menu • DataView object • Assign Table to the DataView • Assign DataSource to DataView • DataAdapter.Fill(DataSet) • Bind the controls to data source • Page.DataBind() or Control.DataBind() Introduction to ASP.NET, Second Edition

  24. The DataAdapter, DataSet, and DataView Objects (continued) • Assign Table to DataView in code SqlDataAdapter1.Fill(MyDS) Dim MyDV As DataView = New DataView(MyDS, "Categories") MyDG.DataSource = MyDV MyDG.DataBind() • Table Collection – use index position Dim objDV As New DataView() objDV = DS11.Tables(0).DefaultView MyDG.DataSource = objDV MyDG.DataBind() Introduction to ASP.NET, Second Edition

  25. The DataAdapter, DataSet, and DataView Objects (continued) • Create objects manually Dim CN As New SqlConnection(CS) Dim MySQL As String = "Select * From Categories" MyDS = New DataSet() MyDA = New SqlDataAdapter(MySQL, CN) MyDA.Fill(MyDS, "Categories") Introduction to ASP.NET, Second Edition

  26. Using the DataView to Retrieve Data from a Database DataViewProducts.aspx (Page 406) • DataAdapter, DataSet, DataView • Products table • Filter DataView • Set RowFilter to SubCategoryID=19 • Add data binding code SqlDataAdapter1.Fill(DS_DataViewProducts1) Page.DataBind() Introduction to ASP.NET, Second Edition

  27. Customizing the DataGrid Control • Code behind the page, or visual tools • Data Columns • Bound columns - data that is bound to a column display the data, use the data in an expression, • Unbound columns - display content – buttons • Supports Sorting, Paging, and Filtering data Introduction to ASP.NET, Second Edition

  28. Paging and Sorting Data with the DataGrid Control PageSortProducts.aspx (Page 409) • Link Button control top of the column • Sorted by that column • DataGrid does not actually sort the rows • Raises a SortCommand event • Data rebound to the DataSource • Add code to sort the data if the event occurs • Sort expression passed • Represented as e.SortExpression • Get value selected as the sort key • Rebind the DataGrid to DataView Introduction to ASP.NET, Second Edition

  29. PageSortProducts.aspx (continued) • Displays a subset of records across Web pages • Navigation bar page (LinkButtons) • Default number of records - 10 • Turned off by default • Paging property of the DataGrid (AllowPaging) • Raises PageIndexChanged event • Add code to handle the event • Set CurrentPageIndex to NewPageIndex • Rebind the data Introduction to ASP.NET, Second Edition

  30. PageSortProducts.aspx (continued) • DataAdapter, DataSet, DataView • Set DataGrid Properties • Set to True • ShowFooter, ShowHeader, • AllowSorting, AllowPaging • PageSize – 3 • Change NextPrevText to Next; PrevPageText to Previous; Position to TopAndBottom • Fill DataSet and bind DataGrid Introduction to ASP.NET, Second Edition

  31. PageSortProducts.aspx (continued) • SortCommand DataView1.Sort = e.SortExpression MyDG.DataBind() • PageIndexChanged MyDG.CurrentPageIndex = e.NewPageIndex MyDG.DataBind() Introduction to ASP.NET, Second Edition

  32. Filtering Data with the DataGrid Control DataSetSearch.aspx (Page 412) • Temporarily select a subset of records • Does not remove the data from the database • RowFilter property of DataView • When the filter is removed, the records are redisplayed within the Web page Introduction to ASP.NET, Second Edition

  33. DataSetSearch.aspx (continued) Dim MySearch As String If Not Page.IsPostBack Then txtSearch.Text = "" MyDG.Visible=False Else BindMyDG() End If MySearch = "ModelName LIKE '*" &  txtSearch.Text & "*'" objDV.RowFilter = MySearch RecNum = objDV.Count.ToString MyDG.DataSource = objDV Page.DataBind() Introduction to ASP.NET, Second Edition

  34. DataSetSearch.aspx (continued) Introduction to ASP.NET, Second Edition

  35. DataSetSearch.aspx (continued) Introduction to ASP.NET, Second Edition

  36. DataSetSearch.aspx (continued) CREATE PROCEDURE dbo.FilterDataGrid @MySearchTerm nvarchar(20) AS SELECT * FROM Products WHERE (ModelName LIKE @MySearchTerm ) ORDER BY ModelName RETURN Introduction to ASP.NET, Second Edition

  37. DataSetSearch.aspx (continued) Introduction to ASP.NET, Second Edition

  38. Inserting, Modifying, and Deleting Records • Create new records, modify existing records, and delete records • Use SQL commands • Methods built into Data controls • ItemCommand and CommandBuilder – to build your own methods Introduction to ASP.NET, Second Edition

  39. Using the DataGrid Control to Maintain a Database • Style Properties • AlternatingItemStyle – alternating rows • EditItemStyle – row being edited • FooterStyle – footer row • HeaderStyle – header row • ItemStyle – individual items within list or control • PagerStyle – page selection controls • SelectedItemStyle – currently selected item Introduction to ASP.NET, Second Edition

  40. Using the DataGrid Control to Maintain a Database (continued) • HeaderText, HeaderImageURL, and FooterText • top and bottom of TemplateColumn • contain HTML elements and controls • Visible property – show or hide column • SortExpression property – identify the column used when sorting the column • DataField property – data column bound • DataFormatString property – formatting rules • ReadOnly property – stop editing a column Introduction to ASP.NET, Second Edition

  41. Using the DataGrid Control to Maintain a Database (continued) • ItemTemplate – HTML elements and controls • TemplateColumn – additional content, HTML • EditCommandColumn (cover later) • DeleteCommandColumn (cover later) • EditItemTemplate – edit mode • HyperLinkColumn – bind a hyperlink to data • ButtonColumn – insert a user defined button • ButtonType – LinkButton (hyperlink) or PushButton (button) Introduction to ASP.NET, Second Edition

  42. Using the DataGrid Control to Maintain a Database (continued) • Retrieve data in TextBox control Sub UpdateItem(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) Dim MyData As String Dim MyTB As TextBox MyTB = CType(e.Item.Cells(1).Controls(0), TextBox) MyData = MyTB.Text End Sub  Introduction to ASP.NET, Second Edition

  43. Using the DataGrid Control to Maintain a Database (continued) • E.Item and FindControl to locate column Sub UpdateItem(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) Dim MyData As String Dim box As TextBox MyTB = CType(e.Item.Cells(1).FindControl("FirstName"), TextBox) MyData = MyTB.text End Sub   Introduction to ASP.NET, Second Edition

  44. Inserting a New Record with the DataReader Control and with Stored Procedures and Parameters • Create stored procedure CREATE Procedure AddCatSQL ( @CatName nvarchar(50), @CatImage nvarchar(50), @CatThumb nvarchar(50), @CatDesc ntext, @CatID int OUTPUT ) AS INSERT INTO Categories (CategoryName, CatImage, Thumbnail, Description) VALUES (@CatName, @CatImage, @CatThumb, @CatDesc) SELECT @CatID = @@Identity Introduction to ASP.NET, Second Edition

  45. InsertCat.aspx (Page 420) • btnAdd event handler Dim CatDesc As String = tCatDesc.Text • Create parameters – returns CatID Dim pCatID As SqlClient.SqlParameter pCatID = New SqlClient.SqlParameter("@CatID", SqlDbType.Int, 4) pCatID.Direction = ParameterDirection.Output oCM.Parameters.Add(pCatID)  Introduction to ASP.NET, Second Edition

  46. InsertCat.aspx (continued) • Create parameters Dim pCatDesc As SqlClient.SqlParameter pCatDesc = New SqlClient.SqlParameter("@CatDesc", SqlDbType.NText) pCatDesc.Value = CatDesc oCM.Parameters.Add(pCatDesc)  Introduction to ASP.NET, Second Edition

  47. InsertCat.aspx (continued) • Run command • Preview page SqlConnection1.Open() oCM.ExecuteNonQuery() SqlConnection1.Close() Introduction to ASP.NET, Second Edition

  48. Deleting a Record with the DataGrid Control • Different methods • Use a built-in TemplateColumn • DeleteCommand • Built-in method of Delete Column • Delete LinkButton • ItemCommand – create command functions • ButtonColumn – trigger Delete function Introduction to ASP.NET, Second Edition

  49. DeleteCat.aspx (Page 421) • DataAdapter, DataSet, DataView • Fill DataSet and bind DataGrid • Preview Data Introduction to ASP.NET, Second Edition

More Related