Lập Trình .NET

1. What is ADO.NET?

ADO stands for Active Data Object and ADO.NET is a set of .NET libraries for ADO.

ADO.NET is a collection of managed libraries used by .NET applications for data source communication using a driver or provider:

Enterprise applications handle a large amount of data. This data is primarily stored in relational databases, such as Oracle, SQL Server, and Access and so on. These databases use Structured Query Language (SQL) for retrieval of data.

To access enterprise data from a .NET application, an interface was needed. This interface acts as a bridge between an RDBMS system and a .NET application. ADO.NET is such an interface that is created to connect .NET applications to RDBMS systems.

In the .NET framework, Microsoft introduced a new version of Active X Data Objects (ADO) called ADO.NET. Any .NET application, either Windows based or web based, can interact with the database using a rich set of classes of the ADO.NET library. Data can be accessed from any database using connected or disconnected architecture.
ADO.NET provides mainly the following two types of architectures:

Connected Architecture.
Disconnected Architecture.

2. What are the ADO.NET components?

ADO.NET components categorized in three modes: disconnected, common or shared and the .NET data providers.

The disconnected components build the basic ADO.NET architecture. You can use these components (or classes) with or without data providers. For example, you can use a DataTable object with or without providers and shared or common components are the base classes for data providers. Shared or common components are the base classes for data providers and shared by all data providers. The data provider components are specifically designed to work with different kinds of data sources. For example, ODBC data providers work with ODBC data sources and OleDb data providers work with OLE-DB data sources.

3. How can you define the DataSet structure?

A DataSet object falls in disconnected components series. The DataSet consists of a collection of tables, rows, columns and relationships.

DataSet contains a collection of DataTables and the DataTable contains a collection of DataRows, DataRelations, and DataColumns. A DataTable maps to a table in the database.

4. What is Connection Pooling in ADO.NET?

Connection pooling is the ability of reusing your connection to the database. This means if you enable Connection pooling in the connection object, actually you enable the re-use of the connection to more than one user.

ADO.NET uses a technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections. Connection pooling reuses existing active connections with the same connection string instead of creating new connections when a request is made to the database. It involves the use of a connection manager that is responsible for maintaining a list, or pool, of available connections for a given connection string. Several pools exist if different connection strings ask for connection pooling.

SQL Server connection string pooling attributes:

Connection Lifetime: Length of time in seconds after creation after which a connection is destroyed. The default is 0, indicating that connection will have the maximum timeout.

Connection Reset: Specifies whether the connection is reset when removed from the pool. The default is true.

Enlist: Specifies whether the connection is automatically enlisted in the current transaction context of the creation thread if that transaction context exists. The default is true.

Load Balance Timeout: Length of time in seconds that a connection can remain idle in a connection pool before being removed.

Max Pool Size: Maximum number of connections allowed in the pool. The default is 100.

Min Pool Size: Minimum number of connections maintained in the pool. The default is 0.

Pooling: When true, the connection is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool. The default is true.

5. What is the differences Between DataReader and DataSet?

Data Reader:
Used in a connected architecture.
Provides better performance.
DataReader object has read-only access.
DataReader object supports a single table based on a single SQL query of one database.
A DataReader object is bound to a single control.
A DataReader object has faster access to data.
A DataReader object must be manually coded.
We can't create a relation in a data reader.
Whereas a DataReader doesn't support data reader communicates with the command object.
DataReader cannot modify data.

DataSet:
Used in a disconnected architecture.
Provides lower performance.
A DataSet object has read/write access.
A DataSet object supports multiple tables from various databases.
A DataSet object is bound to multiple controls.
A DataSet object has slower access to data.
A DataSet object is supported by Visual Studio tools.
We can create relations in a dataset.
A Dataset supports integration with XML Dataset communicates with the Data Adapter only.
A DataSet can modify data.

6. What is SqlCommand Object?

The SqlCommand carries the SQL statement that needs to be executed on the database. SqlCommand carries the command in the CommandText property and this property will be used when the SqlCommand calls any of its execute methods.

The Command Object uses the connection object to execute SQL queries.
The queries can be in the form of Inline text, Stored Procedures or direct Table access.
An important feature of Command object is that it can be used to execute queries and Stored Procedures with Parameters.
If a select query is issued, the result set it returns is usually stored in either a DataSet or a DataReader object.
The three important methods exposed by the SqlCommand object is shown below:

ExecuteScalar
ExecuteNonQuery
ExecuteReader

ExecuteScalar is useful for returning a single value from the database. For example, using this method we can retrieve a sum of sales made by a specific product, total number of records in the employee table, unique id by supplying filtering conditions and so on. Since this method performs faster we do not need to go for the Reader method just to retrieve a single scalar value.

