What is the namespace in which .NET has the data

functionality classes ?

Following are the namespaces provided by .NET for data management :-

This contains the basic objects used for accessing and storing relational data, such as

DataSet,DataTable, and DataRelation. Each of these is independent of the type of data

source and the way we connect to it.


It contains the objects that we use to connect to a data source via an OLE-DB provider,

such as OleDbConnection, OleDbCommand, etc. These objects inherit from the common

base classes, and so have the same properties, methods, and events as the SqlClient



This contains the objects that we use to connect to a data source via the Tabular Data

Stream (TDS) interface of Microsoft SQL Server (only). This can generally provide better

performance as it removes some of the intermediate layers required by an OLE-DB



This Contains the basic objects required to create, read, store, write, and manipulate

XML documents according to W3C recommendations.

(B) Can you give a overview of ADO.NET architecture ?

The most important section in ADO.NET architecture is “Data Provider”. Data Provider

provides access to datasource (SQL SERVER, ACCESS, ORACLE).In short it provides

object to achieve functionalities like opening and closing connection, retrieve data and

update data. In the below figure you can see the four main sections of a data provider :-


Command object (This is the responsible object to use stored procedures)

Data Adapter (This object acts as a bridge between datastore and dataset).

Datareader (This object reads data from data store in forward only mode).

Dataset object represents disconnected and cached data. If you see the diagram it is not

in direct connection with the data store (SQL SERVER, ORACLE etc) rather it talks

with Data adapter, who is responsible for filling the dataset. Dataset can have one or

more Datatable and relations.

Figure :- 9.1 ADO.NET Architecture


“DataView” object is used to sort and filter data in Datatable.

Note:- This is one of the favorite questions in .NET. Just paste the picture in your mind

and during interview try to refer that image.

(B)What are the two fundamental objects in ADO.NET ?

Datareader and Dataset are the two fundamental objects in ADO.NET.

(B)What is difference between dataset and datareader ?

Following are some major differences between dataset and datareader :-

DataReader provides forward-only and read-only access to data, while the

DataSet object can hold more than one table (in other words more than one

rowset) from the same data source as well as the relationships between them.

Dataset is a disconnected architecture while datareader is connected


Dataset can persist contents while datareader can not persist contents, they

are forward only.

(I)What are major difference between classic ADO and


Following are some major differences between both

As in classic ADO we had client and server side cursors they are no more

present in ADO.NET. Note it’s a disconnected model so they are no more


Locking is not supported due to disconnected model.

All data persist in XML as compared to classic ADO where data

persisted in Binary format also.

(B)What is the use of connection object ?

They are used to connect a data to a Command object.

An OleDbConnection object is used with an OLE-DB provider


A SqlConnection object uses Tabular Data Services (TDS) with MS SQL Server

(B)What is the use of command objects and what are the

methods provided by the command object ?

They are used to connect connection object to Datareader or dataset. Following are the

methods provided by command object :-

ExecuteNonQuery :- Executes the command defined in the CommandText

property against the connection defined in the Connection property for a query

that does not return any row (an UPDATE, DELETE or INSERT). Returns

an Integer indicating the number of rows affected by the query.

ExecuteReader :- Executes the command defined in the CommandText property

against the connection defined in the Connection property. Returns a “reader”

object that is connected to the resulting rowset within the database, allowing

the rows to be retrieved.

ExecuteScalar :- Executes the command defined in the CommandText property

against the connection defined in the Connection property. Returns only

single value (effectively the first column of the first row of the resulting rowset)

any other returned columns and rows are discarded. It is fast and efficient

when only a “singleton” value is required

(B)What is the use of dataadapter ?

These are objects that connect one or more Command objects to a Dataset object. They

provide logic that would get data from the data store and populates the tables in the

DataSet, or pushes the changes in the DataSet back into the data store.

An OleDbDataAdapter object is used with an OLE-DB provider

A SqlDataAdapter object uses Tabular Data Services with MS SQL Server.

(B)What are basic methods of Dataadapter ?

There are three most commonly used methods of Dataadapter :-

