ADO.Net Interview Questions(68)

           ADO.Net Interview Questions it consists of set of interview questions which grabs the All levels from Intial Level to High Level definately it covers the potential stuff to all my viewers.
             ADO.Net Interview Questions all provides the list of all entitled elements which provides an integrity solutions to the all my aspirants.
                                  "I Wish You All The Best...!To Crack Your Desired Position..."

                                      ADO.Net Interview Questions(68)

1.What is the Acronym of ADO?

A)ADO-Active or Activex Data Object.In traditional approaches of MicroSoft we use ADO for front end to backend connection.Now with introduction of .Net usese ADO.Net

2.What is the Acronym of ADO.Net?

A)Active or Activex Data Object For .Net.

3.Define ADO.Net?

A)ADO.Net is a collection of different set of native providers which provides flexibility to work with front end to backend.ADO.Net is one of the primary component in .Net for making to store information in the database.

4.List the different set of Namespaces Available in Ado.Net?

A)Primarly we have Four different Namespaces Available in Ado.Net.They are

1.      System.Data

2.      System.Data.SqlClient

3.      System.Data.Oledb

4.      System.XML

5.What is the purpose of System.Data?

A) This contains the basic objects used for accessing and storing relational data, such as

DataSet,DataTable, and DataRelation. Each of these is independent of the type of data

source and the way we connect to it.

6.What is the purpose of System.Data.SqlClient?

A) This contains the objects that we use to connect to a data source via the Tabular Data Stream (TDS) interface of Microsoft SQL Server (only). This can generally provide better performance as it removes some of the intermediate layers required by an OLE-DB connection.

7. What is the purpose of System.Data.Oledb?

A) It contains the objects that we use to connect to a data source via an OLE-DB provider,such as OleDbConnection, OleDbCommand, etc. These objects inherit from the common base classes, and so have the same properties, methods, and events as the SqlClient equivalents.

8.What is the purpose of System.XML?

A) This Contains the basic objects required to create, read, store, write, and manipulate XML documents according to W3C recommendations.

9.List the difference between ADO and ADO.Net?

A)Following are the difference between ADO and ADO.Net.They are:

SNO	ADO(Classic ADO)	ADO.Net
1	In ADO Client and Server Side Cursors are there	In ADO.Net there are no more Client and Server side cursors
2	It can be used in Traditional Model approach	It can be used in Conventional Model approach
3	Here we have a RecordSet	Here we have DataSet
4	In RecordSet we have to store only one table.If we want to store multiple tables we need to use join those tables and fill it in recordset	Dataset can accomidate multiple tables.
5	Locking Mechanism is Supported	Locking Mechanism is Not Supported
6	All data persist in XML as compared to classic ADO.	where data persisted in Binary format also.

10.Can you illustrate an Overview of ADO.Net architecture?

A)The most important component in ADO.Net architecture is “DataProvider”.Data Provider is nothing but database either we connect with MSSqlServer or MSAcess or Oracle Or MySql etc.In order to connect with DataProvider we have to follow the following steps.This is the Overview of an ADO.Net architecture.They are

1.Connection(It is used to establish a connection from Front End to Back End)

2.Command Object(It is used as an callable object from Front End to Back End.Here we can pass command i.e query as per our requirement)

3.DataAdapter(It acts as a bridge between datastore and dataset)

4.DataReader(This object reads data from data store in forward only mode)

11.What are two basic Objects in ADO.Net?

A)DataReader and Dataset are the two basic Objects in ADO.Net.

12.List the Difference between DataReader and DataSet?

A)

SNO	DataSet	DataReader
1	It is a InMemory object used to store data in an efficient manner	It is an memory object which holds data in an indexed based format
2	DataSet provides both forward and reverse direction	DataReader provides forward-only and read-only access to data
3	DataSet works under disconnected architecture	DataReader works under connected architecture
4	DataSet can persist contents	DataReader cannot persist contents

13.What is the Use of Connection Object?

A) They are used to connect a data to a Command object.

 An OleDbConnection object is used with an OLE-DB provider.

A SqlConnection object uses Tabular Data Services (TDS) with MS SQL Server

14.What are the connections does MicroSoft Sql Server supports?

A)Windows Authentication and SQL Server Authentication

15.Which type of authentication is trusted and which one is untrusted?

A) Windows Authentication is trusted because the username and password are checked with the active directory,the SQL Server authentication is untrusted,since SQL Server is the only verifier participating in the transaction.

16.What does the Initial catalog parameter define in the connection string?

A)The database name to connect to.

17.What does the Dispose method do with the connection object?

A)Dispose method is used to deletes it from the memory.

18.What is a pre-requisite for connection pooling?

A)Multiple processes must agree that they will share the same connection,where every parameter is the same,including the security settings.The connection string must be identical.

19. What is connection pooling?

