private void FixButton_Click(object sender, EventArgs e)
{
AppendStatusText(string.Empty);
foreach (UpdateInfo updateInfo in Updates)
{
// This could be better done with a single update statement
string sqlText =
"SELECT " + updateInfo.ColumnName +
" FROM " + updateInfo.TableName +
" WHERE " + updateInfo.ColumnName + "=@OriginalKey";
using (var cn = VoteDb.GetOpenConnection())
{
DataTable table = new DataTable("Updates");
var command = VoteDb.GetCommand(sqlText, cn);
VoteDb.AddCommandParameter(command, "OriginalKey", OriginalKeyTextBox.Text);
var adapter = VoteDb.GetDataAdapter(command);
adapter.Fill(table);
AppendStatusText("Table {0}, Column {1}, Found {2}",
updateInfo.TableName, updateInfo.ColumnName, table.Rows.Count);
if (table.Rows.Count > 0)
{
var builder = VoteDb.GetCommandBuilder(adapter);
foreach (DataRow row in table.Rows)
{
row[updateInfo.ColumnName] = NewKeyTextBox.Text;
}
adapter.Update(table);
}
}
}
}
public void UpdateValue(TableInfo tableInfo, ColumnInfo columnInfo,
DataRow row, string newValue)
{
try
{
using (var cn = VoteDb.GetOpenConnection() as MySqlConnection)
{
string template = "UPDATE {0} SET {1} = @NewValue WHERE {2}";
string where = string.Join(" AND ",
tableInfo.PrimaryKeyColumns.Select(col => col + "=@" + col));
string sqlText = string.Format(template, tableInfo.Name,
columnInfo.Name, where);
var command = new MySqlCommand(sqlText, cn);
VoteDb.AddCommandParameter(command, "NewValue", newValue);
foreach (string keyColumn in tableInfo.PrimaryKeyColumns)
{
VoteDb.AddCommandParameter(command, keyColumn, row[keyColumn]);
}
VoteDb.ExecuteNonQuery(command);
}
}
catch (Exception ex)
{
AppendStatusText("An error occurred updating value: {0}",
ex.Message);
}
}
public static DataTable GetPoliticianLoginsByDateStampRange(DateTime lowDate,
DateTime?highDate = null, int commandTimeout = -1)
{
lowDate = lowDate.Date;
if (highDate == null)
{
highDate = lowDate;
}
highDate = highDate.Value.AddDays(1).Date;
const string cmdText = "SELECT l.DateStamp,p.StateCode,p.PoliticianKey,p.FName AS FirstName," +
"p.MName as MiddleName,p.LName AS LastName,p.Nickname,p.Suffix,p.LiveOfficeStatus," +
"o.OfficeKey,o.OfficeLine1,o.OfficeLine2,e.ElectionDesc FROM votelog.LogLogins l" +
" INNER JOIN vote.Politicians p ON p.PoliticianKey=l.UserPoliticianKey" +
" LEFT OUTER JOIN vote.Offices o ON o.OfficeKey=p.LiveOfficeKey" +
" LEFT OUTER JOIN vote.Elections e ON e.ElectionKey=p.LiveElectionKey" +
" WHERE l.UserSecurity='POLITICIAN'" +
" AND l.DateStamp>=@LowDate AND l.DateStamp<@HighDate" +
" GROUP BY DATE(l.DateStamp),p.PoliticianKey" +
" ORDER BY DATE(l.DateStamp),p.StateCode,p.LName,p.FName,p.MName,p.Suffix";
using (var cn = VoteDb.GetOpenConnection())
{
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "LowDate", lowDate);
VoteDb.AddCommandParameter(cmd, "HighDate", highDate);
cmd.Connection = cn;
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
public static int UpdatePoliticianElectionsAllRows(string newValue)
{
const string cmdText = "UPDATE Sitemap SET PoliticianElections=@newValue";
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "newValue", newValue);
return(VoteDb.ExecuteNonQuery(cmd));
}
public static int UpdateMustHaveStatementAllRows(bool newValue)
{
const string cmdText = "UPDATE Sitemap SET MustHaveStatement=@newValue";
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "newValue", newValue);
return(VoteDb.ExecuteNonQuery(cmd));
}
public static int UpdateMinimumCandidatesAllRows(int newValue)
{
const string cmdText = "UPDATE Sitemap SET MinimumCandidates=@newValue";
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "newValue", newValue);
return(VoteDb.ExecuteNonQuery(cmd));
}
public static PartiesTable GetMajorPartyDataByStateCode(string stateCode,
int commandTimeout = -1)
{
const string cmdText =
"SELECT PartyKey,PartyCode,StateCode,PartyOrder,PartyName,PartyURL,PartyAddressLine1,PartyAddressLine2,PartyCityStateZip,IsPartyMajor FROM Parties WHERE StateCode=@StateCode AND IsPartyMajor=1 AND LENGTH(PartyCode)=1 ORDER BY PartyOrder";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
return(FillTable(cmd, PartiesTable.ColumnSet.All));
}
public static int UpdateColumnAllRows(Column column, object newValue)
{
var cmdText = "UPDATE Sitemap SET {0}=@newValue";
cmdText = string.Format(cmdText, GetColumnName(column));
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "newValue", newValue);
return(VoteDb.ExecuteNonQuery(cmd));
}
public static OfficesAllIdentifiedTable GetDataByStateCode(string stateCode,
string countyCode = "", string localCode = "",
int commandTimeout = -1)
{
const string cmdText = "SELECT StateCode,CountyCode,LocalCode,OfficeLevel," +
"IsOfficesAllIdentified FROM OfficesAllIdentified" +
" WHERE StateCode=@StateCode" +
" AND CountyCode=@CountyCode AND LocalCode=@LocalCode";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
VoteDb.AddCommandParameter(cmd, "LocalCode", localCode);
return(FillTable(cmd, OfficesAllIdentifiedTable.ColumnSet.All));
}
public static int UpdateIsOfficesAllIdentified(bool newValue,
string stateCode, int officeLevel, string countyCode = "",
string localCode = "")
{
const string cmdText = "UPDATE OfficesAllIdentified" +
" SET IsOfficesAllIdentified=@newValue WHERE StateCode=@StateCode" +
" AND OfficeLevel=@OfficeLevel AND CountyCode=@CountyCode" +
" AND LocalCode=@LocalCode";
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "OfficeLevel", officeLevel);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
VoteDb.AddCommandParameter(cmd, "LocalCode", localCode);
VoteDb.AddCommandParameter(cmd, "newValue", newValue);
return(VoteDb.ExecuteNonQuery(cmd));
}
public static AnswersViewTable GetDataForConsolidation(string politicianKey1,
string politicianKey2, int commandTimeout = -1)
{
const string cmdText =
"SELECT a.PoliticianKey,a.Answer,a.IssueKey,a.QuestionKey,a.Sequence," +
"a.Source,a.DateStamp,a.YouTubeUrl,a.YouTubeDescription,a.YouTubeRunningTime,a.YouTubeSourceUrl," +
"a.YouTubeSource,a.YouTubeDate,i.Issue,i.IssueLevel,a.YouTubeRefreshTime,a.YouTubeAutoDisable," +
"i.IssueOrder,q.Question,q.QuestionOrder FROM Answers a" +
" INNER JOIN Issues i ON i.IssueKey=a.IssueKey AND i.IsIssueOmit=0" +
" INNER JOIN Questions q ON q.QuestionKey=a.QuestionKey AND q.IsQuestionOmit=0" +
" WHERE PoliticianKey IN (@PoliticianKey1,@PoliticianKey2) AND" +
" (TRIM(a.Answer) <> '' OR" +
" TRIM(a.YouTubeUrl)<>'' AND NOT a.YouTubeUrl IS NULL AND (a.YouTubeAutoDisable IS NULL OR a.YouTubeAutoDisable='')" +
")" +
" ORDER BY IssueLevel,IssueOrder,Issue,QuestionOrder,Question,DateStamp DESC,PoliticianKey";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "PoliticianKey1", politicianKey1);
VoteDb.AddCommandParameter(cmd, "PoliticianKey2", politicianKey2);
return(FillTable(cmd, AnswersViewTable.ColumnSet.All));
}
public static AnswersViewTable GetAllDataByIssueKeyPoliticianKey(
string issueKey, string politicianKey, int commandTimeout = -1)
{
const string cmdText =
"SELECT ig.IssueGroupKey,ig.IssueGroupOrder,ig.Heading AS IssueGroupHeading," +
"ig.SubHeading AS IssueGroupSubHeading,i.IssueKey,i.IssueOrder,i.IssueLevel,i.Issue,i.IsTextSourceOptional," +
"i.StateCode AS StateCode,q.QuestionKey,q.QuestionOrder,q.Question,a.PoliticianKey,a.Source," +
"a.DateStamp,a.Answer,a.YouTubeUrl,a.YouTubeDescription,a.YouTubeRunningTime,a.YouTubeSourceUrl," +
"a.YouTubeSource,a.YouTubeDate,a.Sequence,a.YouTubeRefreshTime,a.YouTubeAutoDisable FROM Issues i" +
" INNER JOIN IssueGroupsIssues ON IssueGroupsIssues.IssueKey = i.IssueKey" +
" INNER JOIN IssueGroups ig ON ig.IssueGroupKey = IssueGroupsIssues.IssueGroupKey" +
" INNER JOIN Questions q ON q.IssueKey = i.IssueKey" +
" LEFT JOIN Answers a on a.QuestionKey = q.QuestionKey AND a.PoliticianKey=@PoliticianKey" +
" WHERE i.IssueKey=@IssueKey" +
" AND i.IsIssueOmit=0 AND q.IsQuestionOmit=0" +
" ORDER BY IssueGroupOrder,IssueOrder,QuestionOrder,DateStamp DESC,Sequence DESC";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "IssueKey", issueKey);
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
return(FillTable(cmd, AnswersViewTable.ColumnSet.All));
}
private void UpdateValue(TableInfo tableInfo, ColumnInfo columnInfo,
DataRow row, string value)
{
if (columnInfo.Size > 0 && value.Length > columnInfo.Size)
{
MessageBox.Show(string.Format("The new text length exceeds the maximum for this column of {0}",
columnInfo.Size), "Cannot Update New Value", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
else
{
using (var cn = VoteDb.GetOpenConnection() as MySqlConnection)
{
try
{
string template = "UPDATE {0} SET {1} = @NewValue WHERE {2}";
string where = string.Join(" AND ",
tableInfo.PrimaryKeyColumns.Select(col => col + "=@" + col));
string sqlText = string.Format(template, tableInfo.Name,
columnInfo.Name, where);
var command = new MySqlCommand(sqlText, cn);
VoteDb.AddCommandParameter(command, "NewValue", value);
foreach (string keyColumn in tableInfo.PrimaryKeyColumns)
{
VoteDb.AddCommandParameter(command, keyColumn, row[keyColumn]);
}
VoteDb.ExecuteNonQuery(command);
}
catch (Exception ex)
{
AppendStatusText("An error occurred updating value: {0}",
ex.Message);
}
}
}
}