Fill :- Executes the SelectCommand to fill the DataSet object with data from the data

source. It an also be used to update (refresh) an existing table in a DataSet with changes


made to the data in the original datasource if there is a primary key in the table in the


FillSchema :- Uses the SelectCommand to extract just the schema for a table from the

data source, and creates an empty table in the DataSet object with all the corresponding


Update:- Calls the respective InsertCommand, UpdateCommand, or DeleteCommand for

each inserted, updated,or deleted row in the DataSet so as to update the original data

source with the changes made to the content of the DataSet. This is a little like the

UpdateBatch method provided by the ADO Recordset object, but in the DataSet it can

be used to update more than one table.

(B)What is Dataset object?

The DataSet provides the basis for disconnected storage and manipulation of relational

data. We fill it from a data store,work with it while disconnected from that data store,

then reconnect and flush changes back to the data store if required.

(B)What are the various objects in Dataset ?

Dataset has a collection of DataTable object within the Tables collection. Each DataTable

object contains a collection of DataRow objects and a collection of DataColumn objects.

There are also collections for the primary keys, constraints, and default values used in

this table which is called as constraint collection, and the parent and child relationships

between the tables. Finally, there is a DefaultView object for each table. This 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.

Note :- Look back again to the main diagram for ADO.NET architecture for visualizing

this answer in pictorial form.

(B) How can we connect to Microsoft Access , Foxpro ,

Oracle etc ?

Microsoft provides System.Data.OleDb namespace to communicate with databases like

scess , Oracle etc. In short any OLE DB-Compliant database can be connected using

System.Data.OldDb namespace.


Note :- Small sample of OLEDB is provided in “WindowsAppOleDb” which uses

“Nwind.mdb” in bin directory to display data in Listbox.

Private Sub loadData()

Dim strPath As String

strPath = AppDomain.CurrentDomain.BaseDirectory

Dim objOLEDBCon As New

OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source =” &

strPath & “Nwind.mdb”)

Dim objOLEDBCommand As OleDbCommand

Dim objOLEDBReader As OleDbDataReader


objOLEDBCommand = New OleDbCommand(“Select FirstName

from Employees”)


objOLEDBCommand.Connection = objOLEDBCon

objOLEDBReader = objOLEDBCommand.ExecuteReader()

Do While objOLEDBReader.Read()



Catch ex As Exception

Throw ex



End Try

End Sub

The main heart is the “Loaddata()” method which actually loads the data in listbox.

Note:- This source code has the connectionstring hard coded in the program itself which is

not a good programming practice. For windows application the best place to store

connectionstring is “App.config”. Also note that

“AppDomain.CurrentDomain.BaseDirectory” function gives the current path of the

running exe which is “BIN” and the MDB file is in that directory. Also note that the

final block which executes irrespective that there is error or not. Thus ensuring that all the

connection to the datastore is freed. Its best practice to put all clean up statements in finally

block thus ensuring that the resources are deallocated properly.

(B) How do we connect to SQL SERVER, which namespace

do we use ?


Below is the code, after the code I have given the explanation for it. For this sample we

will also need a SQL Table setup which I have imported using the DTS wizard.

Private Sub LoadData()

‘ note :- with and end with makes your code more readable

Dim strConnectionString As String

Dim objConnection As New SqlConnection

Dim objCommand As New SqlCommand

Dim objReader As SqlDataReader


‘ this gets the connectionstring from the app.config


‘ note if this gives error see where the MDB file is

stored in your pc and point to that

strConnectionString =


‘ take the connectiostring and initialize the connection


With objConnection

.ConnectionString = strConnectionString


End With

