Unit 6: Accessing and Displaying Data
Overview
Database Connections and the Web.Config File Relational Data and Data Source Controls XML Data and Data Source Controls Object Data and Data Source Controls Lab Scenario Lab Task and Objectives Lab: Accessing and Displaying Data

Introduction
This unit describes how to add database connections to the Web.Config file and the benefits that this approach adds when building manageable Web applications. This unit then describes the new data controls for accessing data in a variety of formats. It includes details about using the SqlDataSource control, the XmlDataSource control, and the ObjectDataSource control. This unit also describes how user interface data controls are bound to the data source controls, and it includes a discussion about binding data-aware standard controls to data.

Objectives
After completing this module, students will be able to: Explain how to store and retrieve database connections by using the Web.Config file. Explain how to use data source controls to access relational data. Explain how to use data source controls to access XML data. Explain how to use data source controls to access object data. Create and retrieve database connections by using the Web.Config file. Access relational data by using the SqlDataSource control and data controls. Access XML data by using the XmlDataSource control and data controls. Access objects as databy using the ObjectDataSource control and data controls.

Unit 6: Accessing and Displaying Data

Introduction
Web application use database connections as the underlying mechanism for retrieving data from database and for inserting, updating and deleting data.

Database Connection and Connection Strings
You define database connection in terms of a string that specifies the properties of the connection. These properties usually include the location of the database and security information for accessing the database. Different database support different properties and therefore require different connection strings; the connection string format depends on the provider being used to open the connection. For example, the provider for Microsoft SQL Server database supports both Microsoft Windows authenticated logins and SQL Server authenticated logins and it requires the name of the server to be included in the connection string. Conversely, the provider for Microsoft Office Access does not support Windows authenticated logins, and it requires the file path to the access database file rather than a server name.

Using the Web.config File to Simplify connection String Management
Although you can had-code connection strings into your Web application pages, this approach can lead to a difficult-to-manage solution. It is not usual for data source to be moved, defined, or upgraded after an application has beenreleased. For example, you might initially deploy a small web site with an access database. As the usage and number of visitors to the site increases, you might decide to upgrade to SQL Server. If you have hard-code all the connection to use the access database, you will have to replace every occurrence with the new connection detail for the SQL Server database. However, if you have used the Web.Config file to store connection strings, and your web page have retrieved the details from the Web.Config file at run time, your solution will be more manageable because you will need only to change the connection details in the Web.Config file.

2

www.intech.edu.pe


Unit 6: Accessing and Displaying Data

Connection details are also typically stored in the Web.Config file to ease the deployment of the Web application from the development environment to the testing, staging, and production environments, each of which will typically have different database servers. The following example show part of the Web.Config file with a connection string. SQL Server authenticated Windows authenticated

The connectionStrings element is part of the configuration element in the Web.Config file

Retrieving Connection Strings at Run Time
If you store a connection string in the connectionString section of the Web.Config file, you can retrieve the string at run time by using the ConfigurationManager class. Thisclass provides a ConnectionStrings collection that enumerates the connection string added to the connectionStrings section of the Web.Config file. The following example shows how to retrieve connection strings at run time and how to use them to open a database connection. Visual Basic Dim connStr As String = _ ConfigurationManager.ConnectionStrings('AdvWorks').ConnectionString Dim sqlConn As System.Data.SqlClient.SqlConnection = _ New System.Data.SqlClient.SqlConnection(connStr) sqlConn.Open() C# string connStr = ConfigurationManager.ConnectionStrings['AdvWorks'].ConnectionString; System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(connStr); sqlConn.Open();

3

www.intech.edu.pe


Unit 6: Accessing and Displaying Data

Introduction
Data-driven Web application frequently retrieve data from database and render it on Web pagres, ASP.NET provides control for performing this type of operation. You can implement many datadriven scenarios using these controls without writing any data access code.

Accessing Relational Databases by Using Data Source Controls
ASP.NET includes data source controls that allow you to work with different types of data sources such as a database, an XML file, or a middle-tier business object. Data source controls connect to and retrieve data from a data source and make it available for other controls to bind to, without requiring code. They canalso support modifying data. SqlDataSource: Enables you to work with Microsoft SQL Server, OLE DB, ODBC, or Oracle databases. When used with SQL Server, supports advanced caching capabilities. The control also supports sorting, filtering, and paging when data is returned as a DataSet object. AccessDataSource: Enables you to work with a Microsoft Access database. Supports sorting, filtering, and paging when data is returned as a DataSet object. XmlDataSource: Enables you to work with an XML file, especially for hierarchical ASP.NET server controls such as the TreeView or Menu control. Supports filtering capabilities using XPath expressions and enables you to apply an XSLT transformation to the data. The XmlDataSource allows you to update data by saving the entire XML document with changes.

Displaying Relational Data on Web Pages
The basic process for displaying data from relational database on Web pages is: 1. Add a data source control on the web page, and then configure it to connect to the required database. 2. Specify the SELECT statement in the SelectCommand property of the data source control, to retrieve the data.

4

www.intech.edu.pe


Unit 6: Accessing and Displaying Data

3. Bind data controls or data-aware controls to the data source control. The data controls available to web applications include the GridView, DataList, DataRepeater and FormView controls. Data-aware controls are standardcontrols, such as a DropDownList control or a ListBox control, than you can bind to a data source.

5

www.intech.edu.pe


Unit 6: Accessing and Displaying Data

Introduction
ASP.NET provides control for retrieving XML data from a data source and rendering it. You can use these controls without writing any data access code.

Accessing XML Data by Using Data Source Controls
ASP.NET provides the XMLDataSource control for accessing XML data. You can configure the XmlDataSource control to retrieve data from XML file, web services that return XML data, string variables that contain XML data, and in-memory XmlDataDocument objects.

Displaying XML Data on Web Pages
The basic process for displaying XML data on web pages is: 1. Add an XmlDataSource control to the web page, and configure in to connect to the required XML source. 2. Bind data-aware controls that are capable of displaying XML data to the XMLDataSource control. Examples of control that can display XML data include the TreeView control and the Xml control.

6

www.intech.edu.pe


Unit 6: Accessing and Displaying Data

Introduction
Web application can also retrieve data from business objects and render it on Web pages. ASP.NET provides control for performing this type of operation, which you can use without writing any data access code.

Accessing Object Data by Using Data Source Controls
ASP.NET provides the ObjectDataSourcesource control for accessing the data managed by business object. The functionality of the business object defines the functionality of the ObjectDataSource control, such as whether data can be retrieved, inserted, updated, or deleted.

Displaying Object Data on Web Pages
The process for displaying data from business object is very similar to the process for relational data. The basic process is: 1. Add an ObjectDataSource control to the web page, and then configure it to connect to the required business object. 2. Specify the name of the method used to retrieve data from the business object in the SelectedMethod property. 3. Bind data controls or data-aware controls to the data source control. Data controls include objects such as the GridView, DataList, DataRepeater, and FormView controls. Data-aware controls are standard controls that can be bound to a data source, such as a DropDownList control or a ListBox control.

Updating, Inserting, and Deleting Object Data
In addition to the Select Method property that defines the data to be retrieved from a business object, ObjectDataSource controls also support the following properties: UpdateMethod InsertMethod

7

www.intech.edu.pe


Unit 6: Accessing and Displaying Data

DeleteMethod The process for using these properties is similar to that for using the commands of the relational data source controls described previously.

8

www.intech.edu.pe