Question forms loop

[ahmed-bf]

Hi,
in my project I have 4 forms (connected to sql server 2005 database) in which I run heavy sql request so my program loses huge amounts of time when loading the forms especially since I made ​​a loop between the forms using timers.

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer2.Tick
      Me.Hide()
      FormX.Show()
End Sub

If anyone have an idea to improve the performance of my program, or is there any other way to make loop more efficient ?

 

[jmcilhinney]

First up, there's no loop there. Secondly, hiding one form and displaying another is almost always a hack. Doing so with a Timer like that just about guarantees it. Simply put, if you want to perform a long-running task without affecting the UI then you need to implement some form of multi-threading. The best option for doing that depends on the circumstances. If you provide a FULL and CLEAR description of exactly what you're trying to achieve then we can provide the best advice on how to achieve it.

 

[ahmed-bf]

What I'm trying to do is to develop an interface for alerts monitoring using the database of McAfee antivirus. So my program contains four forms and I run SQL queries on tables that contain more than 5 millions rows.
Concerning my attempt to loop between the forms I did as follows:
in every form I have a timer

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

Me.Hide()

FormX.Show()

End Sub



we can replace "Hide()" by "Dispose()"

I get this error every time I try to run my application
"Expiration of the waiting period. The timeout period elapsed prior to completion of the operation or the server is not responding."

 

[Dunfiddlin]

The error is being returned from the database. Are you attempting to keep 4 different connections alive or switching between connections on the timers (neither of which can be recommended!) I'm simply baffled by what you're trying to achieve by cycling between the forms. If you need to keep the processes related to each form alive this is not the way to do it. Multithreading is the only possibility but it's still not clear why you need to be doing this at all.

 

[ahmed-bf]

My application is intended to always operate on a display screen to show the statistics of alerts, so it is necessary to loop between the forms.
my 4 forms are connected to the same database :
'Dim connectionString As String = "Data Source = bensrv35; Initial Catalog = ePO4_BENSRV33; Integrated Security = True"

Any idea about the way in which we can solve this problem with the database? and if you have a piece of code that explains how to do multithreading between the forms I'll be thankful

 

[Lotok]

Can you try and explain what is on each form. Why does each form need to hide and then show another. Are you loading different data or wanting to alter a layout?
I think an overview of the end goal is needed before anyone can show you how to do it.

 

[ahmed-bf]

Form 1 : Nothing special just a welcome form
Form 2 : Top 10 Infected Machines of the day using this :

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim connectionString As String = "Data Source=bensrv35;Initial Catalog=ePO4_BENSRV33;Integrated Security=True; "
Dim connectionString As String = "Data Source=bensrv35;Initial Catalog=ePO4_BENSRV33;Integrated Security=True; "
Dim sql As String = "SELECT Top 10 TargetHostName, count(*) AS NumberThreat FROM EPOEvents WHERE (DetectedUTC between CONVERT(datetime,dateadd(dd,-1,getdate()),102) and CONVERT(datetime,getdate(),102)) GROUP BY TargetHostName ORDER BY NumberThreat DESC"
Dim connection As New SqlConnection(connectionString)
Dim dataadapter As New SqlDataAdapter(sql, connection)
Dim ds As New DataSet()
connection.Open()
dataadapter.Fill(ds, "Authors_table")
DataGridView1.DataSource = ds
DataGridView1.DataMember = "Authors_table"
Timer2.Interval = 5000
Timer2.Start()
End Sub
Private Sub Timer2_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer2.Tick
Me.Hide()
Form3.Show()
End Sub

Form 3 : Top 10 Infected Machines of the week (start using BackgroudWorler but I'm getting an error) :
code :
Imports System.Data
Imports System.Data.SqlClient

Public Class Form3

Public sqlconnection As SqlConnection
Dim connectionString As String = "Data Source=bensrv35;Initial Catalog=ePO4_BENSRV33;Integrated Security=True"
Dim sql1 As String = "SELECT TOP 10 TargetHostName, count(*) AS NumberThreat, SourceHostname FROM EPOEvents WHERE (DetectedUTC between CONVERT(datetime,dateadd(ww,-1,getdate()),102) and CONVERT(datetime,getdate(),102)) GROUP BY TargetHostName, SourceHostName ORDER BY NumberThreat DESC"
Dim ds1 As New DataSet()

Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.Show()
Me.WindowState = FormWindowState.Maximized
Me.FormBorderStyle = Windows.Forms.FormBorderStyle.None
If BackgroundWorker1.IsBusy <> True Then
BackgroundWorker1.RunWorkerAsync()
End If
Timer3.Interval = 20000
Timer3.Start()
End Sub

Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)

