Before creating this application we need two databases. Here I’ve named them
“db1” and “db2”.
Firstly, create “db1” & in that create table tblTest.
Now create another database “db2” in that create a table tblTest (here I have use
same name for table in both the databases.
Code for Windows Service
using System.Data.SqlClient;
SqlConnection con1, con2;
protected override void OnStart(string[] args)
{
//creating and opening connection to both the database
con1= new SqlConnection("server=ABC\\SQLEXPRESS; database=db1;uid=sa; password=sa");
con1.Open();
con2= new SqlConnection("server=ABC\\SQLEXPRESS; database=db2;uid=sa; password=sa");
con2.Open();
//enabling, setting interval and starting timer.
//(Note: don’t use the timer in ToolBox. Add another Timer control in ToolBox
//having namespace “System.timer.Timer” and use that timer control)
timer1.Enabled= true;
timer1.Interval= 10000;
timer1.Start();
}
protected override void OnStop()
{
//stoping timer and closing connection to both the databases.
timer1.Stop();
con1.Close();
con2.Close();
}
//code for timer elapsed event which is fired on the interval set for the
//timer. Here it is 10 secs.
private void timer1_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
SqlCommand cmd;
//creating DataAdapter for connection one(db1)
SqlDataAdapter da = new SqlDataAdapter("Select * from tblTest where [Check]='False'", con1);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
//creating and populating table with records in database
DataTable dt = new DataTable();
da.Fill(dt);
int i;
//loop for each row in table.
foreach (DataRow dr in dt.Rows)
{
//inserting values in db2
cmd = new SqlCommand("Insert into tblTest values('" + dr[0].ToString() + "','" + dr[1].ToString() + "','" + dr[2].ToString() + "')", con2);
i = cmd.ExecuteNonQuery();
//setting the value of “Check” column to true as it is updated in the other
//database
dr[3]= true;
}
//updating the data adapter, so that the changes made to the record of first
//database(db1), reflects in original table of db1.
da.Update(dt);
cmd.Dispose();
dt.Dispose();
da.Dispose();
}
Ouissal Boubechri
20-Mar-2020Hello,
i want to create windows service to load text file into sql server
Rahul Roi
19-Oct-2019Very Nice Article Sir.
Anonymous User
13-Oct-2011It's really useful for me.
Thanks