ADO.Net Interview Questions and Answers Part-I

ADO.Net Interview Questions and Answers:

Part -I

1)       What is an ADO.Net?

ADO.Net is commonly termed as ActiveX Data Objects which is a part of .Net Framework. ADO.Net framework has set of classes which are used to handle data access by connecting with different databases like SQL, Access, Oracle, etc…

      2)   What are two important objects of ADO.Net?

There are two important objects of ADO.Net:

DataReader and DataSet.

     3)    What are the namespaces used in ADO.Net to connect to a database?

                         The Following namespaces are used to connect to Database.

·         System.Data

·         System.Data.OleDb – A data provider used to access database such as Access, Oracle, or SQL.

·         System.Data.SQLClient -- Used to access SQL as the data provider.

·          

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

Below Data Providers are used in ADO.NET framework.

·         .NET Framework Data Provider for SQL Server – A Data provider that provides access to Microsoft SQL Server 7.0 or later version and it uses the System.Data.SQLClient namespace.

·         .NET Framework Data Provider for OLE DB – A Data Provider that provides access to any database exposed by using OLE DB and it uses the System.Data.OleDb namespace.

·         .NET Framework Data Provider for ODBC – A Data Provider that provides access to any databases exposed by using ODBC and It uses the System.Data.Odbc namespace.

·         .NET Framework Data Provider for Oracle – A Data Provider that provides access to Oracle database 8.1.7 or later versions and it uses the System.Data.OracleClient namespace.

     5)  What is DataReader Object?

A DataReader is an object of ADO.Net which provides access to data from a specified data source. It consists of classes which sequentially read data from a data source like Oracle, SQL or Access.

    6)  What is Dataset Object?

A Dataset is set to be collection of data with a tabular column representation. Each column in the table represents a variable and the row represents to value of a variable. This Dataset object can be obtained from the database values.

    7) What is object pooling?

An Object pooling is nothing but a repository of the objects in memory which can be used later. This object pooling reduces the load of object creation when it is needed. Whenever there is a need of object, object pool manager will take the request and serve accordingly.

    8) What is connection pooling?

                                A Connection pooling consists of database connection so that the connection can be used or reused whenever there is request to the database. This pooling technique enhances the performance of executing the database commands. This pooling definitely reduces our time and effort.

9)  What is Data view?

Data view is the representation of data in various formats and it can be requested by the users. Data can be exposed in different sort orders or filter on the user condition with the help of Data view. Data Customization is also possible through Data View.

10) What is Data Adapter?

Data Adapter is a part of ADO.NET data provider which acts as a communicator between Dataset and the Data source. This Data adapter can perform Select, Insert, Update and Delete operations in the requested data source.

11)  What is the use of SqlCommand object?

SQLCommand object that allows user to interact with the database. This object mainly used to query the database and it can be of different types – Select, Insert, Update and Delete.

12)   What is the difference between ADO and ADO.Net?

ADO works with the connected data whereas ADO.Net works in a disconnected manner. ADO has main object called Recordset which is used to reference data. But ADO.Net has various objects to access the database.

ADO allows creating client side cursors whereas ADO.Net deals with both server side and server side cursors. ADO allows persisting records in XML format and ADO.Net allows manipulating data using XML.

13)   What are the benefits of ADO.Net?

The Following are the benefits of ADO.Net:

·         Programmability.

·         Maintainability.

·         Interoperability.

·         Performance.

·         Scalability.

14)  Connection string for SQL Server?

Standard Security

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

Trusted Connection:

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

 15)   What is the use of connection object?

ADO.Net Connection object is used to establish a connection between application and the data source. SQL Commands can be executed once this connection has been established. It is mandatory to close the connection object once data base activities are completed.

16)   What are all features of ADO.Net?

The Following are the features of ADO.Net:

·         Data Paging

·         Bulk Copy Operation

·         New Data Controls

·         DataReader’s execute methods.

17)   What is the difference between Responses. Expires and Response.ExpiresAbsolute?

Response. Expires property specify the minutes of page in cache from the time, the request has been served from server.

But Response.ExpiresAbsolute property provides exact time at which the page in cache expires.

Example -

Response. Expires – Set to 10 mins and it will stay in cache for 10 mins from time it has been requested

Response.ExpiresAbsolute – Oct 30 12:20:15. Till this specified time, Page will be in cache.

18)   What is the difference between Datareader and Dataset?

Following table gives difference between DataReader and Dataset:

DataReader	Dataset
Forward only	Loop through Dataset
Connected Recordset	Disconnected Recordset
Single table involved	Multiple tables involved
No relationship required	Relationship between tables maintained
No XML storage	Can be stored as XML
Occupies Less Memory	Occupies More memory
Read only	Can do addition / Updating and Deletion

19)   What are all components of ADO.Net data provider?

Following are the components of ADO.Net Data provider:

• Connection object – Represents connection to the Database
• Command object – Used to execute stored procedure and command on Database
• ExecuteNonQuery – Executes command but doesn’t return any value
• ExecuteScalar – Executes and returns single value
• ExecuteReader – Executes and returns result set
• DataReader – Forward and read only recordset
• DataAdapter – This acts as a bridge between database and a dataset.

 20)   What are the differences between OLEDB and SQLClient Providers?