End Sub

Private Sub Timer3_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer3.Tick
Me.Hide()
Form4.Show()
End Sub

Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
Dim connection As New SqlConnection(connectionString)
connection.Open()
Dim dataadapter1 As New SqlDataAdapter(sql1, connection)
dataadapter1.Fill(ds1, "Authors_table")
DataGridView1.DataSource = ds1
DataGridView1.DataMember = "Authors_table"
End Sub
End Class

Form 4 : statistical curve
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms.DataVisualization.Charting

Public Class Form4

Public sqlconnection As SqlConnection
Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.Show()
Me.WindowState = FormWindowState.Maximized
Me.FormBorderStyle = Windows.Forms.FormBorderStyle.None
Dim connectionString As String = "Data Source=bensrv35;" & _
"Initial Catalog=ePO4_BENSRV33;Integrated Security=True"
Dim sql As String = "SELECT ThreatType, Count FROM EPOEvents_ThreatTypesView ORDER BY Count DESC"
Dim connection As New SqlConnection(connectionString)
Dim dataadapter As New SqlDataAdapter(sql, connection)
Dim ds As New DataSet()
connection.Open()
dataadapter.Fill(ds, "Authors_table")
'DataGridView1.DataSource = ds
'DataGridView1.DataMember = "Authors_table"

Dim ChartArea1 As ChartArea = New ChartArea()
Dim Legend1 As Legend = New Legend()
Dim Series1 As Series = New Series()
Dim Chart1 = New Chart()
Me.Controls.Add(Chart1)


ChartArea1.Name = "ChartArea1"
Chart1.ChartAreas.Add(ChartArea1)
Chart1.Size = New System.Drawing.Size(980, 500)

Legend1.Name = "Legend1"
Chart1.Legends.Add(Legend1)
Chart1.Location = New System.Drawing.Point(20, 250)
Chart1.Name = "Chart1"
Chart1.BackColor = Color.WhiteSmoke
Series1.ChartArea = "ChartArea1"
Series1.Legend = "Legend1"
Series1.Name = "ThreatStat"
Chart1.Series.Add(Series1)
Chart1.TabIndex = 0
Chart1.Text = "Chart1"

Chart1.Series("ThreatStat").XValueMember = "ThreatType"
Chart1.Series("ThreatStat").YValueMembers = "Count"
Chart1.Series("ThreatStat").Font = New Font("Arial", 10, FontStyle.Bold)
Chart1.Series("ThreatStat").IsValueShownAsLabel = True
Chart1.Series("ThreatStat").LabelForeColor = Color.Firebrick

Chart1.AlignDataPointsByAxisLabel()
Series1.ChartType = SeriesChartType.Column
Chart1.ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
Chart1.ChartAreas("ChartArea1").Area3DStyle.Rotation = -40
Chart1.ChartAreas("ChartArea1").Area3DStyle.Inclination = 90
Chart1.ChartAreas("ChartArea1").Area3DStyle.PointDepth = 100
Chart1.ChartAreas("ChartArea1").Area3DStyle.PointGapDepth = 0
Chart1.ChartAreas(0).Area3DStyle.WallWidth = 10
Chart1.ChartAreas(0).Area3DStyle.LightStyle = LightStyle.Realistic

ChartArea1.AxisX.LabelStyle.Angle = -90
ChartArea1.AxisX.LabelStyle.Font = New Font("Verdana", 8, FontStyle.Bold)
ChartArea1.AxisY.LabelStyle.Font = New Font("Verdana", 8, FontStyle.Bold)
ChartArea1.AxisX.Interval = 1
Chart1.DataSource = ds.Tables("Authors_table")
Timer4.Interval = 20000
Timer4.Start()
End Sub

Private Sub Timer4_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer4.Tick
Me.Hide()
Form1.Show()
End Sub
End Class

 

[Bryce Gough]

Well if your forms are all connecting to the same database, maybe you should try using a global instance of the sql connection..
I've never experienced this so I don't know if that's what the problem is, but you should give it a go.

 

[Lotok]

You should create a data access layer to save you repeating the same code in every form. Just have it in one place. See here: The Curve | Simple MSSQL Data Access Layer - Using VB.NET

Also, once your welcome form is no longer needed, close it.
If you want to display different data like you have detailed above, rather than having a form for top 10 of the day, another for week, another for month. Why not use 1 form with a placeholder and dynamically create the DataGridView? Or if that is too complex for you, have a DataGridView object on the form with AutoGenerateColumns set to true so when the data is loaded into that DGV it fills as required.

 

[ahmed-bf]

thanks, I will try both solutions )