Transaction to ado.net

1. Create the transaction structure

Sqlconnection = new sqlconnection ();
... Initialize the connection
// Start the transaction
Sqltransaction = sqlconnection. begintransaction ();
// Apply the transaction to the command
Sqlcommand = new sqlcommand ();
Sqlcommand. Connection = sqlconnection;
Sqlcommand. Transaction = sqltransaction;
Try
{
// Use sqlcommand to perform data operations
...
// Submitted successfully
Sqltransaction. Commit ();
}
Catch (exception ex)
{
// Error rollback
Sqltransaction. rollback ();
}

2. Simple Example

{
Datatable dt = new datatable ();
System. Data. sqlclient. sqlconnection CNN = new system. Data. sqlclient. sqlconnection ("connection string ");
System. Data. sqlclient. sqlcommand CM = new system. Data. sqlclient. sqlcommand ();
Cm. Connection = CNN;
CNN. open ();
System. Data. sqlclient. sqltransaction trans = CNN. begintransaction ();
Try
{
Foreach (datarow DR in DT. Rows)
{
Cm. commandtext = "Update [Table] Set [quantity] = @ amount where productid = @ productid ";
Cm. Parameters. Add ("@ amount", sqldbtype. INT );
Cm. Parameters ["@ amount"]. value = convert. toint32 (Dr ["amount"]);
Cm. Parameters. Add ("@ productid", sqldbtype. varchar );
Cm. Parameters ["@ productid"]. value = Dr ["productid"]. tostring ();
Cm. executenonquery ();
}
Trans. Commit ();
}
Catch
{
Trans. rollback ();
}
Finally
{
CNN. Close ();
Trans. Dispose ();
CNN. Dispose ();
}
}

3. SQL Server transaction example

Begin transaction
Save transaction

Insert into demo values ('bb ',' B terminologies ')
Rollback transaction

Create Table demo2 (name varchar (10), age INT)
Insert into demo2 (name, age) values ('Lis ', 1)
Rollback transaction

 Insert into demo values ('bb ',' B terminologies ')

Commit transaction

Commit transaction

4. Note

1. The transaction must begin intransaction () after the connection is opened ();

2. Add a transaction to sqlcommand (sqlcommand. Transaction = sqltransaction ;)

3. make corresponding adjustments for other databases

4. You can use a DLL provided by Microsoft, which is very convenient.