want to run my software fast

See more:

Windows

C#3.5

application

my software work is select multiple csv file

and generate excel report

problem is:

when i select 20 , 30 file and each file contain 1000 record
then my software run very slowly


plz help me fast

?



my code here:c# .net



private void btngenreport_Click_1(object sender, EventArgs e)
{
lblprocessmsg.Visible = true;
btngenreport.Enabled = false;
for (int j = 0; j < filename.Length; j++)
{
int intLengthOfFileName = filename[j].Length;
int intLastIndex = filename[j].LastIndexOf("\\");
strCSVFile = filename[j].Substring(intLastIndex, intLengthOfFileName - intLastIndex);
strCSVFile = strCSVFile.Remove(0, 1).Trim();
temp = temp+ "," + strCSVFile.Trim();
}
temp = temp.Remove(0, 1);
ConnectCSV(temp);
// ConnectCSV(strCSVFile);
}



public DataSet ConnectCSV(string filetable)
{
DataSet ds = new DataSet();
try
{

//string safeword="";
string unsafeword="";
string sdomain="";
//foreach (object itemchk in chklistsafeword.CheckedItems)
//{

//safeword = safeword + itemchk.ToString()+"','";
//}

foreach (object safedomain in lbsafedomain.Items )
{
//sdomain.su
sdomain = sdomain + "%" + safedomain.ToString() + "%" + "'" + " AND sender NOT LIKE " + "'";
}
// sdomain = sdomain.Remove(sdomain.LastIndexOf(','));

sdomain = sdomain.Remove(sdomain.LastIndexOf("'"));

sdomain = sdomain.Remove(sdomain.LastIndexOf(" AND sender NOT LIKE "));
sdomain = sdomain.Remove(sdomain.LastIndexOf("'"));
foreach (object itemchk in lbunsafeword.Items)
{
unsafeword = unsafeword + "%" + itemchk.ToString() + "%" + "'" + " OR subject LIKE " + "'";
}

// unsafeword = unsafeword.Remove(unsafeword.LastIndexOf(','));
unsafeword = unsafeword.Remove(unsafeword.LastIndexOf("'"));
unsafeword = unsafeword.Remove(unsafeword.LastIndexOf(" OR subject LIKE "));
unsafeword = unsafeword.Remove(unsafeword.LastIndexOf("'"));
string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + folderpath + ";Extensions=csv;Persist Security Info=False";

System.Data.Odbc.OdbcConnection conn;
conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim());
conn.Open();
//char[] sep = {','};
string[] strtablenm= filetable.Split(',');
string[] sql_select = new string[strtablenm.Length];
for (int i = 0; i <= strtablenm.Length-1; i++)
{
//sql_select[i] = "select * from " + strtablenm[i] + " where subject LIKE ('%" + unsafeword + "%') and sender NOT LIKE (SELECT SUBSTRING(CHARINDEX('@', sender) + 1, LEN(sender)) As sender FROM '" + strtablenm[i] + "') ";
//sql_select[i] = "select * from " + strtablenm[i] + " where subject LIKE ('%" + unsafeword + "%') and (SUBSTRING(sender, CHARINDEX('@', sender)+1, LEN(sender)) As sender NOT IN ('" + sdomain + "')) ";

//sql_select[i] = "select * from " + strtablenm[i] + " where subject IN ('" + unsafeword + "') and sender NOT IN ('"+sdomain+"')";
sql_select[i] = "select * from " + strtablenm[i] + " where (subject LIKE '" + unsafeword + "') and (sender NOT LIKE '" + sdomain + "')";
//sql_select[i] = "select * from " + strtablenm[i] + " where subject IN ('" + unsafeword + "') and sender=(SUBSTRING(sender, CHARINDEX('@', sender) + 1, LEN(sender)) NOT LIKE('" + sdomain + "')) ";
//SELECT SUBSTRING(sender, CHARINDEX('@', sender) + 1, LEN(sender)) FROM Table1

temp = "";
obj_oledb_da = new System.Data.Odbc.OdbcDataAdapter(sql_select[i], conn);

obj_oledb_da.Fill(ds);

}
ds.WriteXml(@"D:\MCA-6th sem data\Project\EmailSecurityAuditUtility\EmailSecurityAuditUtility\exceldata.xml");
//for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
//{

// MessageBox.Show(ds.Tables[0].Rows[i][10].ToString());
// MessageBox.Show(ds.Tables[0].Rows[i][6].ToString());
//}

conn.Close();
DataSet ds1 = new DataSet();
ds1.ReadXml(@"D:\MCA-6th sem data\Project\EmailSecurityAuditUtility\EmailSecurityAuditUtility\exceldata.xml");
try
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int j = 0;
int k = 0;

for (j = 0; j <= ds1.Tables[0].Rows.Count - 1; j++)
{
for (k = 0; k <= ds1.Tables[0].Columns.Count - 1; k++)
{
xlWorkSheet.Cells[1, k + 1] = ds1.Tables[0].Columns[k].ColumnName;
xlWorkSheet.Cells[j + 2, k + 1] = ds1.Tables[0].Rows[j][k];
}
}
xlWorkBook.SaveAs("d:\\report1.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
lblprocessmsg.Visible = false;
btngenreport.Enabled = true;
MessageBox.Show("Excel file created , you can find the file D:\\report1.xls");
}
catch (Exception e2)
{
btngenreport.Enabled = true;
lblprocessmsg.Visible = false;
MessageBox.Show("no record found.....");
}



}
catch (Exception e)
{
btngenreport.Enabled = true;
lblprocessmsg.Visible = false;
MessageBox.Show("No CSV file current folder ,select other folder...");
//MessageBox.Show("no csv file in this folder "+e.Message);
}
return ds;

}