objCommand = New SqlCommand(“Select FirstName from


With objCommand

.Connection = objConnection

objReader = .ExecuteReader()

End With

‘ looping through the reader to fill the list box

Do While objReader.Read()



Catch ex As Exception

Throw ex



End Try


<add key=”Connectionstring” value=”Server=ERMBOM1-IT2;User




Note:- The above code is provided in CD in folder WindowsAppSqlClient”. Comments in

the code do explain a lot but we will again iterate through the whole code later.

“LoadData” is the main method which loads the data from SQL SERVER. Before

running this code you have to install SQL SERVER in your machine. As we are dealing

with SQLCLIENT we need to setup database in SQL SERVER. For this sample I

have imported access “Nwind.mdb” in “SampleAccessDatabase” folder in CD in to

SQlSERVER. Depending on computer you will also have to change the connectionstring in

Web.config file.

For setting up the sample SQL table we can use the DTS import wizard to import the

table. See the below figure which is using data source as Microsoft Access.While importing

the database author had give the database name as “Employees”.

Figure:- 9.2 Loading “Nwind.mdb” in SQL SERVER for the sample


Figure :- 9.3 Load only the Employee table.

To make it simple we will only import the employee table as that is the only thing needed

in our sample code.


Figure :- 9.4 View of loaded Employee table

Now from interview point of view definitely you are not going to say the whole source

code which is given in the book. Interviewer expects only the broader answer of what are

the steps needed to connect to SQL SERVER. For fundamental sake author has explained

the whole source code. In short you have to explain the “LoadData” method in broader

way. Following are the steps to connect to SQL SERVER :-

First import the namespace “System.Data.SqlClient”.

Create a connection object as shown in “LoadData” method.

With objConnection

.ConnectionString = strConnectionString


End With

Create the command object with the SQL. Also assign the created connection

object to command object and execute the reader.


objCommand = New SqlCommand(“Select FirstName from Employees”)

With objCommand

.Connection = objConnection

objReader = .ExecuteReader()

End With

Finally loop through the reader and fill the list box. If old VB programmers are

expecting the movenext command it’s replaced by Read() which returns true

if there is any data to be read. If the .Read() return’s false that means that it’s

end of datareader and there is no more data to be read.

Do While objReader.Read()



Do not forget to close the connection object.

Note:- In “LoadData” you will see that connectionstring is stored in Web.config file and is

loaded using “AppSettings.Item(“ConnectionString”)”. While running this sample live on

your database do not forget to change this connectionstring accordingly to your machine name

and SQL SERVER or else the source code will not run.

(B) How do we use stored procedure in ADO.NET and how

do we provide parameters to the stored procedures?

ADO.NET provides the SqlCommand object which provides the functionality of executing

stored procedures.

Note :- Sample code is provided in folder “WindowsSqlClientCommand”. There are two

stored procedures created in same database “Employees” which was created for the previous


CREATE PROCEDURE SelectByEmployee @FirstName nvarchar(200) AS

Select FirstName from Employees where FirstName like @FirstName + ‘%’


Select FirstName from Employees

If txtEmployeeName.Text.Length = 0 Then

objCommand = New SqlCommand(“SelectEmployee”)


objCommand = New SqlCommand(“SelectByEmployee”)



Data.SqlDbType.NVarChar, 200)

objCommand.Parameters.Item(“@FirstName”).Value =


End If

In the above sample not much has been changed only that the SQL is moved to the

stored procedures. There are two stored procedures one is “SelectEmployee” which selects

all the employees and the other is “SelectByEmployee” which returns employee name

starting with a specific character. As you can see to provide parameters to the stored

procedures we are using the parameter object of the command object. In such question

interviewer expects two simple answers one is that we use command object to execute

stored procedures and the parameter object to provide parameter to the stored procedure.

Above sample is provided only for getting the actual feel of it. Be short be nice and get a


(B) How can we force the connection object to close after

my datareader is closed ?

Command method Executereader takes a parameter called as CommandBehavior where

in we can specify saying close connection automatically after the Datareader is close.

pobjDataReader = pobjCommand.ExecuteReader(CommandBehavior.CloseConnection)

(B) I want to force the datareader to return only schema of

the datastore rather than data ?

pobjDataReader = pobjCommand.ExecuteReader(CommandBehavior.SchemaOnly)

(B) How can we fine tune the command object when we are

expecting a single row or a single value ?

Again CommandBehaviour enumeration provides two values SingleResult and SingleRow.

