protected void Page_Load(object sender, EventArgs e) { if (Session["loggedIn"] == null) { //uncomment me when login functionality is working Response.Redirect("Login.aspx"); } //Create table tblSavedProjects = new Table(); tblSavedProjects.ID = "tblSavedProjects"; tblSavedProjects.CssClass = "tblSavedProjects sortable"; //Query DB to find row information //sdsProjectList.SelectCommand = "SELECT project_name, revised_date, project_id, project_type FROM projects WHERE user_id = '" + Session["user_id"] + "'"; sdsProjectList.SelectCommand = "SELECT project_id, project_name, project_type, revised_date FROM projects WHERE user_id = '" + Session["user_id"] + "'"; DataView dvProjectList = (DataView)sdsProjectList.Select(System.Web.UI.DataSourceSelectArguments.Empty); TableHeaderRow aTableRow = new TableHeaderRow(); aTableRow.TableSection = TableRowSection.TableHeader; TableHeaderCell aTableCell = new TableHeaderCell(); aTableCell.CssClass = "thSortable"; aTableCell.Controls.Add(new LiteralControl("Project Name")); aTableRow.Controls.Add(aTableCell); TableHeaderCell aTableCell2 = new TableHeaderCell(); aTableCell2.CssClass = "thSortable"; aTableCell2.Controls.Add(new LiteralControl("Last Modified")); aTableRow.Controls.Add(aTableCell2); TableHeaderCell aTableCellA = new TableHeaderCell(); aTableCellA.CssClass = "thSortable"; aTableCellA.Controls.Add(new LiteralControl(" ")); aTableRow.Controls.Add(aTableCellA); TableHeaderCell aTableCell3 = new TableHeaderCell(); aTableCell3.CssClass = "sorttable_nosort"; aTableCell3.Controls.Add(new LiteralControl("Add to Estimate")); aTableRow.Controls.Add(aTableCell3); tblSavedProjects.Controls.Add(aTableRow); // Initialize array projectArray = new Project[dvProjectList.Count]; for (int i = 0; i < dvProjectList.Count; i++) { projectArray[i] = new Project(); // Fill in project class projectArray[i].ProjectId = (int)dvProjectList[i][0]; projectArray[i].ProjectName = (string)dvProjectList[i][1]; projectArray[i].ProjectType = (string)dvProjectList[i][2]; projectArray[i].RevisedDate = (DateTime)dvProjectList[i][3]; TableRow projectsTableRow = new TableRow(); TableCell projectsNameCell = new TableCell(); Button projectName = new Button(); projectName.Text = projectArray[i].ProjectName; projectName.ID = "lblProjectName" + i; projectName.OnClientClick = "return false;"; //projectName.Attributes["onclientclick"] = "return false;"; //projectName.Click += btnProject_Click; // Add the event handler onto the button // Adds a jquery event handler onto the asp page. ClickEvents += "$(\"#MainContent_lblProjectName" + i + "\").click(function() { ProjectName_Click(\"" + projectArray[i].ProjectId.ToString() + "\",\"" + projectArray[i].ProjectType.ToString() + "\"); });\n\t\t"; // Hidden label for ID Label projectID = new Label(); projectID.ID = "lblProjectID" + i; projectID.Text = projectArray[i].ProjectId.ToString(); projectID.Visible = false; // // Hidden type for ID Label projectType = new Label(); projectType.ID = "lblProjectType" + i; projectType.Text = projectArray[i].ProjectType.ToString(); projectType.Visible = false; // projectsNameCell.Controls.Add(projectName); projectsNameCell.Controls.Add(projectID); projectsNameCell.Controls.Add(projectType); projectsTableRow.Controls.Add(projectsNameCell); TableCell projectsDateCell = new TableCell(); Label projectDate = new Label(); projectDate.ID = "lblProjectDate" + i; projectDate.Text = projectArray[i].RevisedDate.ToString(); projectsDateCell.Controls.Add(projectDate); projectsTableRow.Controls.Add(projectsDateCell); TableCell projectsDeleteCell = new TableCell(); HyperLink lnkDelete = new HyperLink(); lnkDelete.ID = "lnkDelete" + i; lnkDelete.CssClass = "btnDelete"; lnkDelete.Text = "Delete"; projectsDeleteCell.Controls.Add(lnkDelete); projectsTableRow.Controls.Add(projectsDeleteCell); TableCell projectsEstimateCell = new TableCell(); CheckBox projectsEstimateCheck = new CheckBox(); projectsEstimateCheck.ID = "chkAddToEstimate" + i; projectsEstimateCell.Controls.Add(projectsEstimateCheck); Label projectEstimate = new Label(); projectEstimate.AssociatedControlID = "chkAddToEstimate" + i; //projectEstimate.Text = dvProjectList[i][0].ToString(); projectsEstimateCell.Controls.Add(projectEstimate); projectsTableRow.Controls.Add(projectsEstimateCell); tblSavedProjects.Controls.Add(projectsTableRow); //phProjectList.Controls.Add(new LiteralControl("<br/>")); // Store project id (we'll go through it later on add to estimate click) //projectIdsArray[i] = (int)dvProjectList[i][2]; // Store project type (We need this for the overlay on project name button click) //projectTypeArray[i] = dvProjectList[i][3].ToString(); } //Finally add table to project placeholder phProjectList.Controls.Add(tblSavedProjects); // <asp:Table ID="tblSavedProjects" class="tblSavedProjects sortable" runat="server"> // <asp:TableHeaderRow TableSection="TableHeader"> // <asp:TableHeaderCell CssClass="thSortable">Project Name</asp:TableHeaderCell> // <asp:TableHeaderCell CssClass="thSortable">Last Modified</asp:TableHeaderCell> // <asp:TableHeaderCell CssClass="sorttable_nosort"> </asp:TableHeaderCell> // <asp:TableHeaderCell CssClass="sorttable_nosort">Add to Estimate</asp:TableHeaderCell> // </asp:TableHeaderRow> // <asp:TableRow> // <asp:TableCell> // <asp:Label ID="lblProjectName1" runat="server" Text="Project Name"></asp:Label> // </asp:TableCell> // <asp:TableCell sorttable_customkey="20130503"> // <asp:Label ID="Label1" runat="server" Text="May 3, 2013"></asp:Label> // </asp:TableCell> // <asp:TableCell> // <asp:HyperLink ID="lnkDelete" CssClass="btnDelete" runat="server">Delete</asp:HyperLink> // </asp:TableCell> // <asp:TableCell> // <asp:CheckBox ID="chkAddToEstimate1" runat="server" /> // <asp:Label ID="lblAddToCartCheckbox" AssociatedControlID="chkAddToEstimate1" runat="server"></asp:Label> // </asp:TableCell> // </asp:TableRow> // <asp:TableRow> // <asp:TableCell> // <asp:Label ID="Label2" runat="server" Text="Project Name 2"></asp:Label> // </asp:TableCell> // <asp:TableCell sorttable_customkey="20130531"> // <asp:Label ID="Label3" runat="server" Text="May 31, 2013"></asp:Label> // </asp:TableCell> // <asp:TableCell> // <asp:HyperLink ID="HyperLink1" CssClass="btnDelete" runat="server">Delete</asp:HyperLink> // </asp:TableCell> // <asp:TableCell> // <asp:CheckBox ID="chkAddToEstimate2" runat="server" /> // <asp:Label ID="Label4" AssociatedControlID="chkAddToEstimate2" runat="server"></asp:Label> // </asp:TableCell> // </asp:TableRow> //</asp:Table> }
public static bool DuplicateProject(string projectID, string projectNewName) { SqlDataSource dataSource = new SqlDataSource(); // Super bad? Copy pasta from web.config dataSource.ConnectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\sunspace_db.mdf;Integrated Security=True;Connect Timeout=30"; Project aProject = new Project(); System.Data.DataView selectProject = new System.Data.DataView(); string sqlSelect; string sqlInsert; List<string> tableNames = new List<string>(); int newProjectID; // New project ID for all the compenents of a project int tableCount; // Number of tables // Select the project sqlSelect = "SELECT project_type," + "installation_type," + "customer_id," + "user_id," + "date_created," + "status," + "revised_date," + "revised_user_id," + "msrp," + "project_notes," + "hidden," + "cut_pitch" + " FROM Projects" + " WHERE project_ID = " + projectID + ";"; dataSource.SelectCommand = sqlSelect; selectProject = (System.Data.DataView)dataSource.Select(System.Web.UI.DataSourceSelectArguments.Empty); Debug.WriteLine(selectProject[0].Row[0]); // Insert the new project with the duplicated data! sqlInsert = "INSERT INTO Projects(project_type, " + "installation_type, " + "customer_id, " + "user_id, " + "date_created, " + "status, " + "revised_date, " + "revised_user_id, " + "msrp, " + "project_notes, " + "hidden, " + "cut_pitch, " + "project_name " + ") VALUES ( '" + selectProject[0].Row[0] + "'," + "'" + selectProject[0].Row[1] + "'," + "'" + selectProject[0].Row[2] + "'," + "'" + selectProject[0].Row[3] + "'," + "'" + selectProject[0].Row[4] + "'," + "'" + selectProject[0].Row[5] + "'," + "'" + selectProject[0].Row[6] + "'," + "'" + selectProject[0].Row[7] + "'," + "'" + selectProject[0].Row[8] + "'," + "'" + selectProject[0].Row[9] + "'," + "'" + selectProject[0].Row[10] + "'," + "'" + selectProject[0].Row[11] + "'," + "'" + projectNewName + "' ); "; //+ //" WHERE project_ID = '" + projectID + "';"; dataSource.InsertCommand = sqlInsert; dataSource.Insert(); // Grab project id from the last insert! sqlSelect = "SELECT project_ID from Projects WHERE project_ID = IDENT_CURRENT('Projects');"; dataSource.SelectCommand = sqlSelect; selectProject = (System.Data.DataView)dataSource.Select(System.Web.UI.DataSourceSelectArguments.Empty); // Set new project id newProjectID = (int)selectProject[0].Row[0]; Debug.WriteLine(selectProject[0].Row[0]); // Get table count sqlSelect = "Select Count(*) From INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'project_id' AND TABLE_NAME <> 'projects'"; dataSource.SelectCommand = sqlSelect; selectProject = (System.Data.DataView)dataSource.Select(System.Web.UI.DataSourceSelectArguments.Empty); // Set table count tableCount = (int)selectProject[0].Row[0]; Debug.WriteLine(selectProject[0].Row[0]); // Essentially this gets a compenent table info and re-inserts it with the new project ID for (int index = 0; index < tableCount; index++) { // Get table names sqlSelect = "SELECT TABLE_NAME From INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'project_id' AND TABLE_NAME <> 'projects' ORDER BY TABLE_NAME ASC OFFSET "+ index +" ROWS FETCH NEXT 1 ROWS ONLY"; dataSource.SelectCommand = sqlSelect; selectProject = (System.Data.DataView)dataSource.Select(System.Web.UI.DataSourceSelectArguments.Empty); tableNames.Add((string)selectProject[0].Row[0]); Debug.WriteLine("Table Name: " + selectProject[0].Row[0]); Debug.WriteLine("Table Count:" + tableNames.Count()); // Select rows with the old project id sqlSelect = "SELECT * FROM " + selectProject[0].Row[0] + " WHERE project_ID = " + projectID; dataSource.SelectCommand = sqlSelect; selectProject = (System.Data.DataView)dataSource.Select(System.Web.UI.DataSourceSelectArguments.Empty); // // If we actually need this table.. // if (selectProject.Count > 0) { Debug.WriteLine(selectProject[0].Row[0]); // Start the insert statement sqlInsert = "INSERT INTO " + tableNames[index] + " VALUES ("; // Inserts the values for (int index2 = 0; index2 < selectProject[0].Row.ItemArray.Count(); index2++) { Debug.WriteLine(selectProject[0].Row[index2]); if (index2 == 0) sqlInsert += "'" + newProjectID + "', "; else if (index2 != (selectProject[0].Row.ItemArray.Count() - 1)) sqlInsert += "'" + selectProject[0].Row[index2] + "', "; else sqlInsert += "'" + selectProject[0].Row[index2] + "'"; } // Close the insert sqlInsert += ");"; Debug.WriteLine(sqlInsert); // Actually insert the data dataSource.InsertCommand = sqlInsert; dataSource.Insert(); } } return true; }