Transaction usage (ADO. NET and SQL Server)

In the past few days, some stored procedures have been written in the project process to process some business logic. It is found that there are only a few stored procedures in the database, on the other hand, a very small module involves several table operations. Writing a stored procedure is the most convenient and efficient. So I asked the person in charge here, why didn't they write a few stored procedures for those modules they developed earlier? The person in charge gave me the answer: in the future, when someone else maintains the data, it is difficult to track and debug the stored procedure. I do not agree with this statement.

Since the project owner does not allow the use of stored proceduresCodeTo implement these business logic, I chose to use transactions in ADO. net.

The function I want to implement is to upload attachments. Three tables need to be involved: the attachment table appendix, the report base table Report, and the part of the part table. There is also a statusmanage table that represents the appendix and report. When uploading attachments, You need to insert an additional information record to the appendix table. You can also modify the data in the report and part tables on the page. The table relationship is shown in figure

 

The code in the Save button is as follows:

/// <Summary>
/// Upload an attachment storage event
/// Modify by dlw 2010-07-01
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "E"> </param>
Protected void lbtnsave_click (Object sender, eventargs E)
{
Try
{
Upladfile (); // This method uploads files to the server. You do not need to consider this method.

// This is the method to obtain the connection string
String constr = Common. commondb. createconnection (). connectionstring;
Using (sqlconnection con = new sqlconnection (constr ))
{
Con. open ();
Sqltransaction Tran; // define a transaction
Sqlcommand COM = con. createcommand ();
TRAN = con. begintransaction (); // create a transaction
Com. Transaction = Tran; // specify the transaction defined for the sqlcommand object
Try
{
// Insert a new attachment record
Com. commandtext = string. Format (@ "insert into DBO. pab_appendix (flowid, [filename], filepath, fileremark, uploadtime, uploador)
Values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5 }')",
Viewstate ["flowid"]. tostring (),
Fileupload. filename,
Appdomain. currentdomain. basedirectory + "uploadfile //",
"",
Datetime. Now. tostring (),
User. Identity. Name
);
Com. executenonquery ();
// Update the data in the base PAB table
Com. commandtext = string. Format (@ "Update DBO. pab_reportlist set cartype = '{0}', fachabteilung = '{1 }',
Fop_id = '{2}', fop_name = '{3}', factorycode = '{4}' Where pab_reportid = '{5 }'",
Ddlcartype. selecteditem. value. Trim (), // Model
Ddldept. selecteditem. value. Trim (), // Department
Hifop. Value, // fop
Txtfop. Text. Trim (),//
Txtfactorycode. Text. Trim (), // manufacturer
Viewstate ["pab_reportid"]. tostring ());
Com. executenonquery ();
// Update master part information
Com. commandtext = string. Format (@ "Update pab_partlist set part_num = '{0}' Where pab_plid in
(Select pab_partlist.pab_plid from pab_reportlist, pab_partlist
Where pab_reportlist.pab_reportid = pab_partlist.pab_reportid
And pab_partlist.ismainpart = 1
And pab_reportlist.pab_reportid = '{1 }')",
Txtmainpartnum. Text. Trim (),
Viewstate ["pab_reportid"]. tostring ());
Com. executenonquery ();
Tran. Commit (); // transaction commit
Response. Write ("<SCRIPT> alert ('upload successful! '); Returnvalue = true; window. Close (); </SCRIPT> ");
}
Catch (exception ex)
{
Tran. rollback (); // transaction rollback
}
Finally
{
Tran. Dispose (); // transaction destruction
}
}
}
Catch (exception ex)
{
Response. Write ("<SCRIPT> alert ('upload failed! ') </SCRIPT> ");
}
}

In the above Code, the place marked in red is the place where transactions are used.

To sum up, what is the transaction format used by ADO. Net? Extract the Code marked in red in the sample code above, as shown below:

// This is the method to obtain the connection string
String constr = Common. commondb. createconnection (). connectionstring;
Using (sqlconnection con = new sqlconnection (constr ))
{
Con. open ();
Sqltransaction Tran; // define a transaction
Sqlcommand COM = con. createcommand ();
TRAN = con. begintransaction (); // start a local transaction
Com. Transaction = Tran; // specify the transaction defined for the sqlcommand object
Try
{

// Todo

// Process some business logic
Tran. Commit (); // transaction commit
}
Catch (exception ex)
{
Tran. rollback (); // transaction rollback
}
Finally
{
Tran. Dispose (); // transaction destruction
}
}

So far, the business logic implemented by code has come to an end.

At the beginning, I have already said that I have used stored procedures in combination with transactions to implement the above business logic. Below I will share with you the business logic I implemented using stored procedures, I hope you will have some reference value!

Alter procedure [DBO]. [sp_pab_uploadappendix]
(
@ Filename varchar (50), -- upload the attachment name
@ Filepath varchar (100), -- upload the attachment path
@ Fileremark varchar (200), -- attachment remarks
@ Operator varchar (38), -- operator
@ Cartype varchar (10), -- Vehicle Model
@ Mainpartnum varchar (20), -- master part number
@ Fachabteilung varchar (10), -- Department
@ Fop_id varchar (38), -- owner ID
@ Fop_name varchar (50), -- owner name
@ Factorycode varchar (10), -- manufacturer ID
@ Factoryname varchar (100), -- manufacturer name
@ Pab_reportid int,
@ Statusid int --
)
As
Begin
Begin try
Tran in tran -- start of transaction
-- Obtain the flowid corresponding to the status in reportList
Declare @ flowid int
Select @ flowid = pab_statusmanage.flowid from pab_statusmanage
Where pab_reportid = @ pab_reportid and statusid = @ statusid
-- Insert data to the attachment table
Insert into DBO. pab_appendix (flowid, [filename], filepath, fileremark, uploadtime, uploador)
Values (@ flowid, @ filename, @ filepath, @ fileremark, getdate (), @ operator)

-- Update Data in the reportList table
Update DBO. pab_reportlist set cartype = @ cartype, fachabteilung = @ fachabteilung,
Fop_name = @ fop_name, factorycode = @ factorycode where pab_reportid = @ pab_reportid
-- Update Data of the master part in the partlist table

If exists (select 1 from pab_reportlist, pab_partlist where pab_reportlist.pab_reportid = partition and pab_partlist.ismainpart = 1 and partition = @ pab_reportid)
Begin
Update pab_partlist set part_num = @ mainpartnum where pab_plid in
(Select pab_partlist.pab_plid from pab_reportlist, pab_partlist where region = region and pab_partlist.ismainpart = 1 and pab_reportlist.pab_reportid = @ pab_reportid)
End

Commit tran -- transaction commit
End try
Begin catch
Rollback tran -- transaction rollback
End catch
End

The red mark above indicates the place where transactions are used.

To sum up, extract the red Code as follows:

Begin try
Tran in tran -- start of transaction

-- Process some business logic

Commit tran -- transaction commit
End try
Begin catch
Rollback tran -- transaction rollback
End catch

 

So far, both methods have been posted to you, and I hope to help you.