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