SQL Server Interview Questions on ADO .NET

by

We have compiled most frequently asked SQL Server Interview Questions which will help you with different expertise levels.

SQL Server Interview Questions on ADO .NET

Question 1.
Which are the namespaces for ADO.NET?
Answer:
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

This 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.

Question 2.
Can you give an overview of ADO.NET architecture?
Answer:
The most important section in ADO.NET architecture is “Data Provider”.Data Provider provides access to data sources (SQL SERVER, ACCESS, ORACLE).In short, it provides objects to achieve functionalities like opening and closing connection, retrieve data and updates data. In the below figure you can see the four main sections of a data provider

  • Connection.
  • Command object (This is the response object to use stored procedures)
  • Data Adapter (This object acts as a bridge between datastore and dataset).
  • DataReader (This object reads data from the 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.

SQL Server Interview Questions on ADO .NET chapter 4 img 1

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

Note  This is one of thefavorite questions in .NET. Just paste the picture in your mind and during interview 
          try to refer that image.

Question 3.
What are the two fundamental objects in ADO.NET?
Answer:
DataReader and Dataset are the two fundamental objects in ADO.NET.

Question 4.
What is the difference between dataset and DataReader?
Answer:
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 persist contents while DataReader can not persist contents, they are forward only.

Question 5.
What is the major differences between classic ADO and ADO.NET?
Answer:
Following are some major differences between both

  • As in classic ADO we had a 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 the disconnected model.
  • All data is persisted in XML as compared to classic ADO where data was persisted in Binary format also.

Question 6.
What is the use of a connection object?
They are used to connect 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

Question 7.
What are the methods provided by the command object?
Answer:
They are used to connect connection objects to DataReader or dataset-Following are the methods provided by the command object

  1. 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). Returning an Integer indicating the number of rows affected by the query.

2.  ExecuteReader Executes the command defined in the CommandText pro> against the connection defined in the Connection property. Returns a “reader” that is connected to the resulting rowset within the database, allowing the
be retrieved.

