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 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 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 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;
}
}