articles

Home / DeveloperSection / Articles / Copy Data from one table to another Table using Windows Service in C#

Copy Data from one table to another Table using Windows Service in C#

AVADHESH PATEL27399 17-Jan-2013

In this article I have described, how to copy data from one table to another table using windows service with the help of timer control in C#.  Data copy from one table to another table after a certain intervals of times (intervals are feature of Timer Control). Below I have described all steps (Creating windows service, installing/uninstalling windows services, using of timer control etc.).Creating Windows Service

Step 1: Open visual studio and select windows service application as below image. For demonstration I have used Visual Studio 2010 with 4.0 frameworks and SQL Server 2008. If you like to use another framework version, don’t worry, follow the same step.

Copy Data from one table to another Table using Windows Service in C#

1.       Click on Windows tag.

2.       Select Windows Service option.

3.       Give the name of application. Here I have given name “WindowsServiceDemo”of my application.

4.       Press button “OK” and move next steps;

Step 2: When you press button OK, you see as below window.

Copy Data from one table to another Table using Windows Service in C#

Step 3: Open “Service1.cs” as below. This .cs file contain two method “OnStart()” and “OnStop()”. These two methods used for start and stop windows service.

Copy Data from one table to another Table using Windows Service in C#

Step 4: For demonstration first we create two tables and put some data into first table. Use below SQL query for creating table and inserting data.


CREATE DATABASE WindowsServiceDemo
GO
 
USE WindowsServiceDemo
GO
 
CREATE TABLE [dbo].[EmployeeStatus]
(
[EmpID] INT IDENTITY PRIMARY KEY,
[EmpName] VARCHAR(50) NOT NULL,
[IsActive] BIT NOT NULL
)
GO
 
CREATE TABLE [dbo].[xEmployeeStatus]
(
[SNo] INT IDENTITY PRIMARY KEY,
[EmpID] INT NOT NULL,
[EmpName] VARCHAR(50) NOT NULL
)
GO
 
INSERT [dbo].[EmployeeStatus]([EmpName],[IsActive]) VALUES('Mark Alderson','True'), ('Arnold','False'), ('Jems','False')

 Check your table, my table as below Image.

Copy Data from one table to another Table using Windows Service in C#

Note: See above image, “IsActive” column (in red rectangular) have true (1) and false (0) value. In the next steps I have described how to copy rows from “EmployeeStatus” table which column (IsActive) values are false and paste into “xEmployeeStatus” table. If data transfer successfully then false status become true of “EmployeeStatus” table.

Step 5:  Add “System.Timers” namespace and write below code, before OnStart() method.

Timer timer1 = new Timer();

 Write below code within “OnStart()” method (Step 3).

timer1.Elapsed += new ElapsedEventHandler(timer1_Elapsed);

            timer1.Interval = 10000;
            timer1.Enabled = true;
            timer1.Start();

 Note: 10000 millisecond = 10 Seconds. You can change timer intervals according your requirement.

Step 6:  Now, write below code within OnStop() method.

timer1.Enabled = false;

 Step 7: Used below line of code for creating “timer1_Elapsed” event.

private void timer1_Elapsed(object sender, EventArgs e)

        {
            SqlConnection con = new SqlConnection("Data Source = Your database service name; Initial Catalog = WindowsServiceDemo; User Id= sa; Password= password;");
            SqlCommand cmd1;
            SqlCommand cmd2;
            SqlDataAdapter adap = new SqlDataAdapter("Select [EmpID],[EmpName] From [dbo].[EmployeeStatus] Where [IsActive]='False'", con);
            DataTable dt = new DataTable();
            adap.Fill(dt);
            con.Open();
            foreach (DataRow dr in dt.Rows)
            {
 
                cmd1 = new SqlCommand("Insert into [dbo].[xEmployeeStatus]([EmpID],[EmpName]) values('" + dr[0].ToString() + "','" + dr[1].ToString() + "')", con);
                if (cmd1.ExecuteNonQuery() > 0)
                {
                    cmd2 = new SqlCommand("Update [dbo].[EmployeeStatus] Set [IsActive] = 'True' Where [IsActive]='False'", con);
                    int i = cmd2.ExecuteNonQuery();
                }
            }
            con.Close();
            dt.Dispose();
            adap.Dispose();
        }

 Note: Used “System.Data” and “System.Data.SqlClient” namespace and remove unnecessary namespaces.   You can see complete line of code which described below.

