Click here to Skip to main content
14,978,008 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello all, I have written a simple windows service using VB.NET to transfer some data from one DB's table to another DB's table every 10seconds(whichever new data that hasn't been transferred before).

To me the code seems like bug free but can anyone catch what might be the issue here. The windows service is runnable manually but the data won't transfer.

I have tried looking up any errors from "Windows Event Viewer" also "SQL Server Logs" but unfortunately or fortunately there are no error logs that I can find of.

I do really appreciate your time and consideration all!

What I have tried:

MyNewService.vb
VB.NET
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.Configuration.Install
Public Class MyNewService
    Dim con1, con2 As New SqlConnection
    Dim timer1 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.
        timer1 = New Timers.Timer()
        con1 = New SqlConnection("Server = STLWKSENG11;" & "initial Catalog = DEV_TEST;" & "Trusted_Connection = yes")
        con1.Open()
        con2 = New SqlConnection("Server = STLWKSENG11;" & "initial Catalog = DEV_TEST2;" & "Trusted_Connection = yes")
        con2.Open()
        timer1.Enabled = True
        timer1.Interval = 10000
        timer1.Start()
    End Sub

    Protected Overrides Sub OnStop()
        ' Add code here to perform any tear-down necessary to stop your service.
        timer1.[Stop]()
        con1.Close()
        con2.Close()
    End Sub
    Private Sub timer1_Elapsed(ByVal sender As Object, ByVal e As System.Timers.ElapsedEventArgs)
        Dim cmd As SqlCommand
        Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from Table1 where [Check]='False'", con1)
        Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Dim i As Integer

        For Each dr As DataRow In dt.Rows
            cmd = New SqlCommand("Insert into Table_1 values('" & dr(0) & "','" + dr(1).ToString() & "','" & dr(2) & "')", con2)
            i = cmd.ExecuteNonQuery()
            dr(3) = True
        Next

        da.Update(dt)
        cmd.Dispose()
        dt.Dispose()
        da.Dispose()
    End Sub
End Class


ProjectInstaller.vb

VB.NET
Imports System.ComponentModel
Imports System.Configuration.Install

Public Class ProjectInstaller

    Public Sub New()
        MyBase.New()

        'This call is required by the Component Designer.
        InitializeComponent()

        'Add initialization code after the call to InitializeComponent

    End Sub

End Class
Posted
Updated 9-Apr-21 19:41pm
Comments
Richard Deeming 9-Apr-21 12:08pm
   
Also, don't try to keep the connections open for the entire time the service is running. Instead, create and open the connection when the timer event fires, and wrap them in using blocks to ensure they're cleaned up properly.
GlennAshan 9-Apr-21 12:39pm
   
Thank you very much for these valuable advices Richard, will go through these. By the way do you have any idea why does my code doesn't do what it supposed to do?

I'm having some doubts about "ProjectInstaller.vb" file that it might be not complete. Not sure whether I'm doing the right thing.
Gerry Schmitz 9-Apr-21 20:50pm
   
What's the count for dt.Rows?

1 solution

disclaimer - I know NOTHING about writing a service in VB.net

That being said, the two things that are immediately obvious

1) no logging - if you were logging to the the event log you might see what's happening - and, usually, in C#, I'll write a service that runs as a console app first before installing it as a service, so I can log to a console window, debug it 'easier'

2) I may be wrong, but, it doesn't seem like you have connected timer1_Elapsed to timer1 - in c# one would do this
<pre lang="C#">// Set up a timer that triggers every minute.
Timer timer = new Timer();
timer.Interval = 60000; // 60 seconds
timer.Elapsed += new ElapsedEventHandler(this.OnTimer);
timer.Start();


So, if you had logging you would see whether or not your timer event is being called - this https://www.dotnetperls.com/timer-vbnet[^] suggests
VB
AddHandler timer1.Elapsed, New ElapsedEventHandler(AddressOf timer1_Elapsed)
may be what you need

3) nothing is made known/shown about database credentials etc (again, logging could be useful) - I'm assuming that you're installing your service under an account that has the correct permissions for the database, yes ?

4) design-wise, you don't indicate how much data is to be moved in any 'cycle' .. having worked on large migration projects, we usually use a control table to keep track of records to be moved/migrated, and migrated the records in batches that can be handled by the duty cycle of the timing mechanism- using a control table also gives you an audit mechanism, it really depends on the context of what is being performed - it would seem from a cursory look, that after having inserted records into Table_1 (from Table1 ?? (bad naming)) that the next time around you'll get the same records inserted, because you don't update Table1 for the inserted records to set Table1.[check] to 'something else'
   
v5

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900