Chapter 7 Web Forms DatabaseData Access in .NETData ReadersSetting up a Connection and CommandFilling a List Box from a Data ReaderSteps for Filling a List BoxCheck for Post BackDataSetsData BindingA DataReader and Dataset ExampleSetting a Dynamic Connection StringSlide 12Filling a DataGridSlide 14Slide 15Sorting the Data in a GridSlide 17Paging a Data GridSlide 19Slide 20Web DataGrid Column StylesThe DataList ControlA Sample DataListAdding a TemplatePersisting a DatasetUsing the ItemCommand EventSlide 27Finding the Selected Item in the DatasetRetrieve the DataFind the Selected RowAssign the Fields to the LabelsThe Complete ProcedureFormatting a Bound Template ControlThe CheckBoxList and RadioButtonList ControlsSlide 35Retrieving the SelectionOther ControlsUsing a DataGrid in a Multitier ApplicationSlide 39Using a DataList in a Multitier ApplicationSlide 41Slide 42Maintaining the State of List BoxesMaintaining the Selection of a Drop-Down ListMaintaining the List in a Dynamic List BoxControlling ValidationChapter 7 Web Forms DatabaseAdvanced Programming Using Visual Basic.NET © 2003 by The McGraw-Hill Companies, Inc. All rights reserved.© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.7- 2Data Access in .NET•Use a data adapter and its disconnected dataset•Connect to the data directly using a data readerData Access in .NETData Access in .NET© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.7- 3Data Readers•A DataReader object provides a forward-only result set from a data source•Open the connection and transfer the data into the DataReader object•Access is fast  the “fire-hose” connection•Not suitable for file updates–It is read Only•Expedient for loading a list box or retrieving information not subject to changeData Access in .NETData Access in .NET© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.7- 4Setting up a Connection and Command•Use a Connection object for the connection string•A Command object holds the SQL statement or stored procedure name–Set the CommandText property•Declare the connection and command in code OR use the visual toolsData Access in .NETData Access in .NET© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.7- 5Filling a List Box from a Data ReaderData Access in .NETData Access in .NETSqlCommand cmdStoresConnection conPubsCommandType TextCommandText SELECT stor_name from storesSqlCommand cmdStoresConnection conPubsCommandType TextCommandText SELECT stor_name from stores‘Declare a DataReader in codeImports System.DataDim drStores As SqlClient.SqlDataReader‘Declare a DataReader in codeImports System.DataDim drStores As SqlClient.SqlDataReaderOther Command Types: Stored Procedure Table Direct© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.7- 6Steps for Filling a List Box•Open the connection•Assign the return from the connection’s ExecuteReader method to the DataReader object•Set the DropDownList control’s DataSource and DataTextField properties•Bind the data•Close the DataReader•Close the connectionData Access in .NETData Access in .NET© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.7- 7Check for Post BackData Access in .NETData Access in .NETPrivate Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load'Load the list of store names using a data readerDim drStores As SqlClient.SqlDataReader*If Not IsPostBack ThenconPubs.Open()drStores = cmdStores.ExecuteReader()ddlStores.DataSource = drStoresddlStores.DataTextField = "stor_name"ddlStores.DataBind()drStores.Close()conPubs.Close()End IfEnd Sub Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load'Load the list of store names using a data readerDim drStores As SqlClient.SqlDataReader*If Not IsPostBack ThenconPubs.Open()drStores = cmdStores.ExecuteReader()ddlStores.DataSource = drStoresddlStores.DataTextField = "stor_name"ddlStores.DataBind()drStores.Close()conPubs.Close()End IfEnd Sub© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.7- 8DataSets•Create data adapters and datasets in the same way in a Web Form as in a Windows form•The binding process is differentData Access in .NETData Access in .NET© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.7- 9Data Binding•Additional Step needed–Set the DataBindings property of controls–Execute the DataBind method of the control or–The DataBind method of the form binds all controls on the page.Data Access in .NETData Access in .NETlblAddress.DataBind()lblCity.DataBind()lblAddress.DataBind()lblCity.DataBind()Me.DataBind()Me.DataBind()© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.7- 10A DataReader and Dataset Example•Select the store name from a drop-down list and display data for the selected storeData Access in .NETData Access in .NETSee code p. 239-240See code p. 239-240© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.7- 11Setting a Dynamic Connection String•A dynamic connection string allows movement of a database project from one computer to anotherData Access in .NETData Access in .NET© 2003 by The McGraw-Hill Companies, Inc. All rights reserved.7- 12Setting a Dynamic Connection String<appSettings> <!-- User application and configured property settings go here.--> <!-- Example: <add key="settingName" value="settingValue"/> --> <add key="conPubs.ConnectionString" value="data source=YourMachine\NetSDK; initial catalog=pubs;integrated security=SSPI; persist security info=False;workstation id=YourMachine;packet size=4096" /></appSettings><appSettings> <!-- User application and configured property settings go here.--> <!-- Example: <add key="settingName" value="settingValue"/> --> <add key="conPubs.ConnectionString" value="data source=YourMachine\NetSDK; initial catalog=pubs;integrated security=SSPI; persist security info=False;workstation id=YourMachine;packet size=4096" /></appSettings>value="server=(local)\NetSDK;Trusted_Connection=yes;database=pubs" />value="server=(local)\NetSDK;Trusted_Connection=yes;database=pubs" />Data Access in .NETData Access in .NETModify the “value =“ clause to a generic connectionstring that will run on any computer that has the.NET sample files installed.Modify the “value =“ clause to a generic connectionstring that will run on any computer that has the.NET sample files installed.© 2003 by