Write a windows service using VB.NET + SQL server

Question

0.00/5 (No votes)

See more:

, +

I'm trying to build a windows service("Event Collector"), using VB.NET which grabs data from 4 different SQL databases from the network which I've already given DSN using ODBC for each DB. So far I was able to create a simple windows service which prints "1 sec is over!" every second.

My end goal is to connect it with 4 different SQL DB's as mentioned above, to get data every other 5 mins or so(listen to new data which added to the SQL DB's within last 5 minutes and log it in a txt log file).

Assuming the DSN's for SQL DB's are,

SQLDB1
SQLDB2
SQLDB3
SQLDB4
And assuming there are 3 columns for every DB,

Column1
Column2
Column3
Can anyone help/guide me to build the windows service ?

I really appreciate your time and consideration all!

What I have tried:

"TestWindowsService.vb"

VB.NET

Public Class TestWindowsService

    Dim tmr As Timers.Timer

    Protected Overrides Sub OnStart(ByVal args() As String)
        ' Add code here to start your service. This method should set things
        ' in motion so your service can do its work.
        tmr = New Timers.Timer()
        tmr.Interval = 1000 'Setting Interval pf Timer. 1s interval
        AddHandler tmr.Elapsed, AddressOf MyTimerHandler
        tmr.Enabled = True
        FileIO.WriteToFile("Service Started!" + vbNewLine)
    End Sub

    Protected Overrides Sub OnStop()
        ' Add code here to perform any tear-down necessary to stop your service.
        tmr.Enabled = False
        FileIO.WriteToFile("Service Stopped!" + vbNewLine + vbNewLine)
    End Sub

    Private Sub MyTimerHandler(obj As Object, e As EventArgs)
        FileIO.WriteToFile("1 sec is over!" + vbNewLine)
    End Sub
End Class

"FileIO.vb"

VB.NET

Public Class FileIO

    Public Shared Sub WriteToFile(strToWrite As String)
        Dim stream As IO.StreamWriter = Nothing

        Try
            stream = New IO.StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "\myFile_" & DateTime.Now.ToString("yyyyMMdd") & ".txt",
                                         True)
            stream.Write(strToWrite)
            stream.Flush()
            stream.Close()
        Catch ex As Exception

        End Try
    End Sub

End Class

Posted 7-Apr-21 2:27am

GlennAshan

Updated 8-Apr-21 3:03am

Add a Solution

Comments

SeanChupas 7-Apr-21 9:26am

Where are you stuck exactly?

GlennAshan 7-Apr-21 11:42am

Hi Sean,

I was able to create a simple windows service as per you can see my code above. But what I'm trying to do is create a windows service that listens to the DB's and logs to a txt file with new data that has been added within last 5 mins. The service should run every 5 mins. Thanks

Richard Deeming 8-Apr-21 3:59am

If you want something to "run every 5 minutes", then a service is the wrong choice.

Use the Windows Task Scheduler to schedule a normal application to run every 5 minutes instead.

GlennAshan 8-Apr-21 8:14am

Richard,

I think I was not clear when asking the question, my apologies. The windows service does not has to run & stop every 5 mins, all I want is the service to run continuously and detect changes(new instances/ new data) which was added to the DB within last 5 minutes(only grabs the data which was not posted from the last time service check). Is it not possible for a windows service to do using the "OnStart", "OnStop" and by using Sleep function in thread after each loop?
Thank you!

CHill60 8-Apr-21 8:48am

"by using Sleep function in thread after each loop" ... not a good idea. Thread.Sleep is a sign of a poorly designed program. – Peter Ritchie's MVP Blog[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2021-04-08T03:03:00

Have you considered using an alternative approach - have a look at these CodeProject articles
Using SqlDependency for data change events[^]
Query Notification using SqlDependency and SqlCacheDependency[^]

Also refer to the link in my comment above : Thread.Sleep is a sign of a poorly designed program. – Peter Ritchie's MVP Blog[^]

Richard's comment deserves some real consideration - using a Task Scheduler is probably a better approach.

I also question topics like this - just how urgent is it that these updates are notified every 5 minutes? Especially if it is being logged into a text file - realistically is someone going to look at that text file every 5 minutes? Ever?

There is a certain "something" about this question that makes me think it might be homework, but there is definitely something else you need to address first ...

Quote:
And assuming there are 3 columns for every DB...

Columns are features of Tables, not Databases. You are going to struggle with acquiring the data you want until you get a better understanding of the databases you are going to query.