ADO .

NET
As you have learned earlier that when a database is accessed by multiple users over multiple
networks, all the data server resources are consumed quickly, which ultimately leads to congestion in
the network. The database handling mechanisms in the .NET development environment define a
methodology to create a copy of the database objects, such as tables, views, relations, and data
types, in local memory of the ASP.NET application. The application works on the copy of data stored
in the local memory of the application. This shows that the application process works in a detached
mode with the database. The actual connection with the database is made only when the final read,
write, or update operations are performed on the database. This is how ADO.NET preserves the
resources.
ADO is the strongest feature of.NET Framework. Any windows or Web application developed in
the .NET Framework uses ADO objects to work with databases. Creating an application that provide
access to databases is a cumbersome task. ADO.NET provides a group of classes that helps to
make the cumbersome task easier. Incorporating ADO.NET features in the applications has been
made very simple in the .NET Framework.

Disconnected Data Architecture

In most cases, accessing data from a database requires connection with the database. Working with
databases through .NET applications requires a constant connection with the database servers
accessing a Web application by multiple users at the same time increases load on the Web server.
Using connected data architecture in applications affects the performance of applications. The .NET
development environment reads the database objects, such as tables, views, relations, and data
types, into the ASP.NET application's memory. The .NET application processes data in a detached
(disconnected) mode with the databases. The live connection is established only when the user
transaction has to finally write the modified data or new data to a database.
The .NET Framework creates a data layer known as ADO.NET data layer, which contains all physical
data storage services.
Various operations that are performed on the databases by the services offered by this layer are as
follows:
 Connecting to a database or an Extensible Markup Language (XML) file.

 Querying the database by using SQL for filling the filtered data in the virtual memory of the
web application; for example, selecting salaries of all the employees whose age is greater than
50 years. This involves the use of Data Manipulation language, which uses the select query.

 Making changes in the database table(s) as a result of processing the retrieved data in the
code. For example calculating annual salary, increasing salary by some percent and modifying
the salary.

 Inserting new data and performing delete operations; for example, adding records for new
employees and deleting the records for retired employees.
The tasks involved in the second and third points are accomplished by ASP.NET applications in three
steps: reading the data from the database, modifying the data, and storing the modified data in the
database. Although the application processes the data or provides the output for viewing the data by
the users, there is no active connection with the database.
However, when the user wants to finally submit the data to a database or to make permanent
changes in a database, a live connection is established until the changes are committed to the
database.

ADO.NET Objects
ADO.NET is o new evolution of the ADO technology. In ADO, you remain connected to a database as
long as you manipulate the data in the database. Once data manipulation is over, you must close the
connection to the database. This process develops problems with the increase in the number of
users, which, in turn, increases the load on the database server, thereby affecting the performance of
the database. To overcome this problem, the ADO.NET technology is introduced, which is a kind of a
disconnected model. It connects to the database when required, retrieves information, and then gets
disconnected.
The ADO.NET technology uses the following objects to manipulate the data in a database:
 Connection objects: Connects you with the database. A data adopter needs a connection to
a data source to read and write the data. For this purpose, it uses the OleDbConnection,
OdbcConnection, OracleConnection, or SqIConnection objects to communicate with a data
source.
 Data Adapter object: Enables you to communicate between a data source and a dataset. You
can configure a data adopter with SQL to execute against the data source. The available data
adapters are the OleDbDataAdapter, OdbcDataAdapter, OracleDataAdapter,
AccessDataAdapter, and SqlDataAciapter objects.
 Command object: Requires reading, adding, updating, and deleting records in a data source.
For each of these operations, the data adapter contains a command object. Data adapters
support four types of command objects: Select Command, Insert Command,
UpdateCommond, and DeleteCommand.
 DataSet object: Stores data in a disconnected cache. The structure of a dataset is similar to
that of a relational database. The Dataset object represents a complete set of data including
the related tables, constraints, and relationships. Datasets are supported with DataSet objects.
 DataTable object: Uses to store a data table from a dataset. DataTable object contains two
important properties, namely, columns and Rows. The Columns property is a collection of the
Data Column objects that represent the columns of data in a table. The Rows property is a
collection of the Data Row objects that represent the rows of data in a table.
 Data Reader object: Holds a read-only, forward-only set of data from a database. The use of
a data render can increase the speed with which the data is retrieved from the database,
because only one row of the data remains in memory at a time.
 Data view object: Represents a customized view of a single table that can be filtered,
searched or sorted. A data view, supported by the Data View class, is a data snapshot that
takes up a few resources.
 Constraint object: Checks data integrity. A constraint, supported by the Constraint class, is a
