The transaction is completed and cannot be used again.

Question

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);
           }
public class ProjectAttachmentDal
{
    /// <summary>
    /// Insert binary data of Attachment
    /// </summary>
    public static void InsertBinaryDataAttachment(SqlConnection connection, SqlTransaction transaction, int id, byte[] file)
    {
        using (connection)
        {
            using (var sqlCommand = new SqlCommand("INSERT INTO proj.BinaryDataAttachments (ID, Data) Values(@Id, @Datafile)", connection))
            {
                sqlCommand.Transaction = transaction;
                sqlCommand.Parameters.Add("@ID", SqlDbType.Int, id).Value = id;
                sqlCommand.Parameters.Add("@Datafile", SqlDbType.VarBinary, file.Length).Value = file;
                sqlCommand.ExecuteNonQuery();
            }
        }
    }
}