using System;

using System.Data;
using System.ServiceProcess;
using System.Timers;
using System.Data.SqlClient;
 
namespace WindowsServiceDemo
{
    public partial class Service1 : ServiceBase
    {
        public Service1()
        {
            InitializeComponent();
        }
 
        Timer timer1 = new Timer();
 
        protected override void OnStart(string[] args)
        {
            timer1.Elapsed += new ElapsedEventHandler(timer1_Elapsed);
            timer1.Interval = 10000;
            timer1.Enabled = true;
            timer1.Start();
        }
 
        protected override void OnStop()
        {
            timer1.Enabled = false;
        }
 
        private void timer1_Elapsed(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source = Your database service name; Initial Catalog = WindowsServiceDemo; User Id= server ; Password= password;");
            SqlCommand cmd1;
            SqlCommand cmd2;
            SqlDataAdapter adap = new SqlDataAdapter("Select [EmpID],[EmpName] From [dbo].[EmployeeStatus] Where [IsActive]='False'", con);
            DataTable dt = new DataTable();
            adap.Fill(dt);
            con.Open();
            foreach (DataRow dr in dt.Rows)
            {
 
                cmd1 = new SqlCommand("Insert into [dbo].[xEmployeeStatus]([EmpID],[EmpName]) values('" + dr[0].ToString() + "','" + dr[1].ToString() + "')", con);
                if (cmd1.ExecuteNonQuery() > 0)
                {
                    cmd2 = new SqlCommand("Update [dbo].[EmployeeStatus] Set [IsActive] = 'True' Where [IsActive]='False'", con);
                    int i = cmd2.ExecuteNonQuery();
                }
            }
            con.Close();
            dt.Dispose();
            adap.Dispose();
        }
    }
}

 

Step 7: Save and build application. In next steps I tell you how to install web service. Now double click on “Service1.cs” note (red circle in below image).

Copy Data from one table to another Table using Windows Service in C#

Window looks as below, and right clicks on this window for adding windows service installer.

Copy Data from one table to another Table using Windows Service in C#

Step 8: After clicking “Add Installer” option “ProjectInstaller.cs” file are display. This file contains two setting option “serviceProcessInstaller1” and “serviceInstaller1” as below image.

Copy Data from one table to another Table using Windows Service in C#

Step 9: Right click on “serviceProcessInstaller1” and select properties option.  Set “Account” properties as “LocalSystem”.

Copy Data from one table to another Table using Windows Service in C#

Step 10: Right click on “serviceInstaller1” and select properties option.  Give the “DisplayName” and “ServiceName” properties name, WindowsSerrviceDemo and set “StartType” properties as “Automatic” as given below image.

Copy Data from one table to another Table using Windows Service in C#

Installing Windows Service

Step 11: Now build your application and move for installation steps. Windows Service installed by Visual Studio command prompt.  Run Visual Studio command prompt as administrator.

Copy Data from one table to another Table using Windows Service in C#

Step 12: Move up to your application debug folder in Command Prompt as below image.

Copy Data from one table to another Table using Windows Service in C#

Step 13: Write installutil and .exe file name from debug folder and press enter button.  See below image.

Copy Data from one table to another Table using Windows Service in C#

Start Windows Service

Step 14: Your windows service are install successfully. Now time to start windows service. Open computer management where we start and stop windows service. Type compmgmt.msc in run prompt and press enter key.  Computer Management window are opened as below image, and check your ServiceName which you given in step 10.

Copy Data from one table to another Table using Windows Service in C#

1.       Select Services tag.

2.       Search you service name.

3.       Click to start application

Step 15: Check your tables, you find both tables are updated as below image.

Copy Data from one table to another Table using Windows Service in C#

Uninstall Windows Service

Step 16: you can uninstall windows service using “installuntil /u finemane.exe” as below image.

Copy Data from one table to another Table using Windows Service in C#


Updated 13-Dec-2019
Avadhesh Kumar Patel District Project Manager - Aligarh 14 months work experience in Panchayati Raj Department Sector as District Project Manager & 12 months work experience in IT Sector as Software Engineer. :-)

Leave Comment

Comments

Liked By