ADO.NET

1. Explain ADO.NET in brief.

ActiveX Data Object.NET is a very important feature of .NET framework which is used to work with data that is stored in structured data sources such as database and xml files. The following are some important features of ADO.NET  :-

• contains a number of classes that provide you with various methods and attributes to manage the communication between your application and data sources.
• Enables you to access different data sources such as Sql Server and XML as per your requirement.
• Provides a rich set of features such as connection and commands that can be used to develop robust and highly efficient data services in .NET applications.
• Provides various data providers that are specific to data bases produced by various vendors. For example ADO.NET has a separate provider to access data from oracle database whereas another provider is used to access data from sql databases.

2.Explain the architecture of ADO.NET.

ADO.NET contains two fundamental components:

• The Dataset which is disconnected from the data source and does not need to know where the data that it holds is retrieved from.
• The .NET data provider which allows you to connect your application to the data source and execute the sql commands against it.

The data provider contains the Connection, Command, DataReader and DataAdapter object. The Connection object provides connectivity to the database. The Command object provides access to database commands to retrieve and manipulate data in a database. The DataReader object retrieves data from the database in the read-only and forward-only mode. The DataAdapter object uses Command objects to execute SQL commands. The DataAdapter object loads the DataSet object with data and also updates changes that you have made to the data in the DataSet object back to the database.

3.Mention the different types of data provider available in .NET framework.

        Provider                                                                                 Description

========================================================================

.NET Framework Data                    Provides access to Microsoft Slq Server 7.0 or later version. It uses the

Provider for SQL Server                 System.Data.SqlClient namespace

.NET Framework Data                    provides access to database exposed by using OLEDB. It uses the

provider for OLEDB                        System.Data.OleDb namespace

.NET Framework Data                    provides access to databases exposed by using ODBC. It uses the 

provider for ODBC                          System.Data.Odbc namespace

.NET framework Data                     provides access to oracle database. It uses the 

provider for Oracle                          System.Data.OracleClient namespace.

4.Explain DataAdapter class in ADO.NET.

The DataAdapter class retrieves data from the database, stores data in dataset, and reflects the changes made in the dataset to the database. The DataAdapter class acts as an intermediary for all communication between the database and the DataSet object. The DataAdapter class is used to fill a DataTable or DataSet object with data from the database using the Fill() method. The DataAdapter class applies the changes made in dataset to the database by calling the Update() method. The DataAdapter class provides four properties that represent the database commands: SelectCommand,  InsertCommand,  DeleteCommand,  UpdateCommand.

5.What is DataReader object?

The DataReader object helps in retrieving the data from a database in forward-only, read-only mode. The base class for all the Datareader object is the DbDataReader class. The DataReader object is returned as a result or calling the ExecuteReadr() method of the Command object. The DataReader object enables faster retrieval of data from datadases and enhances the performances of .NET applications by providing rapid data access speed. However it is less preferred as compared to the DataAdapter object because the DataReader object needs an open connection till it completes reading all the rows of the specified table. An open connection to read data from large tables consumes most of the system resources. When multiple client applications simultaneously access a database by using the DataReader object the performance of data retrieval and other related processes is substantially reduced. In such a case the database might refuse connections to other .NET applications until other clients free the resources.

6.What is the role of DataSet object  in ADO.NET?

One of the major components of ADO.NET is the DataSet object which always remains disconnected from the database and reduces the load on the database.

7.Describe the disconnected architecture of ADO.NETs data access model.

ADO.NET maintains a disconnected database access model which means the application never remains connected constantly to the data source.  Any changes and operations done on the data are served in a local copy (dataset) that acts as a data source. Whenever the connection to the server is re-established these changes are send back to the server in which these changes are saved in the actual database or data source.

8.What are the usages of Command Object in ADO.NET?

• The Command object in ADO.NET executes a command against the database and retrieves a DataReader or DataSet object.
• It also executes the INSERT, UPDATE and DELETE command against the database.
• All the command objects are derived from the DBCommand class.
• The Command object is represented by two classes that is SqlCommand and OleDbCommand.
• The command object provides three methods to execute commands on the database:-

               Ä    The ExecuteNonQuery() method executes the commands and does not return any value.

               Ä The ExecuteScalar() method returns a single value from a database query.

               Ä    The ExecuteReader() method returns a result set by using the DataReader object.

 

9.What are the various methods provide by the DataSet object to generate XML?

The various methods provider by the DataSet object to generate XML are:

