Support us MVC ASP.NET C# SQL Server WCF Written Test HR Round Subscribe C# Videos C# Programs Buy DVD

Transactions related ASP.NET Interview Questions

What is a transaction?
A transaction is a group of commands that change the data stored in a database. The transaction, which is treated as a single unit, assures that the commands are handled in an all-or-nothing fashion. if one of the commands fails, all of the commands fail, and any data that was written to the database by the commands is backed out. In this way, transactions maintain the integrity of data in a database. ADO.NET lets you group database operations into transactions.

What is the main purpose of database transactions?
The main purpose of database transactions is to maintain the integrity of data in a database.

How do you determine which SQL commands are part of a transaction?
You can determine what database commands belong in a transaction by using the ACID test. Commands must be atomic, consistent, isolated, and durable.

Commands belong in a transaction if they are:
Atomic:In other words, they make up a single unit of work. For example, if a customer moves, you want your data entry operator to change all of the customer’s address fields as a single unit, rather than changing street, then city, then state, and so on.
Consistent:All the relationships between data in a database are maintained correctly. For example, if customer information uses a tax rate from a state tax table, the state entered for the customer must exist in the state tax table. Isolated:Changes made by other clients can’t affect the current changes. For example, if two data entry operators try to make a change to the same customer at the same time, one of two things occurs: either one operator’s changes are accepted and the other is notified that the changes weren’t made, or both operators are notified that their changes were not made. In either case, the customer data is not left in an indeterminate state.
Durable:Once a change is made, it is permanent. If a system error or power failure occurs before a set of commands is complete, those commands are undone and the data is restored to its original state once the system begins running again.

Why is transaction processing very important for web applications?
Transaction processing is very important for Web applications that use data access, because Web applications are distributed among many different clients. In a Web application, databases are a shared resource, and having many different clients distributed over a wide area can present the below key problems.
Contention for resources:Several clients might try to change the same record at the same time. This problem gets worse the more clients you have.
Unexpected failures:The Internet is not the most reliable network around, even if your Web application and Web server are 100 percent reliable. Clients can be unexpectedly disconnected by their service providers, by their modems, or by power failures.
Web application life cycle:Web applications don’t follow the same life cycle as Windows applications—Web forms live for only an instant, and a client can leave your application at any point by simply typing a new address in his or her browser.

List the steps in order to process a transaction?
1.Begin a transaction.
2.Process database commands.
3.Check for errors.
4.If errors occurred, restore the database to its state at the beginning of the transaction. If no errors occurred, commit the transaction to the database.

Explain how a DataSet provides transaction processing?
DataSet provide transaction processing through the RejectChanges and Update methods. DataSet also provide an AcceptChanges method that resets the state of records in a data set to Unchanged. Data sets provide implicit transaction processing, because changes to a data set are not made in the database until you invoke the Update method on the data adapter object. This lets you perform a set of commands on the data and then choose a point at which to make the changes permanent in the database.

If an error occurs during the Update method, none of the changes from the data set is made in the database. At that point, you can either attempt to correct the error and try the Update method again or undo the changes pending in the data set using the data set’s RejectChanges method.

Give an example to show how DataSets provide transaction processing?
Let us assume we have a DataGrid that displays employee information. Every row also has a delete button, which when you click will delete that row. On this page we also have a Restore and Commit buttons. When you click the Restore button you should be able to restore the data to its previous state. When you click the Commit button you should be able to update the database with the deletions made in the DataSet.

The code for Commit and Restore buttons is shown below.
private void butRestore_Click(object sender, System.EventArgs e)
// Restore the data set to its original state.
// Refresh the data grid.

private void butCommit_Click(object sender, System.EventArgs e)
int intRows;
// Update the database from the data set.
intRows = adptContacts.Update(dsContacts);
// Save changes to state variable.
Session["dsContacts"] = dsContacts;
// Refresh the data grid.
The RejectChanges method in the preceding butRestore_Click event procedure returns the data set to its state before the row was deleted. The data set’s AcceptChanges method is the inverse of RejectChanges—it resets the DataRowState property for all the changed rows in a data set to Unchanged and removes any deleted rows.

The AcceptChanges method prevents the Update method from making those changes in the database, however, because Update uses the rows’ DataRowState property to determine which rows to modify in the database. For this reason, the AcceptChanges method is useful only when you do not intend to update a database from the data set.

What are the 3 types of transaction objects available in ADO.NET?
As we have 3 types of database connections in ADO.NET, there are also 3 types of transaction objects:

What are the steps involved in using a transaction object in ADO.NET?
1.Open a database connection.
2.Create the transaction object using the database connection object’s BeginTransaction method.
3.Create command objects to track with this transaction, assigning the Transaction property of each command object to the name of the transaction object created in step 2.
4.Execute the commands. Because the purpose of transaction processing is to detect and correct errors before data is written to the database, this is usually done as part of an error-handling structure.
5.Commit the changes to the database or restore the database state, depending on the success of the commands.
Close the database connection.

What property of a transaction object determines how concurrent changes to a database are handled?
IsolationLevel property of the transaction object is used to determine how concurrent changes to a database are handled.

What are different isolation levels of a transaction object in ADO.NET?
Does not lock the records being read. This means that an uncommitted change can be read and then rolled back by another client, resulting in a local copy of a record that is not consistent with what is stored in the database. This is called a dirty read because the data is inconsistent.
Chaos:Behaves the same way as ReadUncommitted, but checks the isolation level of other pending transactions during a write operation so that transactions with more restrictive isolation levels are not overwritten.
ReadCommitted:Locks the records being read and immediately frees the lock as soon as the records have been read. This prevents any changes from being read before they are committed, but it does not prevent records from being added, deleted, or changed by other clients during the transaction. This is the default isolation level.
RepeatableRead:Locks the records being read and keeps the lock until the transaction completes. This ensures that the data being read does not change during the transaction.
Serializable:Locks the entire data set being read and keeps the lock until the transaction completes. This ensures that the data and its order within the database do not change during the transaction.

What is the default isolation level in a transaction?

What is a Save Point in a transaction in ADO.NET?
SqlConnection object provide one transaction capability that is unavailable for OLE database connections: the ability to create save points within a transaction. Save points let you restore the database state to a specific position within the current transaction. To set a save point within a SQL transaction, use the Save method as shown below.

How do you restore a SQL transaction to a specific save point?
To restore a SQL transaction to a save point, specify the name of the save point in the Rollback method as shown below.

1 comment:

If you are aware of any other questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

Disclaimer - Terms of use - Contact Us