Questions

(B) How do we use a Stored Procedure in ADO.NET and how do we provide parameters to the Stored Procedure?
(B) How can we force the Connection object to close after my data reader is closed?
(B) I want to force the DataReader to return only the schema of the data store rather than data.
(B) How can we fine-tune the Command object when we are expecting a single row?
(B) What are the various methods provided by the DataSet object to generate XML?
(B) How can we save all data from a DataSet?
(B) How can we check if changes have been made to a DataSet since it was loaded?
(B) How can we add/remove row in a DataTable object of a DataSet?
(B) What is the basic use of a DataView?
(B) What is the difference between "Dataset" and "Data Reader"?
(B) How can we load multiple tables in a Dataset?
(B) How can we add relation between tables in a Dataset?
(B) What is the use of the Command Builder?
(B) What is the difference between "Optimistic" and "Pessimistic" locking?
(A) How many ways are there to implement locking in ADO.NET?
(A) How can we perform transactions in .NET?
(I) What is the difference between Dataset.Clone and Dataset.Copy?
(A) Can you explain the difference between an ADO.NET Dataset and an ADO Recordset?
(A) Explain in detail the fundamentals of connection pooling.
(A) What is the maximum pool size in an ADO.NET Connection String?
(A) How do we enable and disable connection pooling?
(I) What extra features does ADO.NET 2.0 have?

Introduction

In this section we will touch base on one of the important concepts in ADO.NET. You can download my .NET interview questions PDF from: http://www.questpond.com/SampleDotNetInterviewQuestionBook.zip .

Previous parts of my Interview Questions series for architects:

Part 1 - SoftArchInter1.aspx
Part 2 - SoftArch2.aspx
Part 3 - SoftArch3.aspx
Part 4 - SoftArch4.aspx

UML interview questions Part 1: SoftArch5.aspx

Happy job hunting......

(B) How do we use a Stored Procedure in ADO.NET and how do we provide parameters to the Stored Procedure?

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

Note: Sample code is provided in the folder WindowsSqlClientCommand. There are two stored procedures created in the 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 much has been changed, only the SQL is moved to the stored procedures. There are two stored procedures: "Select Employee" which selects all the employees, and "SelectByEmployee" which returns the employee name starting with a specific character. As you can see, to provide parameters to stored procedures, we are using the parameter object of the Command object. In such a question the interviewer expects two simple answers: one is that we use the Command object to execute stored procedures, and the parameter object to provide parameters to the Stored Procedure. The above sample is provided only for getting the actual feel of it. Be short, be nice, and get a job.

(B) How can we force the connection object to close after my data reader is closed?

The Command method ExecuteReader takes a parameter called CommandBehavior where we can specify to close the connection automatically after the data reader is closed.

PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior.CloseConnection)

(B) I want to force the data reader to return only the schema of the data store rather than the data

PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior.SchemaOnly)

(B) How can we fine-tune the Command object when we are expecting a single row?

Again, the 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 the query is optimized according to a single row.

(B) Which is the best place to store connection strings in .NET projects?

Config files is the best choice to store connection strings. If it is a web-based application the Web.config file will be used, and if it is a Windows application, App.config files are used.

(B) What are the steps involved in filling a dataset?

Twist: How can we use a data adapter to fill a dataset?

The sample code is provided in the WindowsDataSetSample folder. LoadData has all the implementation of connecting and loading to a dataset. This dataset is finally bound 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 types of questions the interviewer is looking from a practical angle, to see if you have worked with datasets and datadapters. Let me try to explain the above code first and then we will move to what steps should be explained during the interview.

Dim objConn As New SqlConnection(strConnectionString)
objConn.Open()

The first step is to open the connection. Again, note the connection string is loaded from the config file.

Dim objCommand As New SqlCommand("Select FirstName from Employees")
objCommand.Connection = objConn

The second step is to create a Command object with the appropriate SQL and set the connection object to this command.

Dim objDataAdapter As New SqlDataAdapter()
objDataAdapter.SelectCommand = objCommand

The third step is to create the Adapter object and pass the Command object to the Adapter object.

