private static Expansion OpenDatabase(SQLiteConnection sqlConn, string filePath)
{
DataSet expansionSet = new DataSet();
//Read from 'data' table
SQLiteCommand sqlCmd = new SQLiteCommand("Select * from datas", sqlConn);
SQLiteDataAdapter sqlAdapter = new SQLiteDataAdapter(sqlCmd);
sqlAdapter.Fill(expansionSet, "datas");
//Read from 'text' table
sqlAdapter.SelectCommand.CommandText = "Select * from texts";
sqlAdapter.Fill(expansionSet, "texts");
//Create new Expansion Class
Expansion Loaded = new Expansion(filePath, expansionSet);
sqlAdapter.Dispose();
sqlCmd.Dispose();
return(Loaded);
}
public void Merge(Expansion expansion)
{
this.Data.Merge(expansion.Data);
this.Text.Merge(expansion.Text);
}
private void Command_Create_Executed(object sender, ExecutedRoutedEventArgs e)
{
List <UserError> createErrors = new List <UserError>();
//Use Winforms' SaveFile Dialog
try
{
using (WinForms.SaveFileDialog saveDialog = new WinForms.SaveFileDialog())
{
saveDialog.InitialDirectory = Properties.Settings.Default.Expansions;
saveDialog.Filter = "Card Database (*.cdb)|*.cdb";
if (saveDialog.ShowDialog() == WinForms.DialogResult.OK)
{
string filePath = saveDialog.FileName;
toFilePath = filePath;
//Save Settings
Properties.Settings.Default.Expansions = Path.GetDirectoryName(filePath);
Properties.Settings.Default.Save();
//Show Database Name
Label_DatabaseTo.Text = Path.GetFileName(filePath);
SQLiteConnection.CreateFile(filePath);
using (SQLiteConnection sqlConn = new SQLiteConnection("Data Source=" + filePath + ";"))
{
sqlConn.Open();
string execSql_Data = "CREATE TABLE 'datas' ('id' integer,'ot' integer, 'alias' integer," +
"'setcode' integer, 'type' integer, 'atk' integer, 'def' integer, 'level' integer," +
"'race' integer, 'attribute' integer, 'category' integer, PRIMARY KEY('id'))";
string execSql_Text = "CREATE TABLE 'texts'('id' integer, 'name' text, 'desc' text," +
"'str1' text, 'str2' text, 'str3' text, 'str4' text, 'str5' text, 'str6' text," +
"'str7' text, 'str8' text, 'str9' text, 'str10' text, 'str11' text, 'str12' text," +
"'str13' text, 'str14' text, 'str15' text, 'str16' text, PRIMARY KEY('id'))";
SQLiteCommand sqlCmd_Data = new SQLiteCommand(execSql_Data, sqlConn);
sqlCmd_Data.ExecuteNonQuery();
SQLiteCommand sqlCmd_Text = new SQLiteCommand(execSql_Text, sqlConn);
sqlCmd_Text.ExecuteNonQuery();
//Create Expansion
if (TableTo != null)
{
TableTo.Dispose();
}
TableTo = OpenDatabase(sqlConn, filePath);
//Clear or Create new 'changesTo' List
InitializeChangesList();
//Dispose
sqlCmd_Data.Dispose();
sqlCmd_Text.Dispose();
sqlConn.Close();
}
}
}
}
catch (Exception exception)
{
//Add Error
createErrors.Add(new UserError(exception));
//Window
ErrorList errorWindow = new ErrorList(createErrors.ToArray());
errorWindow.ShowDialog();
errorWindow.Close();
}
}
private static void AddParametersFromExpansion(SQLiteCommand dataCmd, SQLiteCommand textCmd, Expansion expansion, long code)
{
//Add Code to Parameters
dataCmd.Parameters.Add("@code", DbType.Int64).Value = code;
textCmd.Parameters.Add("@code", DbType.Int64).Value = code;
//Set Up Data Parameters
DataRow dataRow = expansion.Data.Select("id=" + code)[0];
dataCmd.Parameters.Add("@format", DbType.Int32).Value = dataRow["ot"];
dataCmd.Parameters.Add("@alias", DbType.Int64).Value = dataRow["alias"];
dataCmd.Parameters.Add("@setcode", DbType.Int64).Value = dataRow["setcode"];
dataCmd.Parameters.Add("@type", DbType.Int64).Value = dataRow["type"];
dataCmd.Parameters.Add("@atk", DbType.Int64).Value = dataRow["atk"];
dataCmd.Parameters.Add("@def", DbType.Int64).Value = dataRow["def"];
dataCmd.Parameters.Add("@level", DbType.Int64).Value = dataRow["level"];
dataCmd.Parameters.Add("@race", DbType.Int64).Value = dataRow["race"];
dataCmd.Parameters.Add("@attribute", DbType.Int64).Value = dataRow["attribute"];
dataCmd.Parameters.Add("@category", DbType.Int64).Value = dataRow["category"];
//Set Up Text Parameters
DataRow textRow = expansion.Text.Select("id=" + code)[0];
textCmd.Parameters.Add("@name", DbType.String).Value = textRow["name"];
textCmd.Parameters.Add("@desc", DbType.String).Value = textRow["desc"];
textCmd.Parameters.Add("@str1", DbType.String).Value = textRow["str1"];
textCmd.Parameters.Add("@str2", DbType.String).Value = textRow["str2"];
textCmd.Parameters.Add("@str3", DbType.String).Value = textRow["str3"];
textCmd.Parameters.Add("@str4", DbType.String).Value = textRow["str4"];
textCmd.Parameters.Add("@str5", DbType.String).Value = textRow["str5"];
textCmd.Parameters.Add("@str6", DbType.String).Value = textRow["str6"];
textCmd.Parameters.Add("@str7", DbType.String).Value = textRow["str7"];
textCmd.Parameters.Add("@str8", DbType.String).Value = textRow["str8"];
textCmd.Parameters.Add("@str9", DbType.String).Value = textRow["str9"];
textCmd.Parameters.Add("@str10", DbType.String).Value = textRow["str10"];
textCmd.Parameters.Add("@str11", DbType.String).Value = textRow["str11"];
textCmd.Parameters.Add("@str12", DbType.String).Value = textRow["str12"];
textCmd.Parameters.Add("@str13", DbType.String).Value = textRow["str13"];
textCmd.Parameters.Add("@str14", DbType.String).Value = textRow["str14"];
textCmd.Parameters.Add("@str15", DbType.String).Value = textRow["str15"];
textCmd.Parameters.Add("@str16", DbType.String).Value = textRow["str16"];
}
private void Command_Save_Executed(object sender, ExecutedRoutedEventArgs e)
{
List <UserError> saveErrors = new List <UserError>();
//Set up SQL Connection
try
{
using (SQLiteConnection sqlConn = new SQLiteConnection("Data Source=" + toFilePath))
{
sqlConn.Open();
//int startOfExtension = toFilePath.Length - 4;
if (CheckBox_SaveZip.IsChecked == true)
{
//Set Up Zip File Connection to "To Zip File"
using (var zipFileTo = ZipFile.Open(GetZipFilePath(toFilePath), ZipArchiveMode.Update))
{
foreach (CardItem cardItem in changesTo)
{
if (cardItem.IsNew)
{
//Set Up Zip File Connection to "From Zip File"
if (cardItem.IsOriginal)
{
//Update Row in Table
//Commands
SQLiteCommand sqlCmd_updateData = new SQLiteCommand("UPDATE datas " +
"SET ot=@format, alias=@alias, setcode=@setcode, type=@type, atk=@atk, def=@def, " +
"level=@level, race=@race, attribute=@attribute, category=@category " +
"WHERE id=@code", sqlConn);
SQLiteCommand sqlCmd_updateText = new SQLiteCommand("UPDATE texts " +
"SET name=@name, desc=@desc, " +
"str1=@str1, str2=@str2, str3=@str3, str4=@str4, str5=@str5, str6=@str6, " +
"str7=@str7, str8=@str8, str9=@str9, str10=@str10, str11=@str11, str12=@str12, " +
"str13=@str13, str14=@str14, str15=@str15, str16=@str16 " +
"WHERE id=@code", sqlConn);
//Initialize Parameters
AddParametersFromExpansion(sqlCmd_updateData, sqlCmd_updateText, TableFrom, cardItem.Code);
//Execute
sqlCmd_updateData.ExecuteNonQuery();
sqlCmd_updateText.ExecuteNonQuery();
//Images and Scripts
DeleteFilesFromZipArchive(zipFileTo, cardItem.Code, saveErrors);
MoveFilesToZip(zipFileTo, cardItem.Code, saveErrors);
sqlCmd_updateData.Dispose();
sqlCmd_updateText.Dispose();
}
else
{
//Insert Row in Table
SQLiteCommand sqlCmd_insertData = new SQLiteCommand("INSERT INTO datas (id , ot, alias, " +
"setcode, type, atk, def, level, race, attribute, category) " +
"VALUES (@code, @format, @alias, @setcode, @type, @atk, @def, " +
"@level, @race, @attribute, @category)", sqlConn);
SQLiteCommand sqlCmd_insertText = new SQLiteCommand("INSERT INTO texts (id, name, desc, " +
"str1, str2, str3, str4, str5, str6, str7, str8, str9, str10, str11, str12, " +
"str13, str14, str15, str16) " +
"VALUES (@code, @name, @desc, " +
"@str1, @str2, @str3, @str4, @str5, @str6, @str7, @str8, @str9, @str10, @str11, @str12, " +
"@str13, @str14, @str15, @str16)", sqlConn);
//Instantiate Parameters
AddParametersFromExpansion(sqlCmd_insertData, sqlCmd_insertText, TableFrom, cardItem.Code);
//Execute
sqlCmd_insertData.ExecuteNonQuery();
sqlCmd_insertText.ExecuteNonQuery();
//Images and Scripts
DeleteFilesFromZipArchive(zipFileTo, cardItem.Code, saveErrors, true); //Delete If Necessary
MoveFilesToZip(zipFileTo, cardItem.Code, saveErrors); //Move
sqlCmd_insertData.Dispose();
sqlCmd_insertText.Dispose();
}
}
else if (cardItem.IsDeleted)
{
//Delete Row from Table
SQLiteCommand sqlCmd_deleteData = new SQLiteCommand("DELETE FROM datas WHERE id=@code", sqlConn);
SQLiteCommand sqlCmd_deleteText = new SQLiteCommand("DELETE FROM texts WHERE id=@code", sqlConn);
sqlCmd_deleteData.Parameters.Add("@code", DbType.Int64).Value = cardItem.Code;
sqlCmd_deleteText.Parameters.Add("@code", DbType.Int64).Value = cardItem.Code;
sqlCmd_deleteData.ExecuteNonQuery();
sqlCmd_deleteText.ExecuteNonQuery();
//Delete Files
DeleteFilesFromZipArchive(zipFileTo, cardItem.Code, saveErrors);
sqlCmd_deleteData.Dispose();
sqlCmd_deleteText.Dispose();
}
}
}
}
else if (CheckBox_SaveZip.IsChecked == false)
{
foreach (CardItem cardItem in changesTo)
{
if (cardItem.IsNew)
{
//Set Up Zip File Connection to "From Zip File"
if (cardItem.IsOriginal)
{
//Update Row in Table
//Commands
SQLiteCommand sqlCmd_updateData = new SQLiteCommand("UPDATE datas " +
"SET ot=@format, alias=@alias, setcode=@setcode, type=@type, atk=@atk, def=@def, " +
"level=@level, race=@race, attribute=@attribute, category=@category " +
"WHERE id=@code", sqlConn);
SQLiteCommand sqlCmd_updateText = new SQLiteCommand("UPDATE texts " +
"SET name=@name, desc=@desc, " +
"str1=@str1, str2=@str2, str3=@str3, str4=@str4, str5=@str5, str6=@str6, " +
"str7=@str7, str8=@str8, str9=@str9, str10=@str10, str11=@str11, str12=@str12, " +
"str13=@str13, str14=@str14, str15=@str15, str16=@str16 " +
"WHERE id=@code", sqlConn);
//Initialize Parameters
AddParametersFromExpansion(sqlCmd_updateData, sqlCmd_updateText, TableFrom, cardItem.Code);
//Execute
sqlCmd_updateData.ExecuteNonQuery();
sqlCmd_updateText.ExecuteNonQuery();
//Images and Scripts
DeleteFiles(cardItem.Code, saveErrors); //Delete if Necessary
CopyFiles(cardItem.Code, saveErrors);
sqlCmd_updateData.Dispose();
sqlCmd_updateText.Dispose();
}
else
{
//Insert Row in Table
SQLiteCommand sqlCmd_insertData = new SQLiteCommand("INSERT INTO datas (id , ot, alias, " +
"setcode, type, atk, def, level, race, attribute, category) " +
"VALUES (@code, @format, @alias, @setcode, @type, @atk, @def, " +
"@level, @race, @attribute, @category)", sqlConn);
SQLiteCommand sqlCmd_insertText = new SQLiteCommand("INSERT INTO texts (id, name, desc, " +
"str1, str2, str3, str4, str5, str6, str7, str8, str9, str10, str11, str12, " +
"str13, str14, str15, str16) " +
"VALUES (@code, @name, @desc, " +
"@str1, @str2, @str3, @str4, @str5, @str6, @str7, @str8, @str9, @str10, @str11, @str12, " +
"@str13, @str14, @str15, @str16)", sqlConn);
//Instantiate Parameters
AddParametersFromExpansion(sqlCmd_insertData, sqlCmd_insertText, TableFrom, cardItem.Code);
//Execute
sqlCmd_insertData.ExecuteNonQuery();
sqlCmd_insertText.ExecuteNonQuery();
//Images and Scripts
DeleteFiles(cardItem.Code, saveErrors); //Delete if Necessary
CopyFiles(cardItem.Code, saveErrors);
sqlCmd_insertData.Dispose();
sqlCmd_insertText.Dispose();
}
}
else if (cardItem.IsDeleted)
{
//Delete Row from Table
SQLiteCommand sqlCmd_deleteData = new SQLiteCommand("DELETE FROM datas WHERE id=@code", sqlConn);
SQLiteCommand sqlCmd_deleteText = new SQLiteCommand("DELETE FROM texts WHERE id=@code", sqlConn);
sqlCmd_deleteData.Parameters.Add("@code", DbType.Int64).Value = cardItem.Code;
sqlCmd_deleteText.Parameters.Add("@code", DbType.Int64).Value = cardItem.Code;
sqlCmd_deleteData.ExecuteNonQuery();
sqlCmd_deleteText.ExecuteNonQuery();
//Delete Files
DeleteFiles(cardItem.Code, saveErrors);
sqlCmd_deleteData.Dispose();
sqlCmd_deleteText.Dispose();
}
}
}
//Create Expansion
if (TableTo != null)
{
TableTo.Dispose();
}
TableTo = OpenDatabase(sqlConn, toFilePath);
//Clear or Create new 'changesTo' List
InitializeChangesList();
hasChanges = false;
//Close Connection
sqlConn.Close();
}
foreach (CardItem card in listFrom)
{
card.IsNew = false;
card.IsDeleted = false;
}
}
catch (Exception exception)
{
saveErrors.Add(new UserError(exception));
}
if (saveErrors.Count > 0)
{
ErrorList errorWindow = new ErrorList(saveErrors.ToArray());
errorWindow.ShowDialog();
errorWindow.Close();
}
}
private void Command_Load_Executed(object sender, ExecutedRoutedEventArgs e)
{
//Prevent Loading new Database if changes exist
if (hasChanges)
{
MessageBox.Show("Please save or cancel changes before Loading a new Database.",
"Please Save Changes", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
List <UserError> loadErrors = new List <UserError>();
//Use Winforms' OpenFileDialog
try
{
using (WinForms.OpenFileDialog openDialog = new WinForms.OpenFileDialog())
{
//Set Up OpenFileDialog
openDialog.InitialDirectory = Properties.Settings.Default.Path;
openDialog.Filter = "Card Database (*.cdb)|*.cdb";
//Open FileDialog
if (openDialog.ShowDialog() == WinForms.DialogResult.OK)
{
string filePath = openDialog.FileName;
fromFilePath = filePath;
//Save Settings
Properties.Settings.Default.Path = Path.GetDirectoryName(filePath);
Properties.Settings.Default.Save();
//Show Database Name
Label_DatabaseFrom.Text = Path.GetFileName(filePath);
//Open Database
using (SQLiteConnection sqlConn = new SQLiteConnection("Data Source=" + filePath + ";"))
{
sqlConn.Open();
//Create Expansion
if (TableFrom != null)
{
TableFrom.Dispose();
}
TableFrom = OpenDatabase(sqlConn, filePath);
//Add Card List to Changes
listFrom.Clear();
foreach (DataRow dr in TableFrom.Text.Rows)
{
//Add Cards to List as "Not Original"
listFrom.Add(new CardItem(Convert.ToInt64(dr["id"]), dr["name"].ToString(), false));
}
//Fill ListBox
ListBox_TransFrom.ItemsSource = listFrom;
ListBox_TransFrom.Items.Refresh();
}
}
}
}
catch (Exception exception)
{
//Add Error
loadErrors.Add(new UserError(exception));
//Window
ErrorList errorWindow = new ErrorList(loadErrors.ToArray());
errorWindow.ShowDialog();
errorWindow.Close();
}
}
