private void btnLoadData_Click(object sender, EventArgs e) { files = new string[maxstring]; if (rdoMultipleFiles.Checked) { if (string.IsNullOrEmpty(lblMultipleFilePath.Text.Trim()) || lblMultipleFilePath.Text.Trim() == "-") { MessageBox.Show("When the Multiple File option is selected, a source directory must be provided."); return; } string pattern = "*.{0}"; if (rdoCsv.Checked) { pattern = string.Format(pattern, "csv"); } else if (rdoXls.Checked) { pattern = string.Format(pattern, "xls"); } else if (rdoXlsx.Checked) { pattern = string.Format(pattern, "xlsx"); } else if (rdoXml.Checked) { pattern = string.Format(pattern, "xml"); } else { pattern = string.Format(pattern, txtDelimiter.Text); } // filehandler = string.Format(pattern, pattern); files = Directory.GetFiles(lblMultipleFilePath.Text, pattern); //files = Directory.GetFiles(Properties.Settings.Default.FilePath, pattern); } else { sourceFile = lblSingleFileName.Text; files[0] = sourceFile; } progressBar1.Maximum = files.Length; progressBar1.Minimum = 0; ActivityLogDelegate activityLogDelegate = new ActivityLogDelegate(LogActivityDelegateMethod); tabControl1.SelectedTab = tabPage2; ProcessFiles(activityLogDelegate); }
public void LogActivityDelegateMethod(string message) { if (this.logDataGridView.InvokeRequired) { ActivityLogDelegate d = new ActivityLogDelegate(LogActivityDelegateMethod); this.Invoke(d, new object[] { message }); } else { this.logDataGridView.Rows.Insert(0, DateTime.Now, "Info", "Main", message, ""); logDataGridView.Refresh(); } }
private void ProcessFiles(ActivityLogDelegate activityLogDelegate) { if (activityLogDelegate != null) { activityLogDelegate("Processing Started"); activityLogDelegate(string.Format("{0} file(s) Found.", files.Length)); } string filepath = null; string destinationTableName = ""; string sqlcon = cboDbServerConnections.SelectedValue.ToString(); #region Get Destination Table if (rdoUseSourceTables.Checked) { destinationTableName = null; } else if (rdoUseTableInDestinationDatabase.Checked) { destinationTableName = cboTables.SelectedValue.ToString(); } if (!string.IsNullOrEmpty(destinationTableName)) { if (!destinationTableName.Contains("[")) { destinationTableName = "[" + destinationTableName; } if (!destinationTableName.Contains("]")) { destinationTableName = destinationTableName + "]"; } } #endregion foreach (string fullfilename in files) { if (fullfilename == null) { continue; } activityLogDelegate(string.Format("File: {0}", fullfilename)); string shortfilename = Path.GetFileName(fullfilename); DataOperation dataOperation = new DataOperation(); DataRequest dataRequest = new DataRequest(); SubmitData submitData = new SubmitData(); string connectionstring = null; DataSet ds = new DataSet(); filepath = Path.GetDirectoryName(fullfilename); try { if (rdoCsv.Checked) { connectionstring = string.Format(Properties.Settings.Default.OleFlatFileConnectionTemplate.ToString(), filepath, ','); ds = dataOperation.OleDbExcelSheetsToDataSet(connectionstring, shortfilename); } else if (rdoXls.Checked) { connectionstring = string.Format(Properties.Settings.Default.OleExcelConnectionTemplate.ToString(), fullfilename); ds = dataOperation.OleDbExcelSheetsToDataSet(connectionstring); } else if (rdoXlsx.Checked) { connectionstring = string.Format(Properties.Settings.Default.OleExcelXmlFileConnectionTemplate.ToString(), fullfilename); ds = dataOperation.OleDbExcelSheetsToDataSet(connectionstring); } else if (rdoXml.Checked) { connectionstring = string.Format(Properties.Settings.Default.OleExcelConnectionTemplate.ToString(), fullfilename); ds = dataOperation.OleDbExcelSheetsToDataSet(connectionstring); } else if (rdoOther.Checked) { if (string.IsNullOrEmpty(txtDelimiter.Text.Trim())) { MessageBox.Show("When the Other option is selected, A delimited must be provided"); return; } connectionstring = string.Format(Properties.Settings.Default.OleFlatFileConnectionTemplate.ToString(), filepath, txtDelimiter.Text.Trim()); ds = dataOperation.OleDbExcelSheetsToDataSet(connectionstring, shortfilename); } } catch (Exception exp) { activityLogDelegate(string.Format("Error Reading from: {0} Details {1}", shortfilename, exp.Message)); } if (ds != null) { activityLogDelegate( string.Format(string.Format("File: {0} contains {1} Sheets", fullfilename, ds.Tables.Count))); //Insert Data foreach (DataTable dt in ds.Tables) { if (dt.Rows.Count < 1) { continue; } if (destinationTableName != null) { dt.TableName = destinationTableName; } // string droptable = Properties.Settings.Default.DropExistingTable.Replace("@TableName", dt.TableName); string createstatement = dataOperation.DataTableToMsSqlCreateStatement(dt); bool status = submitData.MsSqlExecuteNonQuery(sqlcon, createstatement); string insertstatement = dataOperation.DataTableToMssqlInsertStatement(dt); try { //Data Cleaning StringReplacementParameter stringReplacementParameter = new StringReplacementParameter(); List <StringReplacementParameter> stringReplacementParameterList = new List <StringReplacementParameter>(); stringReplacementParameter.oldstring = "\r\n"; stringReplacementParameter.newstring = ""; stringReplacementParameterList.Add(stringReplacementParameter); stringReplacementParameter.oldstring = "\r"; stringReplacementParameter.newstring = ""; stringReplacementParameterList.Add(stringReplacementParameter); stringReplacementParameter.oldstring = "\n"; stringReplacementParameter.newstring = ""; stringReplacementParameterList.Add(stringReplacementParameter); DataCleanOptions dataCleanOptions = new DataCleanOptions(); dataCleanOptions.StringReplacementParameterList = stringReplacementParameterList; DataTable cleandata = dataOperation.CleanDataTable(dt, dataCleanOptions); int recordcount = cleandata.Rows.Count; double batchprocessingsize = (double)(recordcount) * ((double)Convert.ToInt16(txtBatchPercentage.Text) / 100); batchprocessingsize = (double)recordcount / batchprocessingsize; if (dataOperation.DataTableBulkInsertMsSqlTable(sqlcon, cleandata, (int)batchprocessingsize)) { activityLogDelegate(string.Format( "{0} record(s) retreived from Sheet: {1} of file: {2} and inserted into database table: {3}", dt.Rows.Count, dt.TableName, fullfilename, dt.TableName)); } else { activityLogDelegate("Insert Failed"); } } catch (Exception exp) { activityLogDelegate("Insert Failed" + exp.StackTrace); } } } else { activityLogDelegate(string.Format("File: {0} contains {1} Sheet(s)", fullfilename, "0")); } } activityLogDelegate("Processed Completed"); MessageBox.Show("Processed Completed"); }