Search This Blog

Saturday 31 March 2018

Windows services in c#


Step 1: - Open Visual Studio 2015 => Goto File Menu => New => Project...

Step 2: - In the Installed Templates list, select Visual C# => Expand Windows => Select Classic Desktop => Select Windows Service from middle pane=> Type WindowsService in the Name box and click OK


Step 3: - Open Service1.cs => Right Click Empty area =>Click View Code or Click link “click here to switch to code view




Complete Code

using System;
using System.ServiceProcess;
using System.Timers;

namespace WindowsService
{
    public partial class Service1 : ServiceBase
    {
        Timer timer = null;
        public Service1()
        {
            InitializeComponent();
        }
        protected override void OnStart(string[] args)
        {
            Console.WriteLine("Timer Started " + DateTime.Now.ToString());

            timer = new Timer();
            timer.Interval = 1000;
            timer.Elapsed += Timer_Elapsed;
            timer.Enabled = true;
        }
        private void Timer_Elapsed(object sender, ElapsedEventArgs e)
        {
            Console.WriteLine("Timer_Elapsed is called " + DateTime.Now.ToString());
        }
        protected override void OnStop()
        {
            timer.Enabled = false;
            Console.WriteLine("Timer Stopped " + DateTime.Now.ToString());
        }
    }
}

Step 4: - Open Service1.cs => Right Click Empty area => Click Add Installer



Step 5: - Right Click ServiceInstaller1 => Click Properties


If you wish, change the ServiceName. In this project I am not changing the ServiceName.

Step 6: - Right Click serviceProcessInstaller1=> Click Properties => Change Account User to LocalService


Step 7: - Right Click WindowsService Project=> Click Build

Window Service created successfully. Let’s create installer for service.

Step 1: - Right Click on Solution ‘WindowsService’ (1 project) => Add => New Project… => Select Other Project Types from left pane=> Select Visual Studio Installer => Select Setup Project from middle pane=> Type WindowsService in the Name box and click OK




Note: -

If Visual Studio Installer template missing in your visual studio 2015. Click link to download Visual Studio Installer.

Step 2: - Right Click WindowsServiceSetup => Add => Click Project Output… => Select Primary output => Click OK




Step 2: - Right Click WindowsServiceSetup => View => Click Custom Actions



Step 3: - Right Click Custom Actions => Click Add Custom Action… => Open Application Folder => Select Primary output from WindowsService (Active) => Click OK





Step 4: - Right Click WindowsServiceSetup => Click Build

Setup is ready to install.

Step 1: - Open Setup folder i.e. reside in WindowsServiceSetup project Debug folder => Click setup.exe to install it.

Step 2: - Open Services windows type services.msc in search box and press Enter


Step 3: - Select Service1 from list => Click Start

Add Done


To Run Scheduler on specified time do following changes in your code.

using System;
using System.ServiceProcess;
using System.Timers;

namespace WindowsService
{
    public partial class Service1 : ServiceBase
    {
        Timer timer = null;
        DateTime scheduledTime = DateTime.MinValue;

        public Service1()
        {
            InitializeComponent();
            // Set Scheduler to run every day at 07:00 am
            scheduledTime = DateTime.Parse("07:00");
        }
        protected override void OnStart(string[] args)
        {
            Console.WriteLine("Timer Started " + DateTime.Now.ToString());

            timer = new Timer();
            timer.Interval = 1000;
            timer.Elapsed += Timer_Elapsed;
            timer.Enabled = true;
        }
        private void Timer_Elapsed(object sender, ElapsedEventArgs e)
        {
            if (DateTime.Now > scheduledTime)
            {
                Console.WriteLine("Timer_Elapsed is called " + DateTime.Now.ToString());

                // Set Scheduler to run next day at 07:00 am
                scheduledTime = scheduledTime.AddDays(1);
            }
        }
        protected override void OnStop()
        {
            timer.Enabled = false;
            Console.WriteLine("Timer Stopped " + DateTime.Now.ToString());
        }
    }
}

Friday 30 March 2018

Export DataTable to excel c#


Step 1: - Open Visual Studio 2015 => Goto File Menu => New => Project...

Step 2: - In the Installed Templates list, select Visual C# => Windows

Step 3: - Select Console Application from the Windows list => Type ExportToExcel in the Name box and click OK

Step  4:- Right Click on References => Click on Add References… => Expand COM link from left pane => Select Type Libraries =>  Checked Microsoft Excel 12.0 Object Library check box from middle pane => Click OK     
    

Step 5:- Copy Past Code in Program.cs

using System;
using System.Data;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExportToExcel
{
    class Program
    {
        protected static DataTable GeTable()
        {
            DataTable dt = new DataTable();
            DataRow dr;

            dt.Columns.Add(new DataColumn("Column1", typeof(string)));
            dt.Columns.Add(new DataColumn("Column2", typeof(string)));
            dt.Columns.Add(new DataColumn("Column3", typeof(string)));
            dt.Columns.Add(new DataColumn("Column4", typeof(string)));

            int totalRow = 10;

            for (int i = 0; i < totalRow; i++)
            {
                dr = dt.NewRow();

                dr["Column1"] = "Row" + i;
                dr["Column2"] = "Row" + i;
                dr["Column3"] = "Row" + i;
                dr["Column4"] = 1000 / (i + 1);

                dt.Rows.Add(dr);
            }

            return dt;
        }

        static void Main(string[] args)
        {
            try
            {
                Excel.Application objExcelApplication;
                Excel.Workbook objExcelWorkbook;
                Excel.Worksheet objExcelWorksheet;
                object misValue = Missing.Value;

                objExcelApplication = new Excel.Application();
                objExcelWorkbook = objExcelApplication.Workbooks.Add(misValue);
                objExcelWorksheet = (Excel.Worksheet)objExcelWorkbook.Worksheets.get_Item(1);

                //Get Data
                DataTable dataTable = GeTable();
                if (dataTable != null)
                {
                    if (dataTable.Rows.Count > 0)
                    {
                        int colCount = dataTable.Columns.Count;
                        int rowCount = dataTable.Rows.Count;

                        // Write Excel Column
                        for (int i = 0; i < colCount; i++)
                        {
                            objExcelWorksheet.Cells[1, (i + 1)] = dataTable.Columns[i].ToString();
                        }
                        // Write Excel Rows
                        for (int i = 0; i < rowCount; i++)
                        {
                            for (int j = 0; j < colCount; j++)
                            {
                                objExcelWorksheet.Cells[(i + 2), (j + 1)] = dataTable.Rows[i][j];
                            }
                        }
                    }
                }

                string fileName = string.Format("{0}{1}.{2}", "ExcelRpt", DateTime.Now.ToString("ddMMyyyy hh_mm_ss tt"), "xls");
                string savePath = AppDomain.CurrentDomain.BaseDirectory + fileName;

                objExcelWorkbook.SaveAs(savePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                objExcelWorkbook.Close(true, misValue, misValue);
                objExcelApplication.Quit();

                Marshal.ReleaseComObject(objExcelWorksheet);
                Marshal.ReleaseComObject(objExcelWorkbook);
                Marshal.ReleaseComObject(objExcelApplication);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

Output


Note: - Excel file will be creating inside the project bin\Debug folder.