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