private void FillListBoxes(ref OperationResult op)
{
try
{
MP3DBManager mdbmgr = new MP3DBManager();
mdbmgr.SetDataStore(ddtbMp3DbFile.ItemText, ref op);
Dictionary <string, string> parms = new Dictionary <string, string>();
string none = "None";
DataTable dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.SelectAllAlbums, ref op);
ucatAlbum.ClearListbox();
ucatAlbum.SetListboxList(dt, "Album");
ucatAlbum.InsertListBoxItem(0, none);
ucatAlbum.SetSelectedIndex(0);
dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.SelectAllArtists, ref op);
ucatArtists.ClearListbox();
ucatArtists.SetListboxList(dt, "Artist_Name");
ucatArtists.InsertListBoxItem(0, none);
ucatArtists.SetSelectedIndex(0);
dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.SelectAllComments, ref op);
ucatComment.ClearListbox();
ucatComment.SetListboxList(dt, "Comments");
ucatComment.InsertListBoxItem(0, none);
ucatComment.SetSelectedIndex(0);
dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.SelectAllFileNames, ref op);
ucatFileName.ClearListbox();
ucatFileName.SetListboxList(dt, "File_Name");
ucatFileName.InsertListBoxItem(0, none);
ucatFileName.SetSelectedIndex(0);
dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.SelectAllGenres, ref op);
ucatGenre.ClearListbox();
ucatGenre.SetListboxList(dt, "Genre");
ucatGenre.InsertListBoxItem(0, none);
ucatGenre.SetSelectedIndex(0);
dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.SelectAllSongs, ref op);
ucatTitle.ClearListbox();
ucatTitle.SetListboxList(dt, "Song_Title");
ucatTitle.InsertListBoxItem(0, none);
ucatTitle.SetSelectedIndex(0);
}
catch (Exception ex)
{
op.AddException(ex);
rtbMessages.Text = op.GetAllErrorsAndExceptions("\n");
return;
}
}
private void CompactRepareDB()
{
OperationResult op = new OperationResult();
MP3DBManager mdbmgr = new MP3DBManager();
mdbmgr.SetDataStore(ddtbMp3DbFile.ItemText, ref op);
string msg = "Db Compact Complete.";
MessageBoxIcon mbi = op.Success ? MessageBoxIcon.Information : MessageBoxIcon.Error;
string msgBxMsg = op.Success ? msg : msg + "\n" + "There were errors!\n" + op.GetAllErrorsAndExceptions("\n");
string msgCap = op.Success ? "INFORMATION" : "ERROR!";
MessageBox.Show(msgBxMsg, msgCap, MessageBoxButtons.OK, mbi);
}
private void CreateDbFiles(ref OperationResult op)
{
if (!MP3DBExists())
{
op.AddError("You must choose an Access 2000 to 2003 version database (mdb) file!");
return;
}
try
{
MP3DBManager mdbmgr = new MP3DBManager();
mdbmgr.SetDataStore(ddtbMp3DbFile.ItemText, ref op);
if (!op.Success)
{
return;
}
string dirName = dddtbTargetDir.ItemText;
string delimiter = rbtnTab.Checked ? "\t" : ",";
List <string> tables = new List <string> {
"tbFileInfo", "tbMp3Info", "tbArtist"
};
foreach (var table in tables)
{
string sql = $"select * from {table}";
List <string> data = GetTableData(sql, table, delimiter, ref op);
if (!op.Success)
{
return;
}
string fName = Path.Combine(dirName, $"{table}.txt");
BCHFileIO.WriteFullFile(fName, data, ref op);
if (!op.Success)
{
return;
}
}
}
catch (Exception ex)
{
op.AddException(ex);
rtbMessages.Text = op.GetAllErrorsAndExceptions("\n");
return;
}
}
private void LoadDB(ref OperationResult op)
{
try
{
MP3DBManager mdbmgr = new MP3DBManager();
mdbmgr.SetDataStore(ddtbMp3DbFile.ItemText, ref op);
mdbmgr.onCountChange += SetCount;
List <string> mp3List = BCHWinFormUtilities.ListBoxToList(lbMP3s);
string mp3RtDir = dddtbGetMp3RootDir.ItemText;
mdbmgr.InsertMP3s(mp3List, mp3RtDir, ckbUseFolderInfo.Checked, ref op);
}
catch (Exception ex)
{
op.AddException(ex);
}
}
private void btnExecuteSql_Click(object sender, EventArgs e)
{
OperationResult op = new OperationResult();
lblResults.Text = "Results (0)";
rtbMessages.Text = "";
dgvSqlResults.DataSource = null;
try
{
MP3DBManager mdbmgr = new MP3DBManager();
mdbmgr.SetDataStore(ddtbMp3DbFile.ItemText, ref op);
Dictionary <string, string> parms = new Dictionary <string, string>();
string sql = rtbSql.Text;
parms.Add("Sql", sql);
DataTable dt = mdbmgr.RunSqlScript(ref parms, DAL.SqlScriptEnum.DynamicSQL, ref op);
int count = 0;
if (dt != null && dt.Rows.Count > 0)
{
count = dt.Rows.Count;
dgvSqlResults.DataSource = dt;
lblResults.Text = $"Results ({count})";
}
if (op.Success)
{
op.AddInformation("SQL Excution Successfull");
rtbMessages.Text = op.GetAllInfos("\n");
}
else
{
rtbMessages.Text = op.GetAllErrorsAndExceptions("\n");
}
}
catch (Exception ex)
{
op.AddException(ex);
rtbMessages.Text = op.GetAllErrorsAndExceptions("\n");
}
}
private string CreateWherePart(BCHControls.UCAddToListBox atl, string field)
{
string wherePart = string.Empty;
if (atl.SelectedCount < 1)
{
return(wherePart);
}
List <string> list = atl.GetSelectedItems();
if (atl.SelectedCount == 1 && list[0] == "None")
{
return(wherePart);
}
wherePart = MP3DBManager.BuildSearchWhereClausePart(field, list, true, rbAnd.Checked, cbxUseLike.Checked);
return(wherePart);
}
private void LoadDbColumnListBox(string tableName, ref OperationResult op)
{
try
{
MP3DBManager mdbmgr = new MP3DBManager();
mdbmgr.SetDataStore(ddtbMp3DbFile.ItemText, ref op);
lbColumns.Items.Clear();
DataTable dt = mdbmgr.GetDbTableColumns(tableName, ref op);
BCHWinFormUtilities.DataTableToListBox(dt, lbColumns, "ColumnName");
}
catch (Exception ex)
{
op.AddException(ex);
rtbMessages.Text = op.GetAllErrorsAndExceptions("\n");
return;
}
}
private void FillListBoxes(ref OperationResult op)
{
try
{
MP3DBManager mdbmgr = new MP3DBManager();
mdbmgr.SetDataStore(ddtbMp3DbFile.ItemText, ref op);
Dictionary <string, string> parms = new Dictionary <string, string>();
int count = 0;
DataTable dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.SelectAllAlbums, ref op);
count = ftAlbum.AddDataTable(dt, "Album", BCHControls.UCFromToEnum.From);
lblAlbum.Text = $"Album ({count})";
dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.SelectAllArtists, ref op);
count = ftArtist.AddDataTable(dt, "Artist_Name", BCHControls.UCFromToEnum.From);
lblArtist.Text = $"Artist ({count})";
dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.SelectAllComments, ref op);
count = ftComment.AddDataTable(dt, "Comments", BCHControls.UCFromToEnum.From);
lblComment.Text = $"Comment ({count})";
dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.SelectAllFileNames, ref op);
count = ftFileName.AddDataTable(dt, "File_Name", BCHControls.UCFromToEnum.From);
lblFileName.Text = $"File Name ({count})";
dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.SelectAllGenres, ref op);
count = ftGenre.AddDataTable(dt, "Genre", BCHControls.UCFromToEnum.From);
lblGenre.Text = $"Genre ({count})";
dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.SelectAllSongs, ref op);
count = ftTitle.AddDataTable(dt, "Song_Title", BCHControls.UCFromToEnum.From);
lblTitle.Text = $"Title ({count})";
}
catch (Exception ex)
{
op.AddException(ex);
rtbMessages.Text = op.GetAllErrorsAndExceptions("\n");
return;
}
}
private string CreateWherePart(BCHControls.UCFromTo atl, string field)
{
string wherePart = string.Empty;
List <string> list = atl.GetStrList(BCHControls.UCFromToEnum.To);
if (!list.Any())
{
return(wherePart);
}
List <string> listFixed = new List <string>();
//make sure to escape sinqle quotes for query
foreach (var item in list)
{
listFixed.Add(item.Replace("'", "''"));
}
wherePart = MP3DBManager.BuildSearchWhereClausePart(field, listFixed, true, rbAnd.Checked, cbxUseLike.Checked);
return(wherePart);
}
private void LoadDbTableListBox(ref OperationResult op)
{
try
{
MP3DBManager mdbmgr = new MP3DBManager();
mdbmgr.SetDataStore(ddtbMp3DbFile.ItemText, ref op);
//string sql =
// "SELECT MSysObjects.Name AS TableName " +
// "FROM MSysObjects " +
// "WHERE (((Left([Name],1))<>\"~\") " +
// "AND ((Left([Name],4))<>\"MSys\") " +
// "AND ((MSysObjects.Type) In (1,4,6))) " +
// "order by MSysObjects.Name " +
// "";
lbTables.Items.Clear();
DataTable dt = mdbmgr.GetDbTables(ref op);
BCHWinFormUtilities.DataTableToListBox(dt, lbTables, "TABLE_NAME");
lbTables.SelectedIndex = 0;
LoadDbColumnListBox(lbTables.SelectedItem.ToString(), ref op);
if (!op.Success)
{
rtbMessages.Text = op.GetAllErrorsAndExceptions("\n");
return;
}
}
catch (Exception ex)
{
op.AddException(ex);
rtbMessages.Text = op.GetAllErrorsAndExceptions("\n");
return;
}
}
private void DeleteAllMp3s()
{
OperationResult op = new OperationResult();
try
{
MP3DBManager mdbmgr = new MP3DBManager();
mdbmgr.SetDataStore(ddtbMp3DbFile.ItemText, ref op);
mdbmgr.DeleteallMP3s(ref op);
}
catch (Exception ex)
{
op.AddException(ex);
}
string msg = "Delete MP3 Complete.";
MessageBoxIcon mbi = op.Success ? MessageBoxIcon.Information : MessageBoxIcon.Error;
string msgBxMsg = op.Success ? msg : msg + "\n" + "There were errors!\n" + op.GetAllErrorsAndExceptions("\n");
string msgCap = op.Success ? "INFORMATION" : "ERROR!";
MessageBox.Show(msgBxMsg, msgCap, MessageBoxButtons.OK, mbi);
}
private void btnSearch_Click(object sender, EventArgs e)
{
List <string> list = new List <string>();
dataGridView1.DataSource = null;
string wherePart = CreateWherePart(ucatAlbum, "Album");
if (wherePart != "'None'" && wherePart.Trim().Length > 0)
{
list.Add(wherePart);
}
wherePart = CreateWherePart(ucatArtists, "artist_Name");
if (wherePart != "'None'" && wherePart.Trim().Length > 0)
{
list.Add(wherePart);
}
wherePart = CreateWherePart(ucatComment, "Comments");
if (wherePart != "'None'" && wherePart.Trim().Length > 0)
{
list.Add(wherePart);
}
wherePart = CreateWherePart(ucatFileName, "File_Name");
if (wherePart != "'None'" && wherePart.Trim().Length > 0)
{
list.Add(wherePart);
}
wherePart = CreateWherePart(ucatGenre, "Genre");
if (wherePart != "'None'" && wherePart.Trim().Length > 0)
{
list.Add(wherePart);
}
wherePart = CreateWherePart(ucatTitle, "Song_Title");
if (wherePart != "'None'" && wherePart.Trim().Length > 0)
{
list.Add(wherePart);
}
OperationResult op = new OperationResult();
try
{
wherePart = MP3DBManager.BuildSearchWhereClause(list, rbAnd.Checked);
MP3DBManager mdbmgr = new MP3DBManager();
mdbmgr.SetDataStore(ddtbMp3DbFile.ItemText, ref op);
Dictionary <string, string> parms = new Dictionary <string, string>();
parms.Add("Where_Clause", wherePart);
DataTable dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.GetMP3Data_EnterWhereClause, ref op);
if (op.Success)
{
dataGridView1.DataSource = dt;
}
}
catch (Exception ex)
{
op.AddException(ex);
rtbMessages.Text = op.GetAllErrorsAndExceptions("\n");
return;
}
}
private List <string> GetTableData(string sql, string tableName, string delimiter, ref OperationResult op)
{
List <string> data = new List <string>();
try
{
MP3DBManager mdbmgr = new MP3DBManager();
mdbmgr.SetDataStore(ddtbMp3DbFile.ItemText, ref op);
Dictionary <string, string> parms = new Dictionary <string, string>();
parms.Add("Sql", sql);
DataTable dt = mdbmgr.RunSqlScript(ref parms, DAL.SqlScriptEnum.DynamicSQL, ref op);
string colVal = "";
if (dt != null && dt.Rows.Count > 0)
{
DataTable dtn = mdbmgr.GetDbTables(ref op);
DataTable dtColomns = mdbmgr.GetDbTableColumns(tableName, ref op);
StringBuilder rowData = new StringBuilder();
string rowString = "";
int colCount = dt.Columns.Count;
string val;
string cols = "";
int c = 0;
foreach (DataRow row in dtColomns.Rows)
{
val = row["ColumnName"] != null && row["ColumnName"].ToString().Trim().Length > 0 ? row["ColumnName"].ToString().Trim() : string.Empty;
cols += val + (c < colCount - 1 ? delimiter : "");
c++;
}
data.Add(cols);
foreach (DataRow row in dt.Rows)
{
rowData = new StringBuilder();
for (int i = 0; i < colCount; i++)
{
//if(tableName == )
colVal = row[i].ToString();
colVal = string.IsNullOrWhiteSpace(colVal) ? "<null>>" : colVal;
rowData.Append(colVal + (i < colCount - 1 ? delimiter: ""));
}
rowString = rowData.ToString();
data.Add(rowString);
}
}
if (op.Success)
{
op.AddInformation("SQL Excution Successfull");
}
}
catch (Exception ex)
{
op.AddException(ex);
}
return(data);
}
private void btnSearch_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = null;
rtbMessages.Text = "";
if (!MP3DBExists())
{
rtbMessages.Text = "Database file does not exists";
return;
}
List <string> list = new List <string>();
string wherePart = CreateWherePart(ftAlbum, "Album");
if (wherePart.Trim().Length > 0)
{
list.Add(wherePart);
}
wherePart = CreateWherePart(ftArtist, "artist_Name");
if (wherePart.Trim().Length > 0)
{
list.Add(wherePart);
}
wherePart = CreateWherePart(ftComment, "Comments");
if (wherePart.Trim().Length > 0)
{
list.Add(wherePart);
}
wherePart = CreateWherePart(ftFileName, "File_Name");
if (wherePart.Trim().Length > 0)
{
list.Add(wherePart);
}
wherePart = CreateWherePart(ftGenre, "Genre");
if (wherePart.Trim().Length > 0)
{
list.Add(wherePart);
}
wherePart = CreateWherePart(ftTitle, "Song_Title");
if (wherePart.Trim().Length > 0)
{
list.Add(wherePart);
}
OperationResult op = new OperationResult();
try
{
wherePart = MP3DBManager.BuildSearchWhereClause(list, rbAnd.Checked);
MP3DBManager mdbmgr = new MP3DBManager();
mdbmgr.SetDataStore(ddtbMp3DbFile.ItemText, ref op);
Dictionary <string, string> parms = new Dictionary <string, string>();
parms.Add("Where_Clause", wherePart);
DataTable dt = mdbmgr.RunSqlScript(ref parms, SqlScriptEnum.GetMP3Data_EnterWhereClause, ref op);
if (op.Success)
{
dataGridView1.DataSource = dt;
int count = 0;
if (dt != null && dt.Rows.Count > 0)
{
count = dt.Rows.Count;
}
rtbMessages.Text = $"Search Results - {count}";
}
else
{
rtbMessages.Text = op.GetAllErrorsAndExceptions("\n");
return;
}
}
catch (Exception ex)
{
op.AddException(ex);
rtbMessages.Text = op.GetAllErrorsAndExceptions("\n");
return;
}
}