A)Connection pooling refers to the task of grouping database connections in cache to make them reusable because opening new connections every time to a database is a time-consuming process. Therefore, connection pooling enables you to reuse already existing and active database connections, whenever required, and increasing the performance of your application.

You can enable or disable connection pooling in your application by setting the pooling property to either true or false in connection string. By default, it is enabled in an application.

20.What is the Wildcard character in SQL?

A)Wildcard character is ‘%’,the proper query with like would involve ‘Ve%’

21.What are different methods in Command object?

A)We have 3 types of methods.They are:

1.ExecuteNonQuery

2.ExecuteReader

3.ExecuteScalar

22.Define ExecuteNonQuery?

A) Executes the command defined in the CommandText property against the connection defined in the Connection property for a query that does not return any row (an UPDATE, DELETE or INSERT). Returns an Integer indicating the number of rows affected by the query.

23.Define ExecuteReader?

A) Executes the command defined in the CommandText property against the connection defined in the Connection property. Returns a "reader" object that is connected to the resulting rowset within the database, allowing the rows to be retrieved.

24.Define ExecuteScalar?

A) Executes the command defined in the CommandText property against the connection defined in the Connection property. Returns only single value (effectively the first column of the first row of the resulting rowset) any other returned columns and rows are discarded. It is fast and efficient when only a "singleton" value is required.

25.What is the use of DataAdapter?

A) These are objects that connect one or more Command objects to a Dataset object. They provide logic that would get data from the data store and populates the tables in the DataSet, or pushes the changes in the DataSet back into the data store.

26.What are the methods in DataAdapter?

A) In DataAdapter we are using three methods most commonly.They are:

1.Fill

2.FillSchema

3.Update

27.Define Fill?

A) Executes the SelectCommand to fill the DataSet object with data from the data source. It an also be used to update (refresh) an existing table in a DataSet with changes made to the data in the original datasource if there is a primary key in the table in the DataSet.

28.Define FillSchema?

A) It Uses the SelectCommand to extract just the schema for a table from the data source, and creates an empty table in the DataSet object with all the corresponding constraints.

29.Define Update?

A)It Calls the respective InsertCommand, UpdateCommand, or DeleteCommand for each inserted, updated,or deleted row in the DataSet so as to update the original data source with the changes made to the content of the DataSet. This is a little like the Update.Batch method provided by the ADO Recordset object, but in the DataSet it can be used to update more than one table.

30.Define DataSet object?

A) It is an In memory object which can works in both forward and reverse direction and in disconnected oriented architecture it provides a flexible mechanism for retrieveing of data.

31.List the various objects in DataSet?

A)These are following objects in DataSet.They are:

1.DataTable

2.Data Row

3.Data Column

4.Default View

5.Data View

DataSet has a collection of DataTable objects within the Tables collection. Each DataTable object contains a collection of DataRow objects and a collection of DataColumn objects. There are also collections for primary keys, constraints, and default values used in this table, which is called as constraint collection, and the parent and child relationships between the tables. Finally, there is a DefaultView object for each table. This is used to create a DataView object based on the table, so that the data can be searched, filtered, or otherwise manipulated while displaying the data.

32.What is the namespace used for connection of SQL Server?

A)Using System.Data.SqlClient

33.What is the namespace used for connection of FoxPro?

A)Using System.Data.Oledb

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

A)Clone:It only copies structure,doesnot copy data.

Copy:It copies both structure and data.

35.List the difference between in ADO.Net Dataset and an ADO Recordset?

A) There two main basic differences between recordset and dataset :-

1.With dataset you an retrieve data from two databases like oracle and sql server and merge them in one dataset , with recordset this is not possible

2.All representation of Dataset is using XML while recordset uses COM.

3.Recordset can not be transmitted on HTTP while Dataset can be.

36.Define Object Pooling?

A) Object pooling is a concept of storing a pool (group) of objects in memory that can be reused later as needed. Whenever, a new object is required to create, an object from the pool can be allocated for this request; thereby, minimizing the object creation. A pool can also refer to a group of connections and threads. Pooling, therefore, helps in minimizing the use of system resources, improves system scalability, and performance.

37. How can we force the connection object to close after my datareader is closed ?

A) Command method Executereader takes a parameter called as CommandBehavior where in we can specify saying close connection automatically after the Datareader is close.

E.g:Datareaderobject=CommandObject.ExecuteReader(CommandBehaviour.CloseConnection)

38. How can we force the datareader to return only schema of the datastore rather than data ?

A)

Datareaderobject=CommandObject.ExecuteReader(CommandBehaviour.SchemaOnly)

39.How can we fine tune the command object when we are expecting a single row or a single value?

