I have method in my code like SaveListOfObjects which I execute inside foreach loop and then insert records to SQL Server.
It works great when there is no error in data I am inserting. But if error occured then only valid data is inserted in SQL.
I want to do following:
- Insert all record only in case that whole data is valid, and if one error occurred in inserting.
- Delete all previously saved data in SQL.
So, I already tried with TransactionScope and SqlTransaction classes and even with SQL TRANSACTION but only thing I could manage is insert valid data and non-valid data was omitted.
Now, as far as I search on web, I found that parallel transaction is not possible. Also, SQL has Isolation Level which prohibited parallel tasks.
Is there any possible way to accomplish insert in SQL like ALL or NOTHING?
UPDATE:
My code is as following:
public int Ramiz_SavePack(IPacking pack)
{
using (var conn = (SqlConnection)connector.GetConnection())
{
conn.Open();
SqlTransaction transaction;
var comm = (SqlCommand)connector.GetCommand("Ramiz_Pack_Save");
comm.CommandType = CommandType.StoredProcedure;
transaction = conn.BeginTransaction();
comm.Transaction = transaction;
int rowNum = 0;
try
{
if (!string.IsNullOrEmpty(pack.BrojKolete))
comm.Parameters.Add("@BrojKolete", SqlDbType.NVarChar).Value = pack.BrojKolete;
else
comm.Parameters.Add("@BrojKolete", SqlDbType.NVarChar).Value = DBNull.Value;
comm.Parameters.Add("@Bosanski", SqlDbType.NVarChar).Value = pack.Bosanski;
comm.Parameters.Add("@Kom", SqlDbType.Float).Value = pack.Kom;
comm.Parameters.Add("@Vrsta", SqlDbType.NVarChar).Value = pack.Vrsta;
comm.Parameters.Add("@Datum", SqlDbType.Date).Value = pack.Datum;
comm.Parameters.Add("@BrojKamiona", SqlDbType.Int).Value = pack.BrojKamiona;
rowNum = comm.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
try
{
conn.Close();
transaction.Rollback();
}
catch (Exception ex2)
{
Console.WriteLine(ex2.Message);
}
}
return rowNum;
}
}and calling this method inside this:
var pack = new Pack();
for (int i = 1; i < lastRow; i++)
{
pack.Ramiz_SavePack(new Packing
{
BrojKolete = Convert.ToString(brojKoleteRange.Offset[i, 0].Value2),
Bosanski = Convert.ToString(nazivArtiklaRange.Offset[i, 0].Value2),
Kom = Convert.ToDouble(komRange.Offset[i, 0].Value2),
Vrsta = Convert.ToString(vrstaRange.Offset[i, 0].Value2),
BrojKamiona = int.Parse(ddlBrojKamiona.SelectedItem.Value),
Datum = Convert.ToDateTime(txtDate.Text)
});
pnlMessageSuccess.Visible = true;
}
4条答案
按热度按时间u4vypkhs1#
It looks to me like you are looping and calling the save method for each object. This isn't a problem if the transaction exists around that loop, but it doesn't. You are rollback/committing each object separately
You need to either create a list of objects to save and send it into the save method, or create a transaction that wraps the loop e.g.:
or
k4ymrczo2#
Your problem lies in the fact that you open and close the transaction for every object that you write in the database. Of course this means that when you hit a problem with your data, the previous correct one has already written to the database and committed. So it cannot be rolled back.
The solution is to open the connection and the transaction outside your data insert method and pass these object instances inside the method.
In this way the method that inserts data, when it fails, will raise an exception trapped by the calling code that has opened the connection and the transaction. This calling code could then call the rollback method to undo every object inserted so far.
slwdgvem3#
It is easier to make constraints on the data entry objects like constraining a certain Textbox to accept only numbers or whatever you would like . And as another idea .. try checking the data Accuracy before Querying it to the database
kuhbmx9i4#
One way is to create a DataTable from your list by mapping each object in the list to a row of the DataTable. Then you can loop through the DataTable rows to perform the insert operation, and include this code block inside a Transaction. This will allow you to prevent entering partial data.
You may refer to this link to get an idea how to do the list to DataTable conversion.