rule that can be used when rows are inserted, updated, or deleted to check the affected table
 after performing the operation. Constraints are of two types: unique constraints and foreign-key
constraints.

 Data Relation object: Specifies a relationship between the parent and child tables based on a
key that is shored by both the tables.

 Data Row object: Corresponds to a particular row in a data table. You use the Item property
to get or set a value in a particular field in the row.

 Data Column object: Represents the columns in a table. Each object has a Data Type
property that specifies the kind of data each column contains, such as integers or string
values.

Now, let's know about the SQLDataSource control.

The SQLDataSource Control

The SqlDataSource control is used to establish a connection with a database, such as SQL server,
this data source control loads the provider for SQL server. The wizard launched by this control helps
the users to do the following:

 Creating provider-specific connection (SQL Connection) to the SQL server

 Building SQL query or use stored procedures
 Fetching the data from the SQL server database

In earlier versions of ASP.NET, this task was done through code, wherein all the values were passed
to provider-specific class constructors.

Table 8.1 lists some noteworthy public properties of the SqlDataSource class:
Properties Description
Connection String Obtains or specifies the connection string parameters
into the Connection class constructor. This string
contains the database name, server name, initial

Obtains or specifies the query string for retrieving the

Select Command records from the SQL database.

Insert Command Obtains or specifies the string containing the Insert

SQL query for inserting the data into an SQL server
database.

Table 8.2 lists some noteworthy public methods of the SqIDataSource class:
methods Description
Data bind binds an SQL data source to the SQL server data
source control

select Retrieves the records or data from an SQL server

database

insert insert the records or data into an SQL server

database
You can create a Web site to view the usage of SQLDataSource control by performing the following
steps:
1. Click Start → All Programs → Microsoft Visual Studio 2012 → Visual Studio 2012.
The Start Page of Microsoft Visual Studio 2012 appears.
2. Select FILE → new → Web Site option from the menu bar on the Start Page.
The New Web Site dialog box appears.

3. Select Installed → Templates → Visual C# → Web option in the left pane of the New Web Site
dialog box.

4. Select ASP.NET Empty Web Application template in the middle pane of the New Web Site
dialog box.

5. Click the Browse button for location, and enter name of the Web site, such as
Sqldatasourcecontrol, in the text box to specify the location and name of the Web site.

6. Click the OK button.

The Sqldatasourcecontrol Web site is created.

7. Open the Solution Explorer window and right-click the Web site name.in our case, we are right
clicking the Sqldatasourcecontrol Web site.

8. Select the Add New Item option from the context menu.
The Add New Item dialog box appears.

9. Select the installed → Visual C# option in the left pane and Web Form in the middle pane of
the Add New Item dialog box.
Click the Add button.

10. The Default.aspx form is added to the Sqldatasourcecontrol Web site.

Download North wind database from the Internet, which is freely available, and install it on
your computer. It is by default installs in C: drive of your computer.

11. Drag and drop the Grid View control and SqIDataSource control from the Data tab in the
Toolbox on the Web page (Fig. ASP-8.1).
Click the smart tag on the SqIDataSource control (Fig. ASP-8.1).

12. The SqIDataSource Tasks appears (Fig.ASP-8.1).

13. Select the Configure Data Source from the SqIDataSource Tasks, as shown in Fig.ASP-8.1:
The Configure Data Source dialog box appears (Fig.ASP-8.2).

14. Click the New Connection button in the dialog box, as shown in Fig.ASP-8.2:
The Add Connection dialog box appears.

15. Select the server name from the Server name drop-down list.

16. Select the Use Windows Authentication radio button from the Log on to the server group.

17. Select the North wind database option from the Select or enter database name drop-down list
in the Connect to a database group.

Click the Test Connection button

.
18. The Microsoft Visual Studio message box appears (Fig.ASP-8.3).

19. Click the OK button to continue, as shown in Fig.ASP-8.3:

20. Click the OK button in the Add Connection dialog box, as shown in Fig.ASP-8.4:
The Choose Your Data Connection page the Choose Data Source dialog box appears (Fig.ASP-8.5).

21. Click the Next button, as shown in Fig.ASP-8.5:

The Save the connection String to the Application Configuration File pane of the configure Data
Source dialog box appears, which prompts you for saving the connection string (Fig.ASP-8.6).

22. Click the Next button, as shown in Fig.ASP-8.6:

The Configure the Select Statement page of the Configure Data Source dialog box appears, which
prompts you for selecting the columns that you want to show on the Web page in the Grid View
control.
23. Select the Specify columns from a table or view radio button.

24. Select the column names that you want to display in the Grid View control. In our case, we are
selecting the Product ID, Product Name, Unit Price, UnitslnStock, and UnitsOnOrder columns.

