Software Code Help - Interview Question ADO.NET

What is the Savepoints in Transaction in ADO.NET.

Raj Shekhar — Fri, 03 Jul 2009 11:35:56 GMT

When we roll back our transaction, it nullifies the effect of every command you’ve executed since you started the last transaction. But what happens if you want to roll back only part of an ongoing transaction? SQL Server handles this with a feature called savepoints.

Savepoints are markers that act like bookmarks. You mark a certain point in the flow of the transaction, and then you can roll back to that point. You set the savepoint using the Transaction.Save() method.

Here’s a conceptual look at how you use a savepoint:

//
Start the transaction. SqlTransaction trans = Connection.BeginTransaction(); //
Mark a savepoint. trans.Save("CompletedUpdate"); //
If needed, roll back to the savepoint. trans.Rollback("CompletedUpdate"); //
Commit or roll back the transaction. trans.Commit();

How many different type of Command Method in ADO.NET

Raj Shekhar — Fri, 03 Jul 2009 11:14:25 GMT

ExecuteNonQuery() Executes non-SELECT commands, such as SQL commands that insert, delete, or update records. The returned value indicates the number of rows affected by the command. You can also use ExecuteNonQuery() to execute data-definition commands that create, alter, or delete database objects (such as tables, indexes, constraints, and so on).

ExecuteScalar() Executes a SELECT query and returns the value of the first field of the first row from the rowset generated by the command. This method is usually used when executing an aggregate SELECT command that uses functions such as COUNT() or SUM() to calculate a single value.

ExecuteReader() Executes a SELECT query and returns a DataReader object that wraps a read-only, forward-only cursor.

How many type of Data Provider in ADO.NET

Raj Shekhar — Thu, 02 Jul 2009 11:02:02 GMT

A data provider >is a set of ADO.NET classes that allows you to access a specific database, execute SQL commands, and retrieve data. Essentially, a data provider is a bridge between your application and a data source.>

There are four type of providers in .net:

• SQL Server provider : Provides optimized access to a SQL Server database (version 7.0 or later).>

• OLE DB provider >: Provides access to any data source that has an OLE DB driver. This includes SQL Server databases prior to version 7.0.>

• Oracle provider >: Provides optimized access to an Oracle database (version 8i >or later).>

• ODBC provider >: Provides access to any data source that has an ODBC driver.> >

How many types of object in ADO.NET

Raj Shekhar — Thu, 02 Jul 2009 10:58:47 GMT

ADO.NET has two types of objects: connection-based >and content-based >.>

Connection-based objects >: These are the data provider objects such as Connection, Command, DataReader, and DataAdapter. They allow you to connect to a database, execute SQL statements, move through a read-only result set, and fill a DataSet. The connection-based objects are specific to the type of data source, and are found in a provider-specific namespace (such as System.Data.SqlClient for the SQL Server provider).>

Content-based objects >: These objects are really just "packages" for data. They include the DataSet, DataColumn, DataRow, DataRelation, and several others. They are completely independent of the type of data source and are found in the System.Data namespace.

What are the different values for the CommandType Enumeration in ADO.NET

Raj Shekhar — Wed, 01 Jul 2009 11:10:36 GMT

There are three type of CommandType enumeration in ADO.NET.

CommandType.Text The command will execute a direct SQL statement. The SQL statement is provided in the CommandText property. This is the default value.

CommandType.StoredProcedure The command will execute a stored procedure in the data source. The CommandText property provides the name of the stored procedure.

CommandType.TableDirect The command will query all the records in the table. The CommandText is the name of the table from which the command will retrieve the records.