private void SongDBUpdate_UpdateDatabaseFile(string UpdateType)
{
Global.TimerStartTime = DateTime.Now;
OleDbConnection conn = new OleDbConnection();
conn = CommonFunc.OleDbOpenConn(Global.CrazyktvDatabaseFile, "");
OleDbCommand SongDBVerUpdatecmd = new OleDbCommand();
OleDbCommand GodLiuColumnDropcmd = new OleDbCommand();
string SongDBBackupFile = "";
string SongDBVerUpdatecmdSqlStr = "";
string GodLiuColumnDropSqlStr = "";
bool UpdateError = false;
if (!Directory.Exists(Application.StartupPath + @"\SongMgr\Backup"))
{
Directory.CreateDirectory(Application.StartupPath + @"\SongMgr\Backup");
}
SongDBBackupFile = Application.StartupPath + @"\SongMgr\Backup\" + DateTime.Now.ToLongDateString() + "_CrazySong.mdb";
File.Copy(Global.CrazyktvDatabaseFile, SongDBBackupFile, true);
List <string> CrazyktvDBTableList = new List <string>(CommonFunc.GetOleDbTableList(Global.CrazyktvDatabaseFile, ""));
// 移除 ktv_AllSinger 資料表
if (CrazyktvDBTableList.IndexOf("ktv_AllSinger") >= 0)
{
try
{
OleDbCommand[] cmds =
{
new OleDbCommand("drop table ktv_AllSinger", conn),
};
foreach (OleDbCommand cmd in cmds)
{
cmd.ExecuteNonQuery();
}
}
catch
{
UpdateError = true;
this.BeginInvoke((Action) delegate()
{
SongMaintenance_DBVerTooltip_Label.Text = "移除 ktv_AllSinger 資料表失敗,已還原為原本的資料庫檔案。";
});
}
}
// 移除 ktv_Version 資料表
if (CrazyktvDBTableList.IndexOf("ktv_Version") >= 0)
{
try
{
OleDbCommand[] cmds =
{
new OleDbCommand("drop table ktv_Version", conn),
};
foreach (OleDbCommand cmd in cmds)
{
cmd.ExecuteNonQuery();
}
}
catch
{
UpdateError = true;
this.BeginInvoke((Action) delegate()
{
SongMaintenance_DBVerTooltip_Label.Text = "移除 ktv_Version 資料表失敗,已還原為原本的資料庫檔案。";
});
}
}
if (!UpdateError)
{
DataTable dt = new DataTable();
OleDbCommand[] Scmds =
{
new OleDbCommand("select * from ktv_Song", conn),
new OleDbCommand("select * from ktv_Singer", conn),
new OleDbCommand("select * from ktv_Phonetics", conn),
new OleDbCommand("select * from ktv_Langauage", conn),
};
foreach (OleDbCommand cmd in Scmds)
{
OleDbDataReader Reader = cmd.ExecuteReader();
if (dt.Rows.Count == 0)
{
dt = Reader.GetSchemaTable();
}
else
{
foreach (DataRow row in Reader.GetSchemaTable().Rows)
{
dt.ImportRow(row);
}
}
Reader.Close();
}
bool UpdateKtvSong = false;
bool UpdateKtvSinger = false;
bool UpdatePhonetics = false;
bool UpdateLangauage = true;
bool RemoveGodLiuColumn = false;
List <string> GodLiuColumnlist = new List <string>();
foreach (DataRow row in dt.AsEnumerable())
{
switch (row["ColumnName"].ToString())
{
case "Song_SongName":
if (row["ColumnSize"].ToString() != "80")
{
UpdateKtvSong = true;
}
break;
case "Song_Singer":
if (row["ColumnSize"].ToString() != "60")
{
UpdateKtvSong = true;
}
break;
case "Song_Spell":
if (row["ColumnSize"].ToString() != "80")
{
UpdateKtvSong = true;
}
break;
case "Song_FileName":
if (row["ColumnSize"].ToString() != "255")
{
UpdateKtvSong = true;
}
break;
case "Song_SpellNum":
if (row["ColumnSize"].ToString() != "80")
{
UpdateKtvSong = true;
}
break;
case "Song_PenStyle":
if (row["ColumnSize"].ToString() != "80")
{
UpdateKtvSong = true;
}
break;
case "Singer_Name":
case "Singer_Spell":
case "Singer_SpellNum":
case "Singer_PenStyle":
if (row["ColumnSize"].ToString() != "60")
{
UpdateKtvSinger = true;
}
break;
case "PenStyle":
if (row["ColumnSize"].ToString() != "40")
{
UpdatePhonetics = true;
}
break;
case "Langauage_KeyWord":
UpdateLangauage = false;
break;
case "Song_SongNameFuzzy":
case "Song_SingerFuzzy":
case "Song_FuzzyVer":
case "DLspace":
case "Epasswd":
case "imgpath":
case "cashboxsongid":
case "cashboxdat":
case "holidaysongid":
RemoveGodLiuColumn = true;
GodLiuColumnlist.Add(row["ColumnName"].ToString());
break;
}
}
dt.Dispose();
dt = null;
string UpdateSqlStr = "";
OleDbCommand UpdateCmd = new OleDbCommand();
if (UpdateKtvSong)
{
UpdateSqlStr = "select * into ktv_Song_temp from ktv_Song";
UpdateCmd = new OleDbCommand(UpdateSqlStr, conn);
UpdateCmd.ExecuteNonQuery();
UpdateSqlStr = "delete * from ktv_Song";
UpdateCmd = new OleDbCommand(UpdateSqlStr, conn);
UpdateCmd.ExecuteNonQuery();
OleDbCommand[] Updatecmds =
{
new OleDbCommand("alter table ktv_Song alter column Song_SongName TEXT(80) WITH COMPRESSION", conn),
new OleDbCommand("alter table ktv_Song alter column Song_Singer TEXT(60) WITH COMPRESSION", conn),
new OleDbCommand("alter table ktv_Song alter column Song_Spell TEXT(80) WITH COMPRESSION", conn),
new OleDbCommand("alter table ktv_Song alter column Song_FileName TEXT(255) WITH COMPRESSION", conn),
new OleDbCommand("alter table ktv_Song alter column Song_SpellNum TEXT(80) WITH COMPRESSION", conn),
new OleDbCommand("alter table ktv_Song alter column Song_PenStyle TEXT(80) WITH COMPRESSION", conn)
};
try
{
foreach (OleDbCommand cmd in Updatecmds)
{
cmd.ExecuteNonQuery();
}
UpdateSqlStr = "insert into ktv_Song select * from ktv_Song_temp";
UpdateCmd = new OleDbCommand(UpdateSqlStr, conn);
UpdateCmd.ExecuteNonQuery();
UpdateSqlStr = "drop table ktv_Song_temp";
UpdateCmd = new OleDbCommand(UpdateSqlStr, conn);
UpdateCmd.ExecuteNonQuery();
}
catch
{
UpdateError = true;
this.BeginInvoke((Action) delegate()
{
SongMaintenance_DBVerTooltip_Label.Text = "更新歌曲資料表失敗,已還原為原本的資料庫檔案。";
});
}
}
if (UpdateKtvSinger)
{
OleDbCommand[] Updatecmds =
{
new OleDbCommand("alter table ktv_Singer alter column Singer_Name TEXT(60) WITH COMPRESSION", conn),
new OleDbCommand("alter table ktv_Singer alter column Singer_Spell TEXT(60) WITH COMPRESSION", conn),
new OleDbCommand("alter table ktv_Singer alter column Singer_SpellNum TEXT(60) WITH COMPRESSION", conn),
new OleDbCommand("alter table ktv_Singer alter column Singer_PenStyle TEXT(60) WITH COMPRESSION", conn)
};
try
{
foreach (OleDbCommand cmd in Updatecmds)
{
cmd.ExecuteNonQuery();
}
}
catch
{
UpdateError = true;
this.BeginInvoke((Action) delegate()
{
SongMaintenance_DBVerTooltip_Label.Text = "更新歌手資料表失敗,已還原為原本的資料庫檔案。";
});
}
}
if (UpdatePhonetics)
{
OleDbCommand[] Updatecmds =
{
new OleDbCommand("alter table ktv_Phonetics alter column PenStyle TEXT(40) WITH COMPRESSION", conn)
};
try
{
foreach (OleDbCommand cmd in Updatecmds)
{
cmd.ExecuteNonQuery();
}
}
catch
{
UpdateError = true;
this.BeginInvoke((Action) delegate()
{
SongMaintenance_DBVerTooltip_Label.Text = "更新拼音資料表失敗,已還原為原本的資料庫檔案。";
});
}
}
if (UpdateLangauage)
{
OleDbCommand[] Updatecmds =
{
new OleDbCommand("alter table ktv_Langauage add column Langauage_KeyWord TEXT(255) WITH COMPRESSION", conn)
};
try
{
foreach (OleDbCommand cmd in Updatecmds)
{
cmd.ExecuteNonQuery();
}
}
catch
{
UpdateError = true;
this.BeginInvoke((Action) delegate()
{
SongMaintenance_DBVerTooltip_Label.Text = "更新語系資料表失敗,已還原為原本的資料庫檔案。";
});
}
}
if (RemoveGodLiuColumn)
{
foreach (string GodLiuColumn in GodLiuColumnlist)
{
switch (GodLiuColumn)
{
case "Song_SongNameFuzzy":
GodLiuColumnDropSqlStr = "alter table ktv_Song drop column Song_SongNameFuzzy";
break;
case "Song_SingerFuzzy":
GodLiuColumnDropSqlStr = "alter table ktv_Song drop column Song_SingerFuzzy";
break;
case "Song_FuzzyVer":
GodLiuColumnDropSqlStr = "alter table ktv_Song drop column Song_FuzzyVer";
break;
case "DLspace":
GodLiuColumnDropSqlStr = "alter table ktv_Song drop column DLspace";
break;
case "Epasswd":
GodLiuColumnDropSqlStr = "alter table ktv_Song drop column Epasswd";
break;
case "imgpath":
GodLiuColumnDropSqlStr = "alter table ktv_Song drop column imgpath";
break;
case "cashboxsongid":
GodLiuColumnDropSqlStr = "alter table ktv_Song drop column cashboxsongid";
break;
case "cashboxdat":
GodLiuColumnDropSqlStr = "alter table ktv_Song drop column cashboxdat";
break;
case "holidaysongid":
GodLiuColumnDropSqlStr = "alter table ktv_Song drop column holidaysongid";
break;
}
GodLiuColumnDropcmd = new OleDbCommand(GodLiuColumnDropSqlStr, conn);
GodLiuColumnDropcmd.ExecuteNonQuery();
}
}
conn.Close();
}
if (UpdateError)
{
File.Copy(SongDBBackupFile, Global.CrazyktvDatabaseFile, true);
}
else
{
conn = CommonFunc.OleDbOpenConn(Global.CrazyktvSongMgrDatabaseFile, "");
SongDBVerUpdatecmdSqlStr = "update ktv_Version set SongDB = @SongDB where Id = @Id";
SongDBVerUpdatecmd = new OleDbCommand(SongDBVerUpdatecmdSqlStr, conn);
SongDBVerUpdatecmd.Parameters.AddWithValue("@SongDB", Global.CrazyktvSongDBVer);
SongDBVerUpdatecmd.Parameters.AddWithValue("@Id", "1");
SongDBVerUpdatecmd.ExecuteNonQuery();
SongDBVerUpdatecmd.Parameters.Clear();
conn.Close();
CommonFunc.CompactAccessDB("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Global.CrazyktvDatabaseFile + ";", Global.CrazyktvDatabaseFile);
string VersionQuerySqlStr = "select * from ktv_Version";
using (DataTable dt = CommonFunc.GetOleDbDataTable(Global.CrazyktvSongMgrDatabaseFile, VersionQuerySqlStr, ""))
{
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
Global.CrazyktvSongDBVer = row["SongDB"].ToString();
Global.CashboxUpdDate = DateTime.Parse(row["CashboxUpdDate"].ToString());
}
}
}
this.BeginInvoke((Action) delegate()
{
Global.TimerEndTime = DateTime.Now;
SongMaintenance_DBVer1Value_Label.Text = Global.CrazyktvSongDBVer + " 版";
Cashbox_UpdDateValue_Label.Text = (CultureInfo.CurrentCulture.Name == "zh-TW") ? Global.CashboxUpdDate.ToLongDateString() : Global.CashboxUpdDate.ToShortDateString();
Cashbox_UpdDate_Button.Enabled = Cashbox.GetUpdDateButtonEnableStatus();
SongMaintenance_DBVerTooltip_Label.Text = "";
SongMaintenance_Tooltip_Label.Text = "已完成歌庫版本更新,共花費 " + (long)(Global.TimerEndTime - Global.TimerStartTime).TotalSeconds + " 秒完成。";
});
SongDBUpdate_UpdateFinish();
}
}
// 頁籤切換處理
private void MainTabControl_SelectedIndexChanged(object sender, EventArgs e)
{
Label[] Tooltip_Label =
{
SongQuery_QueryStatus_Label,
SongAdd_Tooltip_Label,
SingerMgr_Tooltip_Label,
SongMgrCfg_Tooltip_Label,
SongMaintenance_Tooltip_Label,
Cashbox_QueryStatus_Label
};
int i = -1;
switch (MainTabControl.SelectedTab.Name)
{
case "SongQuery_TabPage":
i = 0;
SongQuery_QueryValue_TextBox.Focus();
SongQuery_QueryValue_TextBox.ImeMode = ImeMode.OnHalf;
break;
case "SongAdd_TabPage":
i = 1;
break;
case "SingerMgr_TabPage":
i = 2;
break;
case "SongMgrCfg_TabPage":
i = 3;
break;
case "SongMaintenance_TabPage":
i = 4;
switch (SongMaintenance_TabControl.SelectedTab.Name)
{
case "SongMaintenance_CustomLang_TabPage":
case "SongMaintenance_MultiSongPath_TabPage":
case "SongMaintenance_DBVer_TabPage":
if (SongMaintenance_TabControl.Enabled == true)
{
SongMaintenance_Save_Button.Enabled = true;
}
break;
default:
SongMaintenance_Save_Button.Enabled = false;
break;
}
break;
case "Cashbox_TabPage":
i = 5;
Cashbox_QueryValue_TextBox.Focus();
Cashbox_QueryValue_TextBox.ImeMode = ImeMode.OnHalf;
Cashbox_UpdDate_Button.Enabled = Cashbox.GetUpdDateButtonEnableStatus();
break;
}
if (i >= 0)
{
if (!Global.CrazyktvDatabaseStatus)
{
if (!File.Exists(Global.CrazyktvDatabaseFile))
{
Tooltip_Label[i].Text = "CrazyKTV 資料庫檔案不存在!";
}
else if (!File.Exists(Global.CrazyktvSongMgrDatabaseFile))
{
Tooltip_Label[i].Text = "加歌程式參考資料庫檔案不存在!";
}
else if (Global.CrazyktvDatabaseError)
{
Tooltip_Label[i].Text = "資料庫檔案不是 CrazyKTV 資料庫!";
}
else if (Global.SongMgrDatabaseError)
{
Tooltip_Label[i].Text = "加歌程式參考資料庫不是最新版本!";
}
else if (Global.CrazyktvDatabaseIsOld)
{
Tooltip_Label[i].Text = "資料庫檔案為舊版本!";
}
else if (!Global.CrazyktvDatabaseMaxDigitCode)
{
Tooltip_Label[i].Text = "歌庫編碼混雜 5 及 6 位數編碼!";
}
else if (!Directory.Exists(Global.SongMgrDestFolder))
{
Tooltip_Label[i].Text = "請先設定歌庫資料夾!";
}
}
else
{
List <string> ErrorTextList = new List <string>()
{
"CrazyKTV 資料庫檔案不存在!",
"加歌程式參考資料庫檔案不存在!",
"資料庫檔案不是 CrazyKTV 資料庫!",
"加歌程式參考資料庫不是最新版本!",
"資料庫檔案為舊版本!",
"歌庫編碼混雜 5 及 6 位數編碼!",
"請先設定歌庫資料夾!"
};
if (ErrorTextList.IndexOf(Tooltip_Label[i].Text) >= 0)
{
Tooltip_Label[i].Text = "";
}
ErrorTextList.Clear();
}
}
}
private void SongDBUpdate_CheckDatabaseVersion()
{
bool UpdateDBStatus = false;
double SongDBVer = 1.00;
string CashboxUpdDate = "";
string VersionQuerySqlStr = "select * from ktv_Version";
using (DataTable dt = CommonFunc.GetOleDbDataTable(Global.CrazyktvSongMgrDatabaseFile, VersionQuerySqlStr, ""))
{
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
SongDBVer = Convert.ToDouble(row["SongDB"]);
CashboxUpdDate = row["CashboxUpdDate"].ToString();
}
Global.CashboxUpdDate = DateTime.Parse(CashboxUpdDate);
this.BeginInvoke((Action) delegate()
{
SongMaintenance_DBVer1Value_Label.Text = SongDBVer.ToString("F2") + " 版";
Cashbox_UpdDateValue_Label.Text = (CultureInfo.CurrentCulture.Name == "zh-TW") ? DateTime.Parse(CashboxUpdDate).ToLongDateString() : DateTime.Parse(CashboxUpdDate).ToShortDateString();
Cashbox_UpdDate_Button.Enabled = Cashbox.GetUpdDateButtonEnableStatus();
});
if (Global.DBVerEnableDBVerUpdate == "True")
{
if (!Directory.Exists(Application.StartupPath + @"\SongMgr\Update"))
{
Directory.CreateDirectory(Application.StartupPath + @"\SongMgr\Update");
}
bool DownloadStatus = CommonFunc.DownloadFile(Application.StartupPath + @"\SongMgr\Update\UpdateDB.tmp", "https://raw.githubusercontent.com/KenLuoTW/CrazyKTVSongMgr/master/CrazyKTV_SongMgr/SongMgr/Update/UpdateDB.xml");
if (DownloadStatus)
{
Global.CrazyktvSongDBVer = CommonFunc.LoadConfigXmlFile(Application.StartupPath + @"\SongMgr\Update\UpdateDB.tmp", "SongDBVer");
CommonFunc.SaveConfigXmlFile(Global.CrazyktvSongDBUpdateFile, "SongDBVer", Global.CrazyktvSongDBVer);
}
File.Delete(Application.StartupPath + @"\SongMgr\Update\UpdateDB.tmp");
}
if (Global.DBVerEnableDBVerUpdate == "True")
{
if (File.Exists(Global.CrazyktvDatabaseFile))
{
if (Convert.ToDouble(Global.CrazyktvSongDBVer) > SongDBVer)
{
this.BeginInvoke((Action) delegate()
{
if (MessageBox.Show("你確定要更新歌庫版本嗎?", "偵測到歌庫版本更新", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
Common_SwitchDBVerErrorUI(false);
MainTabControl.SelectedIndex = MainTabControl.TabPages.IndexOf(SongMaintenance_TabPage);
SongMaintenance_TabControl.SelectedIndex = SongMaintenance_TabControl.TabPages.IndexOf(SongMaintenance_DBVer_TabPage);
SongMaintenance_DBVerTooltip_Label.Text = "開始進行歌庫版本更新...";
UpdateDBStatus = true;
var UpdateDBTask = Task.Factory.StartNew(() => SongDBUpdate_UpdateDatabaseFile("UpdateVersion"));
}
});
}
}
}
}
if (!UpdateDBStatus)
{
SongDBUpdate_UpdateFinish();
}
}
}