25. Click the Next button

26. The Test Query page of the Choose Data Source dialog box appears.
Click the Test Query button.
The data appears in the Test Query pane.

27. Click the Finish button:

28. Click the smart tag of the Grid View control The Grid View Tasks appears

29. Select the SQLDataSource1 option from the Choose Data Source drop-down list in the Grid
View Tasks
Listing 8.1 shows the source code of default.aspx Web from after adding the controls and setting their
properties:
Listing 8.1: Code of the Default.aspx Web from in the Sqldatasourcecontrol application

30. Press the F5 key to execute the Web site.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile=-Default.aspx.cs"

inherits= "_Default"

<!DOCTYPE html >

<html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"><tItle></title›<head>

TheAccessDataSource Control

The AccessDataSource Control is a data source control that allow a Web severe control to access a
Microsoft (MS) Access database. This control does not support connection strings, but the Data File
property of this control allows you to specify the Access file (.mdb file) that you want to use to access
the data. When you add on AccessDataSource control to your ASP.NET page, the following line of
code appears in the HTML page:

<asp:AccessDataSource ID="AccessDataSource1" runat="Server">

</asp:AccessDataSource>

The AccessDataSource control does not allow you to provide username and password to access the
data. If you need to use a password-protected database, then select the SqlDataSource control to
access the database.
Table 8.9 lists the noteworthy properties of the AccessDataSource class:

Table 8.9: Noteworthy Properties of the AccessDataSource Class:

Property Description
Connection String Obtains the connection string that is used to connect to
the
Microsoft Access database
Data File Obtains or specifies the location of the Microsoft
Access .mdb file
Provider Name Obtains the name of the .NET data provider that the
AccessDataSource control uses to connect to a
Microsoft Access database
The application uses on AccessDataSource control to retrieve data into the Web Application from an
access file. This example uses a Grid View control to query the AccessDataSource control to display
data on the Web page by performing the following steps:
1. Open Visual Studio 2012.

2. Select FILE → new → Web Site option from the menu bar. The New Web Site dialog box
appears.

3. For selecting Accessdatasource control, you need to downgrade the .NET Framework 4.5 to
NET Framework 4 0.
4. Select Installed → Templates → Visual C# → Web option in the left pane of the New Web Site
dialog box>

5. Select ASP.NET Empty Web Application template option in the middle pane of the New Web
Site dialog box.

6. Click the Browse button for location, and enter the name of the Web site such as
Accessdatasource control.

7. In the text box to specify the location and name of the Web site.
Click the OK button to create Accessdatasource control Web site

8. Add a Web form to the Accessdatasource control Web site.

9. Drag and drop one AccessDataSource control and Grid View control the Data tab of the
toolbox on the Web form.

Create an MS Access file, name it as student.accdb, and save it, for example, on the Desktop.

10. Right-Click the application name and select the Add Existing item from the context menu
.
11. Browse to the location where the Access file is saved. In This case, we have select the student
file which is saved at the Desktop.

12. Click the Add button.

The file Appears in the Solution Explorer windows.

12. Click the smart tag of the AccessDataSource control and select the Configure Data Source
option.it opens the configure Data Source dialog box.

14. Click the Browse button.

15. Select the students.accdb file and click the OK button. You can see the selected MS Access
file in the Configure Data Source wizard.

16. Click the Next button.

17. Select the Product ID, Stud Name, Roll no, and Class options in the Columns pane and click
the Next button.

18. Click the Test Query button. You can see the columns ID, Stud name, Roll no and Class.

19. Click the Finish button.

20. Click the smart tag of the Grid View control and select the data source as AccessDataSource1
 From the drop-down list beside the Choose Data Source option.

Listing 8.5 shows the source code of the Default.aspx Web from after adding the control and setting
their properties:

<%@ Page language="C#" AutoEventwireup="true" CodeFile="Default.aspx.cs" liinherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title></title>

</head>

<body>

<form id="forml" runat="server">

<div> ‹asp:Gridview ID="Gridviewl" runat="server"

AutoGenerateColumns="False" DatakeyNames="ID" DataSourceID="AccessDataSourcel"›

<columns>

‹asp:BoundField DataField="ID" HeaderText="ID" Insertvisible="False"

ReadOnly="Troe" SortExpression="ID" />

<asp:BoundField DataField="studname" HeaderText="Studname" SortExpression="Studname" />

<asp:BoundField DataField="Rollno" HeaderText="Rollno"

SortExpression="Rollno" />

<asp:BoundField DataField="Class" HeaderText="Class"

SortExpression="Class" />
Listing 8.5: Code of the Default.aspx Web from of the Accessdatasource application

20. Press the F5 key to execute the Web site.