public static void DumpHgbstLists(params string[] listNames)
{
var sqlHelper = new SqlHelper();
DataSet listDs;
if (listNames == null || listNames.Length == 0 || (listNames.Length == 1 && listNames[0].Trim().Length == 0))
{
sqlHelper.CommandText = "SELECT * FROM table_hgbst_lst";
listDs = sqlHelper.ExecuteDataSet();
}
else
{
var names = new object[listNames.Length];
string colName = null;
const string hgbstListSql = "SELECT * FROM table_hgbst_lst WHERE (title IN({0}))";
Array.Copy(listNames, 0, names, 0, listNames.Length);
sqlHelper.CommandText = String.Format(hgbstListSql, SqlHelper.IN_LIST_TOKEN);
listDs = sqlHelper.ExecuteDataSetInList(names, colName);
}
if (listDs == null || listDs.Tables.Count == 0 || listDs.Tables[0].Rows.Count == 0)
{
var namesCombined = (listNames == null) ? "" : String.Join("', '", listNames);
Console.WriteLine("No HGBST lists found matching the specified criteria ('{0}').", namesCombined);
return;
}
DumpHgbstLists(listDs);
}
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 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;
}
public static void DumpListForListObjid(int listObjid)
{
var sqlHelper = new SqlHelper("SELECT * FROM table_hgbst_lst WHERE objid = {0}");
sqlHelper.Parameters.Add("listobjid", listObjid);
var listSet = sqlHelper.ExecuteDataSet();
if (listSet == null || listSet.Tables.Count == 0 || listSet.Tables[0].Rows.Count == 0)
{
Console.WriteLine("No HGBST List found with objid '{0}'.", listObjid);
return;
}
DumpHgbstLists(sqlHelper.ExecuteDataSet());
}