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