OLEDB provider is used to access any database and provides flexibility of changing the database at any time.

SQLClient provider is used to access only SQL Server database but it provides excellent performance than OLEDB provider while connecting with SQL Server database.

21)   What are the different execute methods of Ado.Net?

Following are different execute methods of ADO.Net command object:

• ExecuteScalar – Returns single value from the dataset
• ExecutenonQuery – Returns resultset from dataset and it has multiple values
• ExecuteReader – Forwardonly resultset
• ExecuteXMLReader – Build XMLReader object from a SQL Query

 22)   What are all the commands used with Data Adapter?

DataAdapter is used to retrieve data from a data source .Insertcommand, UpdateCommand and DeleteCommand are the commands object used in DataAdapter to manage update on the database.

 23)   What are all the different methods under sqlcommand?

There are different methods under SqlCommand and they are:

·         Cancel – Cancel the query

·         CreateParameter – returns SQL Parameter

·         ExecuteNonQuery – Executes and does not return result set

·         ExecuteReader – executes and returns data in DataReader

·         ExecuteScalar – Executes and returns single value

·         ExecuteXmlReader – Executes and return data in XMLDataReader object

·         ResetCommandTimeout – Reset Timeout property

 24)   What is the difference between Dataset. Clone and Dataset. Copy?

Dataset. Clone object copies structure of the dataset including schemas, relations and constraints. This will not copy data in the table.

Dataset. Copy – Copies both structure and data from the table.

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

Command is used to execute all kind of queries like DML and DDL. DML is nothing but Insert, Update and Delete. DDL are like Create and drop tables.

Command Builder object is used to build and execute DML queries like Create and Drop Tables.

26)  Is it possible to load multiple tables in a Dataset?

Yes, it is possible to load multiple tables in a single dataset.

27)  Which provider is used to connect MS Access, Oracle, etc…?

OLEDB Provider and ODBC Provider are used to connect to MS Access and Oracle. Oracle Data Provider is also used to connect exclusively for oracle database.

28)   Do we use stored procedure in ADO.Net?

Yes, stored procedures are used in ADO.Net and it can be used for common repetitive functions.

29)  What are the methods of XML dataset object?

There are various methods of XML dataset object:

GetXml() – Get XML data in a Dataset as a single string.

GetXmlSchema() – Get XSD Schema in a Dataset as a single string.

ReadXml() – Reads XML data from a file.

ReadXmlSchema() – Reads XML schema from a file.

WriteXml() – Writes the contents of Dataset to a file.

WriteXmlSchema() – Writes XSD Schema into a file.

 30)  What are all the different authentication techniques used to connect to MS SQL Server?

SQL Server should authenticate before performing any activity in the database. There are two types of authentication:

·         Windows Authentication – Use authentication using Windows domain accounts only.

·         SQL Server and Windows Authentication Mode – Authentication provided with the combination of both Windows and SQL Server Authentication.

31)  What are the Data providers in ADO.Net?

Following are the Data Providers used in ADO.Net:.

·         MS SQL Server.

·         OLEDB.

·         ODBC.

 32)  Which method is used by command class to execute SQL statements that return single value?

Execute Scalar method is used by command class to execute SQL statement which can return single values.

33)  Which keyword is used to accept variable number of parameters?

Params keyword is used to accept variable number of parameters.

34)  Which method in OLEDBAdapter is used to populate dataset with records?

Fill Method is used to populate dataset with records.

35)  Which object needs to be closed?

OLEDBReader and OLDDBConnection object need to be closed. This will stay in memory if it is not properly closed.

36)  What are different layers of ADO.Net?

There are three different layers of ADO.Net:

·         Presentation Layer

·         Business Logic Layer

·         Database Access Layer

 37)   What are typed and untyped dataset?

Typed datasets use explicit names and data types for their members but untyped dataset uses table and columns for their members.

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

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

39) Which is the best method to get two values from the database?

ExecuteNonQuery is the best method to get two values from the database.

40)  What are all the classes that are available in System.Data Namespace?

The Following are the classes that are available in System.Data Namespace:

·         Dataset.

·         DataTable.

·         DataColumn.

·         DataRow.

·         DataRelation.

·         Constraint.

41)   What are the uses of Stored Procedure?

The Following are uses of Stored Procedure:

·         Improved Performance.

·         Easy to use and maintain.

·         Security.

·         Less time and effort taken to execute.

·         Less Network traffic.

 42) What is the default Timeout for SqlCommand.CommandTimeout property?

The default timeout of Sqlcommand. Command Timeout property is 30 Seconds.

43)  What are the classes in System.Data.Common Namespace?

There are two classes involved in System.Data.Common Nameapce:

·         DataColumnMapping.

·         DataTableMapping.

44) What extra features does ADO.Net 2.0 have?

         The following are the features in ADO.Net 2.0:

Bulk Copy Operation
               Data Paging
               Batch Update
               Load and Save Methods
              New Data Controls
              DataReader's New Execute Method

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

Optimistic: Optimistic Locking allows multiple clients to use the same data.
Pessimistic: Client who wants to make updating must check out the data and do the work. If it is checked out by some user. it won’t be updated by other user.

Thanks to Google