QuestPond’s Interview Questions & Answers on ADO.NET

What is the use of Command objects?

Command object helps to execute SQL statements. Following are the methods provided by command object:-

ExecuteNonQuery: – Executes insert, update and delete SQL commands. Returns an Integer indicating the number of rows affected by the query.
ExecuteReader: – Executes select SQL statements which can either be in your .NET code or in a stored procedure. Returns a “Datareader” object.
ExecuteScalar: – Executes SQL command and returns only a single value like count,sum , first record etc.

How can we fine-tune the command object when we are expecting a single row?

Again, CommandBehaviour enumeration provides two values Single Result and Single Row. 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.

What are Dataset objects?

Dataset is an in memory object with data tables, rows and columns. You can visualize it as in-memory RDBMS.Dataset has the following features:-

The in memory RDBMS works in a disconnected manner. In other words even if the connection is closed the dataset is still there in memory.
You can do modification in the in-memory database object and send the final changes to the database.

Below is a simple code snippet which shows how to access a column value. You can see how the full dataset object hierarchy is accessed to get the column value.

objDataset.Tables[0].Rows[0]["CustCode"]

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

Command method Execute reader takes a parameter called as Command Behavior wherein we can specify saying close connection automatically after the Data reader is close.

PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior.Close
Connection)

See the following video on ADO.NET connection pooling: –

Click and see here for more training on ADO.NET