objDataAdapter.Fill(objDataSet)

The fourth step is to load the dataset using the Fill method of the data adapter.

lstData.DataSource = objDataSet.Tables(0).DefaultView
lstData.DisplayMember = "FirstName"
lstData.ValueMember = "FirstName"

The fifth step is to bind to the loaded dataset with the GUI. At this moment the sample has a list box as the UI. Binding of the UI is done by using the Default View 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., the Employees table, so we are referring that with an index of zero. Explain all the five steps during the interview and you will see the smile on the interviewer’s face and the 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 leaps between classic ADO and ADO.NET. So this question is normally asked more generally as how can we convert any data to XML format. The best answer is to use the below methods:

ReadXML: Reads XML document into the DataSet.
GetXML: This is a function which returns the string containing the XML document.
Writexml: This writes XML data to disk.

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

Dataset has the AcceptChanges method which commits all the changes since the last time it was executed.

Note: This article does not have any samples of AcceptChanges. I leave that to the reader as homework sample. But yes, from an interview aspect that will be enough.

(B) How can we check if changes have been made to a dataset since it was loaded?

Twist: How can we cancel all changes done in a dataset?

Twist: How do we get the values which were changed in a dataset?

For tracking down changes, the Dataset has two methods which come to the rescue: GetChanges and HasChanges. GetChanges returns the dataset which was changed since it was loaded or since AcceptChanges was executed. HasChanges indicates if any changes have been made since the dataset was loaded or if the AcceptChanges method was executed. To abandon all changes since the dataset was loaded use RejectChanges. Note: One of the most misunderstood things about these properties is that they track the changes in the actual database. That is a fundamental mistake; actually they are related to changes with the dataset and have nothing to do with changes happening in the actual database. Datasets are disconnected and do not know anything about the changes happening in actual database.

(B) How can we add/remove row in the DataTable object of a Dataset?

DataTable provides a NewRow method to add a new row to a DataTable. DataTable has a DataRowCollection object that has all the rows in a DataTable object. Following are the methods provided by the DataRowCollection object:

Add: Adds a new row in the DataTable
Remove: It removes a DataRow object from the DataTable
RemoveAt: It removes a DataRow object from the DataTable depending on the index position of the DataTable

(B) What is the basic use of DataView?

DataView represents a complete table or can be a small section of rows depending on some criteria. It is best used for sorting and finding data within a data table. DataView has the following methods:

Find: It takes an array of values and returns the index of the row.
FindRow: This also takes an array of values but returns a collection of DataRows. If we want to manipulate the data of a DataTable object, create a DataView (using the "Default View", we can create a DataView object) of the DataTable object.
AddNew: Adds a new row to the DataView object.
Delete: Deletes the specified row from DataView object.

(B) What is the difference between DataSet and DataReader?

Twist: Why is a DataSet slower than a DataReader? The fourth point is the answer to the twist.

Note: This is my best question and we expect everyone to answer it. It is asked almost 99% in all companies....Basic, very basic, cram it.

Following are the major differences between a DataSet and a DataReader:

DataSet is a disconnected architecture while DataReader has a live connection while reading data. If we want to cache data and pass to a different tier, DataSet is the best choice and it has decent XML support.
When an application needs to access data from more than one table DataSet is the best choice.
If we need to move back while reading records, DataReader does not support this functionality.
However, one of the biggest drawbacks of a DataSet is speed. As a DataSet carries considerable overhead because of relations, multiple tables, etc., speed is slower than a DataReader. Try to use a DataReader wherever possible, as it is meant especially for 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. The sample code shows two tables Table1 and Table2 in object ObjDataSet.

lstdata.DataSource = objDataSet.Tables("Table1").DefaultView

In order to refer Table1 DataTable, use the Tables collection of the DataSet and the DefaultView object will give you the necessary output.

(B) How can we add a relation between tables 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)

Relations can be added between DataTable objects using the DataRelation object. The above sample code is trying to build a relationship between the Customer and Addresses DataTables using the 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)
pobjCommandBuilder.DeriveParameters(pobjCommand)

