ASP.NET

1
ASP.NET Web Applications and Database Processing

• ASP.NET

2
Using Data Sources in VS.NET

• The process of binding the data is the same
  regardless of the data source
• DataReaderprovides a read-only, sequential
  direct (on-line) connection to the data source
• DataSetaccesses data as multiple DataTables
• DataTableone table returned from the DataSet
• DataViewprovides for alternate methods of
  looking at DataTables including sorting,
  filtering and searching functionallity

3
The DataReader Object

• Retrieves a read-only, non-buffered stream of
  data from a database
• Only one record at any time is stored in memory
• When a new record is read, the old record is
  removed from memory first
• The stream of data is retrieved sequentially
• There are two classes that implement DataReader
• SqlDataReader is used for SQL Server databases
• OleDbDataReader is used for other OLE DB databases

4
DataReader.aspx
5
Using a DataReader Object (Page 1)

• Instantiate Connection and Command objects
• Declare SqlDataReader or OleDbDataReader object
• Dim dataReaderCategories As OleDbDataReader
• Assign a connection string to the connection
  object's ConnectionString property
• Call the Open method for the connection object
• conTaraStore.Open()
• Assign the connection object to the Connection
  property of the command object

6
Using a DataReader Object (Page 2)

• Assign a SQL command string to the CommandText
  property of the command object
• Call ExecuteReader method of the command object
  and assign the return value (reference to the
  disk file) to the DataReader object
• dataReaderCategories cmdCategories.ExecuteReade
  r

7
Using a DataReader Object (Page 3)

• Loop through the records of the DataReader object
  calling the Read method (returns a Boolean value
  indicating if there are more records)