3. ExecuteScalar Executes the command defined in the Commam against the connection defined in the Connection property. Retu value (effectively the first column of the first row of the resulting other returned columns and rows are discarded. A fast and efficiei “singleton” value is required

Question 8.
What is the use of “DataAdapter”?
Answer:
These are objects that connect one or more Command objects to a Dataset object- provide logic that gets the data from the data store and populates the tables in the Dc 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.

Question 9.
What arc basic methods of “Dataadapter”?
Answer:
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.

Question 10.
What is Dataset object?
Answer:
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.

Question 11.
What are the various objects in Dataset?
Answer:
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 architecturefor visualizing this answer in pictorial form

Question 12.
How can we connect to Microsoft Access, FoxPro, Oracle etc?
Answer:
Microsoft provides System.Data.OleDb namespace to communicate with databases like Access, oracle etc.In short any OLE DB-Cornpliant database can be connected using System.Data.OldDb namespace.

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()
                IstNorthwinds.Items.Add(objOLEDBReader.GetString(0))
           Loop
        Catch ex As Exception
           Throw ex
        Finally
           objOLEDBCon.Close( )
           End Try
        End Sub

Question 13.
What’s the namespace to connect to SQL Server?
Answer:
Below is a sample code which shows a simple connection with SQL Server.

Private Sub LoadData ( )
        note with and end with makes your code more readable
      Dim strConnectionString As String
      Dim objConnection As New SglConnection
      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( )
                    IstData.Items.Add(objReader.Item("FirstName"))
              Loop
          Catch ex As Exception
             Throw ex
          Finally
              objConnection.Close( )
          End Try

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 Withs
  • Create the command object with the SQL.Also assign the created connection object to command object, and execute the reader.
objCommand = New SqlCoimand( "Select FirstName from Employees") 
            With objCommand
                          Connection = objConnect ion 
                          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 ( )
            IstData ..Items .Add (objReader. Item ("FirstName"))
Loop
  • Finally do not forget to close the connection object.

Question 14.
How do we use stored procedures in ADO.NET?
Answer:
ADO.NET provides the SqlCommand object which provides the functionality of executing stored procedures.

If txtEmployeeName.Text.Length = 0 Then
objCommand = New SqlCommand("SelectEmployee")
Else
objCommand = New SqlCommand("SelectByEmployee")
objCommand.Parameters.Add("QFirstName",
Data.SqlDbType.NVarChar, 200)
objCommand.Parameters.Item("QFirstName").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 cqmmand 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.

Question 15.
How can we force the connection object to close?
Answer:
Command method Executereader takes a parameter called as CommandBehavior where in we can specify saying close connection automatically after the Datareader is close.

pobj DataReader =
pobjCommand.ExecuteReader(CommandBehavior.CloseConnection)

Question 16.
I want to force the datareader to return only schema?
Answer:

pobjDataReader = pobjCommand.ExecuteReader(CommandBehavior.SchemaOnly)

Question 17.
Can we optimize command object when there is only one row?
Answer:
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.

Question 18.
Which is the best place to store connectionstring?
Answer:
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.

Question 19.
What are steps involved to fill a dataset?
Answer:
TwistHow can we use dataadapter to fill a dataset?
Below is a simple code which loads a dataset and then finally loads the listbox.

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( ) 
obj Da t aAdapt er. Select Command = obj Command

Third step 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.

IstData.DataSource = objDataSet.Tables(0).DefaultView 
IstData. Display-Member = "FirstName"
IstData.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 qne 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.

Question 20.
What are the methods provided by the dataset for XML?
Answer:

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 into dataset and use the below methods.
  1. ReadXML
    Read’s a XML document in to Dataset.
  2. GetXML
    This is function’s which return’s a string containing XML document.
  3. WriteXML
    This writes a XML data to disk.

Question 21.
How can we save all data from dataset?
Answer:
Dataset has “AcceptChanges” method which commits all the changes since last time “Acceptchanges” has been executed. .

Question 22.
How can we check for changes made to dataset?
Answer:
TwistHow 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 indicates 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 “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.

Question 23.
How can we add/remove row’s in “DataTable” object of “DataSet”?
Answer:
“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”.

Question 24.
What’s basic use of “DataView”?
Answer:
“DataView” represent’s a complete table or can be small section of rows depending on some criteria. It’s best used for sorting and finding data with in “datatable”.
Dataview has the following methods

Find

Take’s an array of values and returns 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

Delete the specified row from “DataView” object.

Question 25.
What’s difference between “DataSet” and “DataReader”?
Answer:
TwistWhy 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”

  1. “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.
  2. When application needs to access data from more than one table “DataSet” forms the best choice.
  3. If we need to move back while reading record’s, “datareader” does not support this functionality.
  4. 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.

Question 26.
How can we load multiple tables in a DataSet?
Answer:

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 “Tablel” and “Table2” in object ObjDataSet. Istdata.DataSource = objDataSet.Tables(“Tablel”).DefaultView
In order to refer “Tablel” DataTable, use Tables collection of DataSet and the Defaultview object will give you the necessary output.

Question 27.
How can we add relation’s between table in a DataSet?
Answer:

Dim objRelation As Dat'aRelation 
obj Relation-New 
DataRelation ("CustamerAddresses", objDataSet. Tables ("Customer"). Columns ("Custid”)
objDataSet.Tables("Addresses").Columns("Custid_fk")) 
objDataSet.Relations.Add(objRelation)

Relation’s 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.

Question 28.
What’s the use of CommandBuilder?
Answer:
“CommandBuilder” builds “Parameter” objects automatically. Below is a simple code which uses commandbuilder to load its parameter objects.

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.

Question 29.
What’s difference between “Optimistic” and “Pessimistic” locking?
Answer:
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.

Question 30.
How many way’s arc there to implement locking in ADO.NET?
Answer:
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 L appropriate error message to the user.
  • Define 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 fieldl=@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 fieldl we check that is the old fieldl value same. If not then some one else has updated and necessary action has to be taken.
Update table1 set fieldl=@test where fieldl = @oldfieldlvalue

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 thefavorite question's of interviewer, so cram it.... When Isay cram it i do not mean it.... 
          I mean understand it. This book has tried to cover ADO.NET as much as possible, but unexpected nature 
         of ADO.NET interview questions makes it difficult to make full justice. " But hope so that the above ADO.Net 
        questions will make you quiet confident dhring interviews.

Question 31.
How can we perform transactions in .NET?
Answer:
The most common sequence of steps that would be performed while developing a
transactional application is as follows:

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

2. 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 roll back 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.

3. 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.

4. Commit or roll back the transaction using the Commit or Rollback method of the transaction object.

5. Close the database connection.

Question 32.
What’s the difference between Dataset, clone, and Dataset, copy?
Answer:
Clone: – It only copies structure, does not copy data.
Copy: – Copies both structure and data.

Question 33.
What’s the difference between Dataset and ADO Recordset?
Answer:
There two main basic differences between recordset and dataset

  • With dataset you can 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.
  • The recordset can not be transmitted on HTTP while Dataset can be.