Ишдан мақсади: Маъулмотлар базаси учун оддий транзакция яратиш ва тестдан ўтказиш. Транзакциянинг моделлари, транзакция орқали сақлаш моделлари, қўшилган транзакциялар ва уларни ишлатиш принциялари
4-амалий машғулот. Университет маълумотлар базасида транзакцияларни амалга ошириш ва қайта ишлаш.
Ишдан мақсади: Маъулмотлар базаси учун оддий транзакция яратиш ва тестдан ўтказиш. Транзакциянинг моделлари, транзакция орқали сақлаш моделлари, қўшилган транзакциялар ва уларни ишлатиш принциялари, транзакция билан ишловчи SQL операторлари билан ишлаш.
Масаланингқўйилиши: Университет маълумотлар базасида транзакцияларни амалга ошириш ва қайта ишлаш
Ишни бажариш учун намуна
BEGIN TRANSACTION
INSERT СОТРУДНИКИ (TAB) VALUES (5)INSERT ОТРАБОТАНО (TAB) VALUES (5) IF @@ERROR = 0
COMMIT ELSE ROLLBACK
CREATE PROCEDURE TRANZ
AS BEGIN
BEGIN TRANSACTION
INSERT СОТРУДНИКИ (TAB) VALUES (11)
INSERT ЧАСЫ (TAB) VALUES (11)
IF @@ERROR = 0 COMMIT
ELSE ROLLBACK END
UPDATE authors SET au_fname = 'John' WHERE au_id = '172-32-1176'
UPDATE authors SET au_fname = 'Marg' WHERE au_id = '213-46-8915' COMMIT TRAN
BEGIN TRAN
UPDATE authors SET au_fname = 'John' WHERE au_id = '172-32-1176' UPDATE authors SET au_fname = 'JohnY' WHERE city = 'Lawrence' IF @@ROWCOUNT = 5 COMMIT TRAN
ELSE
ROLLBACK TRAN
INSERT INTO [authors]([au_id], [au_lname], [au_fname], [phone], [contract]) VALUES ('172-32-1176', 'Gates', 'Bill', '800-BUY-MSFT', 1)
UPDATE authors SET au_fname = 'Johnzzz' WHERE au_id = '172-32-1176' COMMIT TRAN GO
Create Proc TranTest2 AS BEGIN TRAN
INSERT INTO [authors]([au_id], [au_lname], [au_fname], [phone], [contract]) VALUES ('172-32-1176', 'Gates', 'Bill', '800-BUY-MSFT', 1)
IF @@ERROR <> 0 BEGIN ROLLBACK TRAN return 10 END
UPDATE authors SET au_fname = 'Johnzzz' WHERE au_id = '172-32-1176' IF @@ERROR <> 0 BEGIN ROLLBACK TRAN return 11 END
COMMIT TRAN GO
USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT -- The value of@@TRANCOUNTis0 BEGIN TRAN
SELECT 'After BEGIN TRAN', @@TRANCOUNT -- The value of@@TRANCOUNTis1 DELETE sales BEGIN TRAN nested
SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
--The valueof @@TRANCOUNTis2 DELETE titleauthor COMMIT TRAN nested
--Doesnothingexceptdecrementthevalueof@@TRANCOUNT
SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
--The valueof @@TRANCOUNTis1 ROLLBACK TRAN
SELECT 'After ROLLBACK TRAN', @@TRANCOUNT -- The value of@@TRANCOUNTis0
// to Command object for a pending local transaction command.Connection = connection; command.Transaction = transaction;
try
{
command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
command.ExecuteNonQuery(); command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
command.ExecuteNonQuery();
// Attempt to commit the transaction. transaction.Commit();
Console.WriteLine("Both records are written to database.");
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType()); Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
}
using (IDbTransaction tran = conn.BeginTransaction()) { try {
// your code tran.Commit();
} catch { tran.Rollback(); throw;
}
}
using(TransactionScope tran = new TransactionScope()) { CallAMethodThatDoesSomeWork(); CallAMethodThatDoesSomeMoreWork(); tran.Complete();
}
protected void Button1_Click(object sender, EventArgs e)
{
using (SqlConnection connection1 = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.mdf;Integ rated Security=True;User Instance=True"))
{
connection1.Open();