The transaction is completed and cannot be used again.

hi,

ive got the following error, and I don't know how to fix it. I preformed a executeNonquery wich seems to work however when I commit the transaction is says the transaction is already completed and cannot be used again. How can I fix this ???

if (_files.Count != 0)
{
using (var transaction = connection.BeginTransaction())
{
try
{
UploadFiles(connection, transaction);
transaction.Commit(); // here it crashed
}
catch (Exception ex)
{
transaction.Rollback();
Log.WriteException(ex);
throw;
}
}
var projatt2 = new ProjectAttachment();
AttachmentsGridView1.DataSource = projatt2.getProjectAttachmentsByProjectId(connection, null, ViewState["projectID"]);
AttachmentsGridView1.DataBind();
}

private void UploadFiles(SqlConnection connection, SqlTransaction transaction)
{
// upload and save files
foreach (string key in _files.Keys)
{
var file = _files[key];
if (string.IsNullOrEmpty(file?.FileName)) continue;
Proj.getProjectById(connection, transaction, ViewState["projectID"]);
var org = ProvisioningOrganisation.getOrganisationById(connection, transaction, Proj.PartnerID);
var orgDir = Server.MapPath("../upload/" + org.UDF);
if (!Directory.Exists(orgDir))
Directory.CreateDirectory(orgDir);
var newFileName = Path.GetFileName(file.FileName);
var saveDir = Server.MapPath("../upload/" + org.UDF + "/" + newFileName);
var tempSaveDir = saveDir;
var exists = File.Exists(saveDir);
var i = 1;
while (exists)
{
var filename = Path.GetFileNameWithoutExtension(saveDir);
var extension = Path.GetExtension(saveDir);
newFileName = filename + i + extension;
tempSaveDir = Server.MapPath("../upload/" + org.UDF + "/" + newFileName);
exists = File.Exists(tempSaveDir);
i++;
}
saveDir = tempSaveDir;
file.SaveAs(saveDir);
// save attachment
var security = new SecurityLayer();
Projatt.FileName = newFileName;
Projatt.ProjectID = Proj.ID;
Projatt.PermissionMediatorID = security.getpermissionMediatorID(connection, transaction);
var projAttID = Projatt.saveProjectAttachment(connection, transaction);
// Set binary data of attachment to database
ProjectAttachmentLogic.saveBinaryDataAttachment(connection, transaction, tempSaveDir, projAttID);
}
}

public class ProjectAttachmentLogic
{
public static void saveBinaryDataAttachment(SqlConnection connection, SqlTransaction transaction, string ProjattFilename, string ProjattId)
{
var id = Int32.Parse(ProjattId);
byte[] file;
using (var stream = new FileStream(ProjattFilename, FileMode.Open, FileAccess.Read))
{
using (var reader = new BinaryReader(stream))
{
file = reader.ReadBytes((int)stream.Length);
}
ProjectAttachmentDal.InsertBinaryDataAttachment(connection, transaction, id, file);
}
1. public class ProjectAttachmentDal
2. {
3. ///
4. /// Insert binary data of Attachment
5. ///
6. public static void InsertBinaryDataAttachment(SqlConnection connection, SqlTransaction transaction, int id, byte[] file)
7. {
8. using (connection)
9. {
10. using (var sqlCommand = new SqlCommand("INSERT INTO proj.BinaryDataAttachments (ID, Data) Values(@Id, @Datafile)", connection))
11. {
12. sqlCommand.Transaction = transaction;
13. sqlCommand.Parameters.Add("@ID", SqlDbType.Int, id).Value = id;
14. sqlCommand.Parameters.Add("@Datafile", SqlDbType.VarBinary, file.Length).Value = file;
15. sqlCommand.ExecuteNonQuery();
16. }
17. }
18. }
19. }