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