Bulk Inserts with Transaction

21 Jan 2006 6:56 PM

Jan Waiz

Hi All,

i am a little bit confused whats wrong with my Code - be so kind and have a
look at this:

I have to do a lot of Insert-Statements into a SQL-Database after few
Select-Statements. This should run in a Transaction. I have implement this
as (in short):

// = = = BOF
oSelConn = new SqlConnection(...);
oSelConn.Open();

oSelTrans = oSelConn.BeginTransaction();

try
{
    oSelCmnd = oSelConn.CreateCommand();
    oSelCmnd.Transaction = oSelTrans;
    oSelCmnd.CommandText = "SELECT * FROM MyTable";

    // Get Pk-Value for further Needs
    oSelRead = oSelCmnd.ExecuteReader();
    sPkValue = oSelRead["PkColumn"]
    oSelRead.Close()

    // Delete all Records in DataTable for PkValue
    oSelCmnd.CommandText = "SELECT * FROM MyData WHERE MyData.PK_MyTable =
'" + sPkValue + "'";
    oSelCmnd.ExecuteNonQuery();

    // Now a Bulk of Insert
    while ( true )
    {
        oSelCmnd.CommandText =
            "INSERT INTO MyData " +
            "(PK_MyTable,PK_MyData)" +
            " VALUES " +
            "(@PKM,@PKD)";

        oSelCmnd.Parameters.Add( "@PKM", sPkValue )
        oSelCmnd.Parameters.Add( "@PKD", Guid.NewGuid().ToString() )

        // HERE IT CRASH - READ BELOW
        oSelCmnd.ExecuteNonQuery();

    }
}
catch
{ oSelTrans.Rollback(); }
finally
{ oSelConn.close(); }
// = = = EOF

I get an Error about Duplicate-Key Unique-Constraint >>PK_MyData<< ???????

I have fix it (do i?) with generating a new Connection-Objekt and assigning
the existing Transaction-Objekt to the new Command-Object of the
Insert-Connection like this:

    while ( true )
    {
        oInsConn = new SqlConnection(...);
        oInsConn.Open();

        oInsCmnd = oSelConn.CreateCommand();
        oInsCmnd.Transaction = oSelTrans;    //    <= Existing
Transaction-Object
        oInsCmnd.CommandText =
            "INSERT INTO MyData " +
            "(PK_MyTable,PK_MyData)" +
            " VALUES " +
            "(@PKM,@PKD)";

        oInsCmnd.Parameters.Add( "@PKM", sPkValue )
        oInsCmnd.Parameters.Add( "@PKD", Guid.NewGuid().ToString() )

        // HERE IT CRASH - READ BELOW
        oInsCmnd.ExecuteNonQuery();

        oInsConn.Close();
    }

Am i right ?

Thanks a lot in Advance for your Time to read this Stuff and your Help :-)

Regards
Jan Waiz

21 Jan 2006 7:30 PM

Jan Waiz

*oups* - sorry - 2 Failures in my Code :-)))

Selecting desiered Record is not:

    oSelCmnd = oSelConn.CreateCommand();
    oSelCmnd.Transaction = oSelTrans;
    oSelCmnd.CommandText = "SELECT * FROM MyTable";

it is:

    oSelCmnd = oSelConn.CreateCommand();
    oSelCmnd.Transaction = oSelTrans;
    oSelCmnd.CommandText = "SELECT * FROM MyTable WHERE TableCode =
'YouAre'";

and deleting the Records is not via:

    // Delete all Records in DataTable for PkValue
    oSelCmnd.CommandText = "SELECT * FROM MyData WHERE MyData.PK_MyTable =
'" + sPkValue + "'";
    oSelCmnd.ExecuteNonQuery();

It is as follows:

    // Delete all Records in DataTable for PkValue
    oSelCmnd.CommandText = "DELETE FROM MyData WHERE MyData.PK_MyTable = '"
+ sPkValue + "'";
    oSelCmnd.ExecuteNonQuery();

of course... :-)

TIA
Jan Waiz

22 Jan 2006 10:23 AM

Jan Waiz

Realy nobody out there who can tell me whats wrong?

I read a lot of Articles but noone talk about how to handle a bulk of
inserts when need to do Selects and Delete before...

TIA
Jan Waiz

Show quote