• Do While dataReaderCategories.Read
• strCategories dataReaderCategories("CategoryNam
  e")
• Loop
• Call the Close method for the connection and
  DataReader objects

8
Comparing OleDbDataReader and SqlDataReader

• For related OleDb the objects are
• OleDbConnection
• OleDbCommand
• For related SqlClient the objects (from the
  System.Data.SqlClient namespace) are
• SqlConnection
• SqlCommand
• Processing steps are the same for both

9
A Stored Procedure

• Create stored procedure
• CREATE PROCEDURE dbo.CategoryList
• AS
• SELECT
• FROM Categories
• ORDER BY CategoryID
• RETURN

10
DataReader and Stored Procedures (Page 1)

• SqlConnection1 New System.Data.SqlClient.SqlConn
  ection
• SqlConnection1.ConnectionString "workstation
  idKALATApacket size4096user idsadata
  source""(local)\NetSDK""persist security
  infoTrueinitial catalogCh8TaraStoreSQLpassword
  password"

11
DataReader and Stored Procedures (Page 2)

• SqlConnection1.Open()
• Dim objCM As New SqlClient.SqlCommand("CategoryLis
  t", SqlConnection1)
• objCM.CommandType CommandType.StoredProcedure
• Dim objDR As SqlClient.SqlDataReader
• objDR objCM.ExecuteReader(CommandBehavior.CloseC
  onnection)
• MyList.DataSource objDR
• MyList.DataBind()

12
Stored Procedures with Parameters

• Create stored procedure
• CREATE PROCEDURE dbo.SubCategoryByCategory
• _at_CategoryID int
• AS
• SELECT
• FROM SubCategories
• WHERE CategoryID _at_CategoryID
• ORDER BY SubCategoryID
• RETURN

13
DataReader and Stored Procedures with
Parameters (Page 1)

• SqlConnection1 New System.Data.SqlClient.SqlConn
  ection
• SqlConnection1.ConnectionString "workstation
  idKALATApacket size4096user idsadata
  source""(local)\NetSDK""persist security
  infoTrueinitial catalogCh8TaraStoreSQLpassword
  password"
• SqlConnection1.Open()
• Dim objCM2 As New SqlCommand("SubCategoryByCategor
  y", SqlConnection1)
• objCM2.CommandType CommandType.StoredProcedure

14
DataReader and Stored Procedures with
Parameters (Page 2)

• Dim paramCatID New SqlParameter("_at_CategoryID",
  SqlDbType.Int, 4)
• paramCatId.Value txtCatID.Text.ToString()
• objCM2.Parameters.Add(paramCatID)
• Dim objDR2 As SqlDataReader
• objDR2 objCM2.ExecuteReader(CommandBehavior.Clos
  eConnection)
• MyCatList.DataSource objDR2
• MyCatList.DataBind()

15
Retrieving Data with Data View (Page 1)

• The DataView object returns a customized view of
  a DataTable
• Format to declare and instantiate
• Dim dataViewName As DataView
• dataViewName dataSetName.Tables(0).DefaultView

16
Retrieving Data with Data View (Page 2)

• Example
• dvProducts dsProducts.Tables(0).DefaultView
• The DefaultView property for a Tables object
  returns a reference that can be sorted, filtered
  and searched (as opposed to a custom view)
• To filter records of a DataView ,use the
  RowFilter property
• dvProducts.RowFilter "SubCategoryID 19"
• The string must be in valid SQL WHERE clause
  format

17
DataViewProducts.aspx
18
Using the DataView Object (Page 1)

1. Assign a ConnectionString property to the
   Connection object
2. Assign a CommandText property to the Command
   object
3. Assign the Connection object to the Command
   object's Connection property
4. Assign the Command object to the DataAdapter's
   SelectCommand property
5. Call the DataAdapter to Fill the DataSet

19
Using the DataView Object (Page 2)

• Assign a subset of the DataSet to the DataView
• dvProducts dsProducts.Tables(0).DefaultView
• Assign the RowFilter property of DataView to
  limit rows returned if desired
• dvProducts.RowFilter "SubCategoryID 19"

20
Using the DataView Object (Page 3)

• Assign the DataView object to the DataGrid
  object's DataSource property
• dgProducts.DataSource dvProducts
• Bind the controls to data source, i.e.
• Page.DataBind()
• or
• dgProducts.DataBind()

21
Item Collections of the DataView
22
Customizing the DataGrid Control

• In addition to Bound columns in which data from a
  data source is bound to the column display, other
  supported operations include
• Sorting, Paging, and Filtering of data when its
  DataSource property is set to a DataView object
• Unbound columns which display additional content
  such as link buttons and other form fields
• Configured either in the code behind the page,
  using visual (GUI) tools, or the PropertyBuilder

23
PageSortProducts.aspx
24
Sorting a DataGrid (Page 1)

• A Link Button control is placed at top of the
  column
• Created by setting
• AllowSorting property for DataGrid to True
• SortExpression property for each BoundColumn to
  the data field
• Data sorted by that column when user clicks it

25
Sorting a DataGrid (Page 2)

• The rows actually are not sorted in the DataGrid
  but rather in the DataView
• Clicking the Link Button of the DataGrid object
  raises (calls) a SortCommand event
• A sort expression is passed to the called
  procedure represented as e.SortExpression
• Assign that value to Sort property of DataView
  object
• Then re-bind the DataGrid

26
Sorting a DataGrid (Page 3)

• Sample code for the SortCommand event
• Private Sub dgProducts_SortCommand( , ByVal e
  As DataGridSortCommandEventArgs) Handles
  dgProducts.SortCommand
• dvProducts.Sort e.SortExpression
• dgProducts.DataBind()

27
Paging within a DataGrid (Page 1)

• Displays subset of records in the control
• The navigation bar displays LinkButtons for
  browsing to previous or next pages
• Default number of records (10) displayed may be
  modified using the PageSize property
• Set AllowPaging property to True
• Clicking a pager (navigation) button on the
  DataGrid
• Raises the PageIndexChanged event which passes a
  new page number to the e.NewPageIndex parameter
• Assign that value to CurrentPageIndex property of
  the DataGrid control

28
Paging within a DataGrid (Page 2)

• Sample code for the PageIndexChanged event
• Private Sub dgProducts_PageIndexChanged( ,
  ByVal e As DataGridPageChangedEventArgs) Handles
  dgProducts.PageIndexChanged
• dgProducts.CurrentPageIndex e.NewPageIndex
• dgProducts.DataBind()

29
The PagerStyle Tag

• Places a pager (navigation) button onto DataGrid
• Provides interface for paging to previous or next
  page
• Properties
• NextPageTextclick on this text to advance to
  next page
• PrevPageTextclick on this text to advance to
  previous page
• Positionvalid positions for the pager are "Top",
  "Bottom" and both "TopAndBottom"
• Updateable in Property Builder

30
The RowFilter Property

• For a DataGrid, temporarily selects subset of
  records
• Does not remove data from the DataView
• The RowFilter property is assigned a criteria
  string in format of a SQL WHERE clause
• When the filter is removed, records are
  redisplayed within the Web page
• Example
• strMySearch "ModelName LIKE '"
  txtSearch.Text "'"
• dvProducts.RowFilter strMySearch

31
DataSetSearch.aspx

32
Products.aspx
33
Products.aspx
34
Products.aspxHTML view (Page 1)

• ltaspdatagrid id"dgProducts"
• style"Z-INDEX 103 LEFT 167px TOP 147px
• POSITION absolute"
• runat"server" Font-Names"Verdana"
• ForeColor"004040" Font-Size"X-Small"
• Height"359px" Width"575px"
• AutoGenerateColumns"False"
• ShowFooter"True" AllowPaging"True"gt

35
Products.aspxHTML view (Page 2)

• ltHeaderStyle ForeColor"Navy"
• BackColor"Silver"gt
• lt/HeaderStylegt
• ltFooterStyle BackColor"Silver"gt
• lt/FooterStylegt

36
Products.aspxHTML view (Page 3)

• ltColumnsgt
• ltaspTemplateColumn HeaderText"Image"gt
• ltHeaderStyle HorizontalAlign"Center"gt
• lt/HeaderStylegt
• ltItemStyle HorizontalAlign"Center"gt
• lt/ItemStylegt

37
Products.aspxHTML view (Page 4)

• ltItemTemplategt
• lta href'images/ProductPics/
• lt
• Container.DataItem(
• "ProductImage")gt'gt
• ltimg src'images/ProductThumbnails/
• lt Container.DataItem(
• "Thumbnail") gt' border"0"gt
• lt/agt
• lt/ItemTemplategt
• lt/aspTemplateColumngt

38
Products.aspxHTML view (Page 5)

• ltaspBoundColumn DataField"ModelName"
• HeaderText"Product Name"gt
• lt/aspBoundColumngt
• ltaspBoundColumn DataField"UnitCost"
• HeaderText"Price"
• DataFormatString"0C"gt
• ltHeaderStyle HorizontalAlign"Right"gt
• lt/HeaderStylegt
• ltItemStyle HorizontalAlign"Right"gt
• lt/ItemStylegt
• lt/aspBoundColumngt

39
Products.aspxHTML view (Page 6)

• lt/Columnsgt
• ltPagerStyle NextPageText"Next"
• PrevPageText"Previous" ForeColor"Red"
• Position"TopAndBottom"gt
• lt/PagerStylegt
• lt/aspdatagridgt

40
Products.aspxPage_Load (Page 1)

• Private Sub Page_Load(ByVal ) Handles
• If Page.Request.QueryString.Count gt 0 Then
• Dim conTaraStore As New OleDbConnection
• Dim daProducts As New OleDbDataAdapter
• Dim cmdProducts As New OleDbCommand
• Dim dsProducts As New DataSet
• Dim dvProducts As DataView

41
Products.aspxPage_Load (Page 2)

• Dim strCategoryID As String _
• Page.Request.QueryString("ID")
• Dim strCategoryName As String
• conTaraStore.ConnectionString _
• "ProviderMicrosoft.Jet.OLEDB.4.0Data
  Sourcec\Inetpub\wwwroot\Chapter8\data\TaraStore.
  mdb"

42
Products.aspxPage_Load (Page 3)

• cmdProducts.CommandText _
• "SELECT ProductID, Products.CategoryID,
  ModelName, ProductImage, UnitCost,
  Products.Thumbnail, CategoryName FROM Products,
  Categories WHERE Products.CategoryID
  Categories.CategoryID ORDER BY ModelName"
• cmdProducts.Connection conTaraStore
• daProducts.SelectCommand cmdProducts

43
Products.aspxPage_Load (Page 4)

• daProducts.Fill(dsProducts)
• dvProducts _
• dsProducts.Tables(0).DefaultView
• dvProducts.RowFilter _
• "CategoryID " strCategoryID
• strCategoryName _
• dvProducts.Item(0).Item( _
• "CategoryName")
• lblResults.Text "Number of quot" _
• strCategoryName "
• "quot items found is " _
• dvProducts.Count.ToString

44
Products.aspxPage_Load (Page 5)

• dgProducts.DataSource dvProducts
• If Not Page.IsPostBack Then
• Page.DataBind()
• End If
• End If
• End Sub

45
Products.aspxdgProducts_PageIndexChanged

• Private Sub dgProducts_PageIndexChanged( ,
  ByVal e As DataGridPageChangedEventArgs) Handles
  dgProducts.PageIndexChanged
• dgProducts.CurrentPageIndex _
• e.NewPageIndex
• Page.DataBind()
• End Sub

46
Data Updating with ASP.NET

• Create new records, modify existing records, and
  delete records (database maintenance)
• Embed SQL INSERT, UPDATE, and DELETE commands as
  strings into ASP.Net code
• Methods for data retrieval and manipulation are
  built into the various OleDb and SqlClient Data
  controls (either visual or programmatic)

47
Using DataGrid to Update Records (Page 1)

• 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

48
Using DataGrid to Update Records (Page 2)

• ItemTemplate HTML elements and controls
• TemplateColumn additional content, HTML
• EditCommandColumn (will cover this later)
• DeleteCommandColumn (will cover this later)
• EditItemTemplate edit mode
• HyperLinkColumn bind a hyperlink to data
• ButtonColumn insert a user defined button
• ButtonType LinkButton (hyperlink) or PushButton
  (button)

49
The SQL INSERT Statement (Page 1)

• The INSERT command adds a row (record) to a table
• Format
• INSERT INTO tableName
• (columnNames)
• VALUES (valueList )
• Example (values must match the number of items,
  their order, and data types in the original
  table)
• INSERT INTO Categories
• VALUES (30, "Irish Things", "30.jpg", "30.gif",
  "Irish Things", "30.ico")

50
The SQL INSERT Statement (Page 2)

• Example (empty values may be indicated by using
  the reserved word NULL)
• INSERT INTO Categories
• VALUES (30, "Irish Things", NULL, NULL, "Irish
  Things", NULL)
• Example (a columnName list may be used if not all
  columns will be assigned a value)
• INSERT INTO Categories
• (CategoryID, CategoryName, Description)
• VALUES (30, "Irish Things", "Irish Things")

51
InsertCat.aspx
52
Parameters

• A parameterized SQL statement is one which uses
  question marks (?) to denote a parameter
• A parameter is an object that can accept
  different (variable) values based upon the logic
  of the running application
• The values are determined dynamically at run-time
• Example
• cmdCategoriesInsert.CommandText "INSERT INTO
  Categories VALUES (?, ?, ?, ?, ?)"

53
Declaring a Parameter (Page 1)

• Parameter objects to be used in SQL statements
  are instantiated from either the OleDbParameter
  or the SqlParameter class
• There should be one parameter object for each
  parameter in the SQL statement
• At run-time, when the SQL statement is executed,
  the current value of the parameter is substituted
  into the statement

54
Declaring a Parameter (Page 2)

• Format
• Dim ParameterName As New OleDbParameter("Name",
  OleDbType.dataType, size, "ColumnName")
• ParameterName is the name of the parameter object
  (it will be substituted into the SQL statement
  later by the Parameter.Add method)
• Name is the parameter name in the SQL statement
  (might be used to assign a value to the
  parameter)
• dataType matches data type in DataTable (may
  differ from Visual Basic .NET data type)

55
Declaring a Parameter (Page 3)

• Format
• Dim ParameterName As New OleDbParameter("Name",
  OleDbType.dataType, size, "ColumnName")
• size is the maximum size of data
• For a String it should match that of the
  DataTable
• For numerics (including dates) and Boolean it
  should be zero (0) since it will be inferred from
  its OleDbType
• ColumnName is the name of the column in the
  underlying DataTable

56
Declaring a Parameter (Page 4)

• Example
• Dim pparamCatID As New OleDbParameter("CategoryID"
  , OleDbType.Integer, 0, "CategoryID")

57
The Value Property for Parameters (Page 1)

• Stores the current value of a parameter which
  usually is updated by when a user modifies a
  column value in a DataRecord object
• Parameters also may be set manually in code
• Formats
• CommandObject.Parameters("Name"/Index).Value
  "String"/Value
• ParameterObject.Value "String"/Value

58
The Value Property for Parameters (Page 2)

• Examples
• cmdCategoriesInsert.Parameters("CategoryID").Value
  intCatId
• paramCatID.Value intCatId
• So if the variable intCatId 26 and the
  parameterized SQL statement reads
• cmdCategories.CommandText "SELECT FROM
  Categories WHERE CategoryID ?"
• The actual statement which executes is
• cmdCategories.CommandText "SELECT FROM
  Categories WHERE CategoryID 26"

59
Running the Query

• The Open() method for the connection object must
  be executed prior to running the query
• The ExcuteQuery() method for a command object
  executes a SQL INSERT, UPDATE or DELETE query
• Close() the connection after the query executes
• Example
• conTaraStore.Open()
• cmdCategories.ExecuteNonQuery()
• conTaraStore.Close()

60
An INSERT Stored Procedure with Parameters

• 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

61
Inserting a New Record with Stored Procedures and
Parameters
62
Inserting a New Record with Stored Procedures and
Parameters (Page 1)

• Private Sub btnAdd_Click(ByVal sender As
  System.Object, ByVal e As System.EventArgs)
  Handles btnAdd.Click
• Dim strConnection As String _
• "user idsadata source(local)\NetSDK" _
• "persist security infoTrue" _
• "initial catalogCh8TaraStoreSQL" _
• "passwordpassword"
• Dim SqlConnection1 As SqlConnection
• SqlConnection1 New SqlConnection(strConnection)

63
Inserting a New Record with Stored Procedures and
Parameters (Page 2)

• Dim oCM As SqlClient.SqlCommand
• oCM New SqlClient.SqlCommand("AddCatSQL",
  SqlConnection1)
• oCM.CommandType CommandType.StoredProcedure
• Dim pCatID As New SqlClient.SqlParameter("_at_CatID",
  SqlDbType.Int, 4)
• pCatID.Direction ParameterDirection.Output
• oCM.Parameters.Add(pCatID)

64
Inserting a New Record with Stored Procedures and
Parameters (Page 3)

• Dim pCatName As New SqlClient.SqlParameter("_at_CatNa
  me", SqlDbType.NVarChar, 50)
• pCatName.Value txtCatName.Text.ToString()
• oCM.Parameters.Add(pCatName)
• Dim pCatImage As New SqlClient.SqlParameter("_at_CatI
  mage", SqlDbType.NVarChar, 50)
• pCatImage.Value txtCatImage.Text.ToString()
• oCM.Parameters.Add(pCatImage)

65
Inserting a New Record with Stored Procedures and
Parameters (Page 4)

• Dim pCatThumb As New SqlClient.SqlParameter("_at_CatT
  humb", SqlDbType.NVarChar, 50)
• pCatThumb.Value txtCatThumb.Text.ToString()
• oCM.Parameters.Add(pCatThumb)
• Dim pCatDesc As New SqlClient.SqlParameter("_at_CatDe
  sc", SqlDbType.NVarChar, 50)
• pCatDesc.Value txtCatDesc.Text.ToString()
• oCM.Parameters.Add(pCatDesc)

66
Inserting a New Record with Stored Procedures and
Parameters (Page 5)

• SqlConnection1.Open()
• ocm.ExecuteNonQuery()
• SqlConnection1.Close()
• txtCatName.Text String.Empty
• txtCatImage.Text String.Empty
• txtCatThumb.Text String.Empty
• txtCatDesc.Text String.Empty
• End Sub

67
The SQL DELETE Statement

• Removes one or more records from a table
• Format
• DELETE FROM tableName
• WHERE column_name criteria
• Example
• DELETE FROM Products
• WHERE ProductID 499
• If no WHERE clause, all rows would be deleted
  (WHERE clause is relation condition)

68
Deleting Records with DataGrid

• Uses a built-in TemplateColumn to display a
  button
• Create a programmer-defined "Delete" LinkButton
• The DeleteCommand is a programmer-defined method
  (Sub or Function) of Delete Column
• The CommandName property names the procedure or
  function to be called when the LinkButton is
  clicked

69
The ltaspLinkButtongt Control (Page 1)

• Inserts a hyperlink-like text button into cells
  of the DataGrid which calls a Sub procedure when
  clicked
• The ltaspLinkButtongt control is inserted in an
  ltItemTemplategt ltItemTemplategt block
• Calls the ItemCommand event for the DataGrid
  control when the button is clicked
• The CommandName property is passed to the called
  procedure as an argument so program can determine
  which LinkButton was clicked

70
The ltaspLinkButtongt Control (Page 2)

• Example
• ltItemTemplategt
• ltaspLinkButton ID"RemoveButton"
  CommandName"RemoveFromCat" Text"Delete"
  ForeColor"blue" runat"server" /gt
• lt/ItemTemplategt

71
The ItemCommand Event

• The Sub procedure that is called when a
  LinkButton object in the DataGrid is clicked
• Partial example
• Private Sub dgCategories_ItemCommand(ByVal source
  As System.Object, ByVal e DataGridCommandEventArgs
  ) Handles dgCategories.ItemCommand
• If e.CommandSource.CommandName "RemoveFromCat"
  Then

72
The e.Item.Cells Collection

• When the ItemCommand event executes, a reference
  is passed to the e parameter representing the row
  (Item) that raised the event
• The Cells collection can be referenced by an
  index to return one cell object from the Item,
  i.e.
• e.Item.Cells(1)
• References the second cell in the row where the
  LinkButton was clicked
• The reference may be stored in a TableCell
  object, i.e.
• Dim CatIDCell As TableCell e.Item.Cells(1).ToStr
  ing()

73
DeleteCat.aspx
74
Updating Records with DataGrid

• Enables editing by converting a row in the table
  temporarily into text boxes
• Creates a special hyperlink button that calls an
  UpdateCommand event when update processing takes
  place

75
The SQL UPDATE Command

• Modifies contents (value) of one or more fields
  in a record or records
• The SET clause is an assignment statement
• Format
• UPDATE tableName
• SET columnName newValue,
• WHERE columnName criteria
• Example
• UPDATE Categories
• SET CategoryName "Irish Things"
• WHERE CategoryID "32"

76
ltaspEditCommandColumngt Control

• A special LinkButton column used for updating
  rows in a DataGrid control
• The text for the buttons by default are labeled
  Edit, Update, and Cancel
• The text for these may be changed by modifying
  EditText, UpdateText, and CancelText properties
• The events that are called when a user clicks on
  one of the buttons for the DataGrid are
  respectively EditCommand, UpdateCommand and
  CancelCommand

77
EditItemIndex Property

• When set to index of a selected row in a
  DataGrid, the cells in the row are displayed as
  text boxes
• The user may modify contents of one or more cells
• Examples
• dgProducts.EditItemIndex 3
• dgProducts.EditItemIndex e.Item.ItemIndex
• When the EditItemIndex property is set to -1, the
  editing feature of the DataGrid is turned off
• dgProducts.EditItemIndex -1

78
Controls Object of the Item.Cells

• Retrieves the contents of a cell in a DataGrid
  control (Item.Cells(n)) as a control object
• Format
• e.Item.Cells(index).Controls(index)
• The following example uses the CType method
  (convert to type) to convert the control to a
  TextBox
• Example
• txtBox.Text e.Item.Cells(2).Controls(0)
• In this instance, the index for controls is 0
  since there is only one text box in each
  DataTable cell

79
The BeginEdit Method

• Enables editing of columns in a DataRow object
• Temporarily suspends the RowChanging event on the
  row that is being edited
• The row will be updated once at the end when the
  EndEdit method executes
• Format
• dataTableName.Rows(index).BeginEdit()
• Example
• dtCategories.Rows(intIndex).BeginEdit()

80
The EndEdit Method

• Disables editing of the DataRow object
• Fires (calls) the RowChanging event to update the
  entire record off-line
• Format
• DataTableName.Rows(index).EndEdit()
• Example
• dtCategories.Rows(intIndex).EndEdit()

81
The Item Collection

• Member of the DataRow object representing the one
  column (field) returned from the row
• Formats
• dataRowName.Item("FieldName"/index)
• dataTableName.Rows(index).Item("FieldName"/index)
• Example
• dtCategories.Rows(intIndex).Item("CatName")

82
The GetChanges Method

• Examines an existing DataSet and returns (points
  to) the changed records in the original DataSet
• Inserts, updates and deletes
• Format
• DataSetName.GetChanges()
• Example
• Dim dsCategoriesUpdate As DataSet
  dsCategories.GetChanges()

83
The Update Method

• Examines a DataSet (usually a new DataSet object
  from previously executed GetChanges method) for
  information about inserted, updated or deleted
  records
• Executes the appropriate SQL statement to modify
  the underlying database table on disk
• Format
• DataAdaptorName.Update(DataSetName)
• Example
• daTaraStore.Update(dsCategoriesUpdate)

84
The AcceptChanges Method

• Causes any records in the DataSet, that
  previously were marked as having been changed, to
  be marked now as unchanged
• Usually called immediately after Update method
  has modified the underlying database table
• Format
• DataSetName.AcceptChanges()
• Example
• dsCategories.AcceptChanges()

85
EditCat.aspx
86
SELECT with Join (Page 1)

• A join operation links related fields from more
  than one table
• Column names that exist in more than a single
  table must be prefixed by the table name
• I.e. "Products.CategoryID"
• Or "Categories.CategoryID"
• Format
• SELECT columnNames
• FROM tableNameList
• WHERE primaryKey foreignKey

87
SELECT with Join (Page 2)

• Example
• SELECT ProductID, ModelName, CategoryName
• FROM Products, Categories
• WHERE Products.CategoryID Categories.CategoryID

88
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

89
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

90
Creating a Global Variable in the Web
Configuration File

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

?
?
91
Creating Visual Basic .NET Components

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

92
Ch8Products.vb (continued)
93
Calling Component from the Web Page

• 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.

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

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

96
CatMenu.aspx