Dot Net Interview Questions

What is the difference between "Dataset" and "DataReader"?

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 is meant specially for speed performance.

What is the 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 users 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 pssimistic locking is not a practical solution.

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 peoperty of all the objects is set to a valid transaction object.
Close the database connection

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.

Can you explain the difference between an ADO.NET Dataset and an ADO Recordset?

There are 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.
Recordset can not be transmitted on HTTP while Dataset can be.

What is the Maximum Pool Size in ADO.NET Connection String?

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 pool. So it is 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.

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.