SQL transaction processing in Asp.net

Source: Internet
Author: User

/// <Summary>
/// Execute multiple SQL statements to implement database transactions.
/// </Summary>
/// <Param name = "sqlstringlist"> hash table of the SQL statement (the key is an SQL statement, and the value is the sqlparameter [] of the statement). 1 success is returned, 0 failed to be returned </param>
Public int executesqltran (hashtable sqlstringlist)
{
Int OK = 1;
Using (sqlconnection Conns = new sqlconnection (conn. connectionstring ))
{
Conns. open ();
Using (sqltransaction trans = Conns. begintransaction ())
{
Sqlcommand cmd = new sqlcommand ();
Try
{
// Loop
Foreach (dictionaryentry myde in sqlstringlist)
{
String plain text = myde. Key. tostring ();
Sqlparameter [] parameter parms = (sqlparameter []) myde. value;
Preparecommand (CMD, Conns, trans, plain text, plain parms );
Int val = cmd. executenonquery ();
Cmd. Parameters. Clear ();
}
Trans. Commit ();
Return OK;
}
Catch
{
OK = 0;
Trans. rollback ();
Return OK;
}
}
}
}

/// <Summary>
/// Execute multiple SQL statements to implement database transactions.
/// </Summary>
/// <Param name = "sqlstringlist"> hash table of an SQL statement (Key is an SQL statement, and value is the sqlparameter [] of this statement) </param>
Public int executesqltran (system. Collections. Generic. List <commandinfo> cmdlist)
{
Using (sqlconnection Conns = new sqlconnection (conn. connectionstring ))
{
Conns. open ();
Using (sqltransaction trans = Conns. begintransaction ())
{
Sqlcommand cmd = new sqlcommand ();
Try
{
Int COUNT = 0;
// Loop
Foreach (commandinfo myde in cmdlist)
{
String plain text = myde. commandtext;
Sqlparameter [] parameter parms = (sqlparameter []) myde. parameters;
Preparecommand (CMD, Conns, trans, plain text, plain parms );

If (myde. effentnexttype = effentnexttype. whenhavecontine | myde. effentnexttype = effentnexttype. whennohavecontine)
{
If (myde. commandtext. tolower (). indexof ("count (") =-1)
{
Trans. rollback ();
Return 0;
}

Object OBJ = cmd. executescalar ();
Bool ishave = false;
If (OBJ = NULL & OBJ = dbnull. value)
{
Ishave = false;
}
Ishave = convert. toint32 (OBJ)> 0;

If (myde. effentnexttype = effentnexttype. whenhavecontine &&! Ishave)
{
Trans. rollback ();
Return 0;
}
If (myde. effentnexttype = effentnexttype. whennohavecontine & ishave)
{
Trans. rollback ();
Return 0;
}
Continue;
}
Int val = cmd. executenonquery ();
Count + = val;
If (myde. effentnexttype = effentnexttype. excute1_trows & val = 0)
{
Trans. rollback ();
Return 0;
}
Cmd. Parameters. Clear ();
}
Trans. Commit ();
Return count;
}
Catch
{
Trans. rollback ();
Throw;
}
}
}
}

/// <Summary>
/// Execute multiple SQL statements to implement database transactions.
/// </Summary>
/// <Param name = "sqlstringlist"> hash table of an SQL statement (Key is an SQL statement, and value is the sqlparameter [] of this statement) </param>
Public void executesqltranwithindentity (system. Collections. Generic. List <commandinfo> sqlstringlist)
{
Using (sqlconnection Conns = new sqlconnection (conn. connectionstring ))
{
Conns. open ();
Using (sqltransaction trans = Conns. begintransaction ())
{
Sqlcommand cmd = new sqlcommand ();
Try
{
Int indentity = 0;
// Loop
Foreach (commandinfo myde in sqlstringlist)
{
String plain text = myde. commandtext;
Sqlparameter [] parameter parms = (sqlparameter []) myde. parameters;
Foreach (sqlparameter Q in parallel parms)
{
If (Q. Direction = parameterdirection. InputOutput)
{
Q. value = indentity;
}
}
Preparecommand (CMD, Conns, trans, plain text, plain parms );
Int val = cmd. executenonquery ();
Foreach (sqlparameter Q in parallel parms)
{
If (Q. Direction = parameterdirection. Output)
{
Indentity = convert. toint32 (Q. value );
}
}
Cmd. Parameters. Clear ();
}
Trans. Commit ();
}
Catch
{
Trans. rollback ();
Throw;
}
}
}
}

/// <Summary>
/// Execute multiple SQL statements to implement database transactions.
/// </Summary>
/// <Param name = "sqlstringlist"> hash table of an SQL statement (Key is an SQL statement, and value is the sqlparameter [] of this statement) </param>
Public void executesqltranwithindentity (hashtable sqlstringlist)
{
Using (sqlconnection Conns = new sqlconnection (conn. connectionstring ))
{
Conns. open ();
Using (sqltransaction trans = Conns. begintransaction ())
{
Sqlcommand cmd = new sqlcommand ();
Try
{
Int indentity = 0;
// Loop
Foreach (dictionaryentry myde in sqlstringlist)
{
String plain text = myde. Key. tostring ();
Sqlparameter [] parameter parms = (sqlparameter []) myde. value;
Foreach (sqlparameter Q in parallel parms)
{
If (Q. Direction = parameterdirection. InputOutput)
{
Q. value = indentity;
}
}
Preparecommand (CMD, Conns, trans, plain text, plain parms );
Int val = cmd. executenonquery ();
Foreach (sqlparameter Q in parallel parms)
{
If (Q. Direction = parameterdirection. Output)
{
Indentity = convert. toint32 (Q. value );
}
}
Cmd. Parameters. Clear ();
}
Trans. Commit ();
}
Catch
{
Trans. rollback ();
Throw;
}
}
}
}

Private Static void preparecommand (sqlcommand cmd, sqlconnection Conn, sqltransaction trans, string plain text, sqlparameter [] partial parms)
{
If (conn. State! = Connectionstate. open)
Conn. open ();
Cmd. Connection = conn;
Cmd. commandtext = plain text;
If (trans! = NULL)
Cmd. Transaction = trans;
Cmd. commandtype = commandtype. Text; // specify type;
If (partition parms! = NULL)
{

Foreach (sqlparameter parameter in parameter parms)
{
If (parameter. Direction = parameterdirection. InputOutput | parameter. Direction = parameterdirection. Input )&&
(Parameter. value = NULL ))
{
Parameter. value = dbnull. value;
}
Cmd. Parameters. Add (parameter );
}
}
}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.