public static void SaveRom(Rom rom)
{
var cmd = Con.CreateCommand();
cmd.CommandText =
"UPDATE rom SET " +
"region=@Region, " +
"version_tags=@VersionTags, " +
"rom_metadata=@RomMetadata, " +
"rom_status=@RomStatus, " +
"catalog=@Catalog, " +
"modified_date=datetime('now','localtime') " +
"WHERE rom_id=@RomId";
cmd.Parameters.Add(new SqliteParameter("@Region", rom.Region));
cmd.Parameters.Add(new SqliteParameter("@VersionTags", rom.VersionTags));
cmd.Parameters.Add(new SqliteParameter("@RomMetadata", rom.RomMetadata));
cmd.Parameters.Add(new SqliteParameter("@RomStatus", rom.RomStatus));
cmd.Parameters.Add(new SqliteParameter("@Catalog", rom.Catalog));
cmd.Parameters.Add(new SqliteParameter("@RomId", rom.RomId));
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = Con.CreateCommand();
cmd.CommandText =
"UPDATE game SET " +
"developer=@Developer, " +
"publisher=@Publisher, " +
"classification=@Classification, " +
"release_date=@ReleaseDate, " +
"players=@Players, " +
"game_metadata=@GameMetadata, " +
"tags=@Tags, " +
"alternate_names=@AltNames, " +
"notes=@Notes, " +
"modified_date=datetime('now','localtime') " +
"WHERE game_id=@GameId";
cmd.Parameters.Add(new SqliteParameter("@Developer", rom.Game.Developer));
cmd.Parameters.Add(new SqliteParameter("@Publisher", rom.Game.Publisher));
cmd.Parameters.Add(new SqliteParameter("@Classification", rom.Game.Classification));
cmd.Parameters.Add(new SqliteParameter("@ReleaseDate", rom.Game.ReleaseDate));
cmd.Parameters.Add(new SqliteParameter("@Players", rom.Game.Players));
cmd.Parameters.Add(new SqliteParameter("@GameMetadata", rom.Game.GameMetadata));
cmd.Parameters.Add(new SqliteParameter("@Tags", rom.Game.Tags));
cmd.Parameters.Add(new SqliteParameter("@AltNames", rom.Game.AltNames));
cmd.Parameters.Add(new SqliteParameter("@Notes", rom.Game.Notes));
cmd.Parameters.Add(new SqliteParameter("@GameId", rom.Game.GameId));
cmd.ExecuteNonQuery();
cmd.Dispose();
}
void selectedRomChanged(object sender, EventArgs e)
{
if (RomChangesMade())
{
var result = MessageBox.Show("Save changes?", "Save or Cancel Changes", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
saveButton_Click(null, null);
}
SelectedRom = null;
}
if (romListView.SelectedItems.Count == 0)
{
detailPanel.Visible = false;
return;
}
var rom = (Rom)romListView.SelectedItems[0].Tag;
SelectedRom = rom;
gameSystemBox.Text = rom.System;
nameBox.Text = rom.Name;
crcBox.Text = rom.CRC32;
md5Box.Text = rom.MD5;
sha1Box.Text = rom.SHA1;
sizeBox.Text = rom.SizeFriendly;
regionBox.Text = rom.Region;
versionBox.Text = rom.VersionTags;
gameMetaBox.Text = rom.Game.GameMetadata;
romMetaBox.Text = rom.RomMetadata;
tagsBox.Text = rom.Game.Tags;
romStatusBox.Text = rom.RomStatus;
developerBox.Text = rom.Game.Developer;
publisherBox.Text = rom.Game.Publisher;
classificationBox.Text = rom.Game.Classification;
releaseDateBox.Text = rom.Game.ReleaseDate;
playersBox.Text = rom.Game.Players;
catalogBox.Text = rom.Catalog;
altNamesBox.Text = rom.Game.AltNames;
notesBox.Text = rom.Game.Notes;
detailPanel.Visible = true;
}
void loadRomsForSelectedSystem()
{
DB.LoadDbForSystem(systemBox.SelectedItem.ToString());
var names = DB.GetDeveloperPublisherNames().ToArray();
romListView.Items.Clear();
foreach (var rom in DB.Roms)
{
var lvi = new ListViewItem(new string[] { rom.DisplayName, rom.Region, rom.VersionTags, rom.CombinedMetaData, rom.Game.Tags });
lvi.Tag = rom;
lvi.BackColor = rom.New ? Color.LightGreen : Color.White;
romListView.Items.Add(lvi);
}
detailPanel.Visible = false;
SelectedRom = null;
developerBox.AutoCompleteCustomSource.Clear();
developerBox.AutoCompleteCustomSource.AddRange(names);
publisherBox.AutoCompleteCustomSource.Clear();
publisherBox.AutoCompleteCustomSource.AddRange(names);
}
void loadRomsForSelectedSystem()
{
DB.LoadDbForSystem(systemBox.SelectedItem.ToString());
var names = DB.GetDeveloperPublisherNames().ToArray();
romListView.Items.Clear();
foreach (var rom in DB.Roms)
{
var lvi = new ListViewItem(new string[] { rom.DisplayName, rom.Region, rom.VersionTags, rom.CombinedMetaData, rom.Game.Tags });
lvi.Tag = rom;
lvi.BackColor = rom.New ? Color.LightGreen : Color.White;
romListView.Items.Add(lvi);
}
detailPanel.Visible = false;
SelectedRom = null;
developerBox.AutoCompleteCustomSource.Clear();
developerBox.AutoCompleteCustomSource.AddRange(names);
publisherBox.AutoCompleteCustomSource.Clear();
publisherBox.AutoCompleteCustomSource.AddRange(names);
}
public static void SaveRom1(Rom rom, string origSystem, string origName)
{
// Does this game already exist?
var game = GameExists(rom.System, rom.Name);
if (game != null) // existing game with this system/name already exists. we'll merge to it. (existing game wins)
{
DeleteGame(origSystem, origName);
rom.Game = game;
}
var cmd = Con.CreateCommand();
cmd.CommandText =
"UPDATE rom SET " +
"system=@System, " +
"name=@Name " +
"WHERE system=@OrigSystem and name=@OrigName";
cmd.Parameters.Add(new SqliteParameter("@System", rom.System));
cmd.Parameters.Add(new SqliteParameter("@Name", rom.Name));
cmd.Parameters.Add(new SqliteParameter("@OrigSystem", origSystem));
cmd.Parameters.Add(new SqliteParameter("@OrigName", origName));
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = Con.CreateCommand();
cmd.CommandText =
"UPDATE game SET " +
"system=@System, " +
"name=@Name " +
"WHERE system=@OrigSystem and name=@OrigName";
cmd.Parameters.Add(new SqliteParameter("@System", rom.System));
cmd.Parameters.Add(new SqliteParameter("@Name", rom.Name));
cmd.Parameters.Add(new SqliteParameter("@OrigSystem", origSystem));
cmd.Parameters.Add(new SqliteParameter("@OrigName", origName));
cmd.ExecuteNonQuery();
cmd.Dispose();
SaveRom(rom);
}
public static void SaveRom2(Rom rom)
{
var cmd = Con.CreateCommand();
cmd.CommandText =
"UPDATE rom SET " +
"system=@System, " +
"name=@Name, " +
"region=@Region, " +
"version_tags=@VersionTags, " +
"rom_metadata=@RomMetadata, " +
"rom_status=@RomStatus, " +
"catalog=@Catalog, " +
"modified_date=datetime('now','localtime') " +
"WHERE rom_id=@RomId";
cmd.Parameters.Add(new SqliteParameter("@System", rom.System));
cmd.Parameters.Add(new SqliteParameter("@Name", rom.Name));
cmd.Parameters.Add(new SqliteParameter("@Region", rom.Region));
cmd.Parameters.Add(new SqliteParameter("@VersionTags", rom.VersionTags));
cmd.Parameters.Add(new SqliteParameter("@RomMetadata", rom.RomMetadata));
cmd.Parameters.Add(new SqliteParameter("@RomStatus", rom.RomStatus));
cmd.Parameters.Add(new SqliteParameter("@Catalog", rom.Catalog));
cmd.Parameters.Add(new SqliteParameter("@RomId", rom.RomId));
cmd.ExecuteNonQuery();
cmd.Dispose();
bool gameAlreadyExists = false;
cmd = Con.CreateCommand();
cmd.CommandText = "SELECT game_id FROM game WHERE system=@System and name=@Name";
cmd.Parameters.Add(new SqliteParameter("@System", rom.System));
cmd.Parameters.Add(new SqliteParameter("@Name", rom.Name));
gameAlreadyExists = cmd.ExecuteScalar() != null;
cmd.Dispose();
if (!gameAlreadyExists)
{
cmd = Con.CreateCommand();
cmd.CommandText = "INSERT INTO game (system, name, created_date) values (@System, @Name, datetime('now','localtime'))";
cmd.Parameters.Add(new SqliteParameter("@System", rom.System));
cmd.Parameters.Add(new SqliteParameter("@Name", rom.Name));
cmd.ExecuteNonQuery();
cmd.Dispose();
}
cmd = Con.CreateCommand();
cmd.CommandText =
"UPDATE game SET " +
"developer=@Developer, " +
"publisher=@Publisher, " +
"classification=@Classification, " +
"release_date=@ReleaseDate, " +
"players=@Players, " +
"game_metadata=@GameMetadata, " +
"tags=@Tags, " +
"alternate_names=@AltNames, " +
"notes=@Notes, " +
"modified_date=datetime('now','localtime') " +
"WHERE system=@System and name=@Name";
cmd.Parameters.Add(new SqliteParameter("@Developer", rom.Game.Developer));
cmd.Parameters.Add(new SqliteParameter("@Publisher", rom.Game.Publisher));
cmd.Parameters.Add(new SqliteParameter("@Classification", rom.Game.Classification));
cmd.Parameters.Add(new SqliteParameter("@ReleaseDate", rom.Game.ReleaseDate));
cmd.Parameters.Add(new SqliteParameter("@Players", rom.Game.Players));
cmd.Parameters.Add(new SqliteParameter("@GameMetadata", rom.Game.GameMetadata));
cmd.Parameters.Add(new SqliteParameter("@Tags", rom.Game.Tags));
cmd.Parameters.Add(new SqliteParameter("@System", rom.System));
cmd.Parameters.Add(new SqliteParameter("@Name", rom.Name));
cmd.Parameters.Add(new SqliteParameter("@AltNames", rom.Game.AltNames));
cmd.Parameters.Add(new SqliteParameter("@Notes", rom.Game.Notes));
cmd.ExecuteNonQuery();
cmd.Dispose();
}
public static void DeleteRom(Rom rom)
{
var cmd = Con.CreateCommand();
cmd.CommandText = "DELETE FROM rom WHERE md5 = @Md5";
cmd.Parameters.Add(new SqliteParameter("@Md5", rom.MD5));
cmd.ExecuteNonQuery();
cmd.Dispose();
}
public static void SaveRom2(Rom rom)
{
var cmd = Con.CreateCommand();
cmd.CommandText =
"UPDATE rom SET " +
"system=@System, "+
"name=@Name, "+
"region=@Region, " +
"version_tags=@VersionTags, " +
"rom_metadata=@RomMetadata, " +
"rom_status=@RomStatus, " +
"catalog=@Catalog, " +
"modified_date=datetime('now','localtime') " +
"WHERE rom_id=@RomId";
cmd.Parameters.Add(new SqliteParameter("@System", rom.System));
cmd.Parameters.Add(new SqliteParameter("@Name", rom.Name));
cmd.Parameters.Add(new SqliteParameter("@Region", rom.Region));
cmd.Parameters.Add(new SqliteParameter("@VersionTags", rom.VersionTags));
cmd.Parameters.Add(new SqliteParameter("@RomMetadata", rom.RomMetadata));
cmd.Parameters.Add(new SqliteParameter("@RomStatus", rom.RomStatus));
cmd.Parameters.Add(new SqliteParameter("@Catalog", rom.Catalog));
cmd.Parameters.Add(new SqliteParameter("@RomId", rom.RomId));
cmd.ExecuteNonQuery();
cmd.Dispose();
bool gameAlreadyExists = false;
cmd = Con.CreateCommand();
cmd.CommandText = "SELECT game_id FROM game WHERE system=@System and name=@Name";
cmd.Parameters.Add(new SqliteParameter("@System", rom.System));
cmd.Parameters.Add(new SqliteParameter("@Name", rom.Name));
gameAlreadyExists = cmd.ExecuteScalar() != null;
cmd.Dispose();
if (!gameAlreadyExists)
{
cmd = Con.CreateCommand();
cmd.CommandText = "INSERT INTO game (system, name, created_date) values (@System, @Name, datetime('now','localtime'))";
cmd.Parameters.Add(new SqliteParameter("@System", rom.System));
cmd.Parameters.Add(new SqliteParameter("@Name", rom.Name));
cmd.ExecuteNonQuery();
cmd.Dispose();
}
cmd = Con.CreateCommand();
cmd.CommandText =
"UPDATE game SET " +
"developer=@Developer, " +
"publisher=@Publisher, " +
"classification=@Classification, " +
"release_date=@ReleaseDate, " +
"players=@Players, " +
"game_metadata=@GameMetadata, " +
"tags=@Tags, " +
"alternate_names=@AltNames, " +
"notes=@Notes, " +
"modified_date=datetime('now','localtime') " +
"WHERE system=@System and name=@Name";
cmd.Parameters.Add(new SqliteParameter("@Developer", rom.Game.Developer));
cmd.Parameters.Add(new SqliteParameter("@Publisher", rom.Game.Publisher));
cmd.Parameters.Add(new SqliteParameter("@Classification", rom.Game.Classification));
cmd.Parameters.Add(new SqliteParameter("@ReleaseDate", rom.Game.ReleaseDate));
cmd.Parameters.Add(new SqliteParameter("@Players", rom.Game.Players));
cmd.Parameters.Add(new SqliteParameter("@GameMetadata", rom.Game.GameMetadata));
cmd.Parameters.Add(new SqliteParameter("@Tags", rom.Game.Tags));
cmd.Parameters.Add(new SqliteParameter("@System", rom.System));
cmd.Parameters.Add(new SqliteParameter("@Name", rom.Name));
cmd.Parameters.Add(new SqliteParameter("@AltNames", rom.Game.AltNames));
cmd.Parameters.Add(new SqliteParameter("@Notes", rom.Game.Notes));
cmd.ExecuteNonQuery();
cmd.Dispose();
}
public static void SaveRom1(Rom rom, string origSystem, string origName)
{
// Does this game already exist?
var game = GameExists(rom.System, rom.Name);
if (game != null) // existing game with this system/name already exists. we'll merge to it. (existing game wins)
{
DeleteGame(origSystem, origName);
rom.Game = game;
}
var cmd = Con.CreateCommand();
cmd.CommandText =
"UPDATE rom SET " +
"system=@System, " +
"name=@Name " +
"WHERE system=@OrigSystem and name=@OrigName";
cmd.Parameters.Add(new SqliteParameter("@System", rom.System));
cmd.Parameters.Add(new SqliteParameter("@Name", rom.Name));
cmd.Parameters.Add(new SqliteParameter("@OrigSystem", origSystem));
cmd.Parameters.Add(new SqliteParameter("@OrigName", origName));
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = Con.CreateCommand();
cmd.CommandText =
"UPDATE game SET " +
"system=@System, " +
"name=@Name " +
"WHERE system=@OrigSystem and name=@OrigName";
cmd.Parameters.Add(new SqliteParameter("@System", rom.System));
cmd.Parameters.Add(new SqliteParameter("@Name", rom.Name));
cmd.Parameters.Add(new SqliteParameter("@OrigSystem", origSystem));
cmd.Parameters.Add(new SqliteParameter("@OrigName", origName));
cmd.ExecuteNonQuery();
cmd.Dispose();
SaveRom(rom);
}
public static void SaveRom(Rom rom)
{
var cmd = Con.CreateCommand();
cmd.CommandText =
"UPDATE rom SET "+
"region=@Region, "+
"version_tags=@VersionTags, "+
"rom_metadata=@RomMetadata, "+
"rom_status=@RomStatus, "+
"catalog=@Catalog, " +
"modified_date=datetime('now','localtime') " +
"WHERE rom_id=@RomId";
cmd.Parameters.Add(new SqliteParameter("@Region", rom.Region));
cmd.Parameters.Add(new SqliteParameter("@VersionTags", rom.VersionTags));
cmd.Parameters.Add(new SqliteParameter("@RomMetadata", rom.RomMetadata));
cmd.Parameters.Add(new SqliteParameter("@RomStatus", rom.RomStatus));
cmd.Parameters.Add(new SqliteParameter("@Catalog", rom.Catalog));
cmd.Parameters.Add(new SqliteParameter("@RomId", rom.RomId));
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = Con.CreateCommand();
cmd.CommandText =
"UPDATE game SET "+
"developer=@Developer, "+
"publisher=@Publisher, "+
"classification=@Classification, "+
"release_date=@ReleaseDate, "+
"players=@Players, "+
"game_metadata=@GameMetadata, "+
"tags=@Tags, "+
"alternate_names=@AltNames, "+
"notes=@Notes, "+
"modified_date=datetime('now','localtime') " +
"WHERE game_id=@GameId";
cmd.Parameters.Add(new SqliteParameter("@Developer", rom.Game.Developer));
cmd.Parameters.Add(new SqliteParameter("@Publisher", rom.Game.Publisher));
cmd.Parameters.Add(new SqliteParameter("@Classification", rom.Game.Classification));
cmd.Parameters.Add(new SqliteParameter("@ReleaseDate", rom.Game.ReleaseDate));
cmd.Parameters.Add(new SqliteParameter("@Players", rom.Game.Players));
cmd.Parameters.Add(new SqliteParameter("@GameMetadata", rom.Game.GameMetadata));
cmd.Parameters.Add(new SqliteParameter("@Tags", rom.Game.Tags));
cmd.Parameters.Add(new SqliteParameter("@AltNames", rom.Game.AltNames));
cmd.Parameters.Add(new SqliteParameter("@Notes", rom.Game.Notes));
cmd.Parameters.Add(new SqliteParameter("@GameId", rom.Game.GameId));
cmd.ExecuteNonQuery();
cmd.Dispose();
}
static void LoadRoms(string system)
{
var cmd = Con.CreateCommand();
cmd.CommandText =
"SELECT rom_id, crc32, md5, sha1, system, name, region, version_tags, rom_metadata, rom_status, catalog, size, created_date, modified_date " +
"FROM rom " +
"WHERE system = @System";
cmd.Parameters.Add(new SqliteParameter("@System", system));
var reader = cmd.ExecuteReader();
while (reader.NextResult())
{
var rom = new Rom();
rom.RomId = reader.GetInt64(0);
rom.CRC32 = reader.GetString(1);
rom.MD5 = reader.GetString(2);
rom.SHA1 = reader.GetString(3);
rom.System = reader.GetString(4);
rom.Name = reader.GetString(5);
rom.Region = reader.GetString(6);
rom.VersionTags = reader.GetString(7);
rom.RomMetadata = reader.GetString(8);
rom.RomStatus = reader.GetString(9);
rom.Catalog = reader.GetString(10);
rom.Size = reader.GetInt64(11);
rom.Created = reader.GetDateTime(12);
rom.Modified = reader.GetDateTime(13);
rom.Game = GameMap[rom.Name];
Roms.Add(rom);
}
reader.Dispose();
cmd.Dispose();
Roms = Roms.OrderBy(x=>x.SortString()).ToList();
}
void selectedRomChanged(object sender, EventArgs e)
{
if (RomChangesMade())
{
var result = MessageBox.Show("Save changes?", "Save or Cancel Changes", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
saveButton_Click(null, null);
SelectedRom = null;
}
if (romListView.SelectedItems.Count == 0)
{
detailPanel.Visible = false;
return;
}
var rom = (Rom)romListView.SelectedItems[0].Tag;
SelectedRom = rom;
gameSystemBox.Text = rom.System;
nameBox.Text = rom.Name;
crcBox.Text = rom.CRC32;
md5Box.Text = rom.MD5;
sha1Box.Text = rom.SHA1;
sizeBox.Text = rom.SizeFriendly;
regionBox.Text = rom.Region;
versionBox.Text = rom.VersionTags;
gameMetaBox.Text = rom.Game.GameMetadata;
romMetaBox.Text = rom.RomMetadata;
tagsBox.Text = rom.Game.Tags;
romStatusBox.Text = rom.RomStatus;
developerBox.Text = rom.Game.Developer;
publisherBox.Text = rom.Game.Publisher;
classificationBox.Text = rom.Game.Classification;
releaseDateBox.Text = rom.Game.ReleaseDate;
playersBox.Text = rom.Game.Players;
catalogBox.Text = rom.Catalog;
altNamesBox.Text = rom.Game.AltNames;
notesBox.Text = rom.Game.Notes;
detailPanel.Visible = true;
}