A) CommandBehaviour enumeration provides two values SingleResult and SingleRow.If you are expecting a single value then pass “CommandBehaviour.SingleResult” and the query is optimized accordingly, if you are expecting single row then pass “CommandBehaviour.SingleRow” and query is optimized according to single row.

40.Where we can store connectionstring in .Net Projects?

A)For Windows Based Projects we use “App.Config” files will be used.

    For Web Based Projects we use “Web.Config” files will be used to store connection string

41. What are the various methods provided by the dataset object to generate XML?

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

42. How can we save all data from dataset ?

A) Dataset has “AcceptChanges” method which commits all the changes since last time “Acceptchanges” has been executed.

43. How can we check that some changes have been made to dataset since it was loaded ?

A) For tracking down changes Dataset has two methods which comes as rescue “GetChanges“ and “HasChanges”.

44.Define GetChanges()?

A) Returns dataset which are changed since it was loaded or since Acceptchanges was executed.

45.Define HasChanges()?

A) This property indicates that has any changes been made since the dataset was loaded or acceptchanges method was executed.

Note:

If we want to revert or abandon all changes since the dataset was loaded use “RejectChanges”.

46. How can we add/remove row’s in “DataTable” object of “DataSet”?

A) “Datatable” provides “NewRow” method to add new row to “DataTable”. “DataTable” has “DataRowCollection” object which has all rows in a “DataTable” object.

Following are the methods provided by “DataRowCollection” object :-

1.Add: Adds a new row in DataTable

2.Remove: It removes a “DataRow” object from “DataTable”

3.RemoveAt: It removes a “DataRow” object from “DataTable” depending on index position of the “DataTable”.

47.What is the use of DataView?

A)“DataView” represents a complete table or can be small section of rows depending on some criteria. It is best used for sorting and finding data with in “datatable”.

48.List the different methods in DataView?

A) Dataview has the following method’s :-

Find

It takes a array of values and returns the index of the row.

FindRow

This also takes array of values but returns a collection of “DataRow”.If we want to manipulate data of “DataTable” object create “DataView” (Using the “DefaultView” we can create “DataView” object) of the “DataTable” object and use the following functionalities :-

AddNew

Adds a new row to the “DataView” object.

Delete

Deletes the specified row from “DataView” object.

49.How can we load multiple tables in DataSet?

A) objCommand.CommandText = "Table1"

objDataAdapter.Fill(objDataSet, "Table1")

objCommand.CommandText = "Table2"

objDataAdapter.Fill(objDataSet, "Table2")

Above is a sample code which shows how to load multiple “DataTable” objects in one

“DataSet” object. Sample code shows two tables “Table1” and “Table2” in object

ObjDataSet.

lstdata.DataSource = objDataSet.Tables("Table1").DefaultView

In order to refer “Table1” DataTable, use Tables collection of DataSet and the Defaultview

object will give you the necessary output.

50. How can we add relation’s between table in a DataSet ?

A)Dim objRelation As DataRelation

objRelation=New

DataRelation("CustomerAddresses",objDataSet.Tables("Customer").Columns("Custid")

,objDataSet.Tables("Addresses").Columns("Custid_fk"))

objDataSet.Relations.Add(objRelation)

Relations can be added between “DataTable” objects using the “DataRelation” object.

Above sample code is trying to build a relationship between “Customer” and “Addresses”

“Datatable” using “CustomerAddresses” “DataRelation” object.

51. Explain the DataAdapter.Update() and DataSetAcceptChanges() methods?

A)The DataAdapter.Update() method calls any of the DML statements, such as the UPDATE, INSERT, or DELETE statements, as the case may be to update, insert, or delete a row in a DataSet. The DataSet.Acceptchanges() method reflects all the changes made to the row since the last time the AcceptChanges() method was called.

52. Which properties are used to bind a DataGridView control?

A) The DataSource property and the DataMember property are used to bind a DataGridView control.

53. What is the method used for bind the data to DataGridView Control?

A)DataBind() is used to bind the data to DataGridView Control.

E.G:DatasetObject.Databind()

54.Name some of the important properties of DataGridView Control?

A)Following are the important properties of DataGridView Control.They are:

AllowSorting:It enables of providing Sorting facilities.

AllowPaging:It enables of providing Paging facilities.

55. Which property is used to check whether a DataReader is closed or opened?

A) The IsClosed property is used to check whether a DataReader is closed or opened. This property returns a true value if a Data Reader is closed, otherwise a false value is returned.

56.What is the use of Command Builder Class?

A) The CommandBuilder class is used to automatically update a database according to the changes made in a DataSet.

This class automatically registers itself as an event listener to the RowUpdating event. Whenever data inside a row changes, the object of the CommandBuilder class automatically generates an SQL statement and uses the SelectCommand property to commit the changes made in DataSet.

OLEDB provider in .NET Framework has the OleDbCommandBuiider class; whereas, the SQL provider has the SqlCommandBuilder class.