If you are expecting a single value then pass “CommandBehaviour.SingleResult” and the

query is optimized accordingly, if you are expecting single row then pass

“CommandBehaviour.SingleRow” and query is optimized according to single row.

(B) Which is the best place to store connectionstring in .NET

projects ?


Config files are the best places to store connectionstrings. If it is a web-based application

“Web.config” file will be used and if it is a windows application “App.config” files will be


(B) What are the steps involved to fill a dataset ?

Twist :- How can we use dataadapter to fill a dataset ?

Sample code is provided in “WindowsDataSetSample” folder in CD.”LoadData” has all

the implementation of connecting and loading to dataset. This dataset is finally bind to a

ListBox. Below is the sample code.

Private Sub LoadData()

Dim strConnectionString As String

strConnectionString = AppSettings.Item(“ConnectionString”)

Dim objConn As New SqlConnection(strConnectionString)


Dim objCommand As New SqlCommand(“Select FirstName from


objCommand.Connection = objConn

Dim objDataAdapter As New SqlDataAdapter()

objDataAdapter.SelectCommand = objCommand

Dim objDataSet As New DataSet

End Sub

In such type of questions interviewer is looking from practical angle, that have you worked

with dataset and datadapters. Let me try to explain the above code first and then we move

to what steps should be told during interview.

Dim objConn As New SqlConnection(strConnectionString)


First step is to open the connection.Again note the connection string is loaded from

config file.

Dim objCommand As New SqlCommand(“Select FirstName from Employees”)

objCommand.Connection = objConn

Second step is to create a command object with appropriate SQL and set the connection

object to this command.

Dim objDataAdapter As New SqlDataAdapter()


objDataAdapter.SelectCommand = objCommand

Third steps is to create the Adapter object and pass the command object to the adapter



Fourth step is to load the dataset using the “Fill” method of the dataadapter.

lstData.DataSource = objDataSet.Tables(0).DefaultView

lstData.DisplayMember = “FirstName”

lstData.ValueMember = “FirstName”

Fifth step is to bind to the loaded dataset with the GUI. At this moment sample has

listbox as the UI. Binding of the UI is done by using DefaultView of the dataset. Just to

revise every dataset has tables and every table has views. In this sample we have only

loaded one table i.e. Employees table so we are referring that with an index of zero.

Just say all the five steps during interview and you will see the smile on the interviewer’s

face and appointment letter in your hand.

(B)What are the various methods provided by the dataset

object to generate XML?

Note:- XML is one of the most important leap between classic ADO and ADO.NET.

So this question is normally asked more generally how can we convert any data to XML

format. Best answer is convert in to dataset and use the below methods.


Read’s a XML document in to Dataset.


This is a function which returns the string containing XML document.


This writes a XML data to disk.

(B) How can we save all data from dataset ?


Dataset has “AcceptChanges” method which commits all the changes since last time

“Acceptchanges” has been executed.

Note :- This book does not have any sample of Acceptchanges. I leave that to readers as

homework sample. But yes from interview aspect that will be enough.

(B) How can we check that some changes have been made

to dataset since it was loaded ?

Twist :- How can we cancel all changes done in dataset ? How do we get values which are

changed in a dataset ?

For tracking down changes Dataset has two methods which comes as rescue “GetChanges

“and “HasChanges”.


Returns dataset which are changed since it was loaded or since Acceptchanges was



This property indicates that has any changes been made since the dataset was loaded or

acceptchanges method was executed.

If we want to revert or abandon all changes since the dataset was loaded use


Note:- One of the most misunderstood things about these properties is that it tracks the

changes of actual database. That is a fundamental mistake; actually the changes are related

to only changes with dataset and have nothing to with changes happening in actual database.

As dataset are disconnected and do not know anything about the changes happening in

actual database.

(B) How can we add/remove row’s in “DataTable” object of

“DataSet” ?

“Datatable” provides “NewRow” method to add new row to “DataTable”. “DataTable”

has “DataRowCollection” object which has all rows in a “DataTable” object. Following

are the methods provided by “DataRowCollection” object :-



Adds a new row in DataTable


It removes a “DataRow” object from “DataTable”


It removes a “DataRow” object from “DataTable” depending on index position of the


(B) What is basic use of “DataView” ?

“DataView” represents a complete table or can be small section of rows depending on

some criteria. It is best used for sorting and finding data with in “datatable”.

Dataview has the following method’s :-


It takes a array of values and returns the index of the row.


This also takes array of values but returns a collection of “DataRow”.

If we want to manipulate data of “DataTable” object create “DataView” (Using the

“DefaultView” we can create “DataView” object) of the “DataTable” object and use the

following functionalities :-


Adds a new row to the “DataView” object.



Deletes the specified row from “DataView” object.

(B) What is the difference between “DataSet” and

“DataReader” ?

Twist :- Why is DataSet slower than DataReader ?

Fourth point is the answer to the twist.

Note:- This is my best question and I expect everyone to answer it. It is asked almost 99%

in all companies….Basic very Basic cram it.

Following are the major differences between “DataSet” and “DataReader” :-

“DataSet” is a disconnected architecture, while “DataReader” has live

connection while reading data. If we want to cache data and pass to a

different tier “DataSet” forms the best choice and it has decent XML support.

When application needs to access data from more than one table “DataSet”

forms the best choice.

If we need to move back while reading records, “datareader” does not support

this functionality.

But one of the biggest drawbacks of DataSet is speed. As “DataSet” carry

considerable overhead because of relations, multiple tables etc speed is slower

than “DataReader”. Always try to use “DataReader” wherever possible, as

it’s meant specially for speed performance.

(B) How can we load multiple tables in a DataSet ?

objCommand.CommandText = “Table1”

objDataAdapter.Fill(objDataSet, “Table1”)

objCommand.CommandText = “Table2”

objDataAdapter.Fill(objDataSet, “Table2”)

Above is a sample code which shows how to load multiple “DataTable” objects in one

“DataSet” object. Sample code shows two tables “Table1” and “Table2” in object



lstdata.DataSource = objDataSet.Tables(“Table1”).DefaultView

In order to refer “Table1” DataTable, use Tables collection of DataSet and the Defaultview

object will give you the necessary output.

(B) How can we add relation’s between table in a DataSet ?

Dim objRelation As DataRelation





Relations can be added between “DataTable” objects using the “DataRelation” object.

Above sample code is trying to build a relationship between “Customer” and “Addresses”

“Datatable” using “CustomerAddresses” “DataRelation” object.

(B) What is the use of CommandBuilder ?

CommandBuilder builds “Parameter” objects automatically. Below is a simple code which

uses commandbuilder to load its parameter objects.

Dim pobjCommandBuilder As New OleDbCommandBuilder(pobjDataAdapter)


Be careful while using “DeriveParameters” method as it needs an extra trip to the Datastore

which can be very inefficient.

(B) What’s difference between “Optimistic” and

“Pessimistic” locking ?

In pessimistic locking when user wants to update data it locks the record and till then no

one can update data. Other user’s can only view the data when there is pessimistic locking.

In optimistic locking multiple users can open the same record for updating, thus increase

maximum concurrency. Record is only locked when updating the record. This is the most


preferred way of locking practically. Now a days browser based application is very common

and having pessimistic locking is not a practical solution.

(A) How many ways are there to implement locking in


Following are the ways to implement locking using ADO.NET :-

When we call “Update” method of DataAdapter it handles locking internally.

If the DataSet values are not matching with current data in Database it raises

concurrency exception error. We can easily trap this error using Try..Catch

block and raise appropriate error message to the user.

Define a Datetime stamp field in the table.When actually you are firing the

UPDATE SQL statements compare the current timestamp with one existing

in the database. Below is a sample SQL which checks for timestamp before

updating and any mismatch in timestamp it will not update the records. This is

the best practice used by industries for locking.

Update table1 set field1=@test where LastTimeStamp=@CurrentTimeStamp

Check for original values stored in SQL SERVER and actual changed values.

In stored procedure check before updating that the old data is same as the

current. Example in the below shown SQL before updating field1 we check

that is the old field1 value same. If not then some one else has updated and

necessary action has to be taken.

Update table1 set field1=@test where field1 = @oldfield1value

Locking can be handled at ADO.NET side or at SQL SERVER side i.e. in stored

procedures. For more details of how to implementing locking in SQL SERVER read

“What are different locks in SQL SERVER ?” in SQL SERVER chapter.

Note:- This is one of the favorite questions of interviewer, so cram it….When I say cram

itI do not mean it…. I mean understand it. This book has tried to cover ADO.NET as

much as possible, but indeterminist nature of ADO.NET interview questions makes it

difficult to make full justice. But hope so that the above questions will make you quiet

confident during interviews.

(A)How can we perform transactions in .NET?


The most common sequence of steps that would be performed while developing a

transactional application is as follows:

Open a database connection using the Open method of the connection object.

Begin a transaction using the Begin Transaction method of the connection

object. This method provides us with a transaction object that we will use

later to commit or rollback the transaction. Note that changes caused by any

queries executed before calling the Begin Transaction method will be committed

to the database immediately after they execute. Set the Transaction property

of the command object to the above mentioned transaction object.

Execute the SQL commands using the command object. We may use one or

more command objects for this purpose, as long as the Transaction property

of all the objects is set to a valid transaction object.

Commit or roll back the transaction using the Commit or Rollback method of

the transaction object.

Close the database connection.

(I)What is difference between Dataset. clone and Dataset.

copy ?

Clone: – It only copies structure, does not copy data.

Copy: – Copies both structure and data.

(A)Can you explain the difference between an ADO.NET

Dataset and an ADO Recordset?

There two main basic differences between recordset and dataset :-

With dataset you an retrieve data from two databases like oracle and sql

server and merge them in one dataset , with recordset this is not possible

All representation of Dataset is using XML while recordset uses COM.

Recordset can not be transmitted on HTTP while Dataset can be.


(A)Explain in detail the fundamental of connection pooling?

When a connection is opened first time a connection pool is created and is based on the

exact match of the connection string given to create the connection object. Connection

pooling only works if the connection string is the same. If the connection string is different,

then a new connection will be opened, and connection pooling won’t be used.

Figure 9.5 : – Connection Pooling action.

Lets try to explain the same pictorially. In the above figure you can see there are three

requests “Request1”, “Request2” and “Request3”. “Request1” and “Request3” have


same connection string so no new connection object is created for “Request3” as the

connection string is same. They share the same object “ConObject1”. But new object

“ConObject2” is created for “Request2” as the connection string is different.

Note: – The difference between the connection string is that one has “User id=sa” and other

has “User id=Testing”.

(A)What is Maximum Pool Size in ADO.NET Connection


Maximum pool size decides the maximum number of connection objects to be pooled. If

the maximum pool size is reached and there is no usable connection available the request

is queued until connections are released back in to pool. So it’s always a good habit to

either call the close or dispose method of the connection as soon as you have finished

work with the connection object.

(A)How to enable and disable connection pooling?

For .NET it is enabled by default but if you want to just make sure set Pooling=true in

the connection string. To disable connection pooling set Pooling=false in connection

string if it is an ADO.NET Connection. If it is an OLEDBConnection object set OLE

DB Services=-4 in the connection string.


Note :- This chapter is too small to cover SQL Server completely. I will suggest you to buy

my SQL Server Interview Question book to gain full confidence during interview for this



8 Responses to “ADO.NET?”

  1. srikanth Says:


    These Questions are really good


  2. hi Says:

    All questions are very good and useful.thank u.


  3. sinha Says:

    Thanks for providing with answers.
    plz do send the E-Book link for Sql server interview questions to

  4. Anju Says:

    Really helpful to everyone… thankz alot…

  5. ravinder kamidi Says:


    All these questions and answers are very useful for the interview thank you.


  6. ganesan Says:

    these questions are useful

  7. jahangir Says:

    thnku very much all question are good and simply defined

  8. prashant Says:

    thank you …
    questions are really importent regarding intervoew.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: