Home > DeveloperSection > Articles > Windows Service to update record from one database to another after certain interval of time

Windows Service to update record from one database to another after certain interval of time


Windows Services Windows Services 
Ratings:
1 Comment(s)
 13801  View(s)
Rate this:

Windows Service to update record from one database to another after certain interval of time.


This service will update records from one database (db1) to another database (db2)

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();

        }

 

Windows service is now created and can be executed for the task.

 


Windows Service to update record from one database to another after certain interval of time.

By Arun Singh on   5 years ago
Hi Haider,
It's really useful for me.

Thanks

Don't want to miss updates? Please click the below button!

Follow MindStick