IMPORTANT in .NET framework

Encode GS1 - 12 in .NET framework IMPORTANT

You will need to have SQL Server 2005 and the Adventure Works database installed before you can complete this lab. Refer to the Introduction for setup instructions.

Exercise 1: Use the Default Isolation Level In this exercise, you create the draft for the stored procedure and use the read committed transaction isolation level. 1. Open SQL Server Management Studio, and connect to an instance of SQL Server 2005. 2. Open a new query window, and type and execute the following SQL statements. This will create the TestDB database, the Test schema, and the tables that are used in this exercise: you will also create the Test.spAccountReset stored procedure. You can execute this procedure to reset the data in the tables if you need to restart the exercise.

6

CREATE DATABASE TestDB; GO USE TestDB; GO CREATE SCHEMA Test; GO CREATE TABLE Test.Accounts ( AccountNumber INT PRIMARY KEY ); CREATE TABLE Test.AccountTransactions ( TransactionID INT IDENTITY PRIMARY KEY ,AccountNumber INT NOT NULL REFERENCES Test.Accounts ,CreatedDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ,Amount DECIMAL(19, 5) NOT NULL ); GO CREATE PROC Test.spAccountReset AS BEGIN SET NOCOUNT ON; DELETE Test.AccountTransactions; DELETE Test.Accounts; INSERT INSERT VALUES INSERT VALUES INSERT VALUES Test.Accounts (AccountNumber) VALUES (1001); Test.AccountTransactions (AccountNumber, Amount) (1001, 100); Test.AccountTransactions (AccountNumber, Amount) (1001, 500); Test.AccountTransactions (AccountNumber, Amount) (1001, 1400);

SELECT AccountNumber, SUM(Amount) AS Balance FROM Test.AccountTransactions GROUP BY AccountNumber; END

3. Open another query window, and type and execute the following SQL statements to create the Test.spAccountWithdraw stored procedure:

USE TestDB; GO CREATE PROC Test.spAccountWithdraw @AccountNumber INT ,@AmountToWithdraw DECIMAL(19, 5) AS

BEGIN SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRY IF(@AmountToWithdraw <= 0) RAISERROR('@AmountToWithdraw must be > 0.', 16, 1); BEGIN TRAN; -- Verify that the account exists... IF NOT EXISTS( SELECT * FROM Test.Accounts WHERE AccountNumber = @AccountNumber ) RAISERROR('Account not found.', 16, 1); -- Verify that the account will not be overdrawn... IF (@AmountToWithdraw > ( SELECT SUM(Amount) FROM Test.AccountTransactions WHERE AccountNumber = @AccountNumber) ) RAISERROR('Not enough funds in account.', 16, 1); -- ** USED TO TEST CONCURRENCY PROBLEMS ** RAISERROR('Pausing procedure for 10 seconds...', 10, 1) WITH NOWAIT; WAITFOR DELAY '00:00:30'; RAISERROR('Procedure continues...', 10, 1) WITH NOWAIT; -- Make the withdrawal... INSERT Test.AccountTransactions (AccountNumber, Amount) VALUES (@AccountNumber, -@AmountToWithdraw); -- Return the new balance of the account: SELECT SUM(Amount) AS BalanceAfterWithdrawal FROM Test.AccountTransactions WHERE AccountNumber = @AccountNumber; COMMIT TRAN; END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(2047); SET @ErrorMessage = ERROR_MESSAGE(); RAISERROR(@ErrorMessage, 16, 1); -- Should also use ERROR_SEVERITY() and ERROR_STATE()... IF(XACT_STATE() <> 0) ROLLBACK TRAN; END CATCH END

6

4. Open another query window, which will be referred to as Connection 1, and type and execute the following SQL statement to prepare the connection:

-- Connection 1 /* Leave the above line to easily see that this query window belongs to Connection 1. */ USE TestDB; GO -- Reset/generate the account data: EXEC Test.spAccountReset;

5. Open another query window, which will be referred to as Connection 2, and type and execute the following SQL statement to prepare the connection:

-- Connection 2 /* Leave the above line to easily see that this query window belongs to Connection 2. */ USE TestDB; GO

6. In this step, you will execute two batches at the same time to try to test for concurrency problems. In both the Connection 1 and Connection 2 query windows, type the following SQL statements without executing them yet. The statements will first retrieve the current account balance and then attempt to empty the account.

SELECT SUM(Amount) AS BalanceBeforeWithdrawal FROM Test.AccountTransactions WHERE AccountNumber = 1001; GO EXEC Test.spAccountWithdraw @AccountNumber = 1001, @AmountToWithdraw = 2000;

To get a better view of what will happen, press Ctrl+T in SQL Server Management Studio to set results to be returned as text instead of grids. Do this for both query windows. Now, start the execution in both query windows simultaneously and wait for both batches to finish execution. (This should take approximately 30 seconds because of the WAITFOR DELAY statement in the Test.spAccountWithdraw stored procedure.) Both connections batches should return two result sets; the first result set will contain the current account balance (which should be 2,000 for both batches), and the second result set will contain the account balance after the withdrawal. What was the result of the two withdrawals Was the account overdrawn What kind of concurrency problem occurred (if any)