Many time row processing becomes dependent process.
Below stuff will shade some lights on
1. Transaction using ADO.net application
2. Transaction using SQL server / stored procedure
E.g. Your application has processed the CC information to third party web and its waiting
for response.
What if their site is down? or there is network falure or communication error?
Obviousely, you need to roll back the transaction. Using C#.Net below code snippet will help you to understand and use
SqlTransaction techniuque
Code is self explanatory
// create SQL connection object and pass connection string to it from application settings
SqlConnection sqlconnect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
// create SQL command object
SqlCommand sqlcommdt = new SqlCommand();
// Tell command object where to connect
sqlcommdt.Connection = sqlconnect;
// Tell command object what to do once get connected
// In this case, I'm telling it to execute Stored procedure
sqlcommdt.CommandText = "SP_Name";
// set type of command object
sqlcommdt.CommandType = CommandType.StoredProcedure;
try
{
// Open connection
sqlcommdt.Connection.Open();
// Create SQL Transaction
SqlTransaction transaction = sqlconnect.BeginTransaction();
// Tell command object to use SQL Transaction object
sqlcommdt.Transaction = transaction;
DataTable dt = new DataTable();
dt.Load(sqlcommdt.ExecuteReader());
try
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
// Process the row
// It may throw excetion. In this case, immediate catch block will throw that exception to outer one.
ProcessRow(dr);
}
}
}
catch (Exception ex)
{
throw ex;
}
// If there is no exception, commit the transaction.
sqlcommdt.Transaction.Commit();
}
catch (Exception ex)
{
// If there is an error, you can roll back the transaction.
sqlcommdt.Transaction.Rollback();
}
2. When to go for SQL transaction using stored procedure?
See below code snippet.
Begin transaction tr1
begin try
-- Do insertion on one table
-- Do deletion from xyz table
-- If every thing worked fine commit transaction.
commit transaction tr1
End try
begin catch
-- If any error occured during insertion or delition, you can roll back that transaction.
rollback transaction tr1
end catch
Scenario when transaction invlove insertion / updation / deletion on multiple SQL server, you need to
go for MSDTC.