Objects in this mirror are closer to Microsoft Technologies. DNM objective is to help users on Microsoft technologies by providing Articles, snippets, Interview Questions.

29 January 2008

ADO.NET Transactions

A transaction is a group SQL statements to be executed as one unit to modify the state of database, a transaction requires a database connection and a transaction object. The crux of utilizing transactions with SQL Server and ADO.NET is the SqlTransaction class. This class name varies depending upon the database platform utilized. For instance, the transaction class for OLEDB databases is OleDbTransaction.

The System.Data.SqlClient namespace contains the SqlTransaction class. There are two properties in the class:
· Connection: Indicates the SqlConnection object associated with the transaction.
· IsolationLevel: Specifies the transaction's IsolationLevel.

The IsolationLevel property is an Enumeration object with the following members:
· Chaos: The pending changes from more highly isolated transactions cannot be overwritten.
· ReadCommitted: Shared locks are held while the data is read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data.
· ReadUncommitted: A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.
· RepeatableRead: Locks are placed on all data that is used in a query, preventing other users from updating the data. This prevents nonrepeatable reads, though phantom rows are still possible.
· Serializable: A range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete.

The IsolationLevel allows a level of record locking, The SqlTransaction object provides methods as well. You may use the following methods to work with the transaction:
· Commit: The database transaction is committed.
· Rollback: A database transaction is rolled back from a pending state. A transaction may not be rolled back once it's committed.
· Save: Creates a savepoint in the transaction that can be used to roll back a portion of the transaction, and specifies the savepoint name.

Example on SqlTransaction:
SqlConnection sqlConn = new SqlConnection(ConnectionString);
cmd1 = new SqlCommand();
cmd2=new SqlCommand();
cmd1.Connection = sqlConn;
cmd2.Connection = sqlConn;
cmd1.CommandText = "insert into Employee(EmpName,EmpSalary,deptid) values('A',1000,9)";
cmd2.CommandText = "Update Employee set EmpSalary=EmpSalary + 1000 where EmpName='A'"; // for commit
//cmd2.CommandText = "Update Employee set EmpSalary=EmpSalary = 1000 where EmpName='A'"; // for Rollback
SqlTransaction SqlTran;
sqlConn.Open();
SqlTran = sqlConn.BeginTransaction();
cmd1.Transaction = SqlTran;
cmd2.Transaction = SqlTran;
try
{
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery(); // if both command excuted sucessfully then will commit if any of command fails that is attched to sqltransaction then that will be roll backed.
SqlTran.Commit();
MessageBox.Show("Transcation Committed Sucessfully");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
SqlTran.Rollback();
MessageBox.Show("Transaction Rollbacked");
}
finally
{
sqlConn.Close();
}
Note: Once a transaction has begin over a connection, a command without its transaction property set cannot be executed in that connection unless either the transaction is committed or roll backed.
Transactions help the database in satisfying all the ACID (Atomic, Consistent, Isolated, and Durable). Transaction processing has improved a lot in ADO.NET 2.0.

Stored Procedure in ADO.NET

It is a precompiled set of SQL statement which is compiled in native form and stored in the backend. They are very fast on their first execution also.

Benefits Using SP:
· They allow modular programming.
You can create the procedure once, store it in the database, and call it any number of times in your program. Stored procedures can be created by a person who specializes in database programming, and they can be modified independently of the program source code.
· They allow faster execution.
If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
· They can reduce network traffic.
An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
· They can be used as a security mechanism.
Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.
· It’s easy to update logic/code in them because it stored at one place in the database.

Sample Stored Procedure:
Create proc GetSalary(@EmpID int,@EmpSal Money out) as
Begin
select @EmpSal=EmpSalary from Employee where EmpID=@EmpID
End

Example:
SqlConnection con=new SqlConnection();
con.ConnectionString = “your connection string”
SqlCommand cmd=new SqlCommand();
cmd.CommandText="GetSalary"; //SP name
cmd.CommandType=CommandType.StoredProcedure;
cmd.Connection=con;

SqlParameter ID=new SqlParameter("@EmpID",SqlDbType.Int);
SqlParameter sal=new SqlParameter("EmpSal",SqlDbType.Money);
sal.Direction=ParameterDirection.Output;
sal.Value = DBNull.Value;
// Parameter direction is enum it has the parameters
// Input - The parameter is an input parameter.
// InputOutput - The parameter is capable of both input and output.
// Output - The parameter is an output parameter.
// ReturnValue - The parameter represents a return value from an operation such as a stored procedure, built-in function, or user-defined function.

ID.Value= “provide employee id”;
cmd.Parameters.Add(ID);
cmd.Parameters.Add(sal);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show(sal.Value.ToString());
Note:
· Every Input Parameter must have a value before the command is executed. If required we can set to DBNull.Value.
· As far as possible try to execute all SQL statements using SP.

25 January 2008

MARS Concept in SqlDataReader

Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You will not be able to execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.
This is over come in .Net 2005 by a concept called MARS(MultipleActiveResources).
If MultipleActiveResources=True is added to connection string then more that one data reader can be opened at the same time in that single connection object. It’s a new feature in ADO.NET 2.0.

Example Data Reader with MARS Concept:
SqlConnection sqlConn = new SqlConnection("Data Source=Server1;Initial Catalog=XXXXDB;Persist Security Info=True;User ID=XXXXX;Password=XXXXX;MultipleActiveResultsets=True");

// In above connection string if you did not write MultipleActiveResultsets=True will get Exception as {There is already an open DataReader associated with this Command which must be closed first.}

SqlCommand cmdDept = new SqlCommand();
cmdDept.Connection=sqlConn;
cmdDept.CommandText="select DeptID,DeptName from Department";
sqlConn.Open();
SqlDataReader DRDept=cmdDept.ExecuteReader();
string tempStr=string.Empty ;

while (DRDept.Read())
{
SqlCommand cmdEmp=new SqlCommand("select * from Employee where Deptid=" + DRDept["DeptID"],sqlConn);
SqlDataReader DREmp = cmdEmp.ExecuteReader();

while (DREmp.Read())
{
tempStr = tempStr + DREmp["EmpID"] + "\t" + DREmp["EmpName"] + "\t" + DREmp["EmpSalary"] + "\t"+ DRDept["DeptName"] + "\n";
}

DREmp.Close();
}
DRDept.Close();
sqlConn.Close();

MessageBox.Show(tempStr);

Note: The result of one select statement should not overlap with the other result of another statement, Means both the select statements should not perform on Same Table.

04 January 2008

Option in VB.Net

In VB Compiler has options like

1. Option Strict: on/off
Restricts implicit data type conversions to only widening conversions.
If used, the Option Strict statement must appear in a file before any other source code statements.
Visual Basic allows conversions of many data types to other data types. Data loss can occur when the value of one data type is converted to a data type with less precision or smaller capacity. A run-time error occurs if such a narrowing conversion fails. Option Strict ensures compile-time notification of these narrowing conversions so they can be avoided.
In addition to disallowing implicit narrowing conversions, Option Strict generates an error for late binding. An object is late bound when it is assigned to a variable that is declared to be of type Object.
Because Option Strict On provides strong typing, prevents unintended type conversions with data loss, disallows late binding, and improves performance, its use is strongly recommended.

2. Option Comapre: Binary/Text
Forces explicit declaration of all variables in a file.
Binary :Optional. Results in string comparisons based on a sort order derived from the internal binary representations of the characters.
Text :Optional. Results in string comparisons based on a case-insensitive text sort order determined by your system's locale.

3. Option Explicit: on/off
Forces explicit declaration of all variables in a file.

The options will not avaliable in C#.