Interview Questions on Ado.net

What is the use of command objects?
They are used to connect connection object to Data reader or dataset. 

Following are the methods provided by command object:-

ExecuteNonQuery

Executes the command defined in the Command Text property against the connection defined in the Connection property for a query that does not return any row (an UPDATE, DELETE, or INSERT). Returns an Integer indicating the number of rows affected by the query.

ExecuteReader

Executes the command defined in the Command Text property against the connection defined in the Connection property. Returns a "reader" object that is connected to the resulting row set within the database, allowing the rows to be retrieved.
 

ExecuteScalar

Executes the command defined in the Command Text property against the connection defined in the Connection property. Returns only single value (effectively the first column of the first row of the resulting row set any other returned columns and rows are discarded. It is fast and efficient when only a "singleton" value is required

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 data adapter?
These objects connect one or more Command objects to a Dataset object. They provide logic that would get 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 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 Data Table object within the Tables collection. 

Each Data Table object contains a collection of Data Row objects and a collection of Data Column 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 Default View object for each table. 

This is used to create a Data View object based on the table, so that the data can be searched, filtered, or otherwise manipulated while displaying the data.

Can you enforce constarints and relations on tables inside a DataSet?

Yes, the DataSet consists of a collection of DataTable objects that you can relate to each other with DataRelation objects. You can also enforce data integrity in the DataSet by using the UniqueConstraint and ForeignKeyConstraint objects.

What happens when you invoke AcceptChanges() method on a DataSet?

Invoking AcceptChanges() method on the DataSet causes AcceptChanges() method to be called on each table within the DataSet.

Both the Data Row and Data Table classes also have AcceptChanges() methods. Calling AcceptChanges() at the DataTable level causes the AcceptChanges method for each DataRow to be called.

When you call AcceptChanges on the DataSet, any DataRow objects still in edit-mode end their edits successfully. The RowState property of each DataRow also changes. Added and Modified rows become Unchanged, and Deleted rows are removed.If the DataSet contains ForeignKeyConstraint objects, invoking the AcceptChanges method also causes the AcceptRejectRule to be enforced.

What is the use of DataSet.HasChanges() Method?  

DataSet.HasChanges method returns a boolean true if there are any changes made to the DataSet, including new, deleted, or modified rows. This method can be used to update a DataSource only if there are any changes.

What is the use of DataSet.HasChanges() Method?
DataSet.HasChanges method returns a boolean true if there are any changes made to the DataSet, including new, deleted, or modified rows. This method can be used to update a DataSource only if there are any changes.

How do you roll back all the changes made to a DataSet since it was created?

Invoke the DataSet.RejectChanges() method to undo or roll back all the changes made to a DataSet since it was created.

What happnes when you invoke RejectChanges method, on a DataSet that contains 3 tables in it?
RejectChanges() method will be automatically invoked on all the 3 tables in the dataset and any changes that were done will be rolled back for all the 3 tables.

When the DataTable.RejectChanges method is called, any rows that are still in edit-mode cancel their edits. New rows are removed. Modified and deleted rows return back to their original state. The DataRowState for all the modified and deleted rows will be flipped back to unchanged.

What is the DataSet.CaseSensitive property used for?
When you set the CaseSensitive property of a DataSet to true, string comparisons for all the DataTables within dataset will be case sensitive. By default the CaseSensitive property is false.

What is Script injection?
A script injection attack attempts to send executable script to your application with the intent of having other users run it. A typical script injection attack sends script to a page that stores the script in a database, so that another user who views the data inadvertently runs the code.

What is SQL injection?
A SQL injection attack attempts to compromise your database by creating SQL commands that are executed instead of, or in addition to, the commands that you have built into your application.

What are the steps to follow to avoid Script Injection attacks?

Encode user input with the HtmlEncode method. This method turns HTML into its text representation. 

If you are using the GridView control with bound fields, set the BoundField object's HtmlEncode property to true. This causes the GridView control to encode user input when the row is in edit mode.   

What are the steps to follow to avoid SQL Injection attacks?
Always use parameterized queries or stored procedures instead of creating SQL commands by concatenating strings together.

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 where in we can specify saying close connection automatically after the Data reader is close.

Example :

objDataReader = objCommand.ExecuteReader;
(CommandBehavior.CloseConnection);

Can we force the data reader to return only schema of the data store rather than data.

objDataReader = pobjCommand.ExecuteReader (CommandBehavior.SchemaOnly) 

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

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.

Which is the best place to store connection string in .NET projects?
Config files are the best places to store connection strings. If it is a web-based application
“Web.config” file will be used and if it is a windows application “App.config” files will be used.

What are the various methods provided by the dataset object to generate XML?

ReadXML
Read’s a XML document in to Dataset.
GetXML
This is a function, which returns the string containing XML document.
Writexml
This writes a XML data to disk.

How can we add/remove row is in “Data Table” object of “Dataset”?

Data table” provides “NewRow” method to add new row to “Data Table”. “

Data Table” has “DataRowCollection” object that has all rows in a “Data Table” object. Following are the methods provided by “DataRowCollection” object

Add
Adds a new row in Data Table

Remove
It removes a “Data Row” object from “Data Table”

Remove At
It removes a “Data Row” object from “Data Table” depending on index position of the “Data
Table”.
 

What is basic use of Data View?
“Data View” represents a complete table or can be small section of rows depending on some
criteria. It is best used for sorting and finding data with in “data table”.
 

Data view has the following methods:-

Find
It takes an array of values and returns the index of the row.  
Find Row
This also takes array of values but returns a collection of “Data Row”.

If we want to manipulate data of “Data Table” object create “Data View” (Using the “Default
View” we can create “Data View” object) of the “Data Table” object and use the following
 

functionalities:-

Add New
Adds a new row to the “Data View” object.
 

Delete
Deletes the specified row from “Data View” object.
 

What is the difference between “Dataset” and “Data Reader” ?

“Dataset” is a disconnected architecture, while “Data Reader” 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, “data reader” does not support this
functionality.
 

Why is Dataset slower than Data Reader is?
However, one of the biggest drawbacks of Dataset is speed. As “Dataset” carry considerable overhead because of relations, multiple table’s etc speed is slower than “Data Reader”. Always try to use “Data Reader” wherever possible, as it is meant especially for speed performance.

What is the use of Command Builder?
Command Builder builds “Parameter” objects automatically. Below is a simple code, which uses command builder to load its parameter objects.


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 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 in browser based application it is very common and having pessimistic locking is not a practical solution.


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

When we call “Update” method of Data Adapter it handles locking internally. If the Dataset values are not 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 Date time 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 I the best practice used by industries for locking.


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


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.


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 is 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 Record set?
With dataset you an retrieve data from two databases like oracle and sql server and merge
them in one dataset , with record set this is not possible. All representation of Dataset is using XML while record set uses COM. Record set cannot be transmitted on HTTP while Dataset can be.


Explain in detail the fundamental of connection pooling?
When a connection is opened 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.