57. Which object is used to add a relationship between two DataTable objects?

A)The DataRelation object is used to add relationship between two DataTable objects.

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

A)The parameters that control most of connection pooling behaviors are as follows:

Connect Timeout

Max Pool Size

Min Pool Size

Pooling

59.What is ADO.Net Entity Framework?

A) The ADO.NET Entity Framework enables developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications.

60.List the benefits with Ado.Net Entity Framework?

A) Entity Framework applications provide the following benefits:

1.Applications can work in terms of a more application-centric conceptual model, including types with inheritance, complex members, and relationships.

2.Applications are freed from hard-coded dependencies on a particular data engine or storage schema.

3.Mappings between the conceptual model and the storage-specific schema can change without changing the application code.

4.Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in different database management systems.

5.Multiple conceptual models can be mapped to a single storage schema.

6.Language-integrated query (LINQ) support provides compile-time syntax validation for queries against a conceptual model.

61. Explain the new features in ADO.NET Entity Framework 4.0?

A) ADO.NET Entity Framework 4.0 is introduced in .NET Framework 4.0 and includes the following new features:

Persistence Ignorance - Facilitates you to define your own Plain Old CLR Objects (POCO) which are independent of any specific persistence technology.

Deferred or Lazy Loading - Specifies that related entities can be loaded automatically whenever required. You can enable lazy loading in your application by setting the DeferredLoadingEnabled property to true.

Self-Tracking Entities - Refers to the entities that are able to track their own changes. These changes can be passed across process boundaries and saved to the database.

Model-First Development - Allows you to create your own EDM and then generate relational model (database) from that EDM with matching tables and relations.

Built-in Functions - Enables you to use built-in SQL Server functions directly in your queries.

Model-Defined Functions - Enables you to use the functions that are defined in conceptual schema definition language (CSDL).

62. What are the benefits of using of ADO.NET in .NET 4.0?

A)The following are the benefits of using ADO.NET in .NET 4.0 are as follows:

Language-Integrated Query (LINQ) - Adds native data-querying capabilities to .NET languages by using a syntax similar to that of SQL. This means that LINQ simplifies querying by eliminating the need to use a separate query language. LINQ is an innovative technology that was introduced in .NET Framework 3.5.

LINQ to DataSet - Allows you to implement LINQ queries for disconnected data stored in a dataset. LINQ to DataSet enables you to query data that is cached in a DataSet object. DataSet objects allow you to use a copy of the data stored in the tables of a database, without actually getting connected to the database.

LINQ to SQL - Allows you to create queries for data stored in SQL server database in your .NET application. You can use the LINQ to SQL technology to translate a query into a SQL query and then use it to retrieve or manipulate data contained in tables of an SQL Server database. LINQ to SQL supports all the key functions that you like to perform while working with SQL, that is, you can insert, update, and delete information from a table.

SqlClient Support for SQL Server 2008 - Specifies that with the starting of .NET Framework version 3.5 Service Pack (SP) 1, .NET Framework Data Provider for SQL Server (System.Data.SqlClient namespace) includes all the new features that make it fully compatible with SQL Server 2008 Database Engine.

ADO.NET Data Platform - Specifies that with the release of .NET Framework 3.5 Service Pack (SP) 1, an Entity Framework 3.5 was introduced that provides a set of Entity Data Model (EDM) functions. These functions are supported by all the data providers; thereby, reducing the amount of coding and maintenance in your application. In .NET Framework 4.0, many new functions, such as string, aggregate, mathematical, and date/time functions have been added.

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

A) In pessimistic locking when user wants to update data it locks the record and till then no

one can update data. Other user’s can only view the data when there is pessimistic locking.

In optimistic locking multiple users can open the same record for updating, thus increase

maximum concurrency. Record is only locked when updating the record. This is the most

preferred way of locking practically. Now a days browser based application is very common

and having pessimistic locking is not a practical solution.

64. How many ways are there to implement locking in ADO.NET ?

A) Following are the ways to implement locking using ADO.NET :-

àWhen we call “Update” method of DataAdapter 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 Datetime stamp 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 is

the best practice used by industries for locking.

Update table1 set field1=@test where LastTimeStamp=@CurrentTimeStamp

à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 field1 we check

that is the old field1 value same. If not then some one else has updated and

necessary action has to be taken.

Update table1 set field1=@test where field1 = @oldfield1value

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

A) 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 in to pool. So it’s always a good habit to either call the close or dispose method of the connection as soon as you have finished work with the connection object.

66. How to enable and disable connection pooling?

A)For .NET it is enabled by default but if you want to just make sure set Pooling=true in the connection string. To disable connection pooling set Pooling=false in connection string if it is an ADO.NET Connection. If it is an OLEDBConnection object set OLEDB Services=-4 in the connection string.