• ReadXml() :- Reads XML document into a DataSet object.
• GetXml() :- Returns a string containing an XML document.
• WriteXml() :- Writes an XML data to disk.

10.How can you add or remove rows from the DataTable object of DataSet?

The DataRowCollection class defines the collection of rows for the DataTable object in a dataset. The DataTable class provides the NewRow method to add a new DataRow to DataTable. The NewRow method creates a new row which implements the same schema as applied to the DataTable. The following are the methods provided by the DataRowCollection object are:-

• Add() :- Adds a new row to DataRowCollection.
• Remove() :- Removes a DataRow object from DataRowCollection.
• RemoveAt() :- Removes a row whose location is specified by an index number.

  

11.What is the use of Dataview?

User defined view of a table is contained in a DataView. A complete or a small section of table depending on some criteria can be presented by an object of the DataView class. You can use this class to sort and find data within DataTable.

DataView has the following methods:

• Find() :- Find a row in a DataView by using sort key value.
• FindRows() :- Uses the sort key value to match it with the columns of DataRowView objects. It returns an array of all the corrosponding objects of DataRowView whose columns match with the sort key value.
• AddNew() :- Adds a new row to the DataView object.
• Delete() :- Deletes the specified row from the DataView object according to the specified index.

12.what is the use of the CommandBuilder Class?

The CommandBuilder class is used to automatically update a database according to the changes made in a DataSet. The 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 OleDbCommandBuilder class where as the SQL provider has the SqlCommandBuilder class.

13.What is the meaning of object pooling.

Object pooling is a concept of storing a pool means 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, improve system scalability and performance.

14.What is the use of Connection object?

The connection object is used to connect your application to a specific data source by providing the required authentication information in connection string. The connection object is used according to the type of the data source. For example the OleDbConnection object is used with an OLEDB provider and the SqlConnection object is used with a Sql Server.

15.What is connection pooling?

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.

16.what is the parameters that control most of connection pooling behaviors?

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

* Connect Timeout

* Max Pool Size

* Min Pool Size

* Pooling

17.what are the pre-requisites for connection pooling?

The Prerequisites for connection pooling are as follows

• There must be multiple processers to share the same connection describing the same parameters and security settings.
• The connection string must be identical.

18.Explain the DataAdapter.Update() and DataSet.AcceptChanges() methods.

The DataAdapter.Update() method calls any of the DML statements such as INSERT, UPDATE and DELETE statements as the case may be to insert, update 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.

19.What property must be set and what method must be called in your code to bind the data from some data sourse to the repeater control?

You must set the DataSource property and call the DataBind() method.

20.which method is invoked on the DataAdapter control to fill the generated DataSet with data?

The Fill() method is used to fill the dataset with data.

21.Name the two properties of the GridView control that have to be specified to turn on sorting and paging.

The properties of the GridView control that need to be specified to turn on sorting and paging are as follows:

• The AllowSorting property of the GridView control indicates whether sorting is enabled or not. You should set the AllowSorting property to true to enable sorting.
• The AllowPaging property of the GridView control indicates wheather paging is enabled or not. You should set the AllowPaging property to True to enable paging.

22.which namespace are require to enable the use of database in asp.net pages?

The following namespaces are required to enable the use of databases in ASP.NET pages:

• The System.Data namespace
• The System.Data.OleDb namespace to use any data provider such as Access, Oracle or SQL
• The System.Data.SqlClient namespace to use SQL as the data provider.

23.which object is used to add a relationship between two DataTable object?

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

24.How would you connect to a database by using .NET?

The Connection class is used to connect a .NET application with a database.

25.What is the difference between OLEDB provider and SqlClient?

With respect to usage there no difference between OLEDB Provider and SqlClient. The difference lies in their performance. SqlClient is explicitly used to connect your application to SQl server directly. OLEDB provider is generic for various databases such as Oracle and Access including SQL Server. Therefore there will be an overhead which leads to performance degradation.

26.Which property is used to check weather a DataReader is closed or opened?

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.

27.what is the difference between the Clone() and Copy() method of the DataSet class.

The clone() method copies only the structure of the DataSet. The copied structure includes all the relation, constraint and DataTable schema used by the DataSet. The

Clone method does not copy the data, which is stored in the DataSet. The Copy() method copies the structure as well as the data stored in the DataSet.

29.Which architecture does datasets follows?

Datasets follow the disconnected data architecture.

30.which properties are used to bind a DataGridview control?

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