Be careful while using the DeriveParameters method as it needs an extra trip to the Data Store, which can be very inefficient.

(B) What’s difference between Optimistic and Pessimistic locking?

In pessimistic locking when the user wants to update data it locks the record and then no one can update data. Other users can only view the data when there is pessimistic locking. In optimistic locking multiple users can open the same record for updating, thus increasing maximum concurrency. A record is only locked when updating the record. This is the most preferred way of locking practically. Nowadays in browser based applications this is very common, and pessimistic locking is not a practical solution.

(A) How many ways are there to implement locking in ADO.NET?

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

Update table1 set field1=@test where Last Timestamp=@Current Timestamp

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

Locking can be handled at the 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 the different locks in SQL Server?" in the SQL Server article.

When we call the Update method of the DataAdapter it handles locking internally. If the DataSet values are not matching with the current data in the database, it raises a concurrency exception error. We can easily trap this error using a Try. Catch block and raise the 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 the existing one in the database. Below is a sample SQL which checks for a timestamp before updating, and any mismatch in timestamp, it will not update the records. This is the best practice used by industries for locking.
Check for original values stored in SQL Server and the actual changed values. In the stored procedure check before updating that the old data is the same as the current example. In the below shown SQL before updating field1, we check that the old field1 value is the same. If not then someone else has updated and the necessary action has to be taken.

(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 BeginTransaction 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 BeginTransaction 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 methods of the transaction object.
Close the database connection.

(I) What is the 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 a record set and a dataset:

With a dataset you can retrieve data from two databases like Oracle and SQL Server and merge them into one dataset, with a record set this is not possible.
All representation of DataSet use XML while record set uses COM.
Record set cannot be transmitted on HTTP while Dataset can be.

(A) Explain in detail the fundamentals of connection pooling

When a connection is opened for the 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 will not be used.

Figure 9.5: Connection pooling action

Let me try to explain this pictorially. In the above figure, you can see there are three requests: Request1, Request2, and Request3. Request1 and Request3 have the same connection string so no new connection object is created for Request3 as the connection string is the same. They share the same object ConObject1. However, a new object ConObject2 is created for Request2 as the connection string is different.

Note: The difference between the connection strings is that one has "User id=sa" and the other has "User id=Testing".

(A) What is the maximum pool size in an ADO.NET Connection String?

The 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 into the pool. So it’s always a good habit to 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 the connection string if it is an ADO.NET Connection. If it is an OLEDB Connection object, set OLE DB Services=-4 in the connection string.

(I) What extra features does ADO.Net 2.0 have ?

Bulk Copy Operation: Bulk copying of data from a data source to another data source is a newly added feature in ADO.NET 2.0. ADO.NET introduces bulk copy classes which provide the fastest way to transfer data from one source to another. Each ADO.NET data provider has bulk copy classes. For example, in the SQL Server .NET data provider, the bulk copy operation is handled by the SqlBulkCopy class, which can read a DataSet, DataTable, DataReader, or XML objects.
Data Paging: A new method is introduced ExecutePageReader which takes three parameters: CommandBehavior, StartIndex, and PageSize. So if you want to get rows only from 10 - 20, you can simply call this method with start index as 10 and page size as 10.
Batch Update: If you want to update a large amount of data, ADO.NET 2.0 provides the UpdateBatchSize property which allows you to set the number of rows to be updated in a batch. This increases the performance dramatically as a round trip to the server is minimized.
Load and Save methods: In the previous version of ADO.NET, only DataSet had Load and Save methods. The Load method can load data from objects such as XML into a DataSet object and Save method saves the data to a persistent media. Now DataTable also supports these two methods. You can also load a DataReader object into a DataTable by using the Load method.
New Data Controls: In the toolbox you can see three new controls - DataGridView, DataConnector, and DataNavigator.
DataReader's new Execute methods: Some new Execute methods introduced are ExecutePageReader, ExecuteResultSet, and ExecuteRow.

For further reading do watch the below interview preparation videos and step by step video series.

ADO.NET Interview Questions: Part 2