Not working well with Timer

Question

Hello everyone,

I am current a student and I’m trying to add a module  to help some store database records in my application. The code I have currently relies on a timer, which has considerably slowed down the system as the database is left OPEN at all times and accessed at data every second and minute. I gathered that I can use modules or collections to store query results as soon as the application is started.

But I'm not getting anywhere with what I have at the moment but consuming so much memory.

 

Code Snippet

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

  cmd = New SqlCommand("SELECT * FROM PRODUCT_SALES Where PROD_SALE_BY_DATE='" & DateTime.Now.Date & "'", con)

        con.Open()

        Dim dataReader As SqlDataReader = cmd.ExecuteReader

        If (dataReader.Read()) Then

        My.Computer.Audio.Play("c:\windows\media\reminder.wav", AudioPlayMode.BackgroundLoop)
           
        MessageBox.Show(dataReader ("PRODUCT") & "has reached the sale by date " & dataReader ("PROD_SALE_BY_DATE"), "Sales Record", MessageBoxButtons.OK)

        End If

 
       dataReader.Close()

        con.Close()
End Sub

Answer 1

Don't leave sqlconnection open for such long time. Read data from datareader into some variables, close the connection and then show the data. Use using clause to have your sqlconnection object disposed automatically for you.

Answer 2

Thanks Giorgi Dalakishvili for your response but how do you that?

Answer 3

It doesn't make a lot of sense your program would be using a lot of memory.  But we can't see your database.  Some random remarks:

1. What do you call "a lot"?  100 megabytes is not a lot.
2. You ought to call SqlCommand.Dispose()
3. SELECT * is not a good idea, only select the columns you need
4. Your WHERE clause doesn't selective enough.  How many records does it select?

Answer 4

Thanks Hans. The application is using timer to check for expiration date and time for accessing the database every second which I have been told is not a good idea at all. My point is here how to get a module to store database queries result and the timer could just access that module instead of the database.

Thanks I would have to get rid of the "SELECT * FROM..."

My database table like this:

PROD_ID	PRODUCT	PROD_DESC	PROD_SALE_BY_DATE
10000 9456-8997 456	Egg	Poultry	27/04/2008 15:45
34576 6758-4567456	Milk	Diary	26/04/2008 20:50

Answer 5

why don't you create a store procedure to return a boolean/string having your expiration time. Do all calulation in Store procedure itself.