ado.net interview questions and answers for experienced – ADO.NET Interview Questions in .NET

ado.net interview questions and answers for experienced: We have compiled most frequently asked .NET Interview Questions which will help you with different expertise levels.

.NET Interview Questions on ADO.NET

Question 1.
What are the different components in ADO.NET?
Answer:
There are six important components in ADO.NET as shown in Figure 4.1.

• Connection: This object creates a connection to the database. If you want to do any operation on the database you have to first create a connection object.

• Command: This object helps us to execute SQL queries against the database. Using command objects we can execute select, insert, update and delete SQL commands.

• Data Reader: This provides a recordset that can be browsed only in the forward direction. It can only be read but not updated. Data reader is good for a large number of records where you want to just browse quickly and display it.

• Dataset Object: This provides a recordset that can be read back and in the forward direction. The record set can also be updated. Dataset is like an in-memory database with tables, rows, and fields.

• Data Adapter: This object acts as a bridge between database and dataset; it helps to load the dataset object.

• Data View: This object is used to sort and filter data in the Data table of the dataset.

Note: Remember (2C4D), 2C for connection and command, 4D for the dataset, DataReader, data view, and DataAdapter.

Question 2.
What is the namespace in which .NET has the data functionality class? B)
Answer:
Following are the namespaces provided by .NET for data management as shown in Figure 4.2

System.Data: This namespace has the dataset object which helps us to access data in a data source-independent manner.

System.Data.SqlClient: This namespace has objects which help us to connect to the SQL Server database.

System.Data.OleDB: This namespace has objects which help us to connect to other databases like Oracle, Microsoft Access, and also SQL Server database.

System.XML: This namespace contains the basic objects required to create, read, store, write, and manipulate XML (extensible Markup Language) documents.

Question 3.
When should we use System.Data.SqlClient and System.Data.OleDB?
Answer:
If you want to connect only to SQL Server use SqlClient or else use OleDB. OleDB also connects to other databases plus SQL servers.SqlClient is specifically meant for SQL server so has better performance as compared to OleDB.

Question 4.
What is the difference between dataset and data reader?
Answer:
Following are some major differences between dataset and data reader:

• Dataset is a disconnected architecture while data reader is connected architecture.
• Data Reader provides forward-only and read-only access to data, while the dataset moves back as well as forward.
• Dataset object is an in-memory database with tables, rows, and columns while DataReader is just a simple table that can be read-only in a forward direction.
• Dataset can persist contents while data readers cannot persist contents, they are read-only and forward only.

Question 5.
What is the use of command objects?
Answer:
Command object helps to execute SQL statements. Following are the methods provided by the 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 the stored procedure. Returns a “DataReader” object. !

• ExecuteScalar: Executes SQL command and returns only a single value like count, sum, first record, etc.

Question 6.
What are Dataset objects?
Answer:
Dataset is an in-memory object with data tables, rows, and columns as shown in Figure 4.3. You can visualize it as an in-memory RDBMS (Relational Database Management System). 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 modifications in the in-memory database object and send the final changes to the database.
  Below is a simple code snippet that 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’]

Question 7.
What is the use of a data adapter?
Answer:
Data adapter object acts as a bridge. It helps to load the dataset object. Below is a sample code snippet that shows how the data adapter object is created and then using the Fill method the dataset object is loaded.

SqlDataAdapter objAdapter = new SqlDataAdapter(objCommand);
DataSet objDataset = new DataSet( );
objAdapter.Fill(objDataset);

Question 8.
What are the basic methods of data adapter?
Answer:
There are three most commonly used methods of Data adapter:
Fill: Executes the Select command to fill the dataset object with data from the data source. It can also be used to update (refresh) an existing table in a dataset with changes made to the data in the original data source if there is a primary key in the table in the dataset.
Fill Schema: Extracts 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: Updates the original data source with the changes made to the content of the dataset.

Question 9.
How can we fire a simple SQL statement using ADO?
Answer:

• First import the namespace “System.Data.SqlClient”.
• Create a connection object and call the “Open” function.

With objConnection
.Connection String = strConnectionString
.Open( )
EndWith

• Create the command object with the SQL. Also, assign the created connection object to the command object and execute the reader.

