Friday, August 3, 2012

Deleting Files with a SQL Job in SQL Server-2008



Suppose you want to delete some files from a directory. This could be one of the easiest task for anyone who is familiar with windows. But what if we want to delete the files in a regular interval, to do this we have some options like:
·         By a windows service that keeps on deleting files in a regular interval.
·         By a SQL Job (Taking advantage of SQL Server Agent Service) that deletes files in a certain interval.

Here I am going to discuss “Deleting files through SQL Job”. In SQL job, you need to put some SQL query in your SQL Job Steps.

In the SQL query to delete the files, you need to call a predefined stored procedure “xp_cmdshell”. But before calling xp_cmdshell”, we also need to enable the Command Shell of your SQL Server if it is not enabled in your system.

Here is the full SQL query that is used for deleting the files.

[Note: 
To know “How to create SQL job”, you can go to my article in www.c-sharpcorner.com site at:


----------------------------------- Enabling the COMMAND SHELL ------------------------------

--Script to enable the XP_CMDSHELL
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
----------------------------------------------------------------------------------------------

----------------------------------- Deleting the files in ------------------------------------
-- Suppose we need to delete all .txt files under "D:\temp\Testing\"

xp_cmdshell 'DEL D:\temp\Testing\*.txt'

----------------------------------------------------------------------------------------------

Adding a Setup Project in Visual Studio-2010

  1. Add a setup project in your project solution by
Right Click on Solution -> Add -> New project
  1. Add New Project window opens. Select Setup Project by
Other Project Types -> Setup and Deployment -> Visual Studio Installer
  1. Give some name to your setup project and click OK
  2. Setup Project gets added into your project solution.
  3. Right click on your setup project
  4. Go to Add option and then Project Output
  5. Select Primary Output of in the list box and select your application in the top combo box. Now click OK.
  6. Again right click on your setup project and Go to View option and then Custom Actions
  7. Now Custom Actions window gets opened. Right click the Custom Actions and then Add Custom Action…
  8. A window opens, now you have to double click Application Folder option. Doing this , you get to see Primary output from “Your Project”. Select that option and click OK
  9. You would see that primary output of your project gets added in Install, Commit, Rollback and Uninstall option.
  10. Make sure that your setup project is got added into your project solution.  Right click the project solution and open the property, go to the Configuration and select the Build option
  11. Build the whole project.
  12. Now you can install your application by right click the Setup project and choose Install option.
  13. Your app gets installed successfully! You can reach its exe in the default path in your setup. (Generally default path is like…C:\Program Files\YourCompanyName\)
Thanks

Thursday, August 2, 2012

Bubbling up events example in winform c#

Suppose you have application (WinForm) in which you like to see the intermediate progress and status of a time taking process on the GUI of the Main Form. This Time taking process is running in a separate DLL. As the process progresses, the DLL sends current status/progress to Calling App (i.e. WinForm app).
In the GUI of the Calling app, progress and current status text of the process is displayed on Progress Bar as shown below:


Let us see how we can do this in a very simple way:
  • Create a Main Application (WinForm app) as above with a “Start Process” button that is used for starting the process in my DLL. Add “StatusStrip” control too in the form. In StatusStrip, add ProgressBar (inbuilt) and StatusLable controls (inbuilt)
  • Create a DLL (Class Library project) with a class named “MyClass”. Add the reference of this DLL into your Main Application.
  • Now create a public delegate to raise the Status event to show the intermediate processing status on  GUI in the Main Form as:
public delegate void MyDLL_InternalStatus(int progress, string statustext);


In this delegate, we are specifying the parameters:
progress variable is used for storing current percentage progress of the process.
Statustext variable is used for storing the current status message related to process.


  • In “MyClass” class, Add a method TimeConsumingProcess() that simulates a process taking a lot of time. TimeConsumingProcess() is built up with smaller several tasks such as Task-1, task-2 etc. Assume each task takes some significant amount of time (to simulate the time consumption, I used Thread.Sleep() method)
Now we want to send current status to calling application. In order to do this, we call MyClassStatusEvent() after the ending of each task.


e.g. MyClassStatusEvent(10, "Task-1 is started. Task-1 is in process...");


  • Now add a reference to your DLL into your Main Application project.  Then create an object of the “MyClass” and subscribe its event as:
// MyClass object is created
oMyClass = new MyClassLibrary.MyClass();


// MyClass's event is subscribed here
oMyClass.MyClassStatusEvent +=
new MyClassLibrary.MyDLL_InternalStatus (oMyClass_MyClassStatusEvent);


                This subscription will require an event handler, which we specify like this:
// Event Handler of MyClass's event
        void oMyClass_MyClassStatusEvent(int progress, string statustext)
        {
            toolStripProgressBar.Value = progress;        // Setting ProgreeBar Value
            toolStripStatusLabel.Text = statustext;       // Setting Status Text


            // To refresh the GUI simultaneously as the background process progresses
            Application.DoEvents();                      
        }


So, the whole code of class “MyClass” looks like:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;


namespace MyClassLibrary
{
    // Created a public delegate in the same namespace but outside of the class "MyClass"
    // in order to raise the Status event to show the intermediate processing status on
    // GUI in the Main Form
    public delegate void MyDLL_InternalStatus(int progress, string statustext);


    public class MyClass
    {
        // Created a public event
        public event MyDLL_InternalStatus MyClassStatusEvent;


        public void TimeConsumingProcess()
        {
            // event firing
            MyClassStatusEvent(10, "Task-1 is started. Task-1 is in process...");
           
            Task1();
            MyClassStatusEvent(20, "Task-1 is finished. Task-2 is in process...");
           
            Task2();
            MyClassStatusEvent(40, "Task-2 is finished. Task-3 is in process...");
           
            Task3();
            MyClassStatusEvent(60, "Task-3 is finished. Task-4 is in process...");
           
            Task4();
            MyClassStatusEvent(80, "Task-4 is finished. Task-5 is in process...");
           
            Task5();
            MyClassStatusEvent(100, "Task-5 is finished. All tasks completed sucessfully!");
        }


        // Suppose Task1 takes 2 seconds
        private void Task1()
        {
            Thread.Sleep(1000);
        }


        // Suppose Task2 takes 2 seconds
        private void Task2()
        {
            Thread.Sleep(2000);
        }


        // Suppose Task3 takes 4 seconds
        private void Task3()
        {
            Thread.Sleep(4000);
        }


        // Suppose Task4 takes 3 seconds
        private void Task4()
        {
            Thread.Sleep(3000);
        }


        // Suppose Task5 takes 2 seconds
        private void Task5()
        {
            Thread.Sleep(2000);
        }
    }
}


And the code of the Calling application’s main form (“MyForm”) will look like:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;


namespace EventBubblerDemo
{
    public partial class MyForm : Form
    {
        // Created an object of the Dll
        MyClassLibrary.MyClass oMyClass;


        public MyForm()
        {
            InitializeComponent();


            // MyClass object is created
            oMyClass = new MyClassLibrary.MyClass();


            // MyClass's event is subscribed here
            oMyClass.MyClassStatusEvent += new MyClassLibrary.MyDLL_InternalStatus(oMyClass_MyClassStatusEvent);
        }


        // Event Handler of MyClass's event
        void oMyClass_MyClassStatusEvent(int progress, string statustext)
        {
            toolStripProgressBar.Value = progress;        // Setting ProgreeBar Value
            toolStripStatusLabel.Text = statustext;       // Setting Status Text


            // To refresh the GUI simultaneously as the background process progresses
            Application.DoEvents();                      
        }




        /// <summary>
        /// Start Button Click Method
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_Start_Click(object sender, EventArgs e)
        {
            oMyClass.TimeConsumingProcess();
        }
    }
}