ExecuteNonQuery is useful for performing data manipulation on the database. Simply, the ExecuteNonQuery is for executing the DML statements. The return value of the ExecuteNonQuery is an integral value that represents the number of rows affected by the Operation.

ExecuteReader is used when we need to retrieve rows and columns of data using the SQL select statements. As the data retrieved is a table of data, ExecuteReader returns SqlDataReader. We should iterate through this object to get the required values.

7. What is the DataAdapter Object in ADO.NET?

A Data Adapter represents a set of data commands and a database connection to fill the dataset and update a SQL Server database.

A Data Adapter contains a set of data commands and a database connection to fill the dataset and update a SQL Server database. Data Adapters form the bridge between a data source and a dataset.

Data Adapters are designed depending on the specific data source. The following table shows the Data Adapter classes with their data source.

A Data Adapter supports mainly the following two methods:

Fill(): The Fill method populates a dataset or a data table object with data from the database. It retrieves rows from the data source using the SELECT statement specified by an associated select command property.

The Fill method leaves the connection in the same state as it encountered before populating the data.

Update(): The Update method commits the changes back to the database. It also analyzes the RowState of each record in the DataSet and calls the appropriate INSERT, UPDATE, and DELETE statements.

8. Use of DataSet object in ADO.NET?

It is used in a disconnected architecture.
Provides lower performance. A DataSet object has read/write access.
A DataSet object supports multiple tables from various databases.
A DataSet object is bound to multiple controls.
A DataSet object has slower access to data.
A DataSet object is supported by Visual Studio tools.
We can create relations in a dataset.
A Dataset supports integration with XML.
A DataSet communicates with the Data Adapter only.
A DataSet can modify data.
A DataSet is a collection of DataTable and DataRelations. Each DataTable is a collection of DataColumn, DataRows and Constraints.


9. What is DataTable in ADO.NET?

DataTable represents a single table in a database.
In this show row and column.
DataSet is a collection of data tables.
In this store data record.

10. What is the DataReader in ADO.Net?

DataReader holds only one table at a time.
It only provides read only access mode and cannot write data.
It is not required local storage to data store.
Holds one row at a time.
Uses less memory.
DataReader do not maintain relation.

11. What is the SqlCommandBuilder?

CommandBuilder helps you to generate update, delete, and insert commands on a single database table for a data adapter. Similar to other objects, each data provider has a command builder class. The OleDbCommandBuilder, SqlCommonBuilder, and OdbcCommandBuilder classes represent the CommonBuilder object in the OleDb, Sql, and ODBC data providers.

12. What is the Connection object in ADO.NET?

A Connection object sits between a data source and a DataAdapter (via Command). You need to define a data provider and a data source when you create a connection. With these two, you can also specify the user ID and password depending on the type of data source.

Connection can also be connected to a Command object to execute SQL queries, which can be used to retrieve, add, update and delete data to a data source

13. Describe the DataView in ADO.NET?

A DataView enables you to create different views of the data stored in a DataTable, a capability that is often used in data binding applications. Using a DataView, you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression. A DataView provides a dynamic view of data whose content, ordering, and membership reflect changes to the underlying DataTable as they occur. This is different from the Select method of the DataTable, which returns a DataRow array from a table per particular filter and/or sort order and whose content reflects changes to the underlying table, but whose membership and ordering remain static. The dynamic capabilities of the DataView make it ideal for data-binding applications.

14. What is ExecuteScalar method in ADO.NET?

The ExecuteScalar Method in SqlCommandObject returns the first column of the first row after executing the query against the Data Source.

If the result set contain more than one column or rows, it takes only the first column of the first row. All other values are ignored.
If the result set is empty it will return null.

15. What is ExecuteNonQuery Method?

The ExecuteNonQuery method is used to execute the command and return the number of rows affected.

The ExecuteNonQuery method cannot be used to return the result set.

16. What is ExecuteReader Method?

The DataReader object is a forward-only and read-only cursor.
It requires a live connection to the data source.
The DataReader object cannot be directly instantiated. Instead, we must call the ExecuteReader() method of the command object to obtain a valid DataReader object.

17. Explain the ExecuteXmlReader?

The execute reader method is flexible when we need the result set in the form of an XML doucment. The ExecuteXmlReader methods returns an instance of XmlReader class.

Example: XmlReader xmlreader = cmd.ExecuteXmlReader();
XmlDocument xdoc = new XmlDocument();

Using the XmlDocument class we load the XmlReader object and save it to the File System using the Save method.