"Jan Waiz" <jw***@icomedv.de> schrieb im Newsbeitrag
news:e29cdxrHGHA.3144@TK2MSFTNGP11.phx.gbl...
> Hi All,
>
> i am a little bit confused whats wrong with my Code - be so kind and have
> a look at this:
>
> I have to do a lot of Insert-Statements into a SQL-Database after few
> Select-Statements. This should run in a Transaction. I have implement this
> as (in short):
>
> // = = = BOF
> oSelConn = new SqlConnection(...);
> oSelConn.Open();
>
> oSelTrans = oSelConn.BeginTransaction();
>
> try
> {
>    oSelCmnd = oSelConn.CreateCommand();
>    oSelCmnd.Transaction = oSelTrans;
>    oSelCmnd.CommandText = "SELECT * FROM MyTable";
>
>    // Get Pk-Value for further Needs
>    oSelRead = oSelCmnd.ExecuteReader();
>    sPkValue = oSelRead["PkColumn"]
>    oSelRead.Close()
>
>    // Delete all Records in DataTable for PkValue
>    oSelCmnd.CommandText = "SELECT * FROM MyData WHERE MyData.PK_MyTable =
> '" + sPkValue + "'";
>    oSelCmnd.ExecuteNonQuery();
>
>    // Now a Bulk of Insert
>    while ( true )
>    {
>        oSelCmnd.CommandText =
>            "INSERT INTO MyData " +
>            "(PK_MyTable,PK_MyData)" +
>            " VALUES " +
>            "(@PKM,@PKD)";
>
>        oSelCmnd.Parameters.Add( "@PKM", sPkValue )
>        oSelCmnd.Parameters.Add( "@PKD", Guid.NewGuid().ToString() )
>
>        // HERE IT CRASH - READ BELOW
>        oSelCmnd.ExecuteNonQuery();
>
>    }
> }
> catch
> { oSelTrans.Rollback(); }
> finally
> { oSelConn.close(); }
> // = = = EOF
>
> I get an Error about Duplicate-Key Unique-Constraint >>PK_MyData<< ???????
>
> I have fix it (do i?) with generating a new Connection-Objekt and
> assigning the existing Transaction-Objekt to the new Command-Object of the
> Insert-Connection like this:
>
>    while ( true )
>    {
>        oInsConn = new SqlConnection(...);
>        oInsConn.Open();
>
>        oInsCmnd = oSelConn.CreateCommand();
>        oInsCmnd.Transaction = oSelTrans;    //    <= Existing
> Transaction-Object
>        oInsCmnd.CommandText =
>            "INSERT INTO MyData " +
>            "(PK_MyTable,PK_MyData)" +
>            " VALUES " +
>            "(@PKM,@PKD)";
>
>        oInsCmnd.Parameters.Add( "@PKM", sPkValue )
>        oInsCmnd.Parameters.Add( "@PKD", Guid.NewGuid().ToString() )
>
>        // HERE IT CRASH - READ BELOW
>        oInsCmnd.ExecuteNonQuery();
>
>        oInsConn.Close();
>    }
>
> Am i right ?
>
> Thanks a lot in Advance for your Time to read this Stuff and your Help :-)
>
> Regards
> Jan Waiz
>

22 Jan 2006 9:03 PM

Triax

Well, when I do bulk INSERTS, one method is to use the DataAdapter.Update
method. This code is taken from EasyObjects, and uses the UpdateDataSet
method from the Enterprise Library's Data Access Application Block.
UpdateDataSet eventually calls DataAdapter.Update.

