transaction on multiple insert in a for loop

Question

0.00/5 (No votes)

See more:

Hi all. In presentation layer(code behind of a form)Inside a for loop I do multiple insert. I Want If an error occurred during Insert operation in loop for,previous inserts will be Rollback.
I don't know how I use transaction

C#

 foreach (var loan in _loanlist)
{
    if (!pda.InsertLoan(new Loan{
        prsnlcd = prsncod,
        LoanCode = loan.LoanCode,
        LoanStatus = loan.LoanAmountRemain == 0 ? true : false}))
      Do Something;
}

"pda" is a business class for handling insert Operation:

C#

//----------InsertLoan--------
public bool InsertLoan(Loan loanMoney)
{
    lasterror = string.Empty;
    if (loanMoney == null)
        return false;
    List<SqlParameter> lst = loanMoneyToParameter(loanMoney);
    if (dbh.Insert("InsertLoanInfo", CommandType.SP, lst, false) == -1)
    {
        if (dbh.lasError != string.Empty)
        {
            lasterror = string.Empty;
            lasterror = dbh.lasError;
        }
        return false;
    }
    else
        return true;
}

dbh is a class in data layer which includes Insert function

C#

public int Insert(String cmdText, CommandType comType, List<SqlParameter> ParameterList, Boolean isRetVal)
       {
           SqlCommand Command = new SqlCommand();
           Command.CommandText = cmdText;
           Command.Connection = Sqlc;
           if (comType == CommandType.SP)
               Command.CommandType = System.Data.CommandType.StoredProcedure;
           else
               Command.CommandType = System.Data.CommandType.Text;
           if (ParameterList != null)
           {
               foreach (SqlParameter item in ParameterList)
                   Command.Parameters.Add(item);
           }
           OpenConnection(Sqlc);
               try
               {
                   Command.ExecuteNonQuery();
                   CloseConnection(Sqlc);
                   return 1;
               }
               catch (Exception ex)
               {
                   CloseConnection(Sqlc);
               }
           return -1;
       }

Posted 9-May-14 16:52pm

mit62

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2014-05-09T18:36:00

Solution 1

Refer:
1. First, understand the concept of Using Transactions in ADO.NET[^]
2. You could try transactionscope[^] in your situation.

Posted 9-May-14 18:36pm

Peter Leow

Comments

mit62 10-May-14 1:44am

I read these articles and changed my program.please tell me if it's correct.
bool statusFlag=true;
using (TransactionScope scope = new TransactionScope())
{
foreach (var loan in _loanlist)
{
if (!pda.InsertLoan(new Loan{
prsnlcd = prsncod,
LoanCode = loan.LoanCode,
LoanStatus = loan.LoanAmountRemain == 0 ? true : false}))
{
statusFlag=false;
break;
}
}
if(statusFlag=true)
scope.Complete();
}

sankarsan parida · Accepted Answer · 2014-05-09T21:10:00

Try something like this

//Pass your values in List of class object
   private static bool Insert(List<loan> loanList)
   {
       bool Result = false;
       SqlConnection db = new SqlConnection("connstringhere");
       SqlTransaction transaction;

       db.Open();
       transaction = db.BeginTransaction();
       try
       {
           foreach (Loan item in loanList)
           {
               new SqlCommand("INSERT INTO TransactionDemo " +
            "( prsnlcd ,LoanCode,LoanStatus) VALUES ('"+item.prsnlcd+"','"+item.LoanCode+"','"+item.LoanStatus+"');", db, transaction)
            .ExecuteNonQuery();
           }
           transaction.Commit();
           Result = true;
       }
       catch (SqlException sqlError)
       {
           transaction.Rollback();
           Result = false;
       }
       db.Close();
       return Result;
   }