0

I am using windows application. I have one dataset. From that i am reading data one by one line using for loop. In for loop i used stored-procedures to inserting the data into database.(This process are doing in inner for loop).

But i have doubt i am reading one by one line and am executing stored procedures.If in 3rd line if got wrong data...am getting exceptions.but before 2 lines are inserted into database. so i have do commit if all the data is correct only.if not i have do rollback.

Can you please any body give me idea......

for (i = 0; i < Passdataset.Tables[0].Rows.Count - 2; i++)
{
    currentRow = Passdataset.Tables[0].Rows[i];
    if (currentRow == null)
        continue;
    GLItem1 = new taGLTransactionLineInsert_ItemsTaGLTransactionLineInsert();
    GLTrans = new GLTransactionType();
    if (rdoDateFromFile.Checked == true)
    {
        trxdate = currentRow[mapdate].ToString();
    }

    if (singleORdouble != "")
    {
        decimal amount = Convert.ToDecimal(currentRow[amount1].ToString());
        if (amount < 0)
        {
            decimal amt = Math.Abs(amount);
            GLItem1.CRDTAMNT = amt;
        }
        else
        {
            decimal amt1 = Math.Abs(amount);
            GLItem1.DEBITAMT = amt1;
        }
    }

    string con2 = String.Format(@"data source=localhost;initial catalog=TWO;integrated security=SSPI;persist security info=False;packet size=4096");
    SqlConnection strcon = new SqlConnection(con2);

    SqlCommand NextJourna = new SqlCommand("taGetNextJournalEntry", strcon);
    NextJourna.CommandType = CommandType.StoredProcedure;
    NextJourna.CommandText = "taGetNextJournalEntry";
    NextJourna.Parameters.Add("@O_vJournalEntryNumber", SqlDbType.Char, 13);
    NextJourna.Parameters["@O_vJournalEntryNumber"].Direction = ParameterDirection.Output;
    NextJourna.Parameters.Add("@O_iErrorState", SqlDbType.Int, 250);
    NextJourna.Parameters["@O_iErrorState"].Direction = ParameterDirection.Output;
    strcon.Open();

    NextJourna.ExecuteNonQuery();

    foreach (taGLTransactionLineInsert_ItemsTaGLTransactionLineInsert items in LineItems)
    {
        if (items != null)
        {
            SqlCommand myCommand = new SqlCommand("taGLTransactionLineInsert", strcon);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandText = "taGLTransactionLineInsert";
            myCommand.Parameters.Add("@I_vBACHNUMB", SqlDbType.Char).Value = items.BACHNUMB;
            myCommand.Parameters.Add("@I_vCRDTAMNT", SqlDbType.Decimal).Value = items.CRDTAMNT;
            myCommand.Parameters.Add("@I_vDEBITAMT", SqlDbType.Decimal).Value = items.DEBITAMT;
            myCommand.Parameters.Add("@I_vSQNCLINE", SqlDbType.Int).Value = SQNCLINE;
            myCommand.Parameters.Add("@I_vACTNUMST", SqlDbType.Char).Value = items.ACTNUMST;
            myCommand.Parameters.Add("@I_vDSCRIPTN", SqlDbType.Char).Value = items.DSCRIPTN;
            myCommand.Parameters.Add("@I_vJRNENTRY", SqlDbType.Int).Value = nextjournl.Trim();
            myCommand.Parameters.Add("@O_iErrorState", SqlDbType.Int, 250);
            myCommand.Parameters["@O_iErrorState"].Direction = ParameterDirection.Output;
            myCommand.Parameters.Add("@oErrString", SqlDbType.VarChar, 255);
            myCommand.Parameters["@oErrString"].Direction = ParameterDirection.Output;
            SQNCLINE = SQNCLINE + 16384;
            strcon.Open();
            myCommand.ExecuteNonQuery();
        }
    }

    SqlCommand myCommand1 = new SqlCommand("taGLTransactionHeaderInsert", strcon);
    myCommand1.CommandType = CommandType.StoredProcedure;
    myCommand1.CommandText = "taGLTransactionHeaderInsert";

    myCommand1.Parameters.Add("@I_vBACHNUMB", SqlDbType.Char).Value = GLHdr.BACHNUMB;
    myCommand1.Parameters.Add("@I_vREFRENCE", SqlDbType.Char).Value = GLHdr.REFRENCE;
    myCommand1.Parameters.Add("@I_vJRNENTRY", SqlDbType.Int).Value = nextjournl.Trim();
    strcon.Open();
    myCommand1.ExecuteNonQuery();
}
Kiran Reddy
  • 1,055
  • 3
  • 12
  • 19
  • A quick google would have got you some resources to get started. It found me http://msdn.microsoft.com/en-us/library/2k2hy99x.aspx and http://stackoverflow.com/questions/224689/transactions-in-net . I would suggest you go do some reading on the topic and then if you run into problems come back and ask more specific questions. The site is more focussed on solutions to specific problems rather than general tutoring. – Chris Aug 06 '12 at 15:55
  • You should send the complete data in one shot using `SqlDbType.Structured` –  Aug 06 '12 at 15:56
  • I'd wrap things I want in a transaction in a [TransactionScope](http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx). – Andy Aug 06 '12 at 15:58
  • Thanks for your reply...am bit confused i kept strored-procedure in loop. Each time it is running. But for doing commit or rollback..i will never do commit or rollback each time. So when i do commit or rollback in this type of scenarios. – Kiran Reddy Aug 07 '12 at 03:27

0 Answers0