ADO.Net Interview Questions 2

By | May 13, 2014

Explain what a diffgram is and its usage ? A DiffGram is an XML format that is used to identify current and original versions of data elements. The DataSet uses the DiffGram format to load and persist its contents, and to serialize its contents for transport across a network connection. When a DataSet is written as a DiffGram, it populates the DiffGram with all the necessary information to accurately recreate the contents, though not the schema, of the DataSet, including column values from both the Original and Current row versions, row error information, and row order. When sending and retrieving a DataSet from an XML Web service, the DiffGram format is implicitly used. Additionally, when loading the contents of a DataSet from XML using the ReadXml method, or when writing the contents of a DataSet in XML using the WriteXml method, you can select that the contents be read or written as a DiffGram. The DiffGram format is divided into three sections: the current data, the original (or “before”) data, and an errors section, as shown in the following example. <?xml version=”1.0″?> <diffgr:diffgram          xmlns:msdata=”urn:schemas-microsoft-com:xml-msdata”          xmlns:diffgr=”urn:schemas-microsoft-com:xml-diffgram-v1″          xmlns:xsd=”http://www.w3.org/2001/XMLSchema“>    <DataInstance>    </DataInstance>   <diffgr:before>   </diffgr:before>   <diffgr:errors>   </diffgr:errors> </diffgr:diffgram> The DiffGram format consists of the following blocks of data: <DataInstance> The name of this element, DataInstance, is used for explanation purposes in this documentation. A DataInstance element represents a DataSet or a row of a DataTable. Instead of DataInstance, the element would contain the name of the DataSet or DataTable. This block of the DiffGram format contains the current data, whether it has been modified or not. An element, or row, that has been modified is identified with the diffgr:hasChanges annotation. <diffgr:before> This block of the DiffGram format contains the original version of a row. Elements in this block are matched to elements in the DataInstance block using the diffgr:id annotation. <diffgr:errors> This block of the DiffGram format contains error information for a particular row in the DataInstance block. Elements in this block are matched to elements in the DataInstance block using the diffgr:id annotation. Which method do you invoke on the DataAdapter control to load your generated dataset with data? You have to use the Fill method of the DataAdapter control and pass the dataset object as an argument to load the generated data.

Can you edit data in the Repeater control? NO.

Which are the different IsolationLevels ? Following are the various IsolationLevels:

  • Serialized   Data read by a current transaction cannot be changed by another transaction until the current transaction finishes. No new data can be inserted that would affect the current transaction. This is the safest isolation level and is the default. 
  • Repeatable Read   Data read by a current transaction cannot be changed by another transaction until the current transaction finishes. Any type of new data can be inserted during a transaction. 
  • Read Committed   A transaction cannot read data that is being modified by another transaction that has not committed. This is the default isolation level in Microsoft® SQL Server. 
  • Read Uncommitted   A transaction can read any data, even if it is being modified by another transaction. This is the least safe isolation level but allows the highest concurrency. 
  • Any   Any isolation level is supported. This setting is most commonly used by downstream components to avoid conflicts. This setting is useful because any downstream component must be configured with an isolation level that is equal to or less than the isolation level of its immediate upstream component. Therefore, a downstream component that has its isolation level configured as Any always uses the same isolation level that its immediate upstream component uses. If the root object in a transaction has its isolation level configured to Any, its isolation level becomes Serialized.

How xml files and be read and write using dataset?. DataSet exposes method like ReadXml and WriteXml to read and write xml

What are the different rowversions available? There are four types of Rowversions. Current: The current values for the row. This row version does not exist for rows with a RowState of Deleted. Default : The row the default version for the current DataRowState. For a DataRowState value of Added, Modified or Current, the default version is Current. For a DataRowState of Deleted, the version is Original. For a DataRowState value of Detached, the version is Proposed. Original: The row contains its original values. Proposed: The proposed values for the row. This row version exists during an edit operation on a row, or for a row that is not part of a DataRowCollection

Explain acid properties?. The term ACID conveys the role transactions play in mission-critical applications. Coined by transaction processing pioneers, ACID stands for atomicity, consistency, isolation, and durability. These properties ensure predictable behavior, reinforcing the role of transactions as all-or-none propositions designed to reduce the management load when there are many variables. Atomicity A transaction is a unit of work in which a series of operations occur between the BEGIN TRANSACTION and END TRANSACTION statements of an application. A transaction executes exactly once and is atomic — all the work is done or none of it is. Operations associated with a transaction usually share a common intent and are interdependent. By performing only a subset of these operations, the system could compromise the overall intent of the transaction. Atomicity eliminates the chance of processing a subset of operations. Consistency A transaction is a unit of integrity because it preserves the consistency of data, transforming one consistent state of data into another consistent state of data. Consistency requires that data bound by a transaction be semantically preserved. Some of the responsibility for maintaining consistency falls to the application developer who must make sure that all known integrity constraints are enforced by the application. For example, in developing an application that transfers money, you should avoid arbitrarily moving decimal points during the transfer. Isolation A transaction is a unit of isolation — allowing concurrent transactions to behave as though each were the only transaction running in the system. Isolation requires that each transaction appear to be the only transaction manipulating the data store, even though other transactions may be running at the same time. A transaction should never see the intermediate stages of another transaction. Transactions attain the highest level of isolation when they are serializable. At this level, the results obtained from a set of concurrent transactions are identical to the results obtained by running each transaction serially. Because a high degree of isolation can limit the number of concurrent transactions, some applications reduce the isolation level in exchange for better throughput. Durability A transaction is also a unit of recovery. If a transaction succeeds, the system guarantees that its updates will persist, even if the computer crashes immediately after the commit. Specialized logging allows the system’s restart procedure to complete unfinished operations, making the transaction durable.

