Unit-IV

Topics:
ADO.NET Fundamentals| Data Binding| Data
Controls
Q1. What is ADO.Net? Explain its architecture.
ADO.NET provides a bridge between the front-end controls and the back-end database. The ADO.NET
objects encapsulate all the data access operations and the controls interact with these objects to
display data, thus hiding the details of movement of data.
ADO.NET consist of a set of Objects that expose data access services to the .NET environment. It is a
data access technology from Microsoft .Net Framework, which provides communication between
relational and non-relational systems through a common set of components.
System.Data namespace is the core of ADO.NET and it contains classes used by all data providers.
ADO.NET is designed to be easy to use, and Visual Studio provides several wizards and other features
that we can use to generate ADO.NET data access code.

The two key components of ADO.NET are Data Providers and DataSet. The Data Provider classes are
meant to work with different kinds of data sources. They are used to perform all data-management
operations on specific databases. DataSet class provides mechanisms for managing data when it is
disconnected from the data source.
Data Providers
The .Net Framework includes mainly three Data Providers for ADO.NET. They are the Microsoft SQL
Server Data Provider, OLEDB Data Provider and ODBC Data Provider. SQL Server uses the
SqlConnection object , OLEDB uses the OleDbConnection Object and ODBC uses OdbcConnection
Object respectively
A data provider contains Connection, Command, DataAdapter, and DataReader objects. These four
objects provide the functionality of Data Providers in the ADO.NET.
DataSet
DataSet provides a disconnected representation of result sets from the Data Source, and it is
completely independent from the Data Source. DataSet provides much greater flexibility when
dealing with related Result Sets.
Q2. List and Explain ADO .NET objects.
ADO.NET includes many objects we can use to work with data. Some important objects of ADO.NET
are:
Connection
81
 To interact with a database, we must have a connection to it. The connection helps identify the
database server, the database name, user name, password, and other parameters that are required
for connecting to the data base.
A connection object is used by command objects so they will know which database to execute the
command on.
Command
The command object is one of the basic components of ADO .NET. The Command Object uses the
connection object to execute SQL queries.
The queries can be in the Form of Inline text, Stored Procedures or direct Table access. An important
feature of Command object is that it can be used to execute queries and Stored Procedures with
Parameters If a select query is issued, the result set it returns is usually stored in either a DataSet or a
DataReader object.
DataReader
Many data operations require that we only get a stream of data for reading. The data reader object
allows us to obtain the results of a SELECT statement from a command object. For performance
reasons, the data returned from a data reader is a fast forward-only stream of data.
This means that we can only pull the data from the stream in a sequential manner this is good for
speed, but if we need to manipulate data, then a DataSet is a better object to work with.
DataSet
DataSet objects are in-memory representations of data. They contain multiple Datatable objects,
which contain columns and rows, just like normal database tables. We can even define relations
between tables to create parent-child relationships.
The DataSet is specifically designed to help manage data in memory and to support disconnected
operations on data, when such a scenario make sense.
DataAdapter
The data adapter fills a DataSet object when reading the data and writes in a single batch when
persisting changes back to the database. A data adapter contains a reference to the connection
object and opens and closes the connection automatically when reading from or writing to the
database.
Q3. Explain Command Class and DataAdapter class with properties and methods
Command class properties and methods

82
Q4. Differentiate between DataSet and DataReader.

83
Q5. Explain the difference between DataReader and DataAdapter in ADO.NET.
DataReader
 DataReader is connection oriented architecture.
 DataReader is like a forward only recordset.
 It fetches one row at a time so very less network cost compare to DataSet (Fetches all the rows at a
time).
 DataReader is readonly so we can't do any update or transaction on them.
 DataReader will be the best choice where we need to show the data to the user which requires no
transaction.
 As DataReader is forward only so we can't fetch data randomly.
 .NET Data Providers optimizes the DataReader to handle huge amount of data.
 Performance is good.
