Explore topic-wise InterviewSolutions in Current Affairs.

The reasons for USING STORED Procedures in ADO.NET are GIVEN below:

• For improved performance
• For security reasons
• Easier to use and maintain
• Lesser NETWORK Traffic
• Execution time is less

Data providers are used to transferring the data between the client application and the data store. It encapsulates the database-specific details. Data providers are HELPFUL for database connection, data retrieval, storing the data in a dataset, reading the retrieved data, and updating the database.

The data providers that comes along with the ADO.NET Framework are:

• OLE DB: The OLEDB provider is available under System.Data.OleDb namespace. This provider can be used to access MICROSOFT Access, DB2/400, SyBase, and SQL Server 6.5 and earlier.
• ODBC: The ODBC provider is available under System.Data.Odbc namespace. This provider is used when there will not be any newer provider is available.
• SQL Server: The Microsoft SQL Server provider is available under System.Data.SqlClient namespace. CLASSES available under this provider will provide the same FUNCTIONALITY as the generic OLEDB provider.

The CONDITIONS for connection pooling are:

• There MUST be several processes with the same PARAMETERS and security settings so that they can share the same connection.
• The connection string should be IDENTICAL.

Some of the properties provided by the DataReader are as follows:

• Depth: It represents the depth of nesting for a row.
• FieldCount: It gives the TOTAL COLUMN count in a row.
• Item: It obtains the column value in a native format.
• RecordsAffected: It gives the number of transaction affected rows.
• IsClosed: It represents whether a data reader is closed.
• VisibleFieldCount: It is used to obtain the number of unhidden fields in the SqlDataReader.

Some of the methods provided by the DataReader are as follows:

• Read(): This method reads a record from the SQL Server database.
• Close(): It closes a SqlDataReader object.
• NextResult(): It moves the data reader to the next result during the time of batch transactions.
• Getxxx(): Various TYPES of Getxxx() methods such as GetBoolean(Int32), GetChar(Int32), GetFloat(Int32), GetDouble(Int32), etc., are provided by the DataReader. These methods will read a value of a particular data TYPE from a column. For example, GetFloat() will return a column value as a Float and GetChar as a character.

DataTable in ADO.NET represents a single table in a DataSet that has in-memory RELATIONAL data. The data within DataTable is local to the .NET framework-based application to which it belongs but can be populated USING a DataAdapter from different data sources such as Microsoft SQL Server. The DataTable CLASS belongs to the System.Data namespace within the LIBRARY of .NET Framework.

DataTable can be represented in .aspx.cs code as given below:

The SQL connection and SQL command object will be created. We pass the SQL query to the object of the SQL command class. A new data table object will be created by using the DataTable class and it is filled with data using a data adapter.

The task of grouping DATABASE connections in the cache memory is to make them available whenever there is a requirement of connection. OPENING a new database connection every time is a time-consuming process. Connection pooling allows you to REUSE existing and active database connections, whenever there is a need, and thus increases the application performance.
By SETTING the pooling property into true or false in the connection STRING, we can enable or disable the connection pooling in the application. It is enabled by default in every application.

• Data binding in ADO.NET is the process through which user interface (UI) controls of a CLIENT application are configured to update or fetch data from data sources like a database or XML document. Using data binding, the user will be able to bind values to the particular control.
• There are two types of data binding based on the type of binding offered:
  1. Simple data binding: It is the process of binding the control with only one value in the DATASET. The controls such as label, text box will be made bound to the control using the control properties.
  2. Complex data binding: It is the method of binding the component with the Database. The controls can be a Dropdown LIST, GridView, or COMBO box. One or more than one value can be displayed from the dataset using the complex data binding.

In ADO.NET, transactions are used when you want to bind several tasks together and execute them in the form of a single unit. The transaction provides data consistency by ensuring either all of the database operations will be succeeded or all of them will be failed. For example, consider an application that performs two tasks. First, it updates an item_order table with order information. Second, it updates an item_inventory table that holds inventory information, where a number of items ordered will be debited. If any one of the tasks FAILS, then both updates must be rolled back.

Two types of transactions supported by ADO.NET are as follows:

• Local Transaction:
  • A local transaction is a single-phase transaction that is directly handled by the database. Every .NET Framework data provider has its own Transaction OBJECT for BRINGING out local transactions.
  • For example, if we want to perform a transaction using SQL Server database, we import a System.Data.SqlClient namespace. Similarly, to perform an Oracle transaction, import the System.Data.OracleClient namespace. A DbTransaction class will be used for writing CODE that is independent of the provider and that requires transactions.
