ExecuteScalar () with SqlTransaction
I am trying to do two inserts to a database using sqltransaction and exectescalar to return the output id of the first insert.
This is the code:
try
{
using (SqlConnection conn = new SqlConnection("ConnectionString"))
{
conn.Open();
SqlTransaction safetransaction = conn.BeginTransaction();
foreach (var dado in NFSe)
{
SqlCommand cmd_NFSe = new SqlCommand("SqlCommand", conn, safetransaction);
var newID = cmd_NFSe.ExecuteScalar();
foreach (var serv in Servico)
{
string ID = newID.Tostring();
SqlCommand cmd_NFSeServ = new SqlCommand("SqlCommand", conn, safetransaction);
cmd_NFSeServ.ExecuteNonQuery();
}
}
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
But it doesn't work. Nothing is inserted into the database and I don't get any errors or exceptions, just nothing happens.
If I use the same code but without sqltransaction everything works fine.
Any ideas?
source to share
You missed so many things
- Trasaction negotiation
- Rollback a transaction
The deal is rolled back on any error or if it was deleted without prior committing
just try this:
try
{
using (SqlConnection conn = new SqlConnection("ConnectionString"))
{
conn.Open();
SqlTransaction safetransaction = conn.BeginTransaction();
foreach (var dado in NFSe)
{
SqlCommand cmd_NFSe = new
SqlCommand("SqlCommand", conn, safetransaction);
cmd_NFSe.Connection = connection;
cmd_NFSe.Transaction = transaction;
var newID = cmd_NFSe.ExecuteScalar();
foreach (var serv in Servico)
{
string ID = newID.Tostring();
SqlCommand cmd_NFSeServ = new
SqlCommand("SqlCommand", conn, safetransaction);
cmd_NFSeServ.ExecuteNonQuery();
cmd_NFSe.Commit();
}
}
}
}
catch (SqlException ex)
{
cmd_NFSe.Rollback();
MessageBox.Show(ex.ToString());
}
source to share
I would like you to show some of your SQL so we can see what you are doing, but I must assume that you have an identity column from which you are returning a new id. I like to avoid multiple trips to the server so you can be well aware that you can combine multiple SQL statements into a single call by splitting them with ";". All statements within this single call are also transactional, so your overall code will be smaller and faster with fewer server trips. IE:
INSERT INTO MyTable (MyField) VALUES ('Test1'); Select SCOPE_IDENTITY(); INSERT INTO MyTable (MyField) VALUES ('Test2'); Select SCOPE_IDENTITY()
Now use this with DataAdapter.Fill and you end up with a DataSet with two tables, each with a new ID for inserts.
Oh, to your original question, you need to commit the transaction: safetransaction.Commit();
source to share