Chapter Objectives

1
(No Transcript)
2
Chapter Objectives
3
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

4
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

5
Using a DataReader Object with an Access
Database

1. Create connection string, Connection object, open
   Connection
2. Create a variable - SQL command or stored
   procedure
3. Create Command object - SQL and Connection object
4. Declare variable - DataReader Object
5. ExecuteReader of Command object - retrieve data
   and place data in DataReader
6. Read data from DataReader object, and write
   column values to the Web page
7. Close the DataReader object and connection

6
Using the DataReader to Retrieve Data from a
Database DataReader.aspx (Page 382)

• Dim CS As String "ProviderMicrosoft.Jet.OLEDB.4
  .0 ?Data SourceC\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") "ltbr
  /gt")
• End While
• objDR.Close()
• objCN.Close()
• lblCat.Text MyCat

7
DataReader.aspx (continued)
8
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 lastVisitgt11/12/2003 OR member'new'

9
Building Advanced Queries Using SQL (continued)

• Search condition expression
• Keyword NULL to search for empty fields
• Valid comparison operators include , lt, gt, ltgt,
  IS, and ISNOT
• SELECT CategoryName, CategoryID, CatImage,
• Thumbnail, Description
• FROM Categories
• WHERE (CategoryName'Jewelry') OR (CategoryIDgt3)
• ORDER BY CategoryName ASC

10
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 ProductID353"

11
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

12
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

13
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

14
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") "ltbrgt")
• End While
• objDR.Close()
• SqlConnection1.Close()
• lblCat.Text MyCat

15
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

16
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.CloseConnectio
  n)
• MyList.DataSource objDR
• MyList.DataBind()

17
Using the DataReader Object with Parameters
DataReaderDisplay.aspx (Page 398)

• Delete Connection object and replace with new
• Create stored procedures
• CREATE PROCEDURE dbo.SubCategoryByCategory
• _at_CategoryID int
• AS
• SELECT
• FROM SubCategories
• WHERE CategoryID _at_CategoryID
• ORDER BY SubCategoryID
• RETURN

18
DataReaderDisplay.aspx (continued)

• CREATE PROCEDURE dbo.ProductsBySubCategory
• _at_SubCategoryID int
• AS
• SELECT
• FROM Products
• WHERE SubCategoryID _at_SubCategoryID
• ORDER BY ModelName
• RETURN
• CREATE PROCEDURE dbo.SingleProductByProductID
• _at_ProductID int
• AS
• SELECT
• FROM Products
• WHERE ProductID _at_ProductID
• ORDER BY ModelName
• RETURN

19
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() -gt CategoryList

20
DataReaderDisplay.aspx (continued)

• Click on category
• DataReaderProducts.aspx?CatID21selItem0Show
  cat
• GetSubCats(CatID)
• SubCategoryByCategory
• SubCategories list bound to MySubCatList
• LargeImage Show cat
• ImageUrl "images/CatPics/" CatID ".jpg"

21
DataReaderDisplay.aspx (continued)

• Click on subcategory
• ?CatID21SubCatID1selItem0Showprodlist
• GetProducts(SubCatID)
• ProductsBySubCategory
• Products list bound to MyProdList
• LargeImage Show prodlist
• "images/SubCatPics/" SubCatID ".jpg"

22
DataReaderDisplay.aspx (continued)

• Click on subcategory
• ?ProdID548CatID21SubCatID1selItem0Showp
  rod
• GetProduct (ProdID )
• SingleProductByProductID
• Products list bound to MyProduct
• LargeImage Show prod
• "images/ProductPics/" ProdID ".jpg"

23
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("_at_CategoryID",
  SqlDbType.Int, 4)
• paramCatID.Value CatID
• objCM2.Parameters.Add(paramCatID)

24
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()

25
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()

26
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")

27
Using the DataView to Retrieve Data from a
Database DataViewProducts.aspx (Page 406)

• DataAdapter, DataSet, DataView
• Products table
• Filter DataView
• Set RowFilter to SubCategoryID19
• Add data binding code
• SqlDataAdapter1.Fill(DS_DataViewProducts1)
• Page.DataBind()

28
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

29
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

30
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

31
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

32
PageSortProducts.aspx (continued)

• SortCommand
• DataView1.Sort e.SortExpression
• MyDG.DataBind()
• PageIndexChanged
• MyDG.CurrentPageIndex e.NewPageIndex
• MyDG.DataBind()

33
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

34
DataSetSearch.aspx (continued)

• Dim MySearch As String
• If Not Page.IsPostBack Then
• txtSearch.Text ""
• MyDG.VisibleFalse
• Else
• BindMyDG()
• End If
• MySearch "ModelName LIKE '" ?
• txtSearch.Text "'"
• objDV.RowFilter MySearch
• RecNum objDV.Count.ToString
• MyDG.DataSource objDV
• Page.DataBind()

35
DataSetSearch.aspx (continued)

36
DataSetSearch.aspx (continued)

37
DataSetSearch.aspx (continued)

• CREATE PROCEDURE dbo.FilterDataGrid
• _at_MySearchTerm nvarchar(20)
• AS
• SELECT
• FROM Products
• WHERE (ModelName LIKE _at_MySearchTerm )
• ORDER BY ModelName
• RETURN