Whate are different types of Commands available with DataAdapter ? The SqlDataAdapter has SelectCommand, InsertCommand, DeleteCommand and UpdateCommand

What is a Dataset? Datasets are the result of bringing together ADO and XML. A dataset contains one or more data of tabular XML, known as DataTables, these data can be treated separately, or can have relationships defined between them. Indeed these relationships give you ADO data SHAPING without needing to master the SHAPE language, which many people are not comfortable with. The dataset is a disconnected in-memory cache database. The dataset object model looks like this: Dataset   DataTableCollection   DataTable    DataView    DataRowCollection     DataRow    DataColumnCollection     DataColumn    ChildRelations    ParentRelations    Constraints    PrimaryKey DataRelationCollection Let’s take a look at each of these: DataTableCollection: As we say that a DataSet is an in-memory database. So it has this collection, which holds data from multiple tables in a single DataSet object. DataTable: In the DataTableCollection, we have DataTable objects, which represents the individual tables of the dataset. DataView: The way we have views in database, same way we can have DataViews. We can use these DataViews to do Sort, filter data. DataRowCollection: Similar to DataTableCollection, to represent each row in each Table we have DataRowCollection. DataRow:  To represent each and every row of the DataRowCollection, we have DataRows. DataColumnCollection: Similar to DataTableCollection, to represent each column in each Table we have DataColumnCollection. DataColumn: To represent each and every Column of the DataColumnCollection, we have DataColumn. PrimaryKey: Dataset defines Primary key for the table and the primary key validation will take place without going to the database. Constraints: We can define various constraints on the Tables, and can use Dataset.Tables(0).enforceConstraints. This will execute all the constraints, whenever we enter data in DataTable. DataRelationCollection: as we know that we can have more than 1 table in the dataset, we can also define relationship between these tables using this collection and maintain a parent-child relationship.

Explain the ADO . Net Architecture ( .Net Data Provider) ADO.Net is the data access model for .Net –based applications. It can be used to access relational database systems such as SQL SERVER 2000, Oracle, and many other data sources for which there is an OLD DB or ODBC provider. To a certain extent, ADO.NET represents the latest evolution of ADO technology. However, ADO.NET introduces some major changes and innovations that are aimed at the loosely coupled and inherently disconnected – nature of web applications. A .Net Framework data provider is used to connecting to a database, executing commands, and retrieving results. Those results are either processed directly, or placed in an ADO.NET DataSet in order to be exposed to the user in an ad-hoc manner, combined with data from multiple sources, or remoted between tiers. The .NET Framework data provider is designed to be lightweight, creating a minimal layer between the data source and your code, increasing performance without sacrificing functionality. Following are the 4 core objects of .Net Framework Data provider:

  • Connection: Establishes a connection to a specific data source
  • Command: Executes a command against a data source. Exposes Parameters and can execute within the scope of a Transaction from a Connection.
  • DataReader: Reads a forward-only, read-only stream of data from a data source.
  • DataAdapter: Populates a DataSet and resolves updates with the data source.

