Hi There,
I am very very new to C#. I need to create a Windows Service, which should check my sql server database for every 30 seconds, and it should do some calculations on some table and insert them in some other table.
I have the C# code to do those calculations..but I am not understanding where to write this code.
should I write the code in On start Event or In timer1_Tick..?
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Data.SqlClient;
namespace CalculatingService
{
public partial class MyService : System.ServiceProcess.ServiceBase
{
public MyService()
{
InitializeComponent();
}
protected override void OnStart(string[] args)
{
timer1.Enabled = true;
timer1.Start();
protected override void OnStop()
{
this.timer1.Enabled = false;
}
private void timer1_Tick(object sender, EventArgs e)
{
}
}
}
Here is My c# code to do the calculations
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=Corp-sqldv05\\Instance1;Initial Catalog=DW;User ID=admin;password=aha!";
con.Open();
string dealQry = "select *, month(BeginDate) as beginmonth, datediff(month,BeginDate,dateadd(month,term,BeginDate))as noofmonths, dateadd(month,term,BeginDate) as enddate from deal where needscalc = 1";
SqlDataAdapter adp = new SqlDataAdapter(dealQry, con);
DataSet ds = new DataSet();
adp.Fill(ds);
string cashquery;
SqlCommand cashdltComd = new SqlCommand();
SqlCommand cashIntComd = new SqlCommand();
cashdltComd.Connection = con;
cashIntComd.Connection = con;
double acc_401011, acc_402011, acc_1650011, acc_184019, acc_407011 = 0, acc_900009, acc_900003;
foreach (DataRow dr in ds.Tables[0].Rows)
{
cashquery = "Delete from cashflow where account not in ('401012','412015') and dealid = " + dr["dealid"].ToString();
cashdltComd.CommandText = cashquery;
cashdltComd.ExecuteNonQuery();
acc_401011 = (Convert.ToDouble(dr["BaseRent_PSF"].ToString()) * Convert.ToDouble(dr["SQFT"].ToString())) / 12;
acc_402011 = (Convert.ToDouble(dr["Abate_PSF"].ToString()) * Convert.ToDouble(dr["SQFT"].ToString())) / 12;
acc_1650011 = Convert.ToDouble(dr["TIOnly_PSF"].ToString()) * Convert.ToDouble(dr["SQFT"].ToString());
acc_184019 = Convert.ToDouble(dr["LC_PSF"].ToString()) * Convert.ToDouble(dr["SQFT"].ToString());
if (dr["GrossNet"].ToString().Trim().Equals("N"))
acc_407011 = (Convert.ToDouble(dr["OE_PSF"].ToString()) * Convert.ToDouble(dr["SQFT"].ToString())) / 12;
else if (dr["GrossNet"].ToString().Trim().Equals("G"))
acc_407011 = (Convert.ToDouble(dr["EI_PSF"].ToString()) * Convert.ToDouble(dr["SQFT"].ToString())) / 12;
acc_900009 = Convert.ToDouble(dr["SQFT"].ToString());
acc_900003 = Convert.ToDouble(dr["SQFT"].ToString());
DateTime dt2009 = new DateTime(2009, 01, 01);
DateTime dt2010 = new DateTime(2010, 01, 01);
DateTime begndate = Convert.ToDateTime(dr["BeginDate"].ToString());
DateTime enddate = Convert.ToDateTime(dr["enddate"].ToString());
int abtmonth = Convert.ToInt32(dr["AbateMos"].ToString());
DateTime abtdate;
if (abtmonth >= 1)
abtdate = begndate.AddMonths(abtmonth );
else
abtdate = new DateTime(2007, 01, 01);
int abtend, abtbgn;
if (begndate < dt2009)
{
if (enddate > dt2009)
{
if (enddate.Year == 2009)
{
abtbgn = 1;
if (abtdate.Year > 2009)
abtend = 12;
else if (abtdate.Year == 2009)
abtend = abtdate.Month;
else
{
abtend = 0;
abtbgn = 0;
}
cashIntComd.CommandText = CreateInstCmd(Convert.ToInt32(dr["dealid"].ToString().Trim()), 2, "401011", 1, enddate.Month, acc_401011);
cashIntComd.ExecuteNonQuery();
cashIntComd.CommandText = CreateInstCmd(Convert.ToInt32(dr["dealid"].ToString().Trim()), 2, "402011", abtbgn, abtend, acc_402011);
cashIntComd.ExecuteNonQuery();
cashIntComd.CommandText = CreateInstCmd(Convert.ToInt32(dr["dealid"].ToString().Trim()), 2, "1650011", begndate.Month, begndate.Month, acc_1650011);
cashIntComd.ExecuteNonQuery();
cashIntComd.CommandText = CreateInstCmd(Convert.ToInt32(dr["dealid"].ToString().Trim()), 2, "184019", begndate.Month, begndate.Month, acc_184019);
cashIntComd.ExecuteNonQuery();
cashIntComd.CommandText = CreateInstCmd(Convert.ToInt32(dr["dealid"].ToString().Trim()), 2, "407011", 1, enddate.Month, acc_407011);
cashIntComd.ExecuteNonQuery();
cashIntComd.CommandText = CreateInstCmd(Convert.ToInt32(dr["dealid"].ToString().Trim()), 2, "900009", 1, enddate.Month, acc_900009);
cashIntComd.ExecuteNonQuery();
cashIntComd.CommandText = CreateInstCmd(Convert.ToInt32(dr["dealid"].ToString().Trim()), 2, "900003", 1, enddate.Month, acc_900003);
cashIntComd.ExecuteNonQuery();
abtend = 0;
public String CreateInstCmd(int dealid, int year, string acct, int begn, int end, double amt)
{
string instQuery = "insert into cashflow(DealID, Account, year, January, february, march, april, may, june, july, august, september, october, november, december ) values (";
instQuery = instQuery + dealid.ToString() + "," + acct.ToString() + "," + year.ToString();
for (int i = 1; i <= 12; i++)
{
if (i >= begn && i <= end)
instQuery = instQuery + "," + amt.ToString();
else
instQuery = instQuery + ",0";
}
instQuery = instQuery + ")";
return instQuery;
}
Can Any One Please Help me..How to create windows Service. I am a beginer in windows Service. I dont have any Idea, what it is..It is very Urgent Please Help Me...
Thanks
