The timeout period has expired before the operation was comp

Hi I get the following error, but I don't know how to fix it. ( still a bit of the same error that I had earlier.. the error message is: Timeout has expired. The timeout period has expired before the operation was completed or the server is not responding. the idea was to convert a file into binay data and upload it to an SQL database.

I get this when I do a transaction.commit in the page_load method. However when I do this in the ProjectAttachmentDal.InsertBinaryDataAttachment is doesn't gives an error ( only it shouldn't be in the dal everyone says so. here is my code

thnx guys

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using OneXS.Framework.Portal.Website;
using OneXS.Orion.BackOffice.Billing.ServiceAgent;
using OneXS.Orion.DatabaseClasses;
using OneXS.Orion.FrontOffice.Sales.BusinessLogic;
using OneXS.Orion.FrontOffice.Sales.BusinessLogic.Billing;
using OneXS.Orion.FrontOffice.Sales.DataAccess;
using OneXS.Orion.FrontOffice.Sales.Model;
using OneXS.Orion.HelperClasses;
using OneXS.Orion.Logging;
using OneXS.Orion.Properties;
using OneXS.Orion.FrontOffice.Sales.Core;
namespace OneXS.Orion.WebSite.Forms.Project
{
public partial class Project : OrionSalesPage
{
protected DatabaseClasses.Project Proj = new DatabaseClasses.Project();
protected ProjectAttachment Projatt = new ProjectAttachment();
protected FrontOffice.Sales.Model.ProjectHosted ProjectHosted;
protected Organisation Organisation;
private HttpFileCollection _files;
readonly LogManager _log = new LogManager();
protected new void Page_Load(object sender, EventArgs e)
{
try
{
base.Page_Load(sender, e);
using (var connection = DatabaseLayer.OpenConnection())
using (var transaction = connection.BeginTransaction())
{
{
_files = Request.Files;
if (string.IsNullOrEmpty(Request.Params["projectid"]))
Response.Redirect(PortalApplication.computeDocumentUrl(PortalCommands.Orion2NotFound));
ViewState["projectID"] = Request.Params["projectid"];
ViewState["locationID"] = Request.Params["locationid"];
ViewState["isAllowedBusinessPro"] = "0";
var projectRow = Proj.getProjectById(connection, transaction, Request.Params["projectid"]);
if (projectRow == null)
throw new Exception("Project niet gevonden.");
CheckPageRight(Convert.ToInt32(Proj.PartnerID) != PortalApplication.User.OrgId ? OrionUserRights.ProjectenPartner : OrionUserRights.Projecten);
if (string.IsNullOrEmpty(Request.Params["locationid"]))
{
var locations = ProvisioningLocation.getLocationsByProjectId(connection, transaction, Request.Params["projectid"]);
if (locations == null || locations.Rows.Count == 0)
throw new Exception("Geen locaties voor het project gevonden.");
HttpContext.Current.Response.Redirect(PortalApplication.computeDocumentUrl(PortalCommands.Orion2Project) + "?projectid=" + Request.Params["projectid"] + "&locationid=" + locations.Rows[0][0]);
}
ProjectHosted = ProjectHostedDal.GetProjectHostedByProjectId(connection, transaction, int.Parse(Proj.ID));
Organisation = OrganisationDal.GetOrganisationById(connection, transaction, int.Parse(Proj.PartnerID));
if (!IsPostBack)
{
FillProductTypeRadioButtonList(connection, transaction);
trStatus.Visible = PortalApplication.User.IsOneXSMedewerker;
var partner = ProvisioningOrganisation.getOrganisationById(connection, transaction, projectRow["PartnerID"]);
if (partner.OrgType != OrgTypeEnum.Reseller.ToString())
{
pnlHardwareOptions1.Visible = false;
pnlHardwareOptions2.Visible = false;
trReadIsCPSProject.Visible = false;
}
Looptijd.Items.Add("12");
Looptijd.Items.Add("24");
Looptijd.Items.Add("36");
Looptijd.Items.Add("48");
Looptijd.Items.Add("60");
trReadLooptijd.Visible = !Proj.isCPSProject;
trEditLooptijd.Visible = !Proj.isCPSProject;
pnlHardwareOptions1.Visible = !Proj.isCPSProject;
pnlHardwareOptions2.Visible = !Proj.isCPSProject;
trEditHasVPN.Visible = !Proj.isCPSProject && Proj.hasVPN;
// set Partner contacts
contactPersoonPartner.Items.Clear();
contactPersoonPartner.Items.Add(new ListItem(KeyConstants.PLEASE_SELECT, string.Empty));
var orgContacts = ProvisioningContact.getProvisioningContactsByOrgId(connection, transaction, Proj.PartnerID);
if (orgContacts != null && orgContacts.Rows.Count > 0)
foreach (DataRow orgContact in orgContacts.Rows)
contactPersoonPartner.Items.Add(new ListItem(orgContact["Voornaam"] + " " + orgContact["Tussenvoegsel"] + "" + orgContact["Achternaam"], orgContact["ID"].ToString()));
// set Customer contacts
contactPersoonCustomer.Items.Clear();
contactPersoonCustomer.Items.Add(new ListItem(KeyConstants.PLEASE_SELECT, string.Empty));
var custContacts = ProvisioningContact.getProvisioningContactsByOrgId(connection, transaction, Proj.OrgID);
if (custContacts != null)
foreach (DataRow custContact in custContacts.Rows)
contactPersoonCustomer.Items.Add(new ListItem(custContact["Voornaam"] + " " + custContact["Tussenvoegsel"] + "" + custContact["Achternaam"], custContact["ID"].ToString()));
PageHeader.InnerText = "P" + Proj.ProjectCode;
OneXSCode.Text = Proj.OneXSCode;
var loggedInUserOrgId = PortalApplication.User.OrgId.ToString();
if (!string.IsNullOrEmpty(loggedInUserOrgId))
{
var loggedInUserOrg = ProvisioningOrganisation.getOrganisationById(connection, transaction, loggedInUserOrgId);
if (!string.IsNullOrEmpty(loggedInUserOrg?.ID))
ViewState["isAllowedBusinessPro"] = loggedInUserOrg.isAllowedBusinessPro ? "1" : "0";
}
chkInstallationPartner.Enabled = ProjectLogic.IsOneProProductType(ProjectHosted) && Organisation.isAllowedCustomerInstallation;
ProjectCode.Text = $"P{Proj.ProjectCode}";
Looptijd.Text = Proj.Looptijd;
PartnerCode.Text = Proj.PartnerCode;
ResellerLevertHardware.Checked = Proj.ResellerLevertHardware == "1";
Status.Text = Proj.Status.ToString();
chkHasVPN.Checked = Proj.hasVPN;
chkInstallationPartner.Checked = Proj.InstallationByPartner;
chkHardwareServicePlus.Checked = Proj.HardwareServicePlus;
var projatt = new ProjectAttachment();
AttachmentsGridView1.DataSource = projatt.getProjectAttachmentsByProjectId(connection, transaction, ViewState["projectID"]);
AttachmentsGridView1.DataBind();
var canEditProject = CanEditProject(Proj.Status, Convert.ToInt32(Proj.PartnerID));
EditButton.Visible = canEditProject;
SaveButton.Visible = canEditProject;
if (!string.IsNullOrEmpty(Request.Params["attachmentid"]) && _files.Count == 0)
{
projatt.getProjectAttachmentById(connection, transaction, Request.Params["attachmentid"]);
if (File.Exists(Server.MapPath("../upload/" + partner.UDF + "/" + projatt.FileName)))
{
Response.Buffer = true;
Response.Clear();
Response.TransmitFile(Server.MapPath("../upload/" + partner.UDF + "/" + projatt.FileName));
Response.AddHeader("Content-Disposition", "Attachment; filename=" + HttpUtility.UrlEncode(projatt.FileName));
Response.ContentType = "application/octet-stream";
Response.End();
}
else
throw new Exception("Bestandslocatie niet gevonden op server.");
}
}
if (_files.Count != 0)
{
UploadFiles(connection, transaction);
var projatt2 = new ProjectAttachment();
AttachmentsGridView1.DataSource = projatt2.getProjectAttachmentsByProjectId(connection, transaction, ViewState["projectID"]);
AttachmentsGridView1.DataBind();
}
if (Proj.Status != ProjectStatusEnum.CONCEPT && Proj.Status != ProjectStatusEnum.OFFERTE)
{
Looptijd.Enabled = false;
ResellerLevertHardware.Enabled = false;
chkInstallationPartner.Enabled = false;
HostedTelefonieStatusDropDownList.Enabled = false;
ProductTypeDropDownList.Enabled = false;
PartitionNameDropDownList.Enabled = false;
chkAllowPartnerAccess.Enabled = false;
}
if (ViewState["projectID"] != null && ViewState["locationID"] != null)
{
ProjectLeftMenu1.ProjectID = ViewState["projectID"].ToString();
ProjectLeftMenu1.ProjectLocationID = ViewState["locationID"].ToString();
contactPersoonPartner.Visible = false;
contactPersoonCustomer.Visible = false;
}
if (!IsPostBack)
{
SetUpHostedTypeControls(ProjectHosted, true);
FillHostedTypeControls(connection, transaction, ProjectHosted);
}
if (ProjectSipLogic.Validate(_log, connection, transaction, int.Parse(Proj.ID)) != 0)
{
WarningBox.Visible = true;
SIPOnbeperktMinimalDuration.Text = Resources.SipUnlimitedMinimalDuration;
}
//BI6545: BT CPS Verwijderen uit Orion. CPS Selectieveld verwijderd. Indien CPS in database op true staat, moet de rij met CPS getoond worden.
trReadIsCPSProject.Visible = Proj.isCPSProject;
}
transaction.Commmit(); // here i get the error it is represented in the exeption
}
}
catch (Exception ex)
{
Log.WriteException(ex);
}
}

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
ProjectAttachmentLogic.saveBinaryDataAttachment(connection, transaction, tempSaveDir, projAttID);
}
1. public class ProjectAttachmentLogic
2. {
4. public static void saveBinaryDataAttachment(SqlConnection connection, SqlTransaction transaction, string ProjattFilename, string ProjattId)
5. {
6. var id = int.Parse(ProjattId);
7. byte[] file;
8. using (var stream = new FileStream(ProjattFilename, FileMode.Open, FileAccess.Read))
9. {
10. using (var reader = new BinaryReader(stream))
11. {
12. file = reader.ReadBytes((int)stream.Length);
13. }
14. ProjectAttachmentDal.InsertBinaryDataAttachment(connection, transaction, id, file);
15. }
16. }
17. }
  public class ProjectAttachmentDal
  {
      public static void InsertBinaryDataAttachment(SqlConnection connection, SqlTransaction transaction, int id, byte[] file)
      {
          using (var sqlCommand = new SqlCommand("INSERT INTO proj.BinaryDataAttachments (ID, Data) Values(@Id, @Data)", connection))
          {
              sqlCommand.Transaction = transaction;
              sqlCommand.Parameters.Add("@ID", SqlDbType.Int, id).Value = id;
              sqlCommand.Parameters.Add("@Data", SqlDbType.VarBinary, file.Length).Value = file;
              sqlCommand.ExecuteNonQuery();
          }
      }
  }

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ranjan Dailata10y ago
Accepted answer
I see a major problem in your code with regards to the creation of Connection, Transaction object. Never ever create these things with the codebehind and pass in to multiple layers like Business Logic, Data Access etc. Also you never know some of the method calls like getProjectById would have already destroyed the Transaction objects. Try to isolate the Connection, Command and Transaction objects within your Data Access Layer. What is the purpose of layered architecture if you don't really follow one.
Cassie Mod10y ago
thnx that helped a lot.
I was able to solve the problem with some help of my boss.
The first problem was that there was a redirect so that was why the transaction was olrady used and closed.
The second problem started because a coleque replaced the null with transaction in the beginning. By replacing it back to null and capsulate the transaction only on upload files it wend well.

so thanks