I made a web application for managing about three tables in a 2008 R2 database in VB.Net with several GridViews in a <asp:MultiView> using Tabs to navigate from View to View. ...Maybe 4 years ago.
The pages take excessive time to refresh. Half a minute for some of the GridViews to refresh data or even to Select a row.
The first thing I want to fix is in the Page_Load I have this block of code to get four "counts" from a table to display in labels at the top of the page. It's using four separate SqlCommands and I know it can be done with a single Select with a Group By maybe. I'm just not sure how to do it.
Here is the whole thing. Could you show me a better way, using only a single "trip" to the database?
If Not Page.IsPostBack Then
Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("CSPConnectionString1").ConnectionString)
Dim DBCmd As New SqlCommand
Dim DBCmd2 As New SqlCommand
Dim DBCmd3 As New SqlCommand
Dim DBCmd4 As New SqlCommand
DBCmd = New SqlCommand("SELECT COUNT(keyApplicantID) FROM[Applicants]", DBConn)
DBCmd2 = New SqlCommand("SELECT COUNT(keyApplicantID) FROM[Applicants] WHERE (Center = 'North')", DBConn)
DBCmd3 = New SqlCommand("SELECT COUNT(keyApplicantID) FROM[Applicants] WHERE (Center = 'Central')", DBConn)
DBCmd4 = New SqlCommand("SELECT COUNT(keyApplicantID) FROM[Applicants] WHERE (Center = 'South')", DBConn)
Dim varCount As Integer = DBCmd.ExecuteScalar()
Dim varCount2 As Integer = DBCmd2.ExecuteScalar()
Dim varCount3 As Integer = DBCmd3.ExecuteScalar()
Dim varCount4 As Integer = DBCmd4.ExecuteScalar()
lblCounts.Text = " Participants: " & varCount.ToString & ", " & " North: " & varCount2.ToString & ", " & " Central: " & varCount3.ToString & ", " & " South: " & varCount4.ToString
Catch exp As Exception
DBConn = Nothing