DataAdapter
 DataAdapter acts as a bridge between DataSet and database.
 DataAdapter object is used to read the data from the database and bind that data to dataset.
 DataAdapter is a disconnected oriented architecture.
 DataAdapter resolves the changes made to the DataSet back to the database.

Q6. What is DataReader in ADO.NET? Explain with example.

DataReader provides an easy way for the programmer to read data from a database as if it were
coming from a stream. The DataReader is the solution for forward streaming data through ADO.NET.
DataReader is also called a firehose cursor or forward read-only cursor because it moves forward
through the data. The DataReader not only allows us to move forward through each record of
database, but it also enables us to parse the data from each column. The DataReader class represents
a data reader in ADO.NET.
Example:
In below example we read the all records from customer table using DataReader class.
string SQL = "SELECT * FROM Customers";
SqlConnection conn = new SqlConnection(ConnectionString);
// create a command object
SqlCommand cmd = new SqlCommand(SQL, conn);
conn.Open();
// Call ExecuteReader to return a DataReader
SqlDataReader reader = cmd.ExecuteReader();
Console.WriteLine("customer ID, Contact Name, " + "Contact Title, Address ");
Console.WriteLine("=============================");
while (reader.Read())
{
Console.Write(reader["CustomerID"].ToString() + ", ");
Console.Write(reader["ContactName"].ToString() + ", ");
Console.Write(reader["ContactTitle"].ToString() + ", ");
Console.WriteLine(reader["Address"].ToString() + ", ");
}
//Release resources
reader.Close();
conn.Close();

84
Q7. What is data binding? Its types.
Data binding in ASP.NET is superficially similar to data binding in the world of desktop or client/server
applications, but in truth, it's fundamentally different. In those environments, data binding involves
creating a direct connection between a data source and a control in an application window.
If the user modifies the data in a data-bound control, our program can update the corresponding
record in the database, but nothing happens automatically.
ASP.NET data binding is much more flexible than old-style data binding. Many of the most powerful
data binding controls, such as the GridView and DetailsView, give us unprecedented control over the
presentation of our data, allowing us to format it, change its layout, embed it in other ASP.NET
controls, and so on.
Types of ASP.NET Data Binding
Two types of ASP.NET data binding exist: single-value binding and repeated-value binding. Single-
value data binding is by far the simpler of the two, whereas repeated-value binding provides the
foundation for the most advanced ASP.NET data controls.
Single-Value, or "Simple," Data Binding
We can use single-value data binding to add information anywhere on an ASP.NET page. We can even
place information into a control property or as plain text inside an HTML tag.
Single-value data binding doesn't necessarily have anything to do with ADO.NET. Instead, single-value
data binding allows us to take a variable, a property, or an expression and insert it dynamically into a
page.
Repeated-Value, or "List," Binding
Repeated-value data binding allows us to display an entire table (or just a single field from a table).
Unlike single-value data binding, this type of data binding requires a special control that supports it.
Typically, this will be a list control such as CheckBoxList or ListBox, but it can also be a much more
sophisticated control such as the GridView.
Q8. What is a Data source? Explain various types of data sources in ASP.NET.
The Data source control connects to and retrieves data from a data source and makes it available for
other controls to bind to, without requiring code. ASP.NET allows a variety of data sources such as a
database, an XML file, or a middle-tier business object.
The common data source controls are:
 AccessDataSource – Enables you to work with a Microsoft Access database.
 XmlDataSource – Enables you to work with an XML file.
 SqlDataSource – Enables you to work with Microsoft SQL Server, OLE DB, ODBC, or Oracle
databases.
 ObjectDataSource – Enables you to work with a business object or other class
 SiteMapDataSource – Used for ASP.NET site navigation.
 EntityDataSource - Enables you to bind to data that is based on the Entity Data Model.
 LinqDataSource – Enables you to use Language-Integrated Query (LINQ) in an ASP.NET Web page.

Q9. Explain SqlDataSource in ADO.NET.

