Блог

Мне нужно 3 операции вставки в транзакцию. 2 Операция вставки имеет цикл for, первая операция вставки цикла работает. Но, во-вторых, для операции вставки цикла не работает.

Я использую for в цикле for, но он не фиксирует результат бесчисленного цикла for, когда он совершает транзакцию.

Мой код

             // Set the Connection to the new OleDbConnection.
            command.Connection = connection;

            // Open the connection and execute the transaction.
            try
            {
                connection.Open();

                // Start a local transaction
                transaction = connection.BeginTransaction();

                // Assign transaction object for a pending local transaction.
                command.Connection = connection;
                command.Transaction = transaction;

                // Execute the commands.
                command.CommandText =
                    @"INSERT INTO STOKASIL (STOK_ISLEMA_NO,ISLEM_KODU, ISLEM_YONU, ISLEM_ADI, GIRIS_CARI_NO, CIKIS_CARI_NO, GIRIS_STOK_YERI_NO,
                                                      CIKIS_STOK_YERI_NO, GIRIS_ISLEM_NOKTASI_NO, CIKIS_ISLEM_NOKTASI_NO, GIRIS_PERSONEL_NO, 
                                                        CIKIS_PERSONEL_NO,FATURA_NO,TARIH)

                                VALUES("   stokAsil.IslemANo   ",'STKGİR', 1, 'Stok Girişi', "   stokAsil.GirisCariNo   ", "   stokAsil.CikisCariNo   @", 
                                        "   stokAsil.GirisStokYeriNo   @", 
                                        "   stokAsil.CikisStokYeriNo   @", 
                                        "   stokAsil.GirisIslemNoktasiNo   @",
                                        "   stokAsil.CikisIslemNoktasiNo   @", 
                                        "   stokAsil.GirisPersonelNo   @",
                                        "   stokAsil.CikisPersonelNo   @",
                                        '"   stokAsil.FaturaNo   @"',CURRENT_DATE)";
                command.ExecuteNonQuery();
                for (int i = 0; i < stokAsil.StokIslm.Count; i  )
                {
                    command.CommandText = @"INSERT INTO STOKISLM(STOK_ISLEMA_NO, ISLEM_KODU, ISLEM_ADI, ISLEM_YONU, TARIH,STOK_YERI_NO, 
                                                             CARI_NO, PERSONEL_NO, ISLEM_NOKTASI_NO, STOK_NO, BIRIM,BIRIMX,MIKTAR, 
                                                             DSTOK_NO, DOVIZ_BIRIMI, DOVIZ_KURU, TAKIP_SEKLI , FSTOK_URET)

                                       VALUES("   stokAsil.IslemANo   @", 'STKGİR', 'Stok Girişi', 1, CURRENT_DATE, 
                                              "   stokAsil.StokIslm.ElementAt(i).StokYeriNo   @", "   stokAsil.StokIslm.ElementAt(i).CariNo   @", 
                                              "   stokAsil.StokIslm.ElementAt(i).PersonelNo   @",
                                              "   stokAsil.StokIslm.ElementAt(i).IslemNoktasıNo   @",
                                              "   stokAsil.StokIslm.ElementAt(i).StokNo   @",  
                                             (SELECT FIRST 1 BIRIM FROM STOKBIRI WHERE STOK_NO = "   stokAsil.StokIslm.ElementAt(i).StokNo   @" ORDER BY SIRA_NO), 
                                             (SELECT FIRST 1 BIRIMX FROM STOKBIRI WHERE STOK_NO = "   stokAsil.StokIslm.ElementAt(i).StokNo   @" ORDER BY SIRA_NO),
                                             "   stokAsil.StokIslm.ElementAt(i).Miktar   ", 0, 'TL', 1, 'M', 'E')";
                    command.ExecuteNonQuery();

                    for (int j = 0; j < stokAsil.StokIslm.ElementAt(i).TakipNo.Count(); j  )
                    {
                        command.CommandText = @"INSERT INTO STOKCEKI (STOK_ISLEM_NO, TAKIP_NO,SIRA_NO ,MIKTAR,BIRIM, ACIKLAMA2, ACIKLAMA3,  ACIKLAMA4, ACIKLAMA5)
                                              VALUES("   stokAsil.StokIslm.ElementAt(i).StokIslemNo   ", '"   stokAsil.StokIslm.ElementAt(i).TakipNo.ElementAt(j)   @"',"   j   1   @" ,
                                              "   stokAsil.StokIslm.ElementAt(i).Miktar   ", 'Adet' , '"   stokAsil.StokIslm.ElementAt(i).FaturaNo   "' ,'"   stokAsil.StokIslm.ElementAt(i).BagBarkod[j]   @"', '"   stokAsil.StokIslm.ElementAt(i).KoliBarkod[j]   "', '"   stokAsil.StokIslm.ElementAt(i).PaletBarkod[j]   "')";

                        command.ExecuteNonQuery();
                    }

                }
                // Commit the transaction.
                transaction.Commit();
                Console.WriteLine("Bütün Kayıtlar başarı ile eklendi.");
            }

            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                MessageBox.Show(" "   ex.Message);
                try
                {
                    // Attempt to roll back the transaction.
                    transaction.Rollback();
                }
                catch
                {
                    // Do nothing here; transaction is not active.
                }
            }
  

