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.

 

  Modified On Nov-17-2017 02:47:49 PM

Leave Comment