The SqlDataSource control represents a connection to a relational database such as SQL Server or
Oracle database, or data accessible through OLEDB or Open Database Connectivity (ODBC).
Connection to data is made through two important properties ConnectionString and ProviderName.
The following code snippet provides the basic syntax of the control:
<asp:SqlDataSource runat="server" ID="MySqlSource"

85
 ConnectionString='<%$ ConnectionStrings:Empconstr %>'
SelectionCommand= "SELECT * FROM EMPLOYEES" />

The following table provides the related sets of properties of the SqlDataSource control, which
provides the programming interface of the control:

The following code snippet shows a data source control enabled for data manipulation:
<asp:SqlDataSource runat="server" ID= "MySqlSource"
ConnectionString=' <%$ ConnectionStrings:Empconstr %>'
SelectCommand= "SELECT * FROM EMPLOYEES"
UpdateCommand= "UPDATE EMPLOYEES SET LASTNAME=@lame"
DeleteCommand= "DELETE FROM EMPLOYEES WHERE EMPLOYEEID=@eid"
FilterExpression= "EMPLOYEEID > 10">
.....
.....
</asp:SqlDataSource>
Q10. What is a GridView control? Explain how to enable row selection, paging and sorting features of
GridView.
The GridView control displays the values of a data source in a table. Each column represents a field,
while each row represents a record. The GridView control supports the following features:
 Binding to data source controls, such as SqlDataSource.
 Built-in sort capabilities.
 Built-in update and delete capabilities.
 Built-in paging capabilities.
 Built-in row selection capabilities.
 Multiple key fields.
 Multiple data fields for the hyperlink columns.
 Customizable appearance through themes and styles

Sorting allows the user to sort the items in the GridView control with respect to a specific column by
clicking on the column's header. To enable sorting, set the AllowSorting property to true.
AllowSorting="True"

86
 Instead of displaying all the records in the data source at the same time, the GridView control can
