private Hashtable GetSqlModules(DbReplacePostModel model)
{
Hashtable htModules = new Hashtable();
var connstr = GetConnectionString(model);
SqlConnection con = new SqlConnection(connstr);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = model.SqlSelect;
cmd.Connection = con;
try
{
con.Open();
var reader = cmd.ExecuteReader(); // (CommandBehavior.SingleRow)
while (reader.Read())
{
int portalid = CheckDbValueInteger(reader, "PortalId", PortalSettings.PortalId);
if (model.AllPortals == bool.TrueString || portalid == PortalSettings.PortalId)
{
// Check if the item was already added
string id = reader["ContentId"].ToString();
if (!htModules.ContainsKey(id))
{
var oMod = new ModuleContent
{
PortalId = CheckDbValueInteger(reader, "PortalId", PortalSettings.PortalId),
TabId = CheckDbValueInteger(reader, "TabId", -1),
ModuleId = CheckDbValueInteger(reader, "ModuleId", -1),
ContentSubId = CheckDbValueInteger(reader, "ContentSubId", -1),
ContentId = (int)reader["ContentId"],
Text = reader["ContentText"].ToString(),
Title = reader["ContentTitle"].ToString()
};
htModules.Add(id, oMod);
}
}
}
reader.Close();
}
finally
{
con.Close();
}
return(htModules);
}
private static string GetConnectionString(DbReplacePostModel model)
{
var connstr = "";
foreach (ConnectionStringSettings cs in ConfigurationManager.ConnectionStrings)
{
if (cs.Name == model.ConnectionString)
{
connstr = cs.ConnectionString;
break;
}
}
return(connstr);
}
public HttpResponseMessage DoGetScript(DbReplacePostModel model)
{
var retval = SqlModulesReplace(model, ModuleReplaceMode.GetScript);
return(Request.CreateResponse(HttpStatusCode.OK, retval));
}
private string SqlModulesReplace(DbReplacePostModel model, ModuleReplaceMode mode)
{
// Get all modules
Hashtable htModules = GetSqlModules(model);
// Output
StringBuilder sOut = new StringBuilder();
var rxList = new List <KeyValuePair <string, string> >();
foreach (var searchReplacePairModel in model.SearchReplace)
{
if (!string.IsNullOrEmpty(searchReplacePairModel.Search))
{
rxList.Add(new KeyValuePair <string, string>(searchReplacePairModel.Search, searchReplacePairModel.Replace));
}
}
int iModules = 0;
int iReplaced = 0;
foreach (DictionaryEntry Item in htModules)
{
// Count number of found modules
iModules += 1;
var oMod = (ModuleContent)Item.Value;
string sText = HttpUtility.HtmlDecode(oMod.Text);
RegexOptions rxOptions = RegexOptions.None;
if (model.CaseSensitive != bool.TrueString)
{
rxOptions = RegexOptions.IgnoreCase;
}
string sNewText = RegexReplace(sText, mode, rxList, rxOptions);
if (sNewText != sText)
{
string sPortal = GetPortalString(model.AllPortals == bool.TrueString, oMod.PortalId);
sNewText = HttpUtility.HtmlEncode(sNewText);
switch (mode)
{
case ModuleReplaceMode.Find:
{
if (sNewText != string.Empty)
{
sNewText = sNewText.Replace("[*]", "<br />");
sOut.AppendLine(string.Format("<h4>Module: <a href=\"{3}/Default.aspx?Tabid={0}#{1}\" target=\"_blank\">{2}</a></h4><pre>{4}</pre>", oMod.TabId, oMod.ModuleId, oMod.Title, sPortal, sNewText));
iReplaced += 1;
}
break;
}
case ModuleReplaceMode.ReplaceTest:
{
sOut.AppendLine(string.Format("<div><h4>Module: <a href=\"{3}/Default.aspx?Tabid={0}#{1}\" target=\"_blank\">{2}</a></h4><pre>{4}</pre><pre class=\"New\">{5}</pre></div><hr />", oMod.TabId, oMod.ModuleId, oMod.Title, sPortal, oMod.Text, sNewText));
iReplaced += 1;
break;
}
case ModuleReplaceMode.Replace:
{
var sUpdateSql = model.SqlUpdate;
if (model.SqlUpdate.Trim().ToLower().StartsWith("select"))
{
sOut.AppendLine("Invalid Update SQL <br />");
}
else
{
// Get the update statement
sUpdateSql = GetUpdateSql(sUpdateSql, sNewText, oMod.ContentId);
// Update
if (ModuleSqlReplaceSave(GetConnectionString(model), sUpdateSql))
{
sOut.AppendLine(string.Format("<div><a href=\"/Default.aspx?Tabid={0}#{1}\" target=\"_blank\">{2}</a></div>", oMod.TabId, oMod.ModuleId, oMod.Title));
iReplaced += 1;
}
else
{
sOut.AppendLine("Errors SQL update <br />");
}
}
break;
}
case ModuleReplaceMode.GetScript:
{
var sUpdateSql = model.SqlUpdate;
if (model.SqlUpdate.ToLower().Contains("update"))
{
// Get the update statement
sUpdateSql = GetUpdateSql(sUpdateSql, sNewText, oMod.ContentId);
// To show copyable SQL on page
string sPrint = sUpdateSql.Replace("'", "''");
sOut.AppendLine(string.Format("<pre>{0}</pre><hr />", sPrint));
iReplaced += 1;
}
else
{
sOut.AppendLine("Invalid Update SQL <br />");
}
break;
}
}
}
}
var retval = "";
// Output
switch (mode)
{
case ModuleReplaceMode.Find:
{
retval = string.Format("<div class=\"Report\"><h2>Checked Items: {0} - found {1}</h2>{2}</div>", iModules, iReplaced, sOut.ToString());
break;
}
case ModuleReplaceMode.ReplaceTest:
{
retval = string.Format("<div class=\"Report\"><h2>Checked Items: {0} - replaced {1}</h2>{2}</div>", iModules, iReplaced, sOut.ToString());
break;
}
case ModuleReplaceMode.GetScript:
{
retval = string.Format("<div class=\"Report\"><h2>Generated SQL script for {0}/{1} Items</h2>{2}</div>", iReplaced, iModules, sOut.ToString());
break;
}
case ModuleReplaceMode.Replace:
{
retval = string.Format("<div class=\"Report\"><h2>Checked Items: {0} - replaced {1}</h2>{2}</div>", iModules, iReplaced, sOut.ToString());
break;
}
}
return(retval);
}
