Bulk Inserts with Transaction

[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

 

[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

 

[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

 

[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;
}
}

 

[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

 

[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

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