Thursday 17 December 2015

ADO.Net Interview Questions and Answers for Freshers, Experienced page

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();

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

No comments:

Post a Comment