You should use a thread instead of a timer. Here's your code rewqorked to use a thread, and to abstract out some of the code into discreet methods (monolithic methods are so 1980's). Keep in mind that I merely typed this into the editor here at CP, so it will probably require a tweak or two to actually compile/run.
The way the code is written in my version, it would be a simple matter to move the code to a console app so it's easier yto debug.
Final note - according to your code, you execute the automate method every three minutes but only process the data on wednesdays and fridays - evry three minutes. I'm almost positive that's not what you really want.
public class MyService...
{
Thread m_thread = null;
public void OnStart()
{
m_thread = new Thread(new ThreadStart(ThreadProc));
m_thread.Start();
}
public void ThreadProc()
{
int interval = 30000;
int elapsed = 0;
int waitTime = 1000;
try
{
while (true)
{
if (interval >= elapsed)
{
elapsed = 0;
automate();
}
Thread.Sleep(waitTime);
elapsed += waitTime;
}
}
catch (ThreadAbortException)
{
}
}
private bool automate()
{
bool automated = false;
DateTime now = dateTime.Now;
if (now.DayOfWeek == DayOfWeek.Wednesday &&
now.DayOfWeek == DayOfWeek.Wednesday)
{
MakeFileFromDatabase();
}
}
private void MakeFileFromDatabase()
{
DataSet d = null;
try
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.AppSettings["Connection"];
SqlCommand cmd = new SqlCommand("select rs.contactID, SurveyType, ModuleName, " +
"LogDate from tblReportLOg rs inner join " +
"tblContact c on c.contactID = rs.contactID " +
"where LogDate >= '2011-01-20 00:00:00.000' " +
"and c.email like '%micro%' and rs.contactid " +
"not in (39287,39286,27546) order by LogDate ",
conn);
SqlCommand cmd1 = new SqlCommand(ReportCount, conn);
SqlCommand cmd2 = new SqlCommand(UserCount, conn);
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(cmd);
d = new DataSet();
mySqlDataAdapter.Fill(d, "dataset");
conn.Open();
string reports = Convert.ToString(cmd1.ExecuteScalar());
string users = Convert.ToString(cmd2.ExecuteScalar());
MakeExcelFile(d);
}
catch (Exception ex)
{
throw new Exception("Exception will retrieveing Data", ex);
}
finally
{
conn.Close();
}
}
private void MakeExcelFile(DataSet d)
{
try
{
Microsoft.Office.Interop.Excel._Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
int sheetIndex = 0;
foreach (System.Data.DataTable dt in d.Tables)
{
object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
for (int col = 0; col < dt.Columns.Count; col++)
{
rawData[0, col] = dt.Columns[col].ColumnName;
}
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
}
}
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring((dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring((dt.Columns.Count - 1) % colCharsetLen, 1);
Excel.Worksheet excelSheet = (Excel.Worksheet)excelWorkbook.Sheets.Add
(excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1,
Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
excelSheet.Name = dt.TableName;
string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);
excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
Excel.Range rg = excelSheet.get_Range("D:D", Type.Missing);
rg.NumberFormat = "MM-DD-YYYY hh:mm";
}
string fileName = MakeFileName()
excelWorkbook.SaveCopyAs(fileName);
excelWorkbook.Close(true, Type.Missing, Type.Missing);
excelWorkbook = null;
excelApp.Quit();
excelApp = null;
}
catch (Exception ex)
{
throw new Exception("Exception while creating exel file", ex);
}
}
private string MakeFilename()
{
string fileExcel = "t" + nRandom.Next().ToString() + ".xls";
string filepath = "C:\\test";
filename = "C:\\Test\\test.xlsx";
string renamedfile = string.Format("{0}_{1}{2}",
System.IO.Path.GetFileNameWithoutExtension(filename),
DateTime.Now.ToString("yyyyMMdd_HHmm"),
System.IO.Path.GetExtension(filename));
return renamedfile;
}
}
Submit an article or tip