public virtual void Save()
{
   if (_dataTable == null) { return; }

   TransactionManager txMgr = TransactionManager.ThreadTransactionMgr();

   try
   {
    bool needToInsert = false;
    bool needToUpdate = false;
    bool needToDelete = false;
    foreach (DataRow row in _dataTable.Rows)
    {
     switch (row.RowState)
     {
      case DataRowState.Added:
       needToInsert = true;
       break;
      case DataRowState.Modified:
       needToUpdate = true;
       break;
      case DataRowState.Deleted:
       needToDelete = true;
       break;
     }
    }
    if ((needToInsert || (needToUpdate || needToDelete)))
    {
     DBCommandWrapper insertCommand = null;
     DBCommandWrapper updateCommand = null;
     DBCommandWrapper deleteCommand = null;

     if (needToInsert) { insertCommand = GetInsertCommand(); }
     if (needToUpdate) { updateCommand = GetUpdateCommand(); }
     if (needToDelete) { deleteCommand = GetDeleteCommand(); }

     Database db = GetDatabase();

     // Add the current DataTable to a DataSet
     DataSet ds = new DataSet();
     ds.Tables.Add(this.DataTable);

     // Initialize the transaction, including an event watcher so
     // that we get notified when the transaction is committed.
     txMgr.TransactionCommitted += new
TransactionManager.TransactionCommittedDelegate(txMgr_TransactionCommitted);
     txMgr.BeginTransaction();

     // Perform the update
     int rowsAffected = db.UpdateDataSet(ds, this.TableName, insertCommand,
updateCommand, deleteCommand, txMgr.GetTransaction(db));

     // Clean up resources
     txMgr.CommitTransaction();
     ds.Tables.Clear();
    }
   }
   catch (Exception ex)
   {
    if (!(txMgr == null))
    {
     txMgr.RollbackTransaction();
     txMgr.TransactionCommitted -= new
TransactionManager.TransactionCommittedDelegate(this.txMgr_TransactionCommitted);
    }
    throw ex;
   }
}

--
Triax
EasyObjects.NET: The O/RM for the Enterprise Library
http://www.easyobjects.net

Show quote

"Jan Waiz" <jw***@icomedv.de> wrote in message
news:OeU%23O3zHGHA.3896@TK2MSFTNGP15.phx.gbl...
> Realy nobody out there who can tell me whats wrong?

23 Jan 2006 9:44 AM

Jan Waiz

Hi Triax,

thanks a lot for your Help and Code. I am sorry, but i can´t use the
Enterprise-Lib - several Circumstances will make the Fact that i have to do
it "basic" as descriped in my Sample.

TIA
Jan Waiz

Show quote

"Triax" <nore***@noreply.com> schrieb im Newsbeitrag
news:uswPYd5HGHA.2444@TK2MSFTNGP11.phx.gbl...
> Well, when I do bulk INSERTS, one method is to use the DataAdapter.Update
> method. This code is taken from EasyObjects, and uses the UpdateDataSet
> method from the Enterprise Library's Data Access Application Block.
> UpdateDataSet eventually calls DataAdapter.Update.
>
> public virtual void Save()
> {
>   if (_dataTable == null) { return; }
>
>   TransactionManager txMgr = TransactionManager.ThreadTransactionMgr();
>
>   try
>   {
>    bool needToInsert = false;
>    bool needToUpdate = false;
>    bool needToDelete = false;
>    foreach (DataRow row in _dataTable.Rows)
>    {
>     switch (row.RowState)
>     {
>      case DataRowState.Added:
>       needToInsert = true;
>       break;
>      case DataRowState.Modified:
>       needToUpdate = true;
>       break;
>      case DataRowState.Deleted:
>       needToDelete = true;
>       break;
>     }
>    }
>    if ((needToInsert || (needToUpdate || needToDelete)))
>    {
>     DBCommandWrapper insertCommand = null;
>     DBCommandWrapper updateCommand = null;
>     DBCommandWrapper deleteCommand = null;
>
>     if (needToInsert) { insertCommand = GetInsertCommand(); }
>     if (needToUpdate) { updateCommand = GetUpdateCommand(); }
>     if (needToDelete) { deleteCommand = GetDeleteCommand(); }
>
>     Database db = GetDatabase();
>
>     // Add the current DataTable to a DataSet
>     DataSet ds = new DataSet();
>     ds.Tables.Add(this.DataTable);
>
>     // Initialize the transaction, including an event watcher so
>     // that we get notified when the transaction is committed.
>     txMgr.TransactionCommitted += new
> TransactionManager.TransactionCommittedDelegate(txMgr_TransactionCommitted);
>     txMgr.BeginTransaction();
>
>     // Perform the update
>     int rowsAffected = db.UpdateDataSet(ds, this.TableName, insertCommand,
> updateCommand, deleteCommand, txMgr.GetTransaction(db));
>
>     // Clean up resources
>     txMgr.CommitTransaction();
>     ds.Tables.Clear();
>    }
>   }
>   catch (Exception ex)
>   {
>    if (!(txMgr == null))
>    {
>     txMgr.RollbackTransaction();
>     txMgr.TransactionCommitted -= new
> TransactionManager.TransactionCommittedDelegate(this.txMgr_TransactionCommitted);
>    }
>    throw ex;
>   }
> }
>
>
> --
> Triax
> EasyObjects.NET: The O/RM for the Enterprise Library
> http://www.easyobjects.net
>
>
> "Jan Waiz" <jw***@icomedv.de> wrote in message
> news:OeU%23O3zHGHA.3896@TK2MSFTNGP15.phx.gbl...
>> Realy nobody out there who can tell me whats wrong?
>
>