ObjCommand = New SqlCommand (“Select First Name from Employees”) With objCommand

.Connection = objConnection
Breeder = .Execute Reader ( )
EndWith

• You can then loop through the reader object to read the data.

Do while objReader.Read ( )
IstData. Items .Add (obj Reader. Item (“First Name”))
Loop

Do not forget to close the connection object.

objconnection.close*( );

Question 10.
How do we use stored procedures in ADO.NET and how do we provide parameters to the stored procedures?
Answer:
ADO.NET provides the SqlCommand object, which provides the functionality of executing stored procedures. In the command type, we need to provide the command type as stored procedure as shown in the below code snippet.

SqlCommand objCommand = new SqlCommand(“sp_Insert”, objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.ExecuteNonQuery( );

Question 10.
How can we force the connection object to close after my data reader is closed?
Answer:
Command method ExecuteReader takes a parameter called as CommandBehavior wherein we can specify saying close connection automatically after the DataReader is close.

PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior.CloseConnection)

Question 11.
I want to force the data reader to return only the schema of the datastore rather than the data.
Answer:

PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior. SchemaOnly)

Question 12.
How can we fine-tune the command object when we are expecting a single row?
Answer:
Again, CommandBehavior enumeration provides two values Single Result and Single Row. If you are expecting a single value then pass “CommandBehavior. single result” and the query is optimized accordingly if you are expecting a single row then pass “CommandBehavior. SingleRow” and the query is optimized according to a single row.

Question 13.
Which is the best place to store the connection string in .NET projects?
Answer:
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.

Question 14.
How do you fill the dataset?
Answer:
Create object of data adapter and call the Fill command method of the adapter.

SqlDataAdapter objAdapter = new SqlDataAdapter(objCommand);
DataSet objDataset = new DataSet();
objAdapter.Fill(objDataset);

Question 15.
What are the various methods provided by the dataset object to generate XML?
Answer:

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

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

Question 17.
How can we check which rows have changed since the dataset was loaded?
Answer:
For tracking down changes, Dataset has two methods, which comes to the rescue “Get Changes “and “Has Changes”.
Get Changes: Returns dataset, which is changed since it, was loaded, or since Acceptchanges was executed.
Has Changes: Or abandon all changes since the dataset was loaded use “RejectChanges. This property indicates that has any changes have been made since the dataset was loaded or the Acceptchanges method was executed.

Note: One of the most misunderstood things about these properties is that it tracks the changes 
of the actual database. That is a fundamental mistake; actually, the changes are related to only 
changes within the dataset and have nothing to do with changes happening in the actual database. 
Dataset is disconnected and does not know anything about the changes happening in the actual database.

Question 18.
How can we add/remove a row is in the “Data Table” object of “Dataset”?
Answer:
“Data table” provides the “NewRow” method to add a new row to the “Data Table”. “Data Table” has a “DataRowCollection” object that has all rows in a “Data Table” object. Following are the methods provided by the “DataRowCollection” object:
Add: Adds a new row in Data Table
Remove: It removes a “Data Row” object from the “Data Table”
Remove At: It removes a “Data Row” object from “Data Table” depending on the index position of the “Data Table”.

Question 19.
What is the basic use of “DataView”?
Answer:
“DataView” is used for sorting and finding data within the “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 an array of values but returns a collection of “Data Row”.

If we want to manipulate data of the “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 the “Data View” object.

Question 20.
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 “Data Table” objects in one “Dataset” object. Sample code shows two tables “Table1” and “Table2” in object ObjDataSet.

IstData.DataSource = objDataSet.Tables(“Table1’).DefaultView

In order to refer to the “Table1” Data Table, use Tables collection of Datasets, and the Default view object will give you the necessary output.

Question
How can we add relation between tables in a Dataset?
Answer:
Dim objRelation As DataRelation
obj Relation=New
DataRelation(“Cus tomerAddres ses “, objDataSet.Tables(“Customer”)
.Columns(“Custid”)
, objDataSet.Tables(“Addresses”).Columns(“Custid_fk”)) objDataSet.Relations.Add(objRelation)

