|
|
| |
ADO.NET Interview Questions |
|
| |
|
| |
|
| |
What is the namespace in which .NET has the data
functionality classes ? |
| |
Following are the namespaces provided by .NET for data management :- |
| |
System.data |
| |
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. |
| |
System.Data.OleDB |
| |
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
equivalents. |
| |
System.Data.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
connection. |
| |
System.XML |
| |
This Contains the basic objects required to create, read, store, write, and manipulate
XML documents according to W3C recommendations. |
| |
|
| |
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 :- |
| |
Connection. |
| |
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. |
| |
“DataView” object is used to sort and filter data in Datatable. |
| |
|
| |
What are the two fundamental objects in ADO.NET ? |
| |
Datareader and Dataset are the two fundamental objects in ADO.NET. |
| |
|
| |
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
architecture. |
| |
Dataset can persists contents while datareader can not persist contents , they
are forward only. |
| |
|
| |
What are major difference between classic ADO and
ADO.NET ? |
| |
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
applicable. |
| |
Locking is not supported due to disconnected model. |
| |
All data is persisted in XML as compared to classic ADO where data
was persisted in Binary format also. |
| |
|
| |
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 |
| |
|
| |
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 rows (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 a
single value (effectively the first column of the first row of the resulting rowset).
Any other returned columns and rows are discarded. Fast and efficient when
only a "singleton" value is required |
| |
|
| |
What is the use of dataadapter ? |
| |
These are objects that connect one or more Command objects to a Dataset object..They
provide logic that gets the 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. |
| |
|
| |
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. Can 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
DataSet. |
| |
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
constraints. |
| |
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. |
| |
|
| |
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. |
| |
|
| |
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. |
| |
|
| |
How can we connect to Microsoft Access , Foxpro ,
Oracle etc ? |
| |
Microsoft provides System.Data.OleDb namespace to communicate with databases like
Access , 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
Try
objOLEDBCommand = New OleDbCommand(“Select FirstName
from Employees”)
objOLEDBCon.Open()
objOLEDBCommand.Connection = objOLEDBCon
objOLEDBReader = objOLEDBCommand.ExecuteReader()
Do While objOLEDBReader.Read()
lstNorthwinds.Items.Add(objOLEDBReader.GetString(0))
Loop
Catch ex As Exception
Throw ex
Finally
objOLEDBCon.Close()
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 “AppDomain.CurrentDomain.BaseDirectory”
function this gives the current path of the running exe which is “BIN” and the MDB file is
in that directory.Also note the finally block which executes irrespective that there is error or
not.Thus ensuring that all the connection to the datastore is freed.It’s best practice to put all
clean up statements in finally block thus ensuring that the resources are deallocated properly. |
| |
|
| |
How do we connect to SQL SERVER , which namespace
do we use ? |
| |
First 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
Try
‘ this gets the connectionstring from the app.config
file.
‘ note if this gives error see where the MDB file is
stored in your pc and point to that
strConnectionString =
AppSettings.Item(“ConnectionString”)
‘ take the connectiostring and initialize the connection
object
With objConnection
.ConnectionString = strConnectionString
.Open()
End With
objCommand = New SqlCommand(“Select FirstName from
Employees”)
With objCommand
.Connection = objConnection
objReader = .ExecuteReader()
End With
‘ looping through the reader to fill the list box
Do While objReader.Read()
lstData.Items.Add(objReader.Item(“FirstName”))
Loop
Catch ex As Exception
Throw ex
Finally
objConnection.Close()
End Try
<appSettings>
<add key=”Connectionstring” value=”Server=ERMBOM1-IT2;User
ID=sa;Database=Employees”/>
</appSettings> |
| |
For setting up the sample SQL table we can use the DTS import wizard to import.See the
below figure which is using data source as Microsoft Access.While importing the database
author had give the database name as “Employees”. |
| |
Now from interview point of view definitely you are not going to say the whole source
code which is given in 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 is import the namespace “System.Data.SqlClient”. |
| |
Create a connection object as shown in “LoadData” method.
With objConnection
.ConnectionString = strConnectionString
.Open()
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()
lstData.Items.Add(objReader.Item(“FirstName”))
Loop |
| |
Finally 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. |
| |
|
| |
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
question. |
| |
CREATE PROCEDURE SelectByEmployee @FirstName nvarchar(200) AS
Select FirstName from Employees where FirstName like @FirstName + '%'
CREATE PROCEDURE SelectEmployee AS
Select FirstName from Employees
If txtEmployeeName.Text.Length = 0 Then
objCommand = New SqlCommand(“SelectEmployee”)
Else
objCommand = New sqlCommand(“SelectByEmployee”)
objCommand.Parameters.Add(“@FirstName”,
Data.SqlDbType.NVarChar, 200)
objCommand.Parameters.Item(“@FirstName”).Value =
txtEmployeeName.Text.Trim()
End If |
| |
In the above sample not lot 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 job. |
| |
|
| |
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) |
| |
|
| |
I want to force the datareader to return only schema of
the datastore rather than data ? |
| |
pobjDataReader = pobjCommand.ExecuteReader(CommandBehavior.SchemaOnly) |
| |
|
| |
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. |
| |
|
| |
Which is the best place to store connectionstring in .NET
projects ? |
| |
Config files are the best place to store connection strings.If it’s a web-based application
“Web.config” file will be used and if it’s a windows application “App.config” files will be
used. |
| |
|
| |
What are 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 binded 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)
objConn.Open()
Dim objCommand As New SqlCommand(“Select FirstName from
Employees”)
objCommand.Connection = objConn
Dim objDataAdapter As New SqlDataAdapter()
objDataAdapter.SelectCommand = objCommand
Dim objDataSet As New DataSet
End Sub |
| |
In such type of question’s 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 to say suring interview. |
| |
Dim objConn As New SqlConnection(strConnectionString)
objConn.Open() |
| |
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
object. |
| |
objDataAdapter.Fill(objDataSet)
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 a index of zero. |
| |
Just say all the five steps during interview and you will see the smile in the interviewer’s
face.....Hmm and appointment letter in your hand. |
| |
|
| |
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. |
| |
ReadXML
Read’s a XML document in to Dataset. |
| |
GetXML
This is function’s which return’s a string containing XML document. |
| |
WriteXML
This write’s a XML data to disk. |
| |
|
| |
How can we save all data from dataset ? |
| |
Dataset has “AcceptChanges” method which commits all the changes since last time
“Acceptchanges” has been executed. |
| |
|
| |
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”. |
| |
GetChanges |
| |
Return’s dataset which are changed since it was loaded or since Acceptchanges was
executed. |
| |
HasChanges |
| |
This property indicate’s has any change’s been made since the dataset was loaded or
acceptchanges method was executed.
If we want to revert or abandon all change’s since the dataset was loaded use
“RejectChanges”. |
| |
Note:- One of the most misunderstood things about these properties is that it tracks the
changes of actual database.That’s a fundamental mistake , actually the changes are related
to only changes with dataset and has nothing to with changes happening in actual
database.As dataset are disconnected and do not know anything about the changes
happening in actual database. |
| |
|
| |
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 :- |
| |
Add |
| |
Add’s a new row in DataTable |
| |
Remove |
| |
Remove’s a “DataRow” object from “DataTable” |
| |
RemoveAt |
| |
Remove’s a “DataRow” object from “DataTable” depending on index position of the
“DataTable”. |
| |
|
| |
What’s basic use of “DataView” ? |
| |
“DataView” represent’s a complete table or can be small section of row’s depending on
some criteria.It’s best used for sorting and finding data with in “datatable”.
Dataview has the following method’s :- |
| |
Find |
| |
Take’s a array of value’s and return’s the index of the row. |
| |
FindRow |
| |
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 :- |
| |
AddNew |
| |
Add’s a new row to the “DataView” object. |
| |
Delete |
| |
Deletes the specified row from “DataView” object. |
| |
|
| |
What’s 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’s asked almost 99% in
all companies....Basic very Basic cram it. |
| |
Following are the major difference between “DataSet” and “DataReader” :- |
| |
“DataSet” is a disconnected architecture , while “DataReader” has live
connection while reading data.So 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 record’s , “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” whereever possible. , as
it’s meant specially for speed performance. |
| |
|
| |
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” object’s in one
“DataSet” object.Sample code shows two tables “Table1” and “Table2” in object
ObjDataSet. |
| |
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. |
| |
|
| |
How can we add relation’s between table in a DataSet ? |
| |
Dim objRelation As DataRelation
objRelation=New
DataRelation("CustomerAddresses",objDataSet.Tables("Customer").Columns("Custid")
,objDataSet.Tables("Addresses").Columns("Custid_fk"))
objDataSet.Relations.Add(objRelation) |
| |
Relation’s can be added between “DataTable” object’s using the “DataRelation”
object.Above sample code is trying to build a relationship between “Customer” and
“Addresses” “Datatable” using “CustomerAddresses” “DataRelation” object. |
| |
|
| |
What’s the use of CommandBuilder ? |
| |
CommandBuilder builds “Parameter” objects automatically.Below is a simple code which
uses commandbuilder to load its parameter object’s. |
| |
Dim pobjCommandBuilder As New OleDbCommandBuilder(pobjDataAdapter)
pobjCommandBuilder.DeriveParameters(pobjCommand) |
| |
Be careful while using “DeriveParameters” method as it needs a extra trip to the Datastore
which can be very inefficient. |
| |
|
| |
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 user’s 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 are very
common and having pessimistic locking is not a practical solution. |
| |
|
| |
How many way’s are there to implement locking in
ADO.NET ? |
| |
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 mot 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 question’s of interviewer , so cram it....When i say cram
it i 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 question’s makes it
difficult to make full justice.But hope so that the above question’s will make you quiet
confident during interviews. |
| |
|
| |
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. |
| |
|
| |
What’s difference between Dataset. clone and Dataset.
copy ? |
| |
Clone: - It only copies structure, does not copy data.
Copy: - Copies both structure and data. |
| |
|
| |
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. |
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
