在三层架构中使用事务处理

使用PetShop sqlHelp

  /**/ /// <summary>
        /// Update shopping cart for current user
        /// </summary>
        /// <param name="uniqueID">User id</param>
        /// <param name="cartItems">Collection of shopping cart items</param>
        /// <param name="isShoppingCart">Shopping cart flag</param>
         public   void  SetCartItems( int  uniqueID, ICollection < CartItemInfo >  cartItems,  bool  isShoppingCart)  ... {
            string sqlDelete = "DELETE FROM Cart WHERE UniqueID = @UniqueID AND IsShoppingCart = @IsShoppingCart;";

            SqlParameter[] parms1 = ...{                   
                new SqlParameter("@UniqueID", SqlDbType.Int),
                new SqlParameter("@IsShoppingCart", SqlDbType.Bit)};
            parms1[0].Value = uniqueID;
            parms1[1].Value = isShoppingCart;

            if (cartItems.Count > 0) ...{

                // update cart using SqlTransaction
                string sqlInsert = "INSERT INTO Cart (UniqueID, ItemId, Name, Type, Price, CategoryId, ProductId, IsShoppingCart, Quantity) VALUES (@UniqueID, @ItemId, @Name, @Type, @Price, @CategoryId, @ProductId, @IsShoppingCart, @Quantity);";

                SqlParameter[] parms2 = ...{                   
                new SqlParameter("@UniqueID", SqlDbType.Int),    
                new SqlParameter("@IsShoppingCart", SqlDbType.Bit),
                new SqlParameter("@ItemId", SqlDbType.VarChar, 10),
                new SqlParameter("@Name", SqlDbType.VarChar, 80),
                new SqlParameter("@Type", SqlDbType.VarChar, 80),
                new SqlParameter("@Price", SqlDbType.Decimal, 8),
                new SqlParameter("@CategoryId", SqlDbType.VarChar, 10),
                new SqlParameter("@ProductId", SqlDbType.VarChar, 10),
                new SqlParameter("@Quantity", SqlDbType.Int)};
                parms2[0].Value = uniqueID;
                parms2[1].Value = isShoppingCart;


                SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringProfile);
                conn.Open();
                SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

                try ...{
                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlDelete, parms1);

                    foreach (CartItemInfo cartItem in cartItems) ...{
                        parms2[2].Value = cartItem.ItemId;
                        parms2[3].Value = cartItem.Name;
                        parms2[4].Value = cartItem.Type;
                        parms2[5].Value = cartItem.Price;
                        parms2[6].Value = cartItem.CategoryId;
                        parms2[7].Value = cartItem.ProductId;
                        parms2[8].Value = cartItem.Quantity;
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlInsert, parms2);
                    }
                    trans.Commit();
                }
                catch (Exception e) ...{
                    trans.Rollback();
                    throw new ApplicationException(e.Message);
                }
                finally ...{
                    conn.Close();
                }
            }
            else 
                // delete cart
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlDelete, parms1);
        }