Очевидно, что вам нужно многое попробовать и принять. Предлагаемые источники для просмотра могут показаться пугающими. Возможно, вам придется немного подправить это, но это ДОЛЖНО заставить вас работать.

Поскольку вы приложили искренние усилия, я предоставил обновление для вас, и, надеюсь, вы все еще можете ознакомиться с Sql-инъекциями и методами предотвращения.

 // Set the Connection to the new OleDbConnection.
command.Connection = connection;

// Open the connection and execute the transaction.
try
{
    connection.Open();

    // Start a local transaction
    transaction = connection.BeginTransaction();

    // Assign transaction object for a pending local transaction.
    command.Connection = connection;
    command.Transaction = transaction;

    // Execute the commands.
    command.CommandText =
    @"INSERT INTO STOKASIL 
    (   STOK_ISLEMA_NO,
        ISLEM_KODU, 
        ISLEM_YONU, 
        ISLEM_ADI, 
        GIRIS_CARI_NO, 
        CIKIS_CARI_NO, 
        GIRIS_STOK_YERI_NO,
        CIKIS_STOK_YERI_NO, 
        GIRIS_ISLEM_NOKTASI_NO, 
        CIKIS_ISLEM_NOKTASI_NO, 
        GIRIS_PERSONEL_NO, 
        CIKIS_PERSONEL_NO,
        FATURA_NO,
        TARIH )
    VALUES
    (   ?,
        'STKGİR', 
        1, 
        'Stok Girişi', 
        ?, 
        ?, 
        ?, 
        ?, 
        ?,
        ?, 
        ?,
        ?,
        ?,
        CURRENT_DATE ) ";

    // NOW, Add the parameters.  Via OleDB, parameters are typically found with "?" as a place-holder 
    // for a corresponding parameter and MUST be added  to the query command in same sequence.
    // others may complain about using AddWithValue, but simplified to show context of order as I dont know your data type values (string, int, decimal, date, etc)

    command.Parameters.AddWithValue( "parmSTOK_ISLEMA_NO", stokAsil.IslemANo );

    command.Parameters.AddWithValue( "parmGIRIS_CARI_NO", stokAsil.GirisCariNo );
    command.Parameters.AddWithValue( "parmCIKIS_CARI_NO", stokAsil.CikisCariNo );

    command.Parameters.AddWithValue( "parmGIRIS_STOK_YERI_NO", stokAsil.GirisStokYeriNo );
    command.Parameters.AddWithValue( "parmCIKIS_STOK_YERI_NO", stokAsil.CikisStokYeriNo );

    command.Parameters.AddWithValue( "parmGIRIS_ISLEM_NOKTASI_NO", stokAsil.GirisIslemNoktasiNo );
    command.Parameters.AddWithValue( "parmCIKIS_ISLEM_NOKTASI_NO", stokAsil.CikisIslemNoktasiNo );

    command.Parameters.AddWithValue( "parmGIRIS_PERSONEL_NO", stokAsil.GirisPersonelNo );
    command.Parameters.AddWithValue( "parmCIKIS_PERSONEL_NO", stokAsil.CikisPersonelNo );

    command.Parameters.AddWithValue( "parmFATURA_NO", stokAsil.FaturaNo );


    // NOW we can insert.
    command.ExecuteNonQuery();


    // Now, for the next one in the loop, prepare a new command and its parameters ONCE.
    var cmdIns1 = new OleDbCommand();
    cmdIns1.Connection = connection;
    cmdIns1.Transaction = transaction;
    cmdIns1.CommandText = 
    @"INSERT INTO STOKISLM
    (   STOK_ISLEMA_NO, 
        ISLEM_KODU, 
        ISLEM_ADI, 
        ISLEM_YONU, 
        TARIH,STOK_YERI_NO, 
        CARI_NO, 
        PERSONEL_NO, 
        ISLEM_NOKTASI_NO, 
        STOK_NO, 
        BIRIM,
        BIRIMX,
        MIKTAR, 
        DSTOK_NO, 
        DOVIZ_BIRIMI, 
        DOVIZ_KURU, 
        TAKIP_SEKLI, 
        FSTOK_URET )
    VALUES
    (   ?, 
        'STKGİR',
        'Stok Girişi', 
        1, 
        CURRENT_DATE,
        ?, 
        ?, 
        ?,
        ?,
        ?,
        (SELECT FIRST 1 BIRIM 
            FROM STOKBIRI 
            WHERE STOK_NO =  ?
            ORDER BY SIRA_NO), 
        (SELECT FIRST 1 BIRIMX 
            FROM STOKBIRI 
            WHERE STOK_NO = ? 
            ORDER BY SIRA_NO),
        ?, 
        0, 
        'TL', 
        1, 
        'M', 
        'E' )";

    // Now, to prepare the parameters here, I am pre-pulling the first instance from your list
    // so the parmeters have proper data types in them.  People may complain about using. Same concept
    var tmpRecord = stokAsil.StokIslm.ElementAt(0);

    // uses the "stokAsil" from parent insert transaction basis record
    cmdIns1.Parameters.AddWithValue( "parmSTOK_ISLEMA_NO", stokAsil.IslemANo );

    // Now use the per-element temporary record
    cmdIns1.Parameters.AddWithValue( "parmSTOK_YERI_NO", tmpRecord.StokYeriNo);
    cmdIns1.Parameters.AddWithValue( "parmCARI_NO", tmpRecord.CariNo);
    cmdIns1.Parameters.AddWithValue( "parmPERSONEL_NO", tmpRecord.PersonelNo);
    cmdIns1.Parameters.AddWithValue( "parmISLEM_NOKTASI_NO", tmpRecord.IslemNoktasıNo);
    cmdIns1.Parameters.AddWithValue( "parmSTOK_NO", tmpRecord.StokNo);
    // this is parameter placement within the insert's SUBQUERY, same value, just required placement of parameter to qualify
    cmdIns1.Parameters.AddWithValue( "parmQry1STOK_NO", tmpRecord.StokNo);
    // same for second Subquery
    cmdIns1.Parameters.AddWithValue( "parmQry2STOK_NO", tmpRecord.StokNo);
    cmdIns1.Parameters.AddWithValue( "parmMIKTAR", tmpRecord.Miktar);

    cmdIns1.Parameters.AddWithValue( "parmDSTOK_NO", tmpRecord.StokYeriNo);
    cmdIns1.Parameters.AddWithValue( "parmDOVIZ_BIRIMI", tmpRecord.StokYeriNo);
    cmdIns1.Parameters.AddWithValue( "parmDOVIZ_KURU", tmpRecord.StokYeriNo);
    cmdIns1.Parameters.AddWithValue( "parmTAKIP_SEKLI", tmpRecord.StokYeriNo);
    cmdIns1.Parameters.AddWithValue( "parmFSTOK_URET", tmpRecord.StokYeriNo);


    // Since you have a nested transaction and additional insert into other table,
    // similarly prepare a seperate command for it so it does not have to keep being rebuilt
    var cmdIns2 = new OleDbCommand();
    cmdIns2.Connection = connection;
    cmdIns2.Transaction = transaction;
    cmdIns2.CommandText = 
@"INSERT INTO STOKCEKI 
(   STOK_ISLEM_NO, 
    TAKIP_NO,
    SIRA_NO,
    MIKTAR,
    BIRIM, 
    ACIKLAMA2,
    ACIKLAMA3,
    ACIKLAMA4,
    ACIKLAMA5 )
VALUES
(   ?, 
    ?,
    ?,
    ?, 
    'Adet',
    ?,
    ?, 
    ?, 
    ? )";

    // still tmp object place-holder
    tmpRecord = stokAsil.StokIslm.ElementAt(0);

    cmdIns2.Parameters.AddWithValue( "parmSTOK_ISLEM_NO", tmpRecord.StokIslemNo);
    cmdIns2.Parameters.AddWithValue( "parmTAKIP_NO", tmpRecord.TakipNo.ElementAt(0) ); 
    // Just a place-holder integer value for the "j   1" value
    cmdIns2.Parameters.AddWithValue( "parmSIRA_NO", 1 );
    cmdIns2.Parameters.AddWithValue( "parmMIKTAR", tmpRecord.Miktar);

    cmdIns2.Parameters.AddWithValue( "parmACIKLAMA2", tmpRecord.FaturaNo);
    // temporary forcing "j" subscript to 0 just for place-holder value of parameter
    cmdIns2.Parameters.AddWithValue( "parmACIKLAMA3", tmpRecord.BagBarkod[0] );
    cmdIns2.Parameters.AddWithValue( "parmACIKLAMA4", tmpRecord.KoliBarkod[0] );
    cmdIns2.Parameters.AddWithValue( "parmACIKLAMA5 ", tmpRecord.PaletBarkod[0] );

    // Now the second insert command is prepared too



    // NOW, we can start the loop with the prepared sql command and parameters.
    for (int i = 0; i < stokAsil.StokIslm.Count; i  )
    {
        // to keep inline with the command parameters added, 
        // re-set the tmpRecord to the specific element at you are trying to work with.
        tmpRecord = stokAsil.StokIslm.ElementAt(i);

        // As for the parameters, we don't need to add them again, just need to 
        // UPDATE the PARAMETER's VALUE based on the actual cycle record... doing in same order
        // we CAN skip the first parameter as that is a constant for all, but you COULD if you wanted to
        cmdIns1.Parameters["parmSTOK_ISLEMA_NO"].Value =  stokAsil.IslemANo;

        // Now the rest per the actual record being processed
        cmdIns1.Parameters["parmSTOK_YERI_NO"].Value = tmpRecord.StokYeriNo;
        cmdIns1.Parameters["parmCARI_NO"].Value = tmpRecord.CariNo;
        cmdIns1.Parameters["parmPERSONEL_NO"].Value = tmpRecord.PersonelNo;
        cmdIns1.Parameters["parmISLEM_NOKTASI_NO"].Value = tmpRecord.IslemNoktasıNo;
        cmdIns1.Parameters["parmSTOK_NO"].Value = tmpRecord.StokNo;
        cmdIns1.Parameters["parmQry1STOK_NO"].Value = tmpRecord.StokNo;
        cmdIns1.Parameters["parmQry2STOK_NO"].Value = tmpRecord.StokNo;
        cmdIns1.Parameters["parmMIKTAR"].Value = tmpRecord.Miktar;
        cmdIns1.Parameters["parmDSTOK_NO"].Value = tmpRecord.StokYeriNo;
        cmdIns1.Parameters["parmDOVIZ_BIRIMI"].Value = tmpRecord.StokYeriNo;
        cmdIns1.Parameters["parmDOVIZ_KURU"].Value = tmpRecord.StokYeriNo;
        cmdIns1.Parameters["parmTAKIP_SEKLI"].Value = tmpRecord.StokYeriNo;
        cmdIns1.Parameters["parmFSTOK_URET"].Value = tmpRecord.StokYeriNo;

        // NOW you can execute the query with parameters in place for it
        cmdIns1.ExecuteNonQuery();

        for (int j = 0; j < stokAsil.StokIslm.ElementAt(i).TakipNo.Count(); j  )
        {
            // similarly, setting the "VALUE" of each parameter from current actual record object/counter
            cmdIns2.Parameters["parmSTOK_ISLEM_NO", tmpRecord.StokIslemNo);
            // NOW we can use the "j" subscipt reference
            cmdIns2.Parameters["parmTAKIP_NO"].Value = tmpRecord.TakipNo.ElementAt(j) ); 
            cmdIns2.Parameters["parmSIRA_NO"].Value = j 1 );
            cmdIns2.Parameters["parmMIKTAR"].Value = tmpRecord.Miktar);

            cmdIns2.Parameters["parmACIKLAMA2"].Value = tmpRecord.FaturaNo);
            cmdIns2.Parameters["parmACIKLAMA3"].Value = tmpRecord.BagBarkod[j] );
            cmdIns2.Parameters["parmACIKLAMA4"].Value = tmpRecord.KoliBarkod[j] );
            cmdIns2.Parameters["parmACIKLAMA5"].Value = tmpRecord.PaletBarkod[j] );

            // NOW we can insert second cycle of records
            cmdIns2.ExecuteNonQuery();
        }
    }

    // Commit the transaction.
    transaction.Commit();
    Console.WriteLine("Bütün Kayıtlar başarı ile eklendi.");
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    MessageBox.Show(" "   ex.Message);
    try
    {
        // Attempt to roll back the transaction.
        transaction.Rollback();
    }
    catch
    {
        // Do nothing here; transaction is not active.
    }
}