ADO.NET INTERVIEW QUESTIONS & ANSWERS

1. What is ADO.NET ? 

ADO.Net is one of the major component of .Net Framework, which is designed to connect to databases like Oracle, MySQL, SQL Server, MS Access etc. and work with data stored in them.

2. Explain Generic Features of ADO.Net ?

• ADO.Net provides in built classes to connect with Databases like Oracle, MySQL, SQL Server, MS Access etc.
• Provides in built classes to do data manipulation operations like Insert, Update, Delete and Select Data.
• Provides data providers for specific databases for efficient interactions with DB. Example ODP.Net provider for Oracle.
• Tight integration with XML
• Provides functionality to combine data from different data sources
• Disconnected Data architecture for better performance

3. What are the important features of ADO.Net 2.0 ?

Bulk Copy Operation from one Data Source to another Data Source 

• Batch Update – To update no of rows in a table in a single call from a program thus by avoiding round trip to database.
• Data Paging – To read data from a certain index
• Connection Details – To get detailed info about connections like buffer information,cursor details etc
• DataSet.RemotingFormat Property – To make dataset serialized in Binary
• DataTable's Load and Save Methods – For XML interactions.

4. What are the namespaces used in ADO.NET for data access ?

Namespaces used to access database are

• System.Data – Contains all generic data access classes
• System.Data.Common – Contains classes which are shared / overridden by data providers
• System.Data.OleDb - OLE DB provider classes used to access database such as Oracle, MySQL, SQL Server and MS Access.
• System.Data.SqlClient – Contains classes for SQL Server
• System.Data.SqlTypes – Contains SQL Server data types

5. What are major difference between classic ADO and ADO.NET ?

ADO

• ADO have recordset
• ADO objects communicate in binary mode.
• ADO Supports mostly Connection oriented Models
• Since ADO derives information about data implicitly at run time based on metadata , it is an expensive process.
• Only Client Side Cursors are allowed in ADO.

ADO.Net

• ADO.Net have Data Adapter and Data set
• ADO.NET uses XML for passing the data.
• ADO.Net works in Disconnected manner
• By leveraging known meta data at design time, ADO.Net provide better runtime performance and more consistent runtime behaviour
• ADO.Net Support both client side and server side cursors

6. What are the two fundamental objects in ADO.NET ?

Fundamental Objects of ADO.Net are DataReader and DataSet . DataSet Object can have a set of DataTables, relationships between these tables. DataSet can be used in disconnected connection with DB. DataReader is used to read the read only data from a database.

7. What is difference between dataset and datareader ?

DataReader is used to fetch data from a database in a much faster way. Since the rows are fetched one at a time, load on the network will be low. Since DataReader is read only, transactions are not allowed . Since it is forward only, random data fetch is not supported.

DataSet is an in memory representation of a tables in a database. Dataset takes lot of application memory compared to DataReader. Its slower compared to DataReader. But user can do transactions using DataSet. It also support querying.

8. What is the use of connection object ?

Connection Objects is used to establish a connection between an application and databases like Oracle, MySQl, SQL Server etc. Once connection is established, SQL Commands like insert, update, delete and select can be executed. Scope of a connection object can be local or global. If local connection object is used, it should be closed after SQL commands are executed.

9. Explain ReadCommitted and ReadUncommitted in Transactions ?

All simple Transcations are ReadCommitted by default. If two persons are accessing same table, if one persons executes an insert , other person will be able to access the newly inserted row only after the first person commit the transaction. If a transaction is set as ReadUncommited, in the two person scenario, second person will be able to read the information inserted by first person before he commits the transaction.

10.  What is a Dataset object ?

The DataSet is used to store data from a data source . It consists of a set of data tables. We can also specify the relation between the tables inside a dataset.

11. What is a Datatable object ?

Data Table is used to store the data retrieved from a database in application memory. Data table will have a set of data columns and data rows. Data table supports operations like adding, updating and deleting a row.

12. What are the different execution methods provided by the command object ?

After creating an SQL or OLEDb Command, it needs to be executed. Following methods are used to execute SQL Commands.

• ExecuteNonQuery() – Used to execute the command which did not return any output
• ExecuteReader() – Used to execute the command and return a typed IDataReader
• ExecuteScalar() – Used to execute the commands that return a single value
• ExecuteXmlReader() – Used to execute the command that returns an XmlReader object. The object can be used to traverse the XML fragment returned from DB.

13. What is the use of data adapter ?

To populates tables within a dataset from a data source, DataAdapter is used. DataAdapter uses ADO.Net Connection object to connect to a data source. It uses Command Objects like Insertcommand, UpdateCommand and DeleteCommand to make changes to a data source.

14.  What’s difference between “Optimistic” and “Pessimistic” locking ? 

Pessimistic locking is used when user wants to update a record in a database and the user want to prevent other users from updating the same record. Other user’s can only view the data when there is pessimistic locking. In optimistic locking is used to improve concurrent operations on a record by allowing multiple users to update the same record. Record is only locked when updating the record. Optimistic locking has been widely used by web developers.

15. What’s difference between Dataset. clone and Dataset. Copy ?

Dataset.Clone only copies the schema of a DataSet object like relations between data tables and constraints. Dataset.Copy copies both the data and structure of a DataSet object.

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

Default max pool size is 100. When an application requests for connection from a pool , if the connections are free, pool will return a free connection. If all the connections are in use and connection pool max size is not reached , pool will create a new connection and send it to the application. When a request for new connection arrives and If the max pool size is reached and all the connections are busy, it will be made to wait till one of the existing connection get released back to pool.

17. How to enable and disable connection pooling ? 

Use Pooling = true in connection string , if we want to enable connection pooling. To disable connection pooling set Pooling = false .

18. What is LINQ ?

Language-Integrated Query (LINQ) is one of the advanced featured provided by .Net. LINQ provides native query language for C# and VB.Net to update and retrieve data from different data sources like SQL Server Database,XML etc.

19.  What is the difference between Command and CommandBuilder object ?

Command is used to execute all kind SQL queries like data manipulation languages (DML) and Data definition language(DDL) . DML operations are SELECT, INSERT, UPDATE, and DELETE . DDL operations are Create and drop tables. Command Builder object is used to build and execute DML queries like select, insert and delete table.

20.  What are the methods of XML dataset object ?

Different methods of XML dataset object:

• ReadXml(Stream) – Uses System.IO.Stream to read XML schema and data into the DataSet
• ReadXml(String) – Reads XML data from the specified file.
• ReadXmlSchema() – Reads XML schema from the specified file.
• GetXml() – Get XML data in a Dataset as a single string.
• GetXmlSchema() – Get XSD Schema in a Dataset as a single string.
• WriteXml() – Writes the contents of Dataset to the specified file.
• WriteXmlSchema() – Writes XSD Schema into the specified file.

21.  What are different layers of ADO.Net? 

• Database Access Layer
• Business Logic Layer
• Presentation Layer

22. What is the difference between typed and untyped dataset ?

Typed datasets have and associated schema file. Error checking will be done during design time with respect to schema. It also use explicit names and data types for their members . Untyped dataset uses table and column collections for their members. There wont be any error checking associated with Untyped Dataset, since they are populated at runtime.

23. Which object is used to add relationship between two Datatables ?

DataRelation object is used to add relationship between two or more datatable objects.

24. What are the parameters that control most of connection pooling behaviors ?

• Max Pool Size – Maximum size of connection pool
• Min Pool Size – Minimum no of connections that will be created in connection pool at application start up.
• Pooling – To set connection pooling to on or off.
• Connect Timeout – Wait period for new connection.