automatically break the records up into pages. To enable paging, set the AllowPaging property to
true.
AllowPaging="True"
Also we can set how many rows we want to see in a page.
PageSize="4"
Example:
<asp:gridview AllowSorting="true" AllowPaging="true" PageSize="5" ID="Gridview1" runat="server"
DataKeyNames="pid" DataSourceID="SqlDS" >
<Columns>
<asp:BoundField DataField="pname" HeaderText="PRODUCT NAME"
SortExpression="pname"></asp:BoundField>
</Columns>
</asp:gridview>
Q11. Write a program which display all records from table to GridView
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
{
cmd.Connection = con;
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
Q12. Explain DetailsView Control.
 DetailsView control is a data-bound control that renders a single record at a time. It can provide
navigation option also. DetailsView control supports the edit, insert, delete and paging functionality.
 It can insert, update and delete the record also. When it is rendered on the page, generally it is
implemented through <table> HTML tag.
 The DetailsView control supports exactly the same fields as the GridView control:
BoundField: Enables us to display the value of a data item as text.
CheckBoxField: Enables us to display the value of a data item as a check box.
CommandField: Enables us to display links for editing, deleting, and selecting rows.
ButtonField: Enables us to display the value of a data item as a button.
HyperLinkField: Enables us to display the value of a data item as a link.
ImageField: Enables us to display the value of a data item as an image.
TemplateField: Enables us to customize the appearance of a data item.
Properties of DetailsView:

Properties Description

87
 AllowPaging Gets or sets a value indicating whether the paging feature is enabled.
DataSource Gets or sets the object from which the data-bound control retrieves its
list of data items.
DataSourceID Gets or sets the ID of the control from which the data-bound control
retrieves its list of data items.
AutoGenerateEditButton Gets or sets a value indicating whether the built-in controls to edit the
current record are displayed in a DetailsView control.
AutoGenerateDeleteButton Gets or sets a value indicating whether the built-in control to delete
the current record is displayed in a DetailsView control.
AutoGenerateRows Gets or sets a value indicating whether row fields for each field in the
data source are automatically generated and displayed in
a DetailsView control.
DefaultMode Get or sets the default data-entry mode of the DetailsView control.
SqlConnection conn;
SqlDataAdapter adapter;
DataSet ds;
SqlCommand cmd;
string cs = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
protected void PopulateDetailView()
{
try
{
conn = new SqlConnection(cs);
adapter = new SqlDataAdapter("select * from tblEmps", conn);
ds = new DataSet();
adapter.Fill(ds);
DetailsView1.DataSource = ds;
DetailsView1.DataBind();
}
catch (Exception ex)
{
Label1.Text = "ERROR :: " + ex.Message;
}
}
protected void DetailsView1_PageIndexChanging(object sender, DetailsViewPageEventArgs e)
{
DetailsView1.PageIndex = e.NewPageIndex;
PopulateDetailView();
}
protected void DetailsView1_ModeChanging(object sender, DetailsViewModeEventArgs e)
{
DetailsView1.ChangeMode(e.NewMode);
PopulateDetailView();
}

88
Q13. Briefly explain FormView control. How is it different from DetailsView?

 Like DetailsView, FormView also displays a single record from the data source at a time. Both
controls can be used to display, edit, insert and delete database records but one at a time. Both have
paging feature and hence support backward and forward traversal.
 FormView is a new data-bound control that is nothing but a templated version of DetailsView
control. The major difference between DetailsView and FormView is, here user need to define the
rendering template for each item.
 The FormView control provides more formatting and layout options than DetailsView.
 The DetailsView control uses <BoundField> elements or <TemplateField> elements to display bound
data whereas FormView can use only templates to display bound data.
 The FormView control renders all fields in a single table row whereas the DetailsView control displays
each field as a table row.
 When compare to DetailsView, the FormView control provides more control over the layout.
Following are some important properties that are very useful.

Q14. What is the difference between ListView and Gridview control? Explain the ListView control.
ListView presents the data in rows and columns just like a GridView control. The main difference
between the ListView control and Gridview control is that the ListView control includes an additional
row for inserting a new row into the table.
ListView Control:
The ListView control displays columns and rows of data and allows sorting and paging. It is by far the
most popular data display control and is ideal for understanding how data display controls interact
with data retrieval controls and code.
ListView provides various templates which we can use to display the data. The templates are:
o LayoutTemplate
o ItemTemplate
o ItemSeparatorTemplate
o GroupTemplate
o GroupSeparatorTemplate
o EmptyItemTemplate
o EmptyDataTemplate
o SelectedItemTemplate

89
 o AlternatingItemTemplate
o EditItemTemplate
o InsertItemTemplate
Example:
<form id="form1" runat="server">
<asp:ListView ID="ListView1" runat="server" DataSourceID="SqlDataSource1">
</asp:ListView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [Customer]"></asp:SqlDataSource>
</form>

Q15. What is the application services provided in ASP.NET? Explain.

ASP.NET 4 ships with a number of application services, of which the most important ones are:
Membership: Enables us to manage and work with user accounts in our system.
Roles: Enables us to manage the roles that your users can be assigned to.
Profile: Enables us to store user-specific data in a back-end database.
Figure below gives an overview of these services and shows how they are related to our web site and
the underlying data stores that the services may use.

A provider is software that provides a standardized interface between a service and a data
source. ASP.NET providers are as follows:
 Membership
 Role management

90
  Site map
 Profile
 Session state etc.
At the top of the diagram you see the ASP.NET 4 web sites and web applications that represent the
web sites that you build. These web sites can contain controls like the login controls that in turn can
talk to the ASP.NET application services such as membership and profile. To create a flexible solution,
these services don’t talk to an underlying data source directly, but instead talk to a configured
provider.
A provider is an interchangeable piece of software that is designed for a specific task. For example, in
the case of the membership services, the membership provider is designed to work with users in the
underlying data store. You can configure different providers for the same application service
depending on your needs.

91