38
DataSetSearch.aspx (continued)
39
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

40
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

41
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

42
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)

43
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

?
44
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("FirstNam
  e"),
• TextBox)
• MyData MyTB.text
• End Sub

?
?
45
Inserting a New Record with the DataReader
Control and with Stored Procedures and Parameters

• Create stored procedure
• CREATE Procedure AddCatSQL
• (
• _at_CatName nvarchar(50),
• _at_CatImage nvarchar(50),
• _at_CatThumb nvarchar(50),
• _at_CatDesc ntext,
• _at_CatID int OUTPUT
• )
• AS
• INSERT INTO Categories
• (CategoryName, CatImage, Thumbnail,
  Description)
• VALUES (_at_CatName, _at_CatImage, _at_CatThumb,
  _at_CatDesc)
• SELECT
• _at_CatID _at__at_Identity

46
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("_at_CatID",
• SqlDbType.Int, 4)
• pCatID.Direction ParameterDirection.Output
• oCM.Parameters.Add(pCatID)

?
47
InsertCat.aspx (continued)

• Create parameters
• Dim pCatDesc As SqlClient.SqlParameter
• pCatDesc New SqlClient.SqlParameter("_at_CatDesc",
  
• SqlDbType.NText)
• pCatDesc.Value CatDesc
• oCM.Parameters.Add(pCatDesc)

?
48
InsertCat.aspx (continued)

• Run command
• Preview page
• SqlConnection1.Open()
• oCM.ExecuteNonQuery()
• SqlConnection1.Close()

49
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

50
DeleteCat.aspx (Page 421)

• DataAdapter, DataSet, DataView
• Fill DataSet and bind DataGrid
• Preview Data

51
DeleteCat.aspx (continued)

• Insert columns manually
• DataKeyField is CategoryID
• Modify template properties
• Insert code to call RemoveFromCat function
• ltItemTemplategt
• ltaspLinkButton ID"RemoveButton"
• CommandName"RemoveFromCat"
• Text"Delete" ForeColor"blue" runat"server" /gt
• lt/ItemTemplategt

52
DeleteCat.aspx (continued)

• ItemCommand function
• If e.CommandSource.CommandName "RemoveFromCat"
  Then
• . . .
• Else
• End If
• Page.DataBind()

?
53
DeleteCat.aspx (continued)

• Retrieve current rows CatID and delete row
• Dim CatIDCell As TableCell e.Item.Cells(1)
• Dim CatID As String CatIDCell.Text
• Dim oCM As SqlClient.SqlCommand
• Dim MySQL As String
• MySQL "DELETE FROM Categories WHERE CategoryID
  "
• CatID
• oCM New SqlClient.SqlCommand(MySQL,
  SqlConnection1)
• SqlConnection1.Open()
• oCM.ExecuteNonQuery()
• SqlConnection1.Close()
• Page.DataBind()
• Response.Redirect("DeleteCat.aspx")

?
?
54
Using the DeleteCommand Property of the DataGrid
Control to Delete DataDeleteCatDelCmd.aspx (Page
426)

• OnDeleteCommand property - assign Delete function
• ltaspDataGrid OnDeleteCommand"MyDG_DeleteCommand"
  id"MyDG DataKeyField"CategoryID"
  AutoGenerateColumns"False" . . .gt
• ltColumnsgt
• ltaspButtonColumn Text"Delete"
  CommandName"Delete"gt
• lt/aspButtonColumngt
• ltaspBoundColumn DataField"CategoryID"gt
• lt/aspBoundColumngt
• ltaspBoundColumn DataField"CategoryName"gt
• lt/aspBoundColumngt
• . . . lt/Columnsgtlt/aspDataGridgt

?
55
DeleteCatDelCmd.aspx (continued)

• Function deletes records, rebinds data
• Sub MyDG_DeleteCommand(ByVal source As Object,
  ByVal e As System.Web.UI.WebControls.DataGridComm
  andEventArgs) Handles MyDG.DeleteCommand
• . . .
• MySQL "DELETE FROM Categories WHERE CategoryID
  _at_CatID"
• objCM New SqlClient.SqlCommand(MySQL, objCN)
• objCM.Parameters.Add(New SqlParameter("_at_CatId",
  SqlDbType.Int))
• objCM.Parameters("_at_CatId").Value
  MyDG.DataKeys(CInt(e.Item.ItemIndex))
• . . . Delete code and rebind function
• End Sub

?
?
?
?
?
56
Updating the Database Using the DataGrid Control
EditCat.aspx (page 427)

• Page 427 - Part 1
• Creates basic data objects, bind DataGrid
• DataAdapter, DataSet, DataView, DataGrid
• SqlDataAdapter1.Fill(DS_EditCat1)
• If Not Page.IsPostBack Then
• Page.DataBind()
• End If
• Page 431 - Part 2
• Inserts the data-editing code

57
EditCat.aspx (continued)
58
EditCat.aspx (continued)

