/// <summary> /// Handles the DoWorker event for the worker /// </summary> /// <param name="sender">Object that fired the event</param> /// <param name="e">.NET supplied event parameters</param> private void worker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e) { lock (syncLock) { System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch(); stopWatch.Start(); try { string tableName = (string)e.Argument; SetGadgetStatusHandler requestUpdateStatus = new SetGadgetStatusHandler(RequestUpdateStatusMessage); CheckForCancellationHandler checkForCancellation = new CheckForCancellationHandler(IsCancelled); if (db.TableExists(tableName)) { db.DeleteTable(tableName); } List <Epi.Data.TableColumn> tcList = dashboardHelper.GetFieldsAsListOfEpiTableColumns(); db.CreateTable(tableName, tcList); DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; System.Data.Common.DbDataReader dataReader = dv.ToTable().CreateDataReader(); db.InsertBulkRows("Select * From [" + tableName + "]", dataReader, requestUpdateStatus, checkForCancellation); } catch (Exception ex) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetWarningMessage), ex.Message); } finally { stopWatch.Stop(); System.Diagnostics.Debug.Print("Export thread finished in " + stopWatch.Elapsed.ToString()); } } }
/// <summary> /// Handles the DoWorker event for the worker /// </summary> /// <param name="sender">Object that fired the event</param> /// <param name="e">.NET supplied event parameters</param> private void datadriverWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e) { lock (syncLock) { System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch(); stopWatch.Start(); try { string tableName = (string)e.Argument; SetGadgetStatusHandler requestUpdateStatus = new SetGadgetStatusHandler(RequestUpdateStatusMessage); CheckForCancellationHandler checkForCancellation = new CheckForCancellationHandler(IsCancelled); if (db.TableExists(tableName) && !db.ConnectionDescription.ToLower().Contains("excel")) { db.DeleteTable(tableName); } else if (db.TableExists(tableName)) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_EXCEL_TABLE_OVERWRITE_FAIL, tableName)); stopWatch.Stop(); return; } dashboardHelper.PopulateDataSet(); // the only reason to call this is to see if any new user-defined vars have been added and apply them. List<Epi.Data.TableColumn> tcList = dashboardHelper.GetFieldsAsListOfEpiTableColumns(useTabOrder); allFieldsSelected = false; // TODO: Fix? if (allFieldsSelected) { if (tcList.Count <= 250) { db.CreateTable(tableName, tcList); DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; DataTable table = dv.ToTable(false); if (useTabOrder) { dashboardHelper.OrderColumns(table, true); } System.Data.Common.DbDataReader dataReader = table.CreateDataReader(); db.InsertBulkRows("Select * From [" + tableName + "]", dataReader, requestUpdateStatus, checkForCancellation); } else { Dictionary<string, List<Epi.Data.TableColumn>> fieldTableDictionary = new Dictionary<string, List<Epi.Data.TableColumn>>(); int totalTablesNeeded = (tcList.Count / 250) + 1; int tableNumber = 0; List<Epi.Data.TableColumn> tableTcList = new List<Epi.Data.TableColumn>(); for (int i = 0; i < tcList.Count; i++) { if (i % 250 == 0) { if (tableNumber != 0) { fieldTableDictionary.Add(tableName + tableNumber.ToString(), tableTcList); } tableTcList = new List<Epi.Data.TableColumn>(); tableNumber++; } tableTcList.Add(tcList[i]); if (i == tcList.Count - 1) { fieldTableDictionary.Add(tableName + tableNumber.ToString(), tableTcList); } } foreach (KeyValuePair<string, List<Epi.Data.TableColumn>> kvp in fieldTableDictionary) { if (db.TableExists(kvp.Key)) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_WIDE_TABLE_FAIL, totalTablesNeeded.ToString())); return; } } foreach (KeyValuePair<string, List<Epi.Data.TableColumn>> kvp in fieldTableDictionary) { db.CreateTable(kvp.Key, kvp.Value); } foreach (KeyValuePair<string, List<Epi.Data.TableColumn>> kvp in fieldTableDictionary) { List<string> exportTableFields = new List<string>(); foreach (Epi.Data.TableColumn tc in kvp.Value) { exportTableFields.Add(tc.Name); } DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; DataTable table = dv.ToTable(false, exportTableFields.ToArray()); if (useTabOrder) { dashboardHelper.OrderColumns(table, true); } System.Data.Common.DbDataReader dataReader = table.CreateDataReader(); //dv.ToTable().CreateDataReader(); db.InsertBulkRows("Select * From [" + kvp.Key + "]", dataReader, requestUpdateStatus, checkForCancellation); } } } else { List<Epi.Data.TableColumn> tcFilteredList = new List<Epi.Data.TableColumn>(); foreach (string columnName in exportFields) { foreach (Epi.Data.TableColumn tc in tcList) { if (tc.Name.Equals(columnName) && !tcFilteredList.Contains(tc)) { tcFilteredList.Add(tc); } } } if (tcFilteredList.Count <= 250) { db.CreateTable(tableName, tcFilteredList); DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; DataTable table = dv.ToTable(false, exportFields.ToArray()); if (useTabOrder) { dashboardHelper.OrderColumns(table, true); } System.Data.Common.DbDataReader dataReader = table.CreateDataReader(); //dv.ToTable().CreateDataReader(); db.InsertBulkRows("Select * From [" + tableName + "]", dataReader, requestUpdateStatus, checkForCancellation); } else { Dictionary<string, List<Epi.Data.TableColumn>> fieldTableDictionary = new Dictionary<string, List<Epi.Data.TableColumn>>(); int totalTablesNeeded = (tcFilteredList.Count / 250) + 1; int tableNumber = 0; List<Epi.Data.TableColumn> tableTcList = new List<Epi.Data.TableColumn>(); for (int i = 0; i < tcFilteredList.Count; i++) { if (i % 250 == 0) { if (tableNumber != 0) { foreach (Epi.Data.TableColumn tc in tcFilteredList) { if (tc.Name == "GlobalRecordId") { if (!tableTcList.Contains(tc)) { tableTcList.Add(tc); } } } fieldTableDictionary.Add(tableName + tableNumber.ToString(), tableTcList); } tableTcList = new List<Epi.Data.TableColumn>(); tableNumber++; } tableTcList.Add(tcFilteredList[i]); if (i == tcFilteredList.Count - 1) { foreach (Epi.Data.TableColumn tc in tcFilteredList) { if (tc.Name == "GlobalRecordId") { if (!tableTcList.Contains(tc)) { tableTcList.Add(tc); } } } fieldTableDictionary.Add(tableName + tableNumber.ToString(), tableTcList); } } foreach (KeyValuePair<string, List<Epi.Data.TableColumn>> kvp in fieldTableDictionary) { if (db.TableExists(kvp.Key)) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_WIDE_TABLE_FAIL, totalTablesNeeded.ToString())); return; } } foreach (KeyValuePair<string, List<Epi.Data.TableColumn>> kvp in fieldTableDictionary) { db.CreateTable(kvp.Key, kvp.Value); } foreach (KeyValuePair<string, List<Epi.Data.TableColumn>> kvp in fieldTableDictionary) { List<string> exportTableFields = new List<string>(); foreach (Epi.Data.TableColumn tc in kvp.Value) { exportTableFields.Add(tc.Name); } DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; DataTable table = dv.ToTable(false, exportTableFields.ToArray()); if (useTabOrder) { dashboardHelper.OrderColumns(table, true); } System.Data.Common.DbDataReader dataReader = table.CreateDataReader(); //dv.ToTable().CreateDataReader(); db.InsertBulkRows("Select * From [" + kvp.Key + "]", dataReader, requestUpdateStatus, checkForCancellation); } } } } catch (Exception ex) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), ex.Message); } finally { stopWatch.Stop(); System.Diagnostics.Debug.Print("Export thread finished in " + stopWatch.Elapsed.ToString()); } } }
/// <summary> /// Handles the DoWorker event for the file I/O worker /// </summary> /// <param name="sender">Object that fired the event</param> /// <param name="e">.NET supplied event parameters</param> private void fileIOWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e) { lock (syncLock) { System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch(); stopWatch.Start(); try { string fileName = (string)e.Argument; SetGadgetStatusHandler requestUpdateStatus = new SetGadgetStatusHandler(RequestUpdateStatusMessage); CheckForCancellationHandler checkForCancellation = new CheckForCancellationHandler(IsCancelled); if (File.Exists(fileName)) { File.Delete(fileName); } dashboardHelper.PopulateDataSet(); // the only reason to call this is to see if any new user-defined vars have been added and apply them. List<Epi.Data.TableColumn> tcList = dashboardHelper.GetFieldsAsListOfEpiTableColumns(useTabOrder); DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; if (!allFieldsSelected) { List<Epi.Data.TableColumn> tcFilteredList = new List<Epi.Data.TableColumn>(); foreach (string columnName in exportFields) { foreach (Epi.Data.TableColumn tc in tcList) { if (tc.Name.Equals(columnName) && !tcFilteredList.Contains(tc)) { tcFilteredList.Add(tc); } } } tcList = tcFilteredList; } Epi.ImportExport.CSVExporter csvExporter = new Epi.ImportExport.CSVExporter( dv, tcList, fileName); if (this.useTabOrder) { csvExporter.ColumnSortOrder = Epi.ImportExport.ColumnSortOrder.TabOrder; } else { csvExporter.ColumnSortOrder = Epi.ImportExport.ColumnSortOrder.None; } csvExporter.SetProgressBar += new SetProgressBarDelegate(CallbackIncrementProgressBar); csvExporter.SetStatus += new UpdateStatusEventHandler(CallbackSetStatusMessage); csvExporter.SetProgressAndStatus += new SetProgressAndStatusHandler(RequestUpdateStatusMessage); csvExporter.CheckForCancellation += new CheckForCancellationHandler(IsCancelled); if (dashboardHelper.IsUsingEpiProject) { csvExporter.AttachView(dashboardHelper.View); } csvExporter.Export(); } catch (Exception ex) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), ex.Message); } finally { stopWatch.Stop(); System.Diagnostics.Debug.Print("File I/O Export thread finished in " + stopWatch.Elapsed.ToString()); } } }
public abstract bool InsertBulkRows(string pSelectSQL, System.Data.Common.DbDataReader pDataReader, SetGadgetStatusHandler pStatusDelegate = null, CheckForCancellationHandler pCancellationDelegate = null);
public override bool InsertBulkRows(string pSelectSQL, System.Data.Common.DbDataReader pDataReader, SetGadgetStatusHandler pStatusDelegate = null, CheckForCancellationHandler pCancellationDelegate = null) { bool result = false; System.Data.SqlClient.SqlConnection Conn = null; System.Data.SqlClient.SqlDataAdapter Adapter = null; System.Data.SqlClient.SqlCommandBuilder builderSQL = null; System.Data.Common.DbCommand cmdSqL = null; DataSet dataSet = new DataSet(); DataTable Temp = new DataTable(); try { StringBuilder InsertSQL; StringBuilder ValueSQL; Conn = new System.Data.SqlClient.SqlConnection(ConnectionString); Adapter = new System.Data.SqlClient.SqlDataAdapter(pSelectSQL, Conn); Adapter.FillSchema(dataSet, SchemaType.Source); builderSQL = new System.Data.SqlClient.SqlCommandBuilder(Adapter); Conn.Open(); cmdSqL = Conn.CreateCommand(); cmdSqL.CommandTimeout = 1500; int rowCount = 0; int skippedRows = 0; int totalRows = 0; if (pStatusDelegate != null && dataSet.Tables.Count > 0) { totalRows = dataSet.Tables[0].Rows.Count; } while (pDataReader.Read()) { cmdSqL = builderSQL.GetInsertCommand(true); InsertSQL = new StringBuilder(); ValueSQL = new StringBuilder(); InsertSQL.Append("Insert Into "); InsertSQL.Append(pSelectSQL.Replace("Select * From ", "")); InsertSQL.Append(" ("); ValueSQL.Append(" values ("); List<System.Data.SqlClient.SqlParameter> ParameterList = new List<SqlParameter>(); foreach (System.Data.SqlClient.SqlParameter param in cmdSqL.Parameters) { string FieldName = param.SourceColumn; InsertSQL.Append("["); InsertSQL.Append(FieldName); InsertSQL.Append("],"); ValueSQL.Append(param.ParameterName); ValueSQL.Append(","); param.Value = pDataReader[FieldName]; ParameterList.Add(param); } InsertSQL.Length = InsertSQL.Length - 1; ValueSQL.Length = ValueSQL.Length - 1; InsertSQL.Append(")"); ValueSQL.Append(")"); InsertSQL.Append(ValueSQL); cmdSqL = null; cmdSqL = Conn.CreateCommand(); cmdSqL.CommandText = InsertSQL.ToString(); foreach (System.Data.SqlClient.SqlParameter param in ParameterList) { DbParameter p2 = cmdSqL.CreateParameter(); p2.DbType = param.DbType; p2.Value = pDataReader[param.SourceColumn]; p2.ParameterName = param.ParameterName; cmdSqL.Parameters.Add(p2); } try { cmdSqL.ExecuteNonQuery(); rowCount++; } catch (Exception ex) { skippedRows++; continue; } if (pStatusDelegate != null) { pStatusDelegate.Invoke(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowCount.ToString(), totalRows.ToString()), (double)rowCount); } if (pCancellationDelegate != null && pCancellationDelegate.Invoke()) { pStatusDelegate.Invoke(string.Format(SharedStrings.DASHBOARD_EXPORT_CANCELLED, rowCount.ToString())); break; } } if (pStatusDelegate != null) { pStatusDelegate.Invoke(string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS, rowCount.ToString())); } } catch (System.Exception ex) { Logger.Log(DateTime.Now + ": " + ex.Message); } finally { if (Conn != null) { Conn.Close(); } } result = true; return result; }
public static bool InsertBulkRows(string pFileString, string pSelectSQL, System.Data.Common.DbDataReader pDataReader, SetGadgetStatusHandler pStatusDelegate = null) { bool result = false; string connString = ParseConnectionString(pFileString); if (DataSource != null) { IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder()); driver.ConnectionString = connString; result = driver.InsertBulkRows(pSelectSQL, pDataReader, pStatusDelegate); } return result; }
/// <summary> /// Handles the DoWorker event for the file I/O worker /// </summary> /// <param name="sender">Object that fired the event</param> /// <param name="e">.NET supplied event parameters</param> private void fileIOWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e) { lock (syncLock) { System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch(); stopWatch.Start(); try { string fileName = (string)e.Argument; SetGadgetStatusHandler requestUpdateStatus = new SetGadgetStatusHandler(RequestUpdateStatusMessage); CheckForCancellationHandler checkForCancellation = new CheckForCancellationHandler(IsCancelled); if (File.Exists(fileName)) { File.Delete(fileName); } dashboardHelper.PopulateDataSet(); // the only reason to call this is to see if any new user-defined vars have been added and apply them. List <Epi.Data.TableColumn> tcList = dashboardHelper.GetFieldsAsListOfEpiTableColumns(useTabOrder); DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; if (!allFieldsSelected) { List <Epi.Data.TableColumn> tcFilteredList = new List <Epi.Data.TableColumn>(); foreach (string columnName in exportFields) { foreach (Epi.Data.TableColumn tc in tcList) { if (tc.Name.Equals(columnName) && !tcFilteredList.Contains(tc)) { tcFilteredList.Add(tc); } } } tcList = tcFilteredList; } Epi.ImportExport.CSVExporter csvExporter = new Epi.ImportExport.CSVExporter( dv, tcList, fileName); if (this.useTabOrder) { csvExporter.ColumnSortOrder = Epi.ImportExport.ColumnSortOrder.TabOrder; } else { csvExporter.ColumnSortOrder = Epi.ImportExport.ColumnSortOrder.None; } csvExporter.SetProgressBar += new SetProgressBarDelegate(CallbackIncrementProgressBar); csvExporter.SetStatus += new UpdateStatusEventHandler(CallbackSetStatusMessage); csvExporter.SetProgressAndStatus += new SetProgressAndStatusHandler(RequestUpdateStatusMessage); csvExporter.CheckForCancellation += new CheckForCancellationHandler(IsCancelled); if (dashboardHelper.IsUsingEpiProject) { csvExporter.AttachView(dashboardHelper.View); } csvExporter.Export(); //StreamWriter sw = File.CreateText(fileName); //DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; //WordBuilder wb = new WordBuilder(SEPARATOR); //DataTable table = dashboardHelper.DataSet.Tables[0]; //if (useTabOrder || !allFieldsSelected) //{ // table = dv.ToTable(false); // dashboardHelper.OrderColumns(table, useTabOrder); // List<DataColumn> columnsToRemove = new List<DataColumn>(); // foreach (DataColumn dc in table.Columns) // { // bool found = false; // foreach (Epi.Data.TableColumn tc in tcList) // { // if (tc.Name.Equals(dc.ColumnName)) // { // found = true; // break; // } // } // if (!found) // { // columnsToRemove.Add(dc); // } // } // foreach (DataColumn dc in columnsToRemove) // { // table.Columns.Remove(dc); // } //} //foreach (DataColumn dc in table.Columns) //{ // wb.Add(dc.ColumnName); //} //sw.WriteLine(wb.ToString()); //int rowsExported = 0; //int totalRows = 0; //if (useTabOrder || !allFieldsSelected) //{ // totalRows = table.Rows.Count; // foreach (DataRow row in table.Rows) // { // wb = new WordBuilder(SEPARATOR); // for (int i = 0; i < table.Columns.Count; i++) // { // string rowValue = row[i].ToString().Replace("\r\n", " "); // if (rowValue.Contains(",") || rowValue.Contains("\"")) // { // rowValue = rowValue.Replace("\"", "\"\""); // rowValue = Util.InsertIn(rowValue, "\""); // } // wb.Add(rowValue); // } // sw.WriteLine(wb); // rowsExported++; // if (rowsExported % 500 == 0) // { // //this.Dispatcher.BeginInvoke(new SetGadgetStatusHandler(RequestUpdateStatusMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported); // RequestUpdateStatusMessage(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported); // } // } //} //else //{ // totalRows = dv.Count; // foreach (DataRowView rowView in dv) // { // wb = new WordBuilder(SEPARATOR); // for (int i = 0; i < table.Columns.Count; i++) // { // DataRow row = rowView.Row; // string rowValue = row[i].ToString().Replace("\r\n", " "); // if (rowValue.Contains(",") || rowValue.Contains("\"")) // { // rowValue = rowValue.Replace("\"", "\"\""); // rowValue = Util.InsertIn(rowValue, "\""); // } // wb.Add(rowValue); // } // sw.WriteLine(wb); // rowsExported++; // if (rowsExported % 500 == 0) // { // //this.Dispatcher.BeginInvoke(new SetGadgetStatusHandler(RequestUpdateStatusMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported); // RequestUpdateStatusMessage(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported); // } // } //} //this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetStatusMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS, rowsExported.ToString())); //sw.Close(); //sw.Dispose(); } catch (Exception ex) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), ex.Message); } finally { stopWatch.Stop(); System.Diagnostics.Debug.Print("File I/O Export thread finished in " + stopWatch.Elapsed.ToString()); } } }
public static bool InsertBulkRows(string pFileString, string pSelectSQL, System.Data.Common.DbDataReader pDataReader, SetGadgetStatusHandler pStatusDelegate = null) { bool result = false; string connString = ParseConnectionString(pFileString); if (DataSource != null) { IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder()); driver.ConnectionString = connString; result = driver.InsertBulkRows(pSelectSQL, pDataReader, pStatusDelegate); } return(result); }
/// <summary> /// Handles the DoWorker event for the worker /// </summary> /// <param name="sender">Object that fired the event</param> /// <param name="e">.NET supplied event parameters</param> private void datadriverWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e) { lock (syncLock) { System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch(); stopWatch.Start(); try { string tableName = (string)e.Argument; SetGadgetStatusHandler requestUpdateStatus = new SetGadgetStatusHandler(RequestUpdateStatusMessage); CheckForCancellationHandler checkForCancellation = new CheckForCancellationHandler(IsCancelled); if (db.TableExists(tableName) && !db.ConnectionDescription.ToLower().Contains("excel")) { db.DeleteTable(tableName); } else if (db.TableExists(tableName)) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_EXCEL_TABLE_OVERWRITE_FAIL, tableName)); stopWatch.Stop(); return; } dashboardHelper.PopulateDataSet(); // the only reason to call this is to see if any new user-defined vars have been added and apply them. List <Epi.Data.TableColumn> tcList = dashboardHelper.GetFieldsAsListOfEpiTableColumns(useTabOrder); allFieldsSelected = false; // TODO: Fix? if (allFieldsSelected) { if (tcList.Count <= 250) { db.CreateTable(tableName, tcList); DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; DataTable table = dv.ToTable(false); if (useTabOrder) { dashboardHelper.OrderColumns(table, true); } System.Data.Common.DbDataReader dataReader = table.CreateDataReader(); db.InsertBulkRows("Select * From [" + tableName + "]", dataReader, requestUpdateStatus, checkForCancellation); } else { Dictionary <string, List <Epi.Data.TableColumn> > fieldTableDictionary = new Dictionary <string, List <Epi.Data.TableColumn> >(); int totalTablesNeeded = (tcList.Count / 250) + 1; int tableNumber = 0; List <Epi.Data.TableColumn> tableTcList = new List <Epi.Data.TableColumn>(); for (int i = 0; i < tcList.Count; i++) { if (i % 250 == 0) { if (tableNumber != 0) { fieldTableDictionary.Add(tableName + tableNumber.ToString(), tableTcList); } tableTcList = new List <Epi.Data.TableColumn>(); tableNumber++; } tableTcList.Add(tcList[i]); if (i == tcList.Count - 1) { fieldTableDictionary.Add(tableName + tableNumber.ToString(), tableTcList); } } foreach (KeyValuePair <string, List <Epi.Data.TableColumn> > kvp in fieldTableDictionary) { if (db.TableExists(kvp.Key)) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_WIDE_TABLE_FAIL, totalTablesNeeded.ToString())); return; } } foreach (KeyValuePair <string, List <Epi.Data.TableColumn> > kvp in fieldTableDictionary) { db.CreateTable(kvp.Key, kvp.Value); } foreach (KeyValuePair <string, List <Epi.Data.TableColumn> > kvp in fieldTableDictionary) { List <string> exportTableFields = new List <string>(); foreach (Epi.Data.TableColumn tc in kvp.Value) { exportTableFields.Add(tc.Name); } DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; DataTable table = dv.ToTable(false, exportTableFields.ToArray()); if (useTabOrder) { dashboardHelper.OrderColumns(table, true); } System.Data.Common.DbDataReader dataReader = table.CreateDataReader(); //dv.ToTable().CreateDataReader(); db.InsertBulkRows("Select * From [" + kvp.Key + "]", dataReader, requestUpdateStatus, checkForCancellation); } } } else { List <Epi.Data.TableColumn> tcFilteredList = new List <Epi.Data.TableColumn>(); foreach (string columnName in exportFields) { foreach (Epi.Data.TableColumn tc in tcList) { if (tc.Name.Equals(columnName) && !tcFilteredList.Contains(tc)) { tcFilteredList.Add(tc); } } } if (tcFilteredList.Count <= 250) { db.CreateTable(tableName, tcFilteredList); DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; DataTable table = dv.ToTable(false, exportFields.ToArray()); if (useTabOrder) { dashboardHelper.OrderColumns(table, true); } System.Data.Common.DbDataReader dataReader = table.CreateDataReader(); //dv.ToTable().CreateDataReader(); db.InsertBulkRows("Select * From [" + tableName + "]", dataReader, requestUpdateStatus, checkForCancellation); } else { Dictionary <string, List <Epi.Data.TableColumn> > fieldTableDictionary = new Dictionary <string, List <Epi.Data.TableColumn> >(); int totalTablesNeeded = (tcFilteredList.Count / 250) + 1; int tableNumber = 0; List <Epi.Data.TableColumn> tableTcList = new List <Epi.Data.TableColumn>(); for (int i = 0; i < tcFilteredList.Count; i++) { if (i % 250 == 0) { if (tableNumber != 0) { foreach (Epi.Data.TableColumn tc in tcFilteredList) { if (tc.Name == "GlobalRecordId") { if (!tableTcList.Contains(tc)) { tableTcList.Add(tc); } } } fieldTableDictionary.Add(tableName + tableNumber.ToString(), tableTcList); } tableTcList = new List <Epi.Data.TableColumn>(); tableNumber++; } tableTcList.Add(tcFilteredList[i]); if (i == tcFilteredList.Count - 1) { foreach (Epi.Data.TableColumn tc in tcFilteredList) { if (tc.Name == "GlobalRecordId") { if (!tableTcList.Contains(tc)) { tableTcList.Add(tc); } } } fieldTableDictionary.Add(tableName + tableNumber.ToString(), tableTcList); } } foreach (KeyValuePair <string, List <Epi.Data.TableColumn> > kvp in fieldTableDictionary) { if (db.TableExists(kvp.Key)) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_WIDE_TABLE_FAIL, totalTablesNeeded.ToString())); return; } } foreach (KeyValuePair <string, List <Epi.Data.TableColumn> > kvp in fieldTableDictionary) { db.CreateTable(kvp.Key, kvp.Value); } foreach (KeyValuePair <string, List <Epi.Data.TableColumn> > kvp in fieldTableDictionary) { List <string> exportTableFields = new List <string>(); foreach (Epi.Data.TableColumn tc in kvp.Value) { exportTableFields.Add(tc.Name); } DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; DataTable table = dv.ToTable(false, exportTableFields.ToArray()); if (useTabOrder) { dashboardHelper.OrderColumns(table, true); } System.Data.Common.DbDataReader dataReader = table.CreateDataReader(); //dv.ToTable().CreateDataReader(); db.InsertBulkRows("Select * From [" + kvp.Key + "]", dataReader, requestUpdateStatus, checkForCancellation); } } } } catch (Exception ex) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), ex.Message); } finally { stopWatch.Stop(); System.Diagnostics.Debug.Print("Export thread finished in " + stopWatch.Elapsed.ToString()); } } }
/// <summary> /// Handles the DoWorker event for the file I/O worker /// </summary> /// <param name="sender">Object that fired the event</param> /// <param name="e">.NET supplied event parameters</param> private void fileIOWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e) { lock (syncLock) { System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch(); stopWatch.Start(); try { string fileName = (string)e.Argument; SetGadgetStatusHandler requestUpdateStatus = new SetGadgetStatusHandler(RequestUpdateStatusMessage); CheckForCancellationHandler checkForCancellation = new CheckForCancellationHandler(IsCancelled); if (File.Exists(fileName)) { File.Delete(fileName); } dashboardHelper.PopulateDataSet(); // the only reason to call this is to see if any new user-defined vars have been added and apply them. List <Epi.Data.TableColumn> tcList = dashboardHelper.GetFieldsAsListOfEpiTableColumns(useTabOrder); DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; if (!allFieldsSelected) { List <Epi.Data.TableColumn> tcFilteredList = new List <Epi.Data.TableColumn>(); foreach (string columnName in exportFields) { foreach (Epi.Data.TableColumn tc in tcList) { if (tc.Name.Equals(columnName) && !tcFilteredList.Contains(tc)) { tcFilteredList.Add(tc); } } } tcList = tcFilteredList; } Epi.ImportExport.CSVExporter csvExporter = new Epi.ImportExport.CSVExporter( dv, tcList, fileName); if (this.useTabOrder) { csvExporter.ColumnSortOrder = Epi.ImportExport.ColumnSortOrder.TabOrder; } else { csvExporter.ColumnSortOrder = Epi.ImportExport.ColumnSortOrder.None; } csvExporter.SetProgressBar += new SetProgressBarDelegate(CallbackIncrementProgressBar); csvExporter.SetStatus += new UpdateStatusEventHandler(CallbackSetStatusMessage); csvExporter.SetProgressAndStatus += new SetProgressAndStatusHandler(RequestUpdateStatusMessage); csvExporter.CheckForCancellation += new CheckForCancellationHandler(IsCancelled); if (dashboardHelper.IsUsingEpiProject) { csvExporter.AttachView(dashboardHelper.View); } csvExporter.Export(); } catch (Exception ex) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), ex.Message); } finally { stopWatch.Stop(); System.Diagnostics.Debug.Print("File I/O Export thread finished in " + stopWatch.Elapsed.ToString()); } } }
public override bool InsertBulkRows(string pSelectSQL, System.Data.Common.DbDataReader pDataReader, SetGadgetStatusHandler pStatusDelegate = null, CheckForCancellationHandler pCancellationDelegate = null) { bool result = false; System.Data.OleDb.OleDbConnection ConnOle = null; System.Data.OleDb.OleDbDataAdapter AdapterOle = null; System.Data.OleDb.OleDbCommandBuilder builderOLE = null; System.Data.Common.DbCommand cmdOle = null; DataSet dataSet = new DataSet(); DataTable Temp = new DataTable(); try { StringBuilder InsertSQL; StringBuilder ValueSQL; ConnOle = new System.Data.OleDb.OleDbConnection(ConnectionString.Replace(";IMEX=1", "")); AdapterOle = new System.Data.OleDb.OleDbDataAdapter(pSelectSQL, ConnOle); AdapterOle.FillSchema(dataSet, SchemaType.Source); AdapterOle.Fill(Temp); builderOLE = new System.Data.OleDb.OleDbCommandBuilder(); builderOLE.DataAdapter = AdapterOle; ConnOle.Open(); cmdOle = ConnOle.CreateCommand(); cmdOle.CommandTimeout = 1500; int rowCount = 0; int skippedRows = 0; int totalRows = 0; int truncatedCellCount = 0; bool numericFieldOverflow = false; while (pDataReader.Read()) { cmdOle = builderOLE.GetInsertCommand(); InsertSQL = new StringBuilder(); ValueSQL = new StringBuilder(); InsertSQL.Append("Insert Into "); InsertSQL.Append(pSelectSQL.Replace("Select * From ", "")); InsertSQL.Append(" ("); ValueSQL.Append(" values ("); int CheckLength = 0; List<OleDbParameter> ParameterList = new List<OleDbParameter>(); foreach (System.Data.OleDb.OleDbParameter param in cmdOle.Parameters) { string FieldName = param.SourceColumn; InsertSQL.Append("["); InsertSQL.Append(FieldName); InsertSQL.Append("],"); ValueSQL.Append(param.ParameterName); ValueSQL.Append(","); try { param.Value = pDataReader[FieldName]; } catch (Exception ex) { param.Value = DBNull.Value; } ParameterList.Add(param); } InsertSQL.Length = InsertSQL.Length - 1; ValueSQL.Length = ValueSQL.Length - 1; InsertSQL.Append(")"); ValueSQL.Append(")"); InsertSQL.Append(ValueSQL); cmdOle = null; cmdOle = ConnOle.CreateCommand(); cmdOle.CommandText = InsertSQL.ToString(); foreach (OleDbParameter param in ParameterList) { DbParameter p2 = cmdOle.CreateParameter(); p2.DbType = param.DbType; try { p2.Value = pDataReader[param.SourceColumn]; CheckLength = p2.Value.ToString().Length; if (CheckLength > 255) { p2.Value = p2.Value.ToString().Substring(0, 255); truncatedCellCount++; } } catch (Exception ex) { p2.Value = DBNull.Value; } p2.ParameterName = param.ParameterName; cmdOle.Parameters.Add(p2); } try { cmdOle.ExecuteNonQuery(); rowCount++; } catch (OleDbException ex) { skippedRows++; if (ex.Message.ToLower().Contains("numeric field overflow")) { numericFieldOverflow = true; } continue; } if (pStatusDelegate != null) { totalRows = rowCount + skippedRows; string messageString = String.Empty; if (skippedRows == 0) { messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowCount.ToString(), totalRows.ToString()); } else { messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS_INCLUDE_SKIPPED, rowCount.ToString(), totalRows.ToString(), skippedRows.ToString()); } pStatusDelegate.Invoke(messageString, (double)rowCount); } if (pCancellationDelegate != null && pCancellationDelegate.Invoke()) { pStatusDelegate.Invoke(string.Format(SharedStrings.DASHBOARD_EXPORT_CANCELLED, rowCount.ToString())); break; } } if (pStatusDelegate != null) { totalRows = rowCount + skippedRows; string messageString = String.Empty; if (skippedRows == 0) { messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS, totalRows.ToString()); } else if (skippedRows > 0 && !numericFieldOverflow) { messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS_SOME_SKIPPED, rowCount.ToString(), totalRows.ToString(), skippedRows.ToString()); } else if (skippedRows > 0 && numericFieldOverflow) { messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS_SOME_SKIPPED_NUMERIC_FIELD_OVERFLOW, rowCount.ToString(), totalRows.ToString(), skippedRows.ToString()); } if (truncatedCellCount > 0) { messageString = messageString + string.Format("; {0} cells truncated to 255 maximum character limit.", truncatedCellCount); } pStatusDelegate.Invoke(messageString); } } //catch (System.Exception ex) //{ // Logger.Log(DateTime.Now + ": " + ex.Message); //} finally { if (ConnOle != null) { ConnOle.Close(); ConnOle.Dispose(); } if (AdapterOle != null) { AdapterOle.Dispose(); } if (builderOLE != null) { builderOLE.Dispose(); } if (cmdOle != null) { cmdOle.Dispose(); } } result = true; return result; }
public override bool InsertBulkRows(string pSelectSQL, System.Data.Common.DbDataReader pDataReader, SetGadgetStatusHandler pStatusDelegate = null, CheckForCancellationHandler pCancellationDelegate = null) { throw new NotImplementedException(); }
/// <summary> /// Handles the DoWorker event for the worker /// </summary> /// <param name="sender">Object that fired the event</param> /// <param name="e">.NET supplied event parameters</param> private void worker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e) { lock (syncLock) { System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch(); stopWatch.Start(); try { string tableName = (string)e.Argument; SetGadgetStatusHandler requestUpdateStatus = new SetGadgetStatusHandler(RequestUpdateStatusMessage); CheckForCancellationHandler checkForCancellation = new CheckForCancellationHandler(IsCancelled); if (db.TableExists(tableName)) { db.DeleteTable(tableName); } List<Epi.Data.TableColumn> tcList = dashboardHelper.GetFieldsAsListOfEpiTableColumns(); db.CreateTable(tableName, tcList); DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; System.Data.Common.DbDataReader dataReader = dv.ToTable().CreateDataReader(); db.InsertBulkRows("Select * From [" + tableName + "]", dataReader, requestUpdateStatus, checkForCancellation); } catch (Exception ex) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetWarningMessage), ex.Message); } finally { stopWatch.Stop(); System.Diagnostics.Debug.Print("Export thread finished in " + stopWatch.Elapsed.ToString()); } } }
/// <summary> /// Handles the DoWorker event for the file I/O worker /// </summary> /// <param name="sender">Object that fired the event</param> /// <param name="e">.NET supplied event parameters</param> private void fileIOWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e) { lock (syncLock) { System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch(); stopWatch.Start(); try { string fileName = (string)e.Argument; SetGadgetStatusHandler requestUpdateStatus = new SetGadgetStatusHandler(RequestUpdateStatusMessage); CheckForCancellationHandler checkForCancellation = new CheckForCancellationHandler(IsCancelled); if (File.Exists(fileName)) { File.Delete(fileName); } dashboardHelper.PopulateDataSet(); // the only reason to call this is to see if any new user-defined vars have been added and apply them. List<Epi.Data.TableColumn> tcList = dashboardHelper.GetFieldsAsListOfEpiTableColumns(useTabOrder); DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; if (!allFieldsSelected) { List<Epi.Data.TableColumn> tcFilteredList = new List<Epi.Data.TableColumn>(); foreach (string columnName in exportFields) { foreach (Epi.Data.TableColumn tc in tcList) { if (tc.Name.Equals(columnName) && !tcFilteredList.Contains(tc)) { tcFilteredList.Add(tc); } } } tcList = tcFilteredList; } Epi.ImportExport.CSVExporter csvExporter = new Epi.ImportExport.CSVExporter( dv, tcList, fileName); if (this.useTabOrder) { csvExporter.ColumnSortOrder = Epi.ImportExport.ColumnSortOrder.TabOrder; } else { csvExporter.ColumnSortOrder = Epi.ImportExport.ColumnSortOrder.None; } csvExporter.SetProgressBar += new SetProgressBarDelegate(CallbackIncrementProgressBar); csvExporter.SetStatus += new UpdateStatusEventHandler(CallbackSetStatusMessage); csvExporter.SetProgressAndStatus += new SetProgressAndStatusHandler(RequestUpdateStatusMessage); csvExporter.CheckForCancellation += new CheckForCancellationHandler(IsCancelled); if (dashboardHelper.IsUsingEpiProject) { csvExporter.AttachView(dashboardHelper.View); } csvExporter.Export(); //StreamWriter sw = File.CreateText(fileName); //DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView; //WordBuilder wb = new WordBuilder(SEPARATOR); //DataTable table = dashboardHelper.DataSet.Tables[0]; //if (useTabOrder || !allFieldsSelected) //{ // table = dv.ToTable(false); // dashboardHelper.OrderColumns(table, useTabOrder); // List<DataColumn> columnsToRemove = new List<DataColumn>(); // foreach (DataColumn dc in table.Columns) // { // bool found = false; // foreach (Epi.Data.TableColumn tc in tcList) // { // if (tc.Name.Equals(dc.ColumnName)) // { // found = true; // break; // } // } // if (!found) // { // columnsToRemove.Add(dc); // } // } // foreach (DataColumn dc in columnsToRemove) // { // table.Columns.Remove(dc); // } //} //foreach (DataColumn dc in table.Columns) //{ // wb.Add(dc.ColumnName); //} //sw.WriteLine(wb.ToString()); //int rowsExported = 0; //int totalRows = 0; //if (useTabOrder || !allFieldsSelected) //{ // totalRows = table.Rows.Count; // foreach (DataRow row in table.Rows) // { // wb = new WordBuilder(SEPARATOR); // for (int i = 0; i < table.Columns.Count; i++) // { // string rowValue = row[i].ToString().Replace("\r\n", " "); // if (rowValue.Contains(",") || rowValue.Contains("\"")) // { // rowValue = rowValue.Replace("\"", "\"\""); // rowValue = Util.InsertIn(rowValue, "\""); // } // wb.Add(rowValue); // } // sw.WriteLine(wb); // rowsExported++; // if (rowsExported % 500 == 0) // { // //this.Dispatcher.BeginInvoke(new SetGadgetStatusHandler(RequestUpdateStatusMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported); // RequestUpdateStatusMessage(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported); // } // } //} //else //{ // totalRows = dv.Count; // foreach (DataRowView rowView in dv) // { // wb = new WordBuilder(SEPARATOR); // for (int i = 0; i < table.Columns.Count; i++) // { // DataRow row = rowView.Row; // string rowValue = row[i].ToString().Replace("\r\n", " "); // if (rowValue.Contains(",") || rowValue.Contains("\"")) // { // rowValue = rowValue.Replace("\"", "\"\""); // rowValue = Util.InsertIn(rowValue, "\""); // } // wb.Add(rowValue); // } // sw.WriteLine(wb); // rowsExported++; // if (rowsExported % 500 == 0) // { // //this.Dispatcher.BeginInvoke(new SetGadgetStatusHandler(RequestUpdateStatusMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported); // RequestUpdateStatusMessage(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowsExported.ToString(), totalRows.ToString()), (double)rowsExported); // } // } //} //this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetStatusMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS, rowsExported.ToString())); //sw.Close(); //sw.Dispose(); } catch (Exception ex) { this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), ex.Message); } finally { stopWatch.Stop(); System.Diagnostics.Debug.Print("File I/O Export thread finished in " + stopWatch.Elapsed.ToString()); } } }
public override bool InsertBulkRows(string tableName, System.Data.Common.DbDataReader reader, SetGadgetStatusHandler pStatusDelegate = null, CheckForCancellationHandler pCancellationDelegate = null) { return(false); }
/// <summary> /// Handles the DoWorker event for the worker /// </summary> /// <param name="sender">Object that fired the event</param> /// <param name="e">.NET supplied event parameters</param> private void worker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e) { bool needsRefresh = false; if (e != null && e.Argument is bool) { needsRefresh = !((bool)e.Argument); } IsPopulatingDataSet = true; RequestUpdateStatusDelegate requestUpdateStatus = new RequestUpdateStatusDelegate(RequestUpdateStatusMessage); CheckForCancellationDelegate checkForCancellation = new CheckForCancellationDelegate(IsCancelled); SetGadgetStatusHandler setGadgetProgress = new SetGadgetStatusHandler(RequestProgressUpdate); GadgetParameters inputs = new GadgetParameters(); inputs.GadgetStatusUpdate += new GadgetStatusUpdateHandler(requestUpdateStatus); inputs.GadgetCheckForCancellation += new GadgetCheckForCancellationHandler(checkForCancellation); inputs.GadgetCheckForProgress += new SetGadgetStatusHandler(setGadgetProgress); try { DashboardHelper.UserVarsNeedUpdating = true; DashboardHelper.PopulateDataSet(inputs); } catch (System.Data.EvaluateException) { this.Dispatcher.BeginInvoke(new SendNotificationMessageHandler(SendNotificationMessage), DashboardSharedStrings.ERROR_FILTER_NO_LONGER_VALID, true, NotificationButtonType.OK, true, 10); } catch (Exception ex) { Epi.Windows.MsgBox.ShowException(ex); } e.Result = needsRefresh; }