• Distributed Transaction:
  • A distributed transaction is coordinated by a transaction monitor and will make use of fail-safe mechanisms like two-phase commit for transaction resolution. This transaction will affect multiple resources.
  • If the user can make use of a distributed transaction, if he wants to do a transaction across multiple data servers such as Oracle, SQL Server, etc.
  • If you want a distributed transaction to commit, all participants must guarantee that data modification made will be permanent. Changes must remain unchanged even if the system crash or other unforeseen events occur. Even if a single participant will make this guarantee fail, then the entire transaction will fail, and updates made to data within the transaction scope are rolled back.

Different execute() methods supported by SqlCommandObject in ADO.NET is given below:

• ExecuteScalar(): This method returns only a single VALUE from the first row and first column of the ResultSet after the execution of the query. Even if ResultSet is having more than one row or column, all those rows and columns will be ignored. If the ResultSet is empty, it will return NULL.
• ExecuteNonQuery(): This method returns the number of rows affected by the execution of a query. This method is not useful to return the ResultSet.
• ExecuteReader(): This method returns an object of DataReader which is a read-only and forward-only ResultSet. It needs a live connection with the DATA Source. We cannot DIRECTLY instantiate the DataReader object. A valid DataReader object can be created with the HELP of the ExecuteReader() method.
• ExecuteXmlReader(): This method builds an object of the XmlReader class and will return the ResultSet in the form of an XML document. This method is made available in SQL Server 2000 or later.

Object pooling is a REPOSITORY of the OBJECTS in memory that can be reused later without creating them. This object pooling reduces the BURDEN of creating objects when it is required. WHENEVER there is a requirement of an object, the object pool manager will process the request and serve accordingly. It is designed for optimizing the use of limited resources so that the demands of CLIENT requests will be fulfilled.

Various namespaces available under ADO.NET is GIVEN below:

1. System.Data: It contains the definition for rows, columns, relations, VIEWS, tables, constraints, and databases.
2. System.Data.SqlClient: It is a collection of classes that are HELPFUL in connecting to a MICROSOFT SQL Server database such as SQLCONNECTION, SqlCommand, SqlDataAdapter, etc.
3. System.Data.Odbc: It consists of classes that are required for connecting with most Odbc Drivers. These classes include OdbcConnection, OdbcCommand.
4. System.Data.OracleClient: It has classes required for connection with an Oracle database, OracleConnection, OracleCommand.

DataSet can be said as a collection of database tables(row and column format) that HOLDS the data. There are two types of DataSet in ADO.NET. They are:

1. Typed DataSet: A typed DataSet is derived from the DataSet base class and can be created by selecting the DataSet option provided by Visual Studio. It will be created as an XML schema(.xsd file) that contains DataSet structure information such as rows, columns, and tables. Data from the database is moved into a dataset and from the dataset to another component in the XML format.
2. Untyped DataSet: Untyped DataSet does not have an associated XML schema with it. Users are supposed to add columns, tables, and other ELEMENTS to it. Properties can be set during design time or can add them during run time.

Example program for the usage of DataSet:

Here, DataSet will be filled by DataAdapter that receives data from the employee table. This DataSet will be used to DISPLAY the information received from the employee database.

• A DataAdapter is used to access DATA from a data source by functioning as a bridge between DataSet and a data source. DataAdapter class includes an SQL command set and a database connection. It is helpful to fill the DataSet and resolve changes to the data source.
• The DataAdapter will make use of the Connection object that belongs to the .NET Framework data provider for connecting with a data source. Along with that, it will also use Command objects to RETRIEVE data from the data source as well as to resolve changes to the data source.
• DataAdapter properties that permit the user to control the database are the Select command, Update command, Insert command, and Delete command.
• Example code for the usage of DataAdapter:

Here, DataAdapter will receive the data from the items table and fill the DataSet, which will be later used to display the information retrieved from the items database.

• The DATASET is a collection of database tables(row and column format) that contain the data. It is helpful for fetching the data without any need for Data Source interaction, that is why it is called a DISCONNECTED data access method.
• It is an in-memory data store that can contain multiple tables at the same time. DataRelation objects can be used to RELATE these tables.
• For creating a DataSet object, ADO.NET provides a DataSet class that consists of constructors and methods to CARRY out data-related operations.
• It can be used with various data sources, with XML data, or to MANAGE the application’s local data. The DataSet will include related tables, data constraints, and relationships among the tables.

• ADO.NET stands for ActiveX Data Object, it is a part of the .NET Framework by Microsoft. ADO.NET framework provides a set of CLASSES that are used to handle data communication with data sources such as XML FILES and databases (such as SQL, Oracle, MySQL, MS Access, etc.).
• ADO.NET can SEPARATE mechanisms for data connectivity, data access, and data manipulation.
• It has introduced the disconnected ARCHITECTURE, in which data can be stored in a DataSet. ADO.NET has providers for database connection, commands for execution, and result retrieval.
• The ADO.NET classes are stored in the DLL named System.Data.dll.
• Various applications like ASP.NET applications, console applications, WINDOWS applications, etc., will use ADO.NET for database connection, command execution, and retrieval of data.