• EditCommandColumn
• A special button column
• LinkButtons labeled Edit, Update, and Cancel
• Change text - EditText, UpdateText, CancelText
• Events when click on the buttons
• EditCommand
• UpdateCommand
• CancelCommand
• Event handlers assigned in DataGrid using
  properties
• onEditCommand, onCancelCommand, onUpdateCommand

59
EditCat.aspx (continued)

• DataGrid
• DataKeyField to CategoryID
• DataMember to Categories
• Insert "Edit, Update, Cancel" column
  (EditCommandColumn)
• Import data namespaces
• Imports System.Data.SqlClient

60
EditCat.aspx (continued)

• CancelCommand method Display mode
• MyDG.EditItemIndex -1
• MyDG.DataBind()
• EditCommand function Edit mode
• MyDG.EditItemIndex e.Item.ItemIndex
• MyDG.DataBind()

61
EditCat.aspx (continued)

• UpdateCommand retrieve values from TextBoxes
• Dim key As String _
• MyDG.DataKeys(e.Item.ItemIndex).ToString
• Dim CatName, CatImage, CatThumb,CatDesc As String
• Dim tb As TextBox
• tb CType(e.Item.Cells(2).Controls(0), TextBox)
• CatName tb.Text
• tb CType(e.Item.Cells(3).Controls(0), TextBox)
• CatImage tb.Text
• tb CType(e.Item.Cells(4).Controls(0), TextBox)
• CatThumb tb.Text
• tb CType(e.Item.Cells(5).Controls(0), TextBox)
• CatDesc tb.Text

62
EditCat.aspx (continued)

• Value of key to locate row (r) and assign values
• Dim r As MyDS.CategoriesRow
• r MyDS1.Categories.FindByCategoryID(key)
• r.CategoryName CatName
• r.CatImage CatImage
• r.Thumbnail CatThumb
• r.Description CatDesc
• Update method
• SqlDataAdapter1.Update(DS1)
• MyDG.EditItemIndex -1
• MyDG.DataBind()

63
Building Reusable Visual Basic .NET Database
Components

• Create reusable, compiled components that create
  objects, access stored procedures, and return
  data
• 3 Parts
• Page 435 - Web.config
• Application variable stores connection string
• Page 436 - Ch8Products.vb
• Component and class - 4 functions retrieve data
• Page 438 - CatMenu.aspx
• Instantiate the object, call functions, retrieve
  data, bind data to DataList

64
Creating a Global Variable in the Web
Configuration File

• Web.Config - contains the connection string
• Is an XML-based text file
• Comments using HTML comment tags
• appSettings tag indicates Web site settings
• Add tag - create global application variables

65
Creating a Global Variable in the Web
Configuration File Web.config (Page 435)

• After ltconfigurationgt tag
• ltappSettingsgt
• ltadd key"CSTS"
• value"server(local)\NetSDK
• uidsapwdpassword
• databaseCh8TaraStoreSQL" /gt
• lt/appSettingsgt

?
?
66
Creating a Visual Basic .NET Component
Ch8Products.vb (Page 436)

• Create component, Add Connection
• Import namespaces
• Imports System.Data.SqlClient
• Imports System.Configuration
• Copy 4 functions from DataReaderDisplay.aspx

67
Ch8Products.vb (continued)
68
Calling the Component from the Web Page
CatMenu.aspx (Page 438)

• Instantiating the class as a new object from
  Ch8ProductsDB class
• General process for each step
• Create a variable named store each object created
  by Ch8Products class
• Assign DataSource property of Data List to data
  returned by DataReader from function
• Bind the data control.

69
CatMenu.aspx (continued)

• Retrieves category list
• Dim CatList As Chapter8.Ch8Products
• CatList New Chapter8.Ch8Products
• MyCatList.DataSource CatList.GetCat()
• MyCatList.DataBind()
• Retrieves subcategory list
• Dim SubCatList As Chapter8.Ch8Products
• SubCatList New Chapter8.Ch8Products
• MySubCatList.DataSource SubCatList.GetSubCats(Ca
  tID)
• MySubCatList.DataBind()

70
CatMenu.aspx (continued)

• Retrieves product list
• Dim ProductList As Chapter8.Ch8Products
• ProductList New Chapter8.Ch8Products
• MyProdList.DataSource ProductList.GetProducts(Su
  bCatID)
• MyProdList.DataBind()
• Retrieves individual product information
• Dim Product As Chapter8.Ch8Products
• Product New Chapter8.Ch8Products
• MyProduct.DataSource Product.GetProduct(ProdID)
• MyProduct.DataBind()

71
CatMenu.aspx (continued)
72
Summary

• DataSet can be used to retrieve data from various
  sources
• Fill method of DataAdapter populates DataSet
  using SQL command or stored procedure
• DataSet consists of DataTables collection
• DataView retrieves subset of DataSet
• Can refer to DataTable by name or position within
  DataTables collection

73
Summary (continued)

• DataGrid column types EditCommandColumn,
  HyperLinkColumn, ButtonColumn, TemplateColumn
• EditCommandColumn uses LinkButtons and is used to
  update database
• Components allow you to separate business logic
  from presentation
• Create global variables in Web.config application
  configuration file located in root of the Web site