public string AddOrUpdateProject(ProjectManagement.Web.Models.Project project) { string connectionString = ConfigurationManager.ConnectionStrings["MBProjectConnectionString"].ConnectionString; string message = null; using (SqlConnection conn = new SqlConnection(connectionString)) { try { var cmd = new SqlCommand { Connection = conn }; conn.Open(); // Addresses var updateQuery = "UPDATE Address " + "SET AddressLine1 = @AddressLine1, AddressLine2 = @AddressLine2, CompanyName = @CompanyName, " + "County = @County, Postcode = @Postcode, TownOrCity = @TownOrCity " + "WHERE Id = @AddressId"; var insertQuery = "INSERT INTO Address (AddressLine1, AddressLine2, CompanyName, County, Postcode, TownOrCity) " + "VALUES (@AddressLine1, @AddressLine2, @CompanyName, @County, @Postcode, @TownOrCity);" + "SELECT SCOPE_IDENTITY();"; // Add/update client address var clientAddressId = project.ClientAddress.Id; if (project.ClientAddress.IsValid) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@AddressLine1", project.ClientAddress.AddressLine1 ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@AddressLine2", project.ClientAddress.AddressLine2 ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@CompanyName", project.ClientAddress.CompanyName ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@County", project.ClientAddress.County ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@Postcode", project.ClientAddress.Postcode ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@TownOrCity", project.ClientAddress.TownOrCity ?? (object)DBNull.Value); if (project.ClientAddress.Id != null) { cmd.Parameters.AddWithValue("@AddressId", project.ClientAddress.Id); cmd.CommandText = updateQuery; cmd.ExecuteNonQuery(); } else { cmd.CommandText = insertQuery; var response = cmd.ExecuteScalar(); clientAddressId = Convert.ToInt32(response); } } // Add/update invoice address var invoiceAddressId = project.InvoiceAddress.Id; if (project.InvoiceAddress.IsValid) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@AddressLine1", project.InvoiceAddress.AddressLine1 ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@AddressLine2", project.InvoiceAddress.AddressLine2 ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@CompanyName", project.InvoiceAddress.CompanyName ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@County", project.InvoiceAddress.County ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@Postcode", project.InvoiceAddress.Postcode ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@TownOrCity", project.InvoiceAddress.TownOrCity ?? (object)DBNull.Value); if (project.InvoiceAddress.Id != null) { cmd.Parameters.AddWithValue("@AddressId", project.InvoiceAddress.Id); cmd.CommandText = updateQuery; cmd.ExecuteNonQuery(); } else { cmd.CommandText = insertQuery; var response = cmd.ExecuteScalar(); invoiceAddressId = Convert.ToInt32(response); } } // Project updateQuery = "UPDATE Project " + "SET [Project Code] = @projectcode, [Project Name] = @projectname, StartDate = @startdate, EndDate = @enddate, Contact = @Contact, Description = @description, Detailed = @detailed, " + "StatusID = @StatusID, ProjectManager = @ProjectManager, DepartmentID = @DepartmentID, CountyId = @CountyId, PlanningAuthorityId = @PlanningAuthorityId, " + "ClientAddressId = @ClientAddressId, InvoiceAddressId = @InvoiceAddressId, Introducer = @Introducer, InvoiceContact = @InvoiceContact, ProjectCity = @ProjectCity, ClientTypeId = @ClientTypeId " + "WHERE(Project_ID = @project_id)"; insertQuery = "INSERT INTO Project " + "([Project Code], [Project Name], StartDate, EndDate, Lat, Lon, Contact, Description, Detailed, StatusID, ProjectManager, DepartmentID, CountyId, PlanningAuthorityId, ClientAddressId, InvoiceAddressId, Introducer, InvoiceContact, ProjectCity, ClientTypeId) " + "VALUES (@projectcode, @projectname, @startdate, @enddate, @Latitude, @Longitude, @Contact, @description, @detailed, @StatusID, @ProjectManager, @DepartmentID, @CountyId, @PlanningAuthorityId, @ClientAddressId, @InvoiceAddressId, @Introducer, @InvoiceContact, @ProjectCity, @ClientTypeId);" + "SELECT SCOPE_IDENTITY();"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@projectcode", project.Code); cmd.Parameters.AddWithValue("@projectname", project.Name); cmd.Parameters.AddWithValue("@startdate", project.StartDate ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@enddate", project.EndDate ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@Contact", project.Contact); cmd.Parameters.AddWithValue("@description", project.Description); cmd.Parameters.AddWithValue("@detailed", project.Detailed); cmd.Parameters.AddWithValue("@StatusID", project.Status); cmd.Parameters.AddWithValue("@ProjectManager", project.ProjectManager); cmd.Parameters.AddWithValue("@DepartmentID", project.Department); cmd.Parameters.AddWithValue("@CountyId", project.CountyId ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@PlanningAuthorityId", project.PlanningAuthorityId ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@ClientAddressId", clientAddressId ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@InvoiceAddressId", invoiceAddressId ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@Introducer", project.Introducer); cmd.Parameters.AddWithValue("@InvoiceContact", project.InvoiceContact); cmd.Parameters.AddWithValue("@ProjectCity", string.IsNullOrEmpty(project.ProjectCity) ? (object)DBNull.Value : project.ProjectCity); cmd.Parameters.AddWithValue("@ClientTypeId", project.ClientTypeId); var projectId = project.Id; if (projectId != null) { cmd.Parameters.AddWithValue("@project_id", project.Id); cmd.CommandText = updateQuery; cmd.ExecuteNonQuery(); } else { cmd.Parameters.AddWithValue("@Latitude", project.Latitude); cmd.Parameters.AddWithValue("@Longitude", project.Longitude); cmd.CommandText = insertQuery; var response = cmd.ExecuteScalar(); projectId = Convert.ToInt32(response); } // Delete current sectors associated with the project cmd.CommandText = "DELETE FROM ProjectSector WHERE Project_ID = @project_id"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@project_id", projectId); cmd.ExecuteNonQuery(); // Add new sectors foreach (var sectorId in project.Sectors) { cmd.CommandText = "INSERT INTO ProjectSector (Project_ID, Sector_ID) VALUES (@project_id, @sector_id)"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@project_id", projectId); cmd.Parameters.AddWithValue("@sector_id", Convert.ToInt32(sectorId)); cmd.ExecuteNonQuery(); } } catch (Exception ex) { message = ex.Message; Console.WriteLine(ex.Message); } finally { if (conn != null && conn.State == ConnectionState.Open) { conn.Close(); } } } return(message); }
private ProjectManagement.Web.Models.Project PopulateProject() { Label LblId = (Label)DetailsView2.FindControl("LBLProjectID"); TextBox TxtProjectname = (TextBox)DetailsView2.FindControl("TxtPorjectname"); DropDownList DDLstatus = (DropDownList)DetailsView2.FindControl("DDlstatus"); DropDownList DDLDepartment = (DropDownList)DetailsView2.FindControl("DDLDepartment"); TextBox TxtContact = (TextBox)DetailsView2.FindControl("TxtContact"); CheckBox ChkDetailed = (CheckBox)DetailsView2.FindControl("ChkDetailed"); TextBox TxtDescription = (TextBox)DetailsView2.FindControl("TxtDescription"); TextBox TxtProjectManager = (TextBox)DetailsView2.FindControl("TxtManager"); DropDownList DDLCounty = (DropDownList)DetailsView2.FindControl("DDLCounty"); DropDownList DDLClientType = (DropDownList)DetailsView2.FindControl("DDLClientType"); TextBox TxtPlanningAuthority = (TextBox)DetailsView2.FindControl("txtPlanningAuthority"); Label LblLat = (Label)DetailsView2.FindControl("LblLat"); Label LblLng = (Label)DetailsView2.FindControl("LblLng"); // Client address var clientAddressId = GetLabelFieldValue("LblClientAddressId"); var clientAddress = new ProjectManagement.Web.Models.Address { AddressLine1 = GetAddressField("TxtClientAddressLine1"), AddressLine2 = GetAddressField("TxtClientAddressLine2"), CompanyName = GetAddressField("TxtClientCompanyName"), County = GetAddressField("TxtClientCounty"), Id = !string.IsNullOrWhiteSpace(clientAddressId) ? int.Parse(clientAddressId) : (int?)null, Postcode = GetAddressField("TxtClientPostcode"), TownOrCity = GetAddressField("TxtClientTownOrCity") }; // Invoice address var invoiceAddressId = GetLabelFieldValue("LblInvoiceAddressId"); var invoiceAddress = new ProjectManagement.Web.Models.Address { AddressLine1 = GetAddressField("TxtInvoiceAddressLine1"), AddressLine2 = GetAddressField("TxtInvoiceAddressLine2"), CompanyName = GetAddressField("TxtInvoiceCompanyName"), County = GetAddressField("TxtInvoiceCounty"), Id = !string.IsNullOrWhiteSpace(invoiceAddressId) ? int.Parse(invoiceAddressId) : (int?)null, Postcode = GetAddressField("TxtInvoicePostcode"), TownOrCity = GetAddressField("TxtInvoiceTownOrCity") }; // Project var project = new ProjectManagement.Web.Models.Project { ClientAddress = clientAddress, // New ClientTypeId = int.Parse(DDLClientType.SelectedValue), Code = GetTextFieldValue("TxtProjectCode"), Contact = TxtContact.Text, CountyId = !string.IsNullOrWhiteSpace(DDLCounty.SelectedValue) ? int.Parse(DDLCounty.SelectedValue) : (int?)null, Department = int.Parse(DDLDepartment.SelectedValue), Description = TxtDescription.Text, Detailed = ChkDetailed.Checked, Id = !string.IsNullOrWhiteSpace(LblId.Text) ? int.Parse(LblId.Text) : (int?)null, Introducer = GetTextFieldValue("TxtIntroducer"), // New InvoiceAddress = invoiceAddress, // New InvoiceContact = GetTextFieldValue("TxtInvoiceContact"), // New Latitude = !string.IsNullOrWhiteSpace(LblLat.Text) ? double.Parse(LblLat.Text) : (double?)null, Longitude = !string.IsNullOrWhiteSpace(LblLng.Text) ? double.Parse(LblLng.Text) : (double?)null, Name = TxtProjectname.Text, PlanningAuthorityId = !string.IsNullOrWhiteSpace(TxtPlanningAuthority.Text) ? int.Parse(TxtPlanningAuthority.Text) : (int?)null, ProjectCity = GetTextFieldValue("TxtProjectCity"), ProjectManager = TxtProjectManager.Text, Status = int.Parse(DDLstatus.SelectedValue), }; TextBox TxtStartDate = (TextBox)DetailsView2.FindControl("TxtStartdate"); TextBox TxtEndDate = (TextBox)DetailsView2.FindControl("TxtEndDate"); if (!string.IsNullOrEmpty(TxtStartDate.Text)) { string[] date = TxtStartDate.Text.Split('/'); project.StartDate = new DateTime(int.Parse(date[2]), int.Parse(date[1]), int.Parse(date[0])); } if (!string.IsNullOrEmpty(TxtEndDate.Text)) { string[] date = TxtEndDate.Text.Split('/'); project.EndDate = new DateTime(int.Parse(date[2]), int.Parse(date[1]), int.Parse(date[0])); } ListBox DDLSector = (ListBox)DetailsView2.FindControl("DDLSector"); project.Sectors = DDLSector .GetSelectedIndices() .Select(index => Convert.ToInt32(DDLSector.Items[index].Value)) .ToList(); return(project); }