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.
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.
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.
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.
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).
Window looks as below, and right clicks on this window for adding windows service installer.
Step 8: After clicking “Add Installer” option “ProjectInstaller.cs” file are display. This file contains two setting option “serviceProcessInstaller1” and “serviceInstaller1” as below image.
Step 9: Right click on “serviceProcessInstaller1” and select properties option. Set “Account” properties as “LocalSystem”.
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.
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.
Step 12: Move up to your application debug folder in Command Prompt as below image.
Step 13: Write installutil and .exe file name from debug folder and press enter button. See below image.
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.
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.
Uninstall Windows Service
Step 16: you can uninstall windows service using “installuntil /u finemane.exe” as below image.
Vimalkumar P
23-Jun-2016bharath reddy
18-Oct-2015