Relations can be added between “Data Table” objects using the “DataRelation” object. Above sample, code is trying to build a relationship between “Customer” and “Addresses” “Data table” using “Customer Addresses” “Data Relation” object.

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

Dim pobjCommandBuilder As New OleDbCommandBuilder(pobjDataAdapter)
pobjCommandBuilder.DeriveParameters(pobjCommand)

Be careful while using the “Derive Parameters” method as it needs an extra trip to the Datastore, which can be very inefficient.

Question 22.
Can you explain the term concurrency?
Answer:
Concurrency issues happen in a multiuser environment when multiple people update the same data at the same time.
Below is the sequence of events how concurrency issues can happen:

• User A fetches data with the value “X” and starts changing this value to “Y”.
• User B also fetches the same “X” data and starts changing this value to “Z”.
• Now User B first updates the data to the database. In other words, he/she changes the data to “Z”.
• Now User A also sends the update to the database, in other words, he/she changes the data to “Y”.

Now User A thinks he/she has changed “X” to “Y” and User B thinks the current data is “Z”. Both of them are wrong and this leads to confusion termed as “Concurrency” problem.

Question 23.
How can we resolve the concurrency issue?
Answer:
Concurrency issues can be resolved by using optimistic or pessimistic locking.

Question 24.
What’s the difference between “Optimistic” and “Pessimistic” locking?
Answer:
In optimistic locking, there is no locking actually. It only checks if the old values are changed, in case they are changed it means somebody has changed the data, so it raises an exception.
In pessimistic locking, you actually lock the record, depending on the type of lock no other process
can make modifications to the record.

Question 25.
How many ways are there to implement optimistic locking in ADO.NET?
Answer:
Following are the ways to implement optimistic locking using ADO.N^T:

• When we call the “Update” method of Data Adapter it handles locking internally. If the Dataset values are not matching with current data in the Database, it raises a concurrency exception error. We can easily trap this error using the Try-Catch block and raise an appropriate error message to the user.
• Define a Date timestamp 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 that checks for timestamp before updating and any mismatch in timestamp will not update the records. This approach is the best practice used by industries for locking.

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

• Check for original values stored in SQL Server and actually changed values. In stored procedure check before updating that the old data is the same as the current Example in the below shown. SQL before updating f field we check that is the old f field value same. If not then someone else has updated and necessary action has to be taken.

Update table1 set field1=@test where field1 = @oldfield1 value

Locking can be handled at the ADO.NET site or at the SQL Server side, i.e., in stored procedures. For more details of how to implement locking in SQL Server read “What are different locks in SQL Server?” in SQL Server Chapter.

Question 26.
How can do pessimistic locking?
Answer:
Pessimistic locking is done by using transaction isolation levels like read committed, read uncommitted, repeatable read, and serializable.

Question 27.
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:

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

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

Question 28.
What is 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 29.
Can you explain the difference between an anADO.NET Dataset and an ADO Recordset?
Answer:
There are two main basic differences between recordset and dataset:

• Using a dataset you can retrieve data from two databases like Oracle and SQL server and merge them in one dataset, with the record set this is not possible.
• All representation of Dataset uses XML while recordset uses COM.
• The recordset cannot be transmitted on HTTP while Dataset can be.

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

Let us try to explain the same pictorially. In Figure 4.4, 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 “ConObjectl”. However, the new object “ConObject2” is created for “Request2” as the connection string is different.

Note: The difference between the connection string is that one has “User id=sa” and the other has “User id=Testing”.

Question 31.
What is the maximum pool size inADO.NET Connection string?
Answer:
The maximum pool size decides the maximum number of connection objects 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 of the method of the connection as soon as you have finished work with the Connection object.

Question 32.
How to enable and disable connection pooling?
Answer:
For .NET it is enabled by default but if you want to just make yourself double sure, set Pooling=true in the connection string. To disable connection pooling set Pooling=false in connection string if it is anADO.NET Connection.
If it is an OLEDBConnection object, set OLEDB Services= -4 in the connection string.

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

• In ADO we have a record set and in ADO.NET we have a dataset.
• In the record set, we can only have one table. If we want to accommodate more than one table we
• need to do inner join and fill the record set. Dataset can have multiple tables.
• All data persist in XML as compared to classic ADO where data persisted in Binary format also.