ADO.NET – DOT NET Chapter Wise Interview Questions
What are the different components in ADO.NET?
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 database. Using command object we can execute select, insert, update and delete SQL commands.
- Data Reader: This provides a recordset which can be browsed only in forward direction. It can only be read but not updated. Data reader is good for large number of records where you want to just browse quickly and display it.
- Dataset Object: This provides a recordset which can be read back and in forward direction. The recordset can also be updated. Dataset is like 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 Data table of dataset.
Note: Remember (2C4D), 2C for connection and command, 4D for dataset, datareader, dataview and dataadapter.
What is the namespace in which .NET has the data functionality class?B)
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 helps us to connect to SQL Server database.
- System.Data.OleDB: This namespace has objects which helps 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.
When should we use System.Data.SqlClient and System.Data.OleDB?
If you want to connect only to SQL Server use SqlClient or else use OleDB. OleDB also connects to other database plus SQL server .SqlClient is specifically meant for SQL server so has better performance as compared to OleDB.
What is difference between dataset and data reader?
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 dataset moves back as well as forward.
- Dataset object is an in-memory database with tables, rows and columns while datareader is just simple table which can be read only in a forward direction.
- Dataset can persist contents while data reader cannot persist contents, they are read only and forward only.
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 stored procedure. Returns a “Datareader” object.
- ExecuteScalar: Executes SQL command and returns only a single value like count, sum, first record, etc.
What are Dataset objects?
Dataset is an in-memory object with data tables, rows and columns as shown in Figure 4.3. You can visualize it as 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 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 yalue. You can see how the full dataset object hierarchy is accessed to get the column value.
objDataset. Tables. Rows[“CustCode "]
What is the use of data adapter?
Data adapter object acts like a bridge. It helps to load the dataset object. Below is a simple code snippet which 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();
What are basic methods of data adapter?
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.
How can we fire a simple SQL statement using ADO?
- First import the namespace “System.Data.SqlClient”.
- Create a connection object and call the “Open” function.
.Connection String = strConnectionString
- Create the command object with the SQL. Also, assign the created connection object to command object and execute the reader.
ObjCommand = New SqlCommand ("Select First Name from Employees")
.Connection = objConnection Breeder = .Execute Reader ()
- You can then loop through the reader object to read the data.
Do while objReader.Read ()
IstData.Items.Add (objReader.Item ("First Name"))
- Do not forget to close the connection object.
objConnection. close *();
How do we use stored procedure in ADO.NET and how do we provide parameters to the stored procedures?
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;
How can we force the connection object to close after my data reader is closed?
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)
I want to force the data reader to return only schema of the data store rather than data.
PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior. SchemaOnly)
How can we fine-tune the command object when we are expecting a single row?
Again, CommandBehavior enumeration provides two values Single Result and Single Row. If you are expecting a single value then pass “CommandBehavior. singleResult” and the query is optimized accordingly, if you are expecting single row then pass “CommandBehavior. 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.
How do you fill the dataset?
Create object of data adapter and call the Fill command method of the adapter.
SqlDataAdapter objAdapter = new SqlDataAdapter (objCommand) ;
DataSet objDataset = new DataSet();
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 save all data from dataset?
Dataset has “AcceptChanges” method, which commits all the changes since last time “AcceptChanges” has been executed.
How can we check which rows have changed since dataset was loaded?
For tracking down changes, Dataset has two methods, which comes to rescue “Get Changes “and “Has Changes”.
- Get Changes: Returns dataset, which are 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 been made since the dataset was loaded or AcceptChanges method was executed.
Note: One of the most misunderstood things about these properties is that it tracks the changes of actual database. That is a fundamental mistake; actually the changes are related to only changes within dataset and have nothing to with changes happening in actual database. Dataset are disconnected and do not know anything about the changes happening in actual database.
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 “DataRowCol lection” 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 “DataView”?
“DataView” is used for sorting and finding data within “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.
How can we load multiple tables in a Dataset?
obj Command. ConimandText = "Tabie1"
objCommand.CommandText = "Table2"
Above is a sample code, which shows howto load multiple “Data Table” objects in one “Dataset” object. Sample code shows two tables “Tablel” and “Table2” in object objDataSet.
Istdata. DataSource = objDataSet. Tables(“Table 1 ’). DefauItView
In order to refer “Tablel” Data Table, use Tables collection of Datasets and the Default view object will give you the necessary output.
How can we add relation between tables in a Dataset?
Dim objRelation As DataRelation
DataRelation("CustomerAddresses " , objDataSet.Tables("Customer" )
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.
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.
Dim pobjCommandBuilder As New OleDbCommandBuilder(pobjDataAdapter)
Be careful while using “Derive Parameters” method as it needs an extra trip to the Data store, which can be very inefficient.
Can you explain the term concurrency?
Concurrency issues happen in 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 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 database. In other words he/she changes the data to “Z”.
- Now User A also sends the update to 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.
How can we resolve concurrency issue?
Concurrency issue can be resolved by using optimistic or pessimistic locking.
What’s difference between “Optimistic” and “Pessimistic” locking?
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 exception.
In pessimistic locking you actually lock the record, depending on the type of lock no other process can make modifications to the record.
How many ways are there to implement optimistic locking in ADO.NET?
Following are the ways to implement optimistic locking using 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 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 which checks for timestamp before updating and any mismatch in timestamp it will not update the records. This approach is the best practice used by industries for locking.
Update tablel set field 1=@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 f ieldl we check that is the old f ieldl value same. If not then someone else has updated and necessary action has to be taken.
Update tablel set field1=@test where fieldl = @oldfieldl value
How can do pessimistic locking?
Pessimistic locking is done by using transaction isolation levels like read committed, read uncommitted, repeatable read and serializable.
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 uselater 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 oneormorecommand 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 anADO.NET Dataset and an ADO Record set?
There two main basic differences between record set and dataset:
- Using dataset you can 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 uses 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.
Let us try to explain the same pictorially. In the Figure 4.4, you can see there are three requests “Requestr, “Request2”, and “Request3”. “Requestl” and “Request3” have same connection string so no new connection object is created for “Request3” as the connection string is same. They share the same object “ConObjectl”. However, 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 other has “User id=Testing”.
What is 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’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.
How to enable and disable connection pooling?
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 connectionstring if it is anADO.NET Connection.
If it is an OLEDBConnection object, set oledb Services= -4 in the connection string.
What are the major differences between classic ADO and ADO.NET?
Following are some major differences between both:
- In ADO we have recordset and in ADO.NET we have dataset.
- In recordset we can only have one table. If we want to accommodate more than one tables we need to do inner join and fill the recordset. Dataset can have multiple tables.
- All data persist in XML as compared to classic ADO where data persisted in Binary format also.