23 Jan 2006 10:49 AM

Jan Waiz

Hi All,

just for your Information - i got it :-)

The Problem was sitting in front of the Keyboard.

>        oSelCmnd.Parameters.Add( "@PKM", sPkValue )
>        oSelCmnd.Parameters.Add( "@PKD", Guid.NewGuid().ToString() )
>
>        // HERE IT CRASH - READ BELOW
>        oSelCmnd.ExecuteNonQuery();

After the ExecuteNonQuery i inserted a:

oSelCmnd.Parameters.Clear()

and everything works.

It seems, that the Parameters are not overwritten - they must be cleared
before new Parameters can be used.

Am i right?

Regards
Jan Waiz

Show quote

"Jan Waiz" <jw***@icomedv.de> schrieb im Newsbeitrag
news:e29cdxrHGHA.3144@TK2MSFTNGP11.phx.gbl...
> Hi All,
>
> i am a little bit confused whats wrong with my Code - be so kind and have
> a look at this:
>
> I have to do a lot of Insert-Statements into a SQL-Database after few
> Select-Statements. This should run in a Transaction. I have implement this
> as (in short):
>
> // = = = BOF
> oSelConn = new SqlConnection(...);
> oSelConn.Open();
>
> oSelTrans = oSelConn.BeginTransaction();
>
> try
> {
>    oSelCmnd = oSelConn.CreateCommand();
>    oSelCmnd.Transaction = oSelTrans;
>    oSelCmnd.CommandText = "SELECT * FROM MyTable";
>
>    // Get Pk-Value for further Needs
>    oSelRead = oSelCmnd.ExecuteReader();
>    sPkValue = oSelRead["PkColumn"]
>    oSelRead.Close()
>
>    // Delete all Records in DataTable for PkValue
>    oSelCmnd.CommandText = "SELECT * FROM MyData WHERE MyData.PK_MyTable =
> '" + sPkValue + "'";
>    oSelCmnd.ExecuteNonQuery();
>
>    // Now a Bulk of Insert
>    while ( true )
>    {
>        oSelCmnd.CommandText =
>            "INSERT INTO MyData " +
>            "(PK_MyTable,PK_MyData)" +
>            " VALUES " +
>            "(@PKM,@PKD)";
>
>        oSelCmnd.Parameters.Add( "@PKM", sPkValue )
>        oSelCmnd.Parameters.Add( "@PKD", Guid.NewGuid().ToString() )
>
>        // HERE IT CRASH - READ BELOW
>        oSelCmnd.ExecuteNonQuery();
>
>    }
> }
> catch
> { oSelTrans.Rollback(); }
> finally
> { oSelConn.close(); }
> // = = = EOF
>
> I get an Error about Duplicate-Key Unique-Constraint >>PK_MyData<< ???????
>
> I have fix it (do i?) with generating a new Connection-Objekt and
> assigning the existing Transaction-Objekt to the new Command-Object of the
> Insert-Connection like this:
>
>    while ( true )
>    {
>        oInsConn = new SqlConnection(...);
>        oInsConn.Open();
>
>        oInsCmnd = oSelConn.CreateCommand();
>        oInsCmnd.Transaction = oSelTrans;    //    <= Existing
> Transaction-Object
>        oInsCmnd.CommandText =
>            "INSERT INTO MyData " +
>            "(PK_MyTable,PK_MyData)" +
>            " VALUES " +
>            "(@PKM,@PKD)";
>
>        oInsCmnd.Parameters.Add( "@PKM", sPkValue )
>        oInsCmnd.Parameters.Add( "@PKD", Guid.NewGuid().ToString() )
>
>        // HERE IT CRASH - READ BELOW
>        oInsCmnd.ExecuteNonQuery();
>
>        oInsConn.Close();
>    }
>
> Am i right ?
>
> Thanks a lot in Advance for your Time to read this Stuff and your Help :-)
>
> Regards
> Jan Waiz
>