This article provides latest ADO.Net Interview Questions and Answers for Freshers and 2-3 years experienced dot net developers with sample code and detailed explanations on Dataset, Datareader, Datatable, DataView, Connection object, Transactions, SQL Commands, Data adapter, Data Providers, Locking, Connection pooling and Connection String.

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 n no of rows in a database 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 and 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 support forward only data iteration, random data fetch is not supported.

DataSet is an in-memory representation of a table in a database. Dataset takes lot of application memory compared to DataReader. It is 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) Write a sample ADO.Net Connection to SQL Server ?

using System.Data.SqlClient;

try
{
// Open DB connection
string Connection = "server=localhost; uid = UserName ; pwd = Password ; database = DemoDB";
SqlConnection conn = new SqlConnection(Connection);
conn.Open();
}
catch ( Exception ex )
{
// Log Exceptions
}

finally
{
conn.Close ( ) ; // Close the connection
}

10) Explain Transactions in ADO.Net ?

When one or more related SQL Commands are executed and if any one statement failed to execute, we need to rollback the entire related operations that has been executed. In such a scenario, Transactions are used.

using System.Data.SqlClient;
try
{
string Connection = "server=localhost; uid = UserName ; pwd = Password ; database = DemoDB";
SqlConnection con = new SqlConnection(Connection);
con.Open();

SqlTransaction SqlTx = con.BeginTransaction();
// Execute SQL commands
tx.Commit(); // Commit Transaction
con.Close();

Also Read - .Net Framework Interview Questions and Answers

11) Explain ReadCommitted and ReadUncommitted in Transactions ?

All simple Transactions are ReadCommitted by default. If two persons are accessing the same table and if one person executes an insert statement, another 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.

12) Explain SqlCommand Objects ?

SQLCommand in simple terms is a collection of strings containing SQL statements which will be send to database for various operations. SQL Commands can be of type Select, Insert, Delete,Update or Stored procedures.

string Connection = "server=localhost; uid = UserName ; pwd = Password ; database = DemoDB";
SqlConnection con = new SqlConnection(Connection);
con.Open();

string select = "Select * from Employee_Table";
SqlCommand cmd = new SqlCommand(select, conn);
con.Close();

Also Read - C# OOPS Interview Questions and Answers

13) What are the different Command types in Commands ?

Different Command Types are Default, CommandType.StoredProcedure and CommandType.TableDirect .

For normal queries like select , command type is not specified .

string select = "Select * from Employee_Table";
SqlCommand cmd = new SqlCommand(select, conn);

For Stored Procedures, command type should be specified as shown below

SqlCommand cmd = new SqlCommand("Employee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@EmployeeID", "EMP-761");

For TableDirect (fetch all rows and columns from a table) , command type should be specified as shown below

OleDbCommand cmd = new OleDbCommand("Employees", conn);
cmd.CommandType = CommandType.TableDirect;

14) 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 which return a typed IDataReader
ExecuteScalar() – Used to execute the commands which 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.

15) 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.

16) 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.

17) What’s is the use of “DataView” ?

Dataview is used for filtering and sorting data in a datatable.

//Code to sort by employee name in a data table
DataTable Employee = GetTable();
Employee.DefaultView.Sort = "Name";

18) What is the use of dataadapter ?

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.

string Connection = "server=localhost; uid = UserName ; pwd = Password ; database = DemoDB";
SqlConnection conn = new SqlConnection(Connection);
conn.Open();
string StrSql = "Select * from Employee_Table";
SqlDataAdapter Adapter = new SqlDataAdapter();
Adapter.SelectCommand = new SqlCommand(StrSql, conn);
DataSet ds = new DataSet("Employees");
Adapter.Fill(ds);

// Code to update data in a data source
ds.Tables["Employee_Table"].Rows[0]["Name"] = "Alen";
Adapter.Update(ds, "Employee_Table");

19) What’s difference between “Optimistic” and “Pessimistic” locking ?

Pessimistic locking is used when the 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. Optimistic locking is used to improve concurrent operations on a record by allowing multiple users to update the same record. The record is only locked when updating the record. Optimistic locking has been widely used by web developers.

20) 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.

21) Explain in detail the fundamental of connection pooling ?

Connection pooling is used to reduce the overhead of creating a connection to database. A connection pool will have a predefined number of min and max connections to database which will used by application to execute queries. Once application completes a transaction, connection will be released and connection will go back to connection pool rather than connection getting closed.

22) 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 and 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.

23) 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 .

24) 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.

25) What are the data providers in ADO.NET framework ?

Major Data Providers used in ADO.NET framework are

.NET Framework Data Provider for SQL Server – For connecting .Net application with SQL Server. Namespace used for SQL Server Connectivity is System.Data.SqlClient namespace.
Data Provider for SQL Server Compact 4.0 - For connecting .Net application with SQL Server Compact 4.0. Namespace used for SQL Server Compact 4.0 Connectivity is System.Data.SqlServerCe
.NET Framework Data Provider for OLE DB – For connecting .Net application with data sources exposed by OLE. Namespace used for OLE DB Connectivity is System.Data.OleDb namespace.
.NET Framework Data Provider for ODBC – For connecting .Net application with data sources exposed by ODBC. Namespace used for ODBC Connectivity is System.Data.Odbc namespace.
.NET Framework Data Provider for Oracle – For connecting .Net application with Oracle database 8.1.7 or later. Namespace used for oracle Connectivity is System.Data.OracleClient namespace.
EntityClient Provider - For connecting .Net application with Entity Data Model (EDM) applications. Namespace used for EDM Connectivity is System.Data.EntityClient namespace

26) What is the difference between Command and CommandBuilder object ?

Command is used to execute all kind of 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.

27) 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.

28) What are different layers of ADO.Net?

Database Access Layer
Business Logic Layer
Presentation Layer

29) What is the difference between typed and untyped dataset ?

Typed datasets have 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.

30) Which object is used to add relationship between two Datatables ?

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

31) 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.

32) Explain the new features in ADO.NET Entity Framework 4.0.

New notable features in ADO.NET Entity Framework 4.0 are

Persistence Ignorance - When an object is persisted in a persistent medium, the underlying details of the object are hidden or ignored
Deferred or Lazy Loading - Allows entities to be loaded in to the memory after a delay. Set ContextOptions.LazyLoadingEnabled property to true to enable lazy loading.
Self-Tracking Entities – To track changes made to entities.
Model-First Development – Allows users to drive physical or logical model from the conceptual model
Built-in Functions - Enables built-in and user defined SQL Server functions directly in queries.
Model-Defined Functions - Enables the use of the conceptual schema definition language (CSDL).

SIRIS

ADO.Net Interview Questions and Answers for Freshers, Experienced

Complete Tutorials

Popular Posts

Labels