ADO.Net
Interview Question and Answers
1. What is
Ado.NET?
ADO.NET is an object-oriented set of
libraries that allows you to interact with data sources. ADO.NET is a set of classes that expose data
access services to the .NET programmer. ADO.NET is also a part of the .NET
Framework. ADO.NET is used to handle
data access.
2. What are
the two fundamental objects in ADO.NET?
There are two fundamental objects in
ADO.NET. Datareader - connected architecture and Dataset - disconnected
architecture.
3. What are the data access namespaces in .NET?
The most common data access namespaces :
System.Data System.Data.OleDb System.Data.SQLClient System.Data.SQLTypes
System.Data.XML
4. What are
major difference between classic ADO and ADO.NET?
In ADO the in-memory representation of data
is the recordset.A Recordset object is used
to hold a set
of records from a database table.In ADO.NET we have dataset.A DataSet is an in
memory representation of data loaded from any data source.
5. what is the use of connection object in
ado.net?
The ADO Connection Object is used to create
an open connection to a data source.
Through this
connection, you can access and manipulate a database.
6. What are
the benefits of ADO.NET?
Scalability
Data Source Independence
Interoperability
Strongly Typed Fields
Performance
7. What is a
Clustered Index?
The data rows are stored in order based on
the clustered index key. Data stored is in a
sequence of
the index. In a clustered index, the physical order of the rows in the table is
the
same as the
logical (indexed) order of the key values. A table can contain only one
clustered
index. A
clustered index usually provides faster access to data than does a
non-clustered index.
8. What is a Non-Clustered Index?
The data rows are not stored in any
particular order, and there is no particular order to the
sequence of
the data pages. In a clustered index, the physical order of the rows in the
table is
not same as
the logical (indexed) order of the key values.
9. Whate are
different types of Commands available with DataAdapter ?
The SqlDataAdapter has
SelectCommand
InsertCommand
DeleteCommand
UpdateCommand
10. What is the
difference between an ADO.NET Dataset and an ADO Recordset?
Dataset can fetch source data from many
tables at a time, for Recordset you can achieve the same only using the SQL
joins. A DataSet can represent an entire relational database in memory,
complete with tables, relations, and views, A Recordset can not.
A DataSet is designed to work without any
continues connection to the original data source;
Recordset
maintains continues connection with the original data source. DataSets have
no current record pointer, you can use For Each loops to move through the data.
Recordsets have pointers to move through them.
11. Which
method do you invoke on the DataAdapter control to load your generated dataset
with data?
DataAdapter’ fill () method is used to fill
load the data in dataset.
12. What are
the different methods available under sqlcommand class to access the data?
ExecuteReader - Used where one or more
records are returned - SELECT Query.
ExecuteNonQuery - Used where it affects a
state of the table and no data is being
queried
- INSERT, UPDATE, DELETE, CREATE and SET
queries.
ExecuteScalar - Used where it returns a
single record.
13 .Explain the namespaces in which .NET has the
data functionality class.
System.data contains basic objects. These
objects are used for accessing and storing
relational
data. Each of these is independent of the type of data source and the way
we
connect to
it.
These objects are:
DataSet
DataTable
DataRelation.
System.Data.OleDB objects are used to connect
to a data source via an OLE-DB provider.
These objects have the same properties, methods, and events as the
SqlClient equivalents. A few of the object providers are:
OleDbConnection
OleDbCommand
System.Data.SqlClient objects are used to
connect to a data source via the Tabular Data
Stream (TDS)
interface of only Microsoft SQL Server. The intermediate layers required by an
OLE-DB connection are removed in this. This provides better performance.
System.XML contains the basic objects required
to create, read, store, write, and manipulate
XML documents
according to W3C recommendations.
14. Overview of ADO.NET architecture.
Data Provider provides objects through which
functionalities like opening and closing
connection,
retrieving and updating data can be availed. It also provides access to data
source like SQL Server, Access, and Oracle). Some of the data provider objects
are: Command object which is used to store procedures. Data Adapter which is a
bridge between datastore and dataset. Datareader which reads data from data
store in forward only mode.
A dataset object is not in directly connected
to any data store. It represents disconnected and
cached data.
The dataset communicates with Data adapter that fills up the dataset.
Dataset
can have one
or more Datatable and relations. DataView object is used to sort and filter
data in Datatable.
ADO.NET provides access to all kind of data
sources such as Microsoft SQL Server, OLEDB,
Oracle, XML.
ADO.NET separates out the data access and data
manipulation componenets. ADO.NET
includes some
providers from the .NET Framework to connect to the database, to execute
commands, and
finally to retrieve results. Those results are either directly used or can be
put
in dataset
and manipulate it.
14. Define connected and disconnected data access
in ADO.NET
Data reader is based on the connected
architecture for data access. Does not allow data
manipulation
Dataset supports disconnected data access
architecture. This gives better performance results.Define connected and
disconnected data access in ADO.NET Data reader is based on the connected
architecture for data access. Does not allow data manipulation
Dataset
supports disconnected data access architecture. This gives better performance
results.
15.Describe
CommandType property of a SQLCommand in ADO.NET.
CommandType
is a property of Command object which can be set to Text, Storedprocedure.
If it is
Text, the command executes the database query. When it is StoredProcedure, the
command runs
the stored procedure. A SqlCommand is an object that allows specifying what
is to be
performed in the database.
Access
database at runtime using ADO.NET
SqlConnection
sqlCon = new SqlConnection(connectionString)
sqlCon.Open();
string
strQuery = "select CategoryName from abcd";
SqlCommand
cmd = new SqlCommand(strQuery, conn);
SqlDataReader
reader = cmd.ExecuteReader();
while
(reader.Read())
{
Console.WriteLine(reader [0]);
}
reader.Close();
con.Close();
16. Difference between dataset and datareader.
Dataset
DataSet
object can contain multiple rowsets from the same data source as well as from
the
relationships
between them Dataset is a disconnected architecture Dataset can persist data.
Datareader
DataReader
provides forward-only and read-only access to data.Datareader is connected
architecture Datareader can not persist data.
Difference
between dataset and datareader.
Dataset
a.
Disconnected
b. Can
traverse data in any order front, back.
c. Data can
be manipulated within the dataset.
d. More
expensive than datareader as it stores multiple rows at the same time.
Datareader
a. Connection
needs to be maintained all the time
b. Can
traverse only forward.
c. It is read
only therefore, data cannot be manipulated.
d. It is less
costly because it stores one row at a time
17. Command objects uses, purposes and their
methods.
The command objects are used to connect to
the Datareader or dataset objects with the
help of the
following methods:
ExecuteNonQuery:
This method
executes the command defined in the CommandText property.The connection used is
defined in the Connection property for a query.It returns an Integer indicating
the number of rows affected by the query.
ExecuteReader:
This method
executes the command defined in the CommandText property.The connection used is
defined in the Connection property. It returns a reader object that is
connected to the resulting rowset within the database, allowing the rows to be
retrieved.
ExecuteScalar:
This method
executes the command defined in the CommandText property.The connection used is
defined in the Connection property. It returns a single value which is the
first column of the first row of the resulting rowset.The rows of the rest of
the result are discarded. It is fast and efficient in cases where a singleton
value is required.
Command
objects uses, purposes and their methods. Command objects are used to execute
the queries, procedures. Sql statements etc. It can execute stored procedures
or queries that use parameters as well.
It works on
the basis of certain properties like ActiveConnection, CommandText, CommandType,
Name etc.
Command
object has three methods:
a) Execute:
executes the queries, stored procedures etc.
b) Cancel: stops
the method execution
c)
CreateParameter: to create a parameter object
18. Explain the use of data adapter.
The data
adapter objects connect a command objects to a Dataset object. They provide the
means for the exchange of data between the data store and the tables in the DataSet.
An
OleDbDataAdapter object is used with an OLE-DB provider A SqlDataAdapter object
uses Tabular Data Services with MS SQL Server.
19.Explain the
use of data adapter.
Data adapters
are the medium of communication between datasource like database and dataset.
It allows activities like reading data, updating data.
20. What are basic methods of Dataadapter?
The most
commonly used methods of the DataAdapter are:
Fill:
This method
executes the SelectCommand to fill the DataSet object with data from the data
source.
Depending on
whether there is a primary key in the DataSet, the ‘fill’ can also be used to
update an
existing table in a DataSet with changes made to the data in the original
datasource.
FillSchema
This method
executes the SelectCommand to extract the schema of a table from the data
source.
It creates an
empty table in the DataSet object with all the corresponding constraints.
Update
This method
executes the InsertCommand, UpdateCommand, or DeleteCommand to update
the original
data source with the changes made to the content of the DataSet.
21.Explain the
basic methods of Dataadapter
Some basic
methods of dataadpater are:
a. Fill: adds
or updates the rows to dataset from the datasource
b.
FillSchema: adds a datatable with the sameschema as in the datasource
c. Update: it
calls insert, update, delete sql commands for transferring all the changes from
the
dataset to
the datasource
d. Dispose :
releases all the resources
22. What is Dataset object? Explain the various
objects in Dataset?
The DataSet
object is a disconnected storage.It is used for manipulation of relational
data. The DataSet is filled with data from the storeWe fill it with data
fetched from the data store. Once the work is done with the dataset, connection
is reestablished and the changes are reflected back into the store.
Dataset has a
collection of Tables which has DataTable collection which further has DataRow
DataColumn
objects collections.
It also has
collections for the primary keys, constraints, and default values called as
constraint
collection. A
DefaultView object for each table is used to create a DataView object based on
the table, so that the data can be searched, filtered or otherwise manipulated
while displaying the data.
23.What is
Dataset object? Explain the various objects in Dataset.
The DataSet
object represents imported or linked data from an application e.g. manually created
Pushpins etc. This object is invalidated once the application is closed.
Dataset contains another
collection known as DataTable object. Each DataTable contains a collection of DataRow
objects and each DataRow is a collection of DataColumn objects.
24. What are
the steps involved to fill a dataset?
The DataSet
object is a disconnected storage.It is used for manipulation of relational
data. The DataSet is filled with data from the store We fill it with data
fetched from the data store. Once the work is done with the dataset, connection
is reestablished and the changes are reflected back into the store.
What are the
steps involved to fill a dataset?
a. Create a
connection object.
b. Create an
adapter by passing the string query and the connection object as parameters.
c. Create a
new object of dataset.
d. Call the
Fill method of the adapter and pass the dataset object.
Example:
VB.NET Code:-
Dim strSQL as
String
strSQL =
"SELECT * from tbl"
Dim sqlCmd As
New SqlCommand(strSQL, sqlConn)
Dim sda As
New SqlDataAdapter(sqlCmd)
Dim ds As New
DataSet
sda.Fill(ds)
24. How can
we check that some changes have been made to dataset since it was loaded?
The changes
made to the dataset can be tracked using the GetChanges and HasChanges
methods.The
GetChanges returns dataset which are changed since it was loaded or since
Acceptchanges
was executed. The HasChanges property indicates if any changes were made to the
dataset since it was loaded or if acceptchanges method was executed.The
RejectChanges can be used to revert thee changes made to the dataset since it
was
loaded.
How can we
check that some changes have been made to dataset since it was loaded?
a.
GetChanges: gives the dataset that has changed since newly loaded or since
Accept
changes has
been executed.
b.
HasChanges: this returns a status that tells if any changes have been made to
the dataset
since accept
changes was executed.
25. How can we add/remove row’s in “DataTable”
object of “DataSet”?
‘NewRow’
method is provided by the ‘Datatable’ to add new row to it. ‘DataTable’ has DataRowCollection”
object which has all rows in a “DataTable” object. Add method of the
DataRowCollection is used to add a new row in DataTable.We fill it with data
fetched from the data store. Once the work is done with the dataset, connection
is reestablished Remove method of the DataRowCollection is used to remove a ‘DataRow’
object from
‘DataTable’. RemoveAt
method of the DataRowCollection is used to remove a ‘DataRow’ object from DataTable’ per the index specified in the
DataTable.
26.How can we
add/remove row’s in “DataTable” object of “DataSet”?
Datatable has
a “NewRow” method that allows addition of a new row to the datatable.
27. How do we
use stored procedure in ADO.NET and how do we provide parameters to the stored
procedures?
CREATE
PROCEDURE RUPDATE (@RID INTEGER, @RDESC NCHAR(50))
AS
SET NOCOUNT
OFF
UPDATE Region
SET RDESC =
@RDESC
SqlCommand
command = new SqlCommand("RUPDATE",con);
command.CommandType
= CommandType.StoredProcedure;
command.Parameters.Add(new
SqlParameter("@RID",SqlDbType.Int,0,"RID"));
command.Parameters.Add(new
SqlParameter("@RDESC",SqlDbType.NChar,50,"RDESC"));
command.Parameters[0].Value=4;
command.Parameters[1].Value="SouthEast";
int
i=command.ExecuteNonQuery();
How do we use
stored procedure in ADO.NET and how do we provide parameters to the
stored
procedures?
SqlCommand
cmd1 = new SqlCommand("Employee", conn); Employee is the name of the
stored
procedure
cmd.CommandType
= CommandType.StoredProcedure;
cmd.Parameters.Add(new
SqlParameter("@empID", empId));
28. Explain
the basic use of “DataView” and explain its methods.
A DataView is a representation of a full table
or a small section of rows.It is used to sort and find data within Datatable.
Following are
the methods of a DataView:
Find :
Parameter: An array of values; Value Returned: Index of the row
FindRow :
Parameter: An array of values; Value Returned: Collection of DataRow
AddNew : Adds
a new row to the DataView object.
Delete :
Deletes the specified row from DataView object
29.Explain the
basic use of “DataView” and explain its methods.
It is a customizable
view of a datatable that allows filtering, searching, editing, navigation.
DataView
method:
a. AddNew: To
add a new row to the DataView.
b. BeginInit:
Begins the initialization of a DataView.
c. CopyTo:
items are copied into an array.
d. Delete:
Used to delete a row at the specified position.
e. Dispose:
Releases the resources.
f. EndInit:
Ends the initialization of a DataView.
g. Equals:
Compares object instances are equal or not.
h. Find:
Finds a specific row in the DataView.
i. FindRows:
Returns an array of DataRowView
j.
GetEnumerator: Gets an enumerator.
k.
GetHashCode: it is used in hashing algorithms.
l. GetService
: fetches the implementer of the IServiceProvider.
m. GetType :
fetches the current instance type.
n. ToString :
Returns a String.
o. Close:
Used to close a DataView.
p.
ColumnCollectionChanged: Occurs after a DataColumnCollection is successfully
changed
q. Dispose:
can be overloaded
r. Finalize:
Used to free resources and cleanup before objects are handeled by garbage
collection.
s.
IndexListChanged: This event tab=kes place when DataView has been changed
successfully.
t.
MemberwiseClone : Creates a shallow copy.
u.
OnListChanged: Raises the ListChanged event.
v. Open: To
open a DataView.
30. Differences between “DataSet” and
“DataReader”.
Dataset
DataSet
object can contain multiple rowsets from the same data source as well as from
the
relationships
between them Dataset is a disconnected architecture
Dataset can
persist data.A DataSet is well suited for data that needs to be retrieved from
multiple tables.Due to overhead DatsSet is slower than DataReader.
Datareader
DataReader
provides forward-only and read-only access to data.Datareader is connected
architecture. It has live connection while reading data Datareader can not
persist data.Speed performance is better in DataReader.
31. Explain how to load multiple tables in a
DataSet.
MyDataSet myds = new MyDataSet();
SqlDataAdapter
myda = new SqlDataAdapter ("procId", this.Connection);
myda.SelectCommand.CommandType
= CommandType.StoredProcedure;
myda.SelectCommand.Parameters.AddWithValue
("@pId", pId);
myda.TableMappings.Add
("Table", myds.xval.TableName);
myda.Fill
(myds);
ADO.NET Code
showing Dataset storing multiple tables.
DataSet ds =
new DataSet();
ds.Tables.Add(dt1);
ds.Tables.Add(dt2);
ds.Tables.Add(dtn);
32. What is the use of CommandBuilder?
CommandBuilder
builds “Parameter” objects automatically.
Example:
Dim
pobjCommandBuilder As New OleDbCommandBuilder(pobjDataAdapter)
pobjCommandBuilder.DeriveParameters(pobjCommand)
If
DeriveParameters method is used, an extra trip to the Datastore is madewhich
can highly
affect the
efficiency.
33.What is the
use of CommandBuilder?
CommandBuilder
is used to build complex queries. It can even build commands that are based
on the
returned results. CommandBuilder is less error prone and more readable than the
command object.
34. What’s
difference between “Optimistic” and “Pessimistic” locking?
In
pessimistic locking, when a user opens a data to update it, a lock is granted.
Other users
can only view
the data until the whole transaction of the data update is completed.In
optimistic locking, a data is opened for updating by multiple users. A lock is
granted only during the update transaction and not for the entire session. Due
to this concurrency is increased and is a practical approach of updating the
data.
What’s
difference between “Optimistic” and “Pessimistic” locking?
Pessimistic
locking: On updating the data, the record gets locked and no one else can
access
that record
for updating. It becomes a read-only record till the lock is released. Once the
lock
gets
released, the record can be locked again and get updated for a different user.
Optimistic
locking: This allows multiple user to open up the same record for updation .
Record
gets locked
only while updating the record. This is the most preferred way of locking for
the
web
application.
35. How can
we perform transactions in .NET?
Following are
the general steps that are followed during a transaction:
Open
connection
Begin
Transaction: the begin transaction method provides with a connection object
this can be
used to
commit or rollback.
Execute the
SQL commands
Commit or
roll back
Close the
database connection
36.How can we
perform transactions in .NET?
Steps to
perform a transaction
a. Call the Begin
Transaction. This marks the beginning of the transaction.
b. Assign the
Transaction object returned by Begin Transaction to the Transaction property of
the
SqlCommand.
c. Execute
the command.
d. Call the
Commit method from SqlTransaction object to save the changes made to the data
through the
transaction. Call Rollback undo all the transaction which belong to this
transaction.
37. What is
connection pooling and what is the maximum Pool Size in ADO.NET Connection
String?
A connection
pool is created when a connection is opened the first time. The next time a
connection is
opened, the connection string is matched and if found exactly equal, the
connection
pooling would work.Otherwise, a new connection is opened, and connection
pooling won't be used.Maximum pool size is the maximum number of connection
objects to be pooled.
If the
maximum pool size is reached, then the requests are queued until some
connections are
released back
to the pool. It is therefore advisable to close the connection once done with
it.
What is
connection pooling and what is the maximum Pool Size in ADO.NET Connection
String?
Connection
pooling is a method of reusing the active database connections instead of
creating
new ones
every time the user request one. Connection pool manager keeps track of all the
open
connections. When a new request comes in, the pool manager checks if there
exists any
unused
connections and returns one if available. If all connections are busy and the
maximum
pool size has
not been reached, a new connection is formed and added to the pool. And if the
max pool size
is reached, then the requests gets queued up until a connection in the pool
becomes
available or the connection attempt times out.
Connection
pooling behavior is controlled by the connection string parameters. The
following
are four
parameters that control most of the connection pooling behavior:
Default
max pool size is 100.
38. Can you
explain how to enable and disable connection pooling?
Set
Pooling=true. However, it is enabled by default in .NET.
To disable
connection pooling set Pooling=false in connection string if it is an ADO.NET
Connection.
If it is an
OLEDBConnection object set OLEDB Services=-4 in the connection string.
Can you
explain how to enable and disable connection pooling?
To enable
connection pooling:
SqlConnection
myConnection = new SqlConnection(@"Data Source=(local)
\SQLEXPRESS;Initial
Catalog=TEST;Integrated Security=SSPI;");
This has
connection pooling on by default
To disable
connection pooling:
SqlConnection
myConnection = new SqlConnection(@"Data Source=(local)
\SQLEXPRESS;Initial
Catalog=TEST;Integrated Security=SSPI;Pooling=false;");
