?
Solved

polling database

Posted on 2012-09-22
9
Medium Priority
?
563 Views
Last Modified: 2012-10-09
i want to create a windows service where i have a task on database to select top 100 record to process it when i fnish sleep 30 second than repeat the task again to ensure if i have new record on the database ?? the task should do a job than sleep than re invoke it

my service should always select for new update on database

who i can do if u can write me a code plz i am beginner and i need your challenge :)
0
Comment
Question by:afifosh
  • 5
  • 4
9 Comments
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 38424907
Why don't you use a trigger?

Create your trigger to watch a table for changes and then code it to do whatever you need with the new records.
0
 
LVL 60

Accepted Solution

by:
Julian Hansen earned 2000 total points
ID: 38424915
Something like this (copied from here http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27871565.html)
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for trigger here

END
GO

Open in new window

0
 
LVL 1

Author Comment

by:afifosh
ID: 38424925
i know this , but my problem when new field are inserted on the table and i use this trigger it\s work !!  but i want when i insert new value for example


select data from the same table and POST it to a url page ?? can i do it on trigger?
if i can please write it but not in general
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 60

Expert Comment

by:Julian Hansen
ID: 38424980
select data from the same table and POST it to a url page ?? can i do it on trigger?
I am having trouble understanding you - what do you mean POST to a URL page? You did not mention this in your original post.

When you process the data - what do you do with it? Put it back in the database?

You can also use xp_cmdshell but read this first (http://stackoverflow.com/questions/1162663/how-to-run-a-program-from-sql) - specifically look at the 4th answer (not counting the original post)
0
 
LVL 1

Author Comment

by:afifosh
ID: 38424995
http://127.0.0.1:8800/?PhoneNumber=@msisdn&Text=@msg

i can\t do it using trigger

when it\s posted and return a success from http
i will change the status of ther record .. to done !

than insert on report the result when i catch any error during post
0
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 38425144
I am sorry but you still have not provided enough information about what it is you are doing - I have a rough idea but could you explain the process completely.
0
 
LVL 1

Author Comment

by:afifosh
ID: 38425156
fetch record from database
process record
sql query update,insert on same record
select data from fetched row and post data into a web page
update record


repeat the following jobs
so i want to make a  windows service can do the following task !
using like thread timer

when the thread finish
wait sleep 30 second
than re do the same thread again... till infinity...

table always have new record

to know what is the new record to fetched we have a flag if is equal to 0 should be do this task if equal to 1 we have already do this task so u will ignore.
0
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 38425198
Ok but which part of this are you having trouble with?

It seems as though you are asking someone to develop this code or point you to an already written app?

What is missing from the Code Project example you posted earlier?

The problem with the question is it is too open ended - to help you you need to narrow it down to say what it is you need help with.
0
 
LVL 1

Author Comment

by:afifosh
ID: 38425216
Imports System.Threading
i need a class or a sample code where i make a funtion
and i call it using thread
and after it finish
sleep 30 or 40 or 60 second
than re do this function again ...

i have view this code but i don\t know if he help me ?

namespace SMSPicker
{
 public partial class SMSPicker : ServiceBase{
    SendSMS smsClass;
    AutoResetEvent autoEvent;
    TimerCallback timerCallBack;
    Timer timerThread;
    public SMSPicker()
    {
        InitializeComponent();
    }

    protected override void OnStart(string[] args)
    {
        // TODO: Add code here to start your service.
        smsClass = new SendSMS();
        autoEvent = new AutoResetEvent(false);
        long timePeriod = string.IsNullOrEmpty(ConfigurationSettings.AppSettings["timerDuration"]) ? 10000 : Convert.ToInt64(ConfigurationSettings.AppSettings["timerDuration"]);
        timerCallBack = new TimerCallback(sendSMS);
        timerThread = new Timer(timerCallBack, autoEvent, 0, timePeriod);
    }


    private void sendSMS(object stateInfo)
    {
        AutoResetEvent autoResetEvent = (AutoResetEvent)stateInfo;
        smsClass.startSendingMessage();
        autoResetEvent.Set();
     }

    protected override void OnStop()
    {
        // TODO: Add code here to perform any tear-down necessary to stop your service.
        smsClass.stopSendingMessage();
        timerThread.Dispose();            

    }
}
}







namespace SMSPicker
{
class SendSMS
{
        //This variable has been done in order to ensure that other thread does not work till this thread ends
    bool taskDone = true;
        public SendSMS()
    {

    }

    //this method will start sending the messages by hitting the database
    public void startSendingMessage()
    {

        if (!taskDone)
        {
            writeToLog("A Thread was already working on the same Priority.");
            return;
        }

        try
        {
        }
        catch (Exception ex)
        {
            writeToLog(ex.Message);
        }
        finally
        {
            taskDone = stopSendingMessage();

            //this will ensure that till the database update is not fine till then, it will not leave trying to update the DB
            while (!taskDone)//infinite looop will fire to ensure that the database is updated in every case
            {
                taskDone = stopSendingMessage();
            }
        }

    }


public bool stopSendingMessage()
    {
        bool smsFlagUpdated = true;
        try
        {

        }
        catch (Exception ex)
        {
            writeToLog(ex.Message);
        }
        return smsFlagUpdated;
    }

}
}


if u can read it and tell me if this what i need ?
bcz i have already told u function work sleep than repeat it agiann
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question