private static void DuplicateChildHierarchy(UpdateQueryBatch batch, int curElmObjid, int oldChildShowObjid, int newChildShowObjid, int objidBase, int hgbstElmTableID, int hgbstShowTableID) { DuplicateElementsForShow(batch, curElmObjid, oldChildShowObjid, newChildShowObjid, objidBase, hgbstElmTableID, hgbstShowTableID); }
private static void DuplicateElementsForShow(UpdateQueryBatch batch, int parentElmObjid, int showObjid, int newShowObjid, int objidBase, int hgbstElmTableID, int hgbstShowTableID) { log.LogDebug("Duplicating elements for show. Parent Element: {0}, Show: {1}, New Parent Show: {2}", parentElmObjid, showObjid, newShowObjid); var sqlHelper = new SqlHelper(batch.Provider) { Transaction = batch.Transaction, CommandText = @" SELECT e.* FROM table_hgbst_elm e, mtm_hgbst_elm0_hgbst_show1 mtm WHERE e.objid = mtm.hgbst_elm2hgbst_show AND mtm.hgbst_show2hgbst_elm = {0} AND mtm.hgbst_elm2hgbst_show != {1}" }; sqlHelper.Parameters.Add("showObjid", showObjid); sqlHelper.Parameters.Add("parentElmObjid", parentElmObjid); var elmSet = sqlHelper.ExecuteDataSet(); foreach (DataRow elmRow in elmSet.Tables[0].Rows) { var oldElmObjid = Convert.ToInt32(elmRow["objid"]); log.LogDebug("Duplicating child element {0}", oldElmObjid); var newElmObjid = DuplicateElement(batch, Convert.ToInt32(oldElmObjid), newShowObjid, objidBase, hgbstElmTableID); // Look for sub-shows/levels and dupe those too sqlHelper = new SqlHelper(batch.Provider) { Transaction = batch.Transaction, CommandText = @" SELECT * FROM mtm_hgbst_elm0_hgbst_show1 WHERE hgbst_elm2hgbst_show = {0} AND hgbst_show2hgbst_elm != {1}" }; sqlHelper.Parameters.Add("elmObjid", oldElmObjid); sqlHelper.Parameters.Add("showObjid", showObjid); var showSet = sqlHelper.ExecuteDataSet(); if (showSet == null || showSet.Tables.Count <= 0 || showSet.Tables[0].Rows.Count <= 0) continue; var showObjidToDupe = Convert.ToInt32(showSet.Tables[0].Rows[0]["hgbst_show2hgbst_elm"]); log.LogDebug("Duplicating child show {0}", showObjidToDupe); var newSubChildShowObjid = DuplicateShow(batch, showObjidToDupe, newElmObjid, newShowObjid, objidBase, hgbstShowTableID); log.LogDebug("New dupe child show create ({0}). Duplicating elements for original show ({1})", newSubChildShowObjid, showObjidToDupe); DuplicateElementsForShow(batch, oldElmObjid, showObjidToDupe, newSubChildShowObjid, objidBase, hgbstElmTableID, hgbstShowTableID); } }
private static int GetNextObjid(UpdateQueryBatch batch, int tableID, int objidBase) { var sqlHelper = new SqlHelper(batch.Provider, CommandType.StoredProcedure, "fc_new_oid") {Transaction = batch.Transaction}; sqlHelper.Parameters.Add("type_num", ConvertType(batch.Provider, tableID)); sqlHelper.Parameters.Add("num_ids", ConvertType(batch.Provider, 1)); sqlHelper.Parameters.Add("out_num", ConvertType(batch.Provider, 0)); sqlHelper.Parameters["out_num"].Direction = ParameterDirection.Output; sqlHelper.ExecuteNonQuery(); return objidBase + Convert.ToInt32(sqlHelper.Parameters["out_num"].Value); }
private static int DuplicateShow(UpdateQueryBatch batch, int curShowObjid, int parentElmObjid, int parentShowObjid, int objidBase, int hgbstShowTableID) { var sqlHelper = new SqlHelper(batch.Provider) { Transaction = batch.Transaction, CommandText = @"SELECT * FROM table_hgbst_show WHERE objid = {0}" }; sqlHelper.Parameters.Add("curShowObjid", curShowObjid); var newObjid = GetNextObjid(batch, hgbstShowTableID, objidBase); var showSet = sqlHelper.ExecuteDataSet(); if (showSet == null || showSet.Tables.Count != 1 || showSet.Tables[0].Rows.Count != 1) { throw new ApplicationException("Show with objid " + curShowObjid + " not found, or more than one row with that objid exists"); } var showRow = showSet.Tables[0].Rows[0]; var cmdParams = new DataParameterCollection { {batch.GetUniqueParamName("objid"), newObjid}, {batch.GetUniqueParamName("title"), showRow["title"]}, {batch.GetUniqueParamName("def_val"), showRow["def_val"]}, {batch.GetUniqueParamName("chld_prnt2hgbst_show"), parentShowObjid} }; batch.AddStatement(String.Format( @" INSERT INTO table_hgbst_show (objid, last_mod_time, title, def_val, dev, chld_prnt2hgbst_show) VALUES ({{0}}, {0}, {{1}}, {{2}}, NULL, {{3}})", batch.Provider.GetDateStatement()), cmdParams); // Now relate it to the new parent cmdParams = new DataParameterCollection { {batch.GetUniqueParamName("parentElmObjid"), parentElmObjid}, {batch.GetUniqueParamName("newShowObjid"), newObjid} }; batch.AddStatement(@"INSERT INTO mtm_hgbst_elm0_hgbst_show1 (hgbst_elm2hgbst_show, hgbst_show2hgbst_elm) VALUES ({0}, {1})", cmdParams); return newObjid; }
private static int DuplicateElement(UpdateQueryBatch batch, int curElmObjid, int newParentShowObjid, int objidBase, int hgbstElmTableID) { var sqlHelper = new SqlHelper(batch.Provider) { Transaction = batch.Transaction, CommandText = @"SELECT * FROM table_hgbst_elm WHERE objid = {0}" }; sqlHelper.Parameters.Add("curElmObjid", curElmObjid); var newObjid = GetNextObjid(batch, hgbstElmTableID, objidBase); var elmSet = sqlHelper.ExecuteDataSet(); if (elmSet == null || elmSet.Tables.Count != 1 || elmSet.Tables[0].Rows.Count != 1) { throw new ApplicationException("Element with objid " + curElmObjid + " not found, or more than one row with that objid exists"); } var elementRow = elmSet.Tables[0].Rows[0]; var commandParameters = new DataParameterCollection { {batch.GetUniqueParamName("objid"), newObjid}, {batch.GetUniqueParamName("title"), elementRow["title"]}, {batch.GetUniqueParamName("rank"), elementRow["rank"]}, {batch.GetUniqueParamName("state"), elementRow["state"]}, {batch.GetUniqueParamName("intval1"), elementRow["intval1"]} }; batch.AddStatement(@"INSERT INTO table_hgbst_elm (objid, title, rank, state, dev, intval1) VALUES ({0}, {1}, {2}, {3}, NULL, {4})", commandParameters); // Now relate it to the new parent commandParameters = new DataParameterCollection { {batch.GetUniqueParamName("newObjid"), newObjid}, {batch.GetUniqueParamName("newParentShowObjid"), newParentShowObjid} }; batch.AddStatement( @"INSERT INTO mtm_hgbst_elm0_hgbst_show1 (hgbst_elm2hgbst_show, hgbst_show2hgbst_elm) VALUES ({0}, {1})", commandParameters); return newObjid; }
private static void CopyAndReparentElement(UpdateQueryBatch batch, int elmObjid, int childShowObjid, int newParentShowObjid) { log.LogDebug("CopyAndReparentElement called. Elmenet: {0}, Child Show: {1}, New Parent Show: {2}", elmObjid, childShowObjid, newParentShowObjid); // Get table ID's for hgbst_elm and hgbst_show var sqlHelper = new SqlHelper(batch.Provider) { Transaction = batch.Transaction, CommandText = @"SELECT type_id, type_name FROM adp_tbl_name_map WHERE type_name = 'hgbst_elm' OR type_name = 'hgbst_show'" }; var hgbstElmTableID = 0; var hgbstShowTableID = 0; using (var dataReader = sqlHelper.ExecuteReader()) { while (dataReader.Read()) { if (String.Compare(Convert.ToString(dataReader["type_name"]), "hgbst_elm", true) == 0) { hgbstElmTableID = Convert.ToInt32(dataReader["type_id"]); } else if (String.Compare(Convert.ToString(dataReader["type_name"]), "hgbst_show", true) == 0) { hgbstShowTableID = Convert.ToInt32(dataReader["type_id"]); } } } sqlHelper = new SqlHelper(batch.Provider) { Transaction = batch.Transaction, CommandText = "SELECT site_id FROM adp_db_header" }; var siteId = Convert.ToInt32(sqlHelper.ExecuteScalar()); var objidBase = siteId*Convert.ToInt32(Math.Pow(2, 28)); //this.objidBase = 0; // Duplicate the element var dupeElmObjid = DuplicateElement(batch, elmObjid, newParentShowObjid, objidBase, hgbstElmTableID); log.LogDebug("Duplicated element. New Objid: {0}", dupeElmObjid); // Duplicate the child show and link it to the new element var dupeChildShowObjid = DuplicateShow(batch, childShowObjid, dupeElmObjid, newParentShowObjid, objidBase, hgbstShowTableID); log.LogDebug("Duplicated child show. New Objid: {0}", dupeChildShowObjid); // Get the whole child hierarchy for this element starting from its childShowObjid and duplicate it log.LogDebug("Duplicating child hierarchy..."); DuplicateChildHierarchy(batch, elmObjid, childShowObjid, dupeChildShowObjid, objidBase, hgbstElmTableID, hgbstShowTableID); // Unlink the old elm from the new parent show (if it was linked - a la the Best Buy double-linked problem) log.LogDebug("Unlinking original element (if necessary) from new parent to complete the separation"); var commandParameters = new DataParameterCollection {{"elmObjid", elmObjid}, {"newParent", newParentShowObjid}}; batch.AddStatement(@"DELETE FROM mtm_hgbst_elm0_hgbst_show1 WHERE hgbst_elm2hgbst_show = {0} and hgbst_show2hgbst_elm = {1}", commandParameters); }
public static void CopyAndReparentElement(int elmObjid, int childShowObjid, int newParentShowObjid) { var prov = DbProviderFactory.Provider; using (var connection = prov.GetConnection()) { connection.Open(); using (var transaction = connection.BeginTransaction()) { var batch = new UpdateQueryBatch(transaction, prov); CopyAndReparentElement(batch, elmObjid, childShowObjid, newParentShowObjid); var ret = batch.Execute(); log.LogDebug("Execute query batch. Result: {0}", ret); transaction.Commit(); } } Console.WriteLine("Copy and Reparent element successful"); }