Protecting against transaction concurency (Transaction type; Transaction IsolationLevel)

Question

Middle-tier component will execute the data access routines in application. The component will call several SQL Server stored procedures to perform database updates. All of these procedure calls run under the control of a single transaction. The code for the middle-tier will implement the following objects:

SqlCommand comm = connection.CreateCommand();
SqlTransaction trans;

How i must add code to component to specify the highest possible level of protection against such errors(two users try to update the same data concurrently).

Answer 1

you use IsolationLevel:

using (SqlConnection con = new SqlConnection(connectionString))
{
    using (SqlTransaction tran = con.BeginTransaction(IsolationLevel.Serializable))
    {
        SqlCommand cmd = con.CreateCommand();

        // etc...

        con.Open();


    }
}

You will still need to catch the appropriate SQL exceptions...