The .NET Framework includes the .NET Framework Data Provider for SQL Server (for Microsoft SQL Server version 7.0 or later), the .NET Framework Data Provider for OLE DB, and the .NET Framework Data Provider for ODBC. The .NET Framework Data Provider for SQL Server:  The .NET Framework Data Provider for SQL Server uses its own protocol to communicate with SQL Server. It is lightweight and performs well because it is optimized to access a SQL Server directly without adding an OLE DB or Open Database Connectivity (ODBC) layer. The following illustration contrasts the .NET Framework Data Provider for SQL Server with the .NET Framework Data Provider for OLE DB. The .NET Framework Data Provider for OLE DB communicates to an OLE DB data source through both the OLE DB Service component, which provides connection pooling and transaction services, and the OLE DB Provider for the data source The .NET Framework Data Provider for OLE DB: The .NET Framework Data Provider for OLE DB uses native OLE DB through COM interoperability to enable data access. The .NET Framework Data Provider for OLE DB supports both local and distributed transactions. For distributed transactions, the .NET Framework Data Provider for OLE DB, by default, automatically enlists in a transaction and obtains transaction details from Windows 2000 Component Services. The .NET Framework Data Provider for ODBC: The .NET Framework Data Provider for ODBC uses native ODBC Driver Manager (DM) through COM interoperability to enable data access. The ODBC data provider supports both local and distributed transactions. For distributed transactions, the ODBC data provider, by default, automatically enlists in a transaction and obtains transaction details from Windows 2000 Component Services. The .NET Framework Data Provider for Oracle: The .NET Framework Data Provider for Oracle enables data access to Oracle data sources through Oracle client connectivity software. The data provider supports Oracle client software version 8.1.7 and later. The data provider supports both local and distributed transactions (the data provider automatically enlists in existing distributed transactions, but does not currently support the EnlistDistributedTransaction method). The .NET Framework Data Provider for Oracle requires that Oracle client software (version 8.1.7 or later) be installed on the system before you can use it to connect to an Oracle data source. .NET Framework Data Provider for Oracle classes are located in the System.Data.OracleClient namespace and are contained in the System.Data.OracleClient.dll assembly. You will need to reference both the System.Data.dll and the System.Data.OracleClient.dll when compiling an application that uses the data provider. Choosing a .NET Framework Data Provider .NET Framework Data Provider for SQL Server: Recommended for middle-tier applications using Microsoft SQL Server 7.0 or later. Recommended for single-tier applications using Microsoft Data Engine (MSDE) or Microsoft SQL Server 7.0 or later. Recommended over use of the OLE DB Provider for SQL Server (SQLOLEDB) with the .NET Framework Data Provider for OLE DB. For Microsoft SQL Server version 6.5 and earlier, you must use the OLE DB Provider for SQL Server with the .NET Framework Data Provider for OLE DB. .NET Framework Data Provider for OLE DB: Recommended for middle-tier applications using Microsoft SQL Server 6.5 or earlier, or any OLE DB provider. For Microsoft SQL Server 7.0 or later, the .NET Framework Data Provider for SQL Server is recommended. Recommended for single-tier applications using Microsoft Access databases. Use of a Microsoft Access database for a middle-tier application is not recommended. .NET Framework Data Provider for ODBC: Recommended for middle-tier applications using ODBC data sources. Recommended for single-tier applications using ODBC data sources. .NET Framework Data Provider for Oracle: Recommended for middle-tier applications using Oracle data sources. Recommended for single-tier applications using Oracle data sources. Supports Oracle client software version 8.1.7 and later. The .NET Framework Data Provider for Oracle classes are located in the System.Data.OracleClient namespace and are contained in the System.Data.OracleClient.dll assembly. You need to reference both the System.Data.dll and the System.Data.OracleClient.dll when compiling an application that uses the data provider. Can you explain the difference between an ADO.NET Dataset and an ADO Recordset? Let’s take a look at the differences between ADO Recordset and ADO.Net DataSet: 1. Table Collection: ADO Recordset provides the ability to navigate through a single table of information. That table would have been formed with a join of multiple tables and returning columns from multiple tables. ADO.NET DataSet is capable of holding instances of multiple tables. It has got a Table Collection, which holds multiple tables in it. If the tables are having a relation, then it can be manipulated on a Parent-Child relationship. It has the ability to support multiple tables with keys, constraints and interconnected relationships. With this ability the DataSet can be considered as a small, in-memory relational database cache. 2. Navigation: Navigation in ADO Recordset is based on the cursor mode. Even though it is specified to be a client-side Recordset, still the navigation pointer will move from one location to another on cursor model only. ADO.NET DataSet is an entirely offline, in-memory, and cache of data. All of its data is available all the time. At any time, we can retrieve any row or column, constraints or relation simply by accessing it either ordinarily or by retrieving it from a name-based collection. 3. Connectivity Model: The ADO Recordset was originally designed without the ability to operate in a disconnected environment. ADO.NET DataSet is specifically designed to be a disconnected in-memory database. ADO.NET DataSet follows a pure disconnected connectivity model and this gives it much more scalability and versatility in the amount of things it can do and how easily it can do that. 4. Marshalling and Serialization: In COM, through Marshalling, we can pass data from 1 COM component to another component at any time. Marshalling involves copying and processing data so that a complex type can appear to the receiving component the same as it appeared to the sending component. Marshalling is an expensive operation. ADO.NET Dataset and DataTable components support Remoting in the form of XML serialization. Rather than doing expensive Marshalling, it uses XML and sent data across boundaries. 5. Firewalls and DCOM and Remoting: Those who have worked with DCOM know that how difficult it is to marshal a DCOM component across a router. People generally came up with workarounds to solve this issue. ADO.NET DataSet uses Remoting, through which a DataSet / DataTable component can be serialized into XML, sent across the wire to a new AppDomain, and then Desterilized back to a fully functional DataSet. As the DataSet is completely disconnected, and it has no dependency, we lose absolutely nothing by serializing and transferring it through Remoting.

Leave a Reply