How to Query SQL programmatically

[ahmish]

Hi Guys,

im trying to create a code that will output result based on 3 [Request Status]. im trying to Query "Pending", "In Progress" and "Completed". below codes work perfectly fine for me but the problem is i have to recreate below codes three times.

is there a way to code this that will use basically below and output the count of Pending, Completed and In Progress of my table?

Dim conn As New SqlConnection
conn.ConnectionString = "Data Source"
Dim sql As String = ("SELECT Count(*) as TotalNumber FROM [Helpdesk Queries]
where CONVERT(VARCHAR, [Request Status])='Pending'")
Dim cmd As New SqlCommand(sql, conn)
cmd.Connection = conn
conn.Open()
Dim Total As Integer
Dim dr As SqlDataReader = cmd.ExecuteReader()
Try
While dr.Read
Total = dr("TotalNumber")
MsgBox(Total)
'Label35.Text = dr("TotalNumber")
End While
Finally
dr.Close()
conn.Close()
End Try


Sorry Guys, Got it to work. below is my working code

Dim conn As New SqlConnection
Dim sql As String = ("SELECT COUNT(Id) as TotalNumber, Type
FROM [Uploadtest]
GROUP BY Type")
Dim cmd As New SqlCommand(sql, conn)
cmd.Connection = conn
conn.Open()
Dim maxval As Integer
Dim typing As String
Dim dr As SqlDataReader = cmd.ExecuteReader()
Try
While dr.Read
typing = dr("type")
maxval = dr("TotalNumber")
If typing = "Completed" Then
MsgBox(maxval)
End If
If typing = "In Progress" Then
MsgBox(maxval)
End If
If typing = "Pending" Then
MsgBox(maxval)
End If
End While
Finally
dr.Close()
conn.Close()
End Try

[TysonLPrice]

Do you know how to code stored procedures? This seems like a good candidate.

[ChrisE]

or try it this way

SELECT Count(*) as TotalNumber , [Request Status]
FROM [Helpdesk Queries]
Group By [Helpdesk Queries].[Request Status], [Helpdesk Queries].[Request Status];

[jmcilhinney]

Please don't post unformatted code.

        Dim conn As New SqlConnection
        conn.ConnectionString = "Data Source"
        Dim sql As String = ("SELECT Count(*) as TotalNumber FROM [Helpdesk Queries]
        where CONVERT(VARCHAR, [Request Status])='Pending'")
        Dim cmd As New SqlCommand(sql, conn)
        cmd.Connection = conn
        conn.Open()
        Dim Total As Integer
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        Try
            While dr.Read
                Total = dr("TotalNumber")
                MsgBox(Total)
                'Label35.Text = dr("TotalNumber")
            End While
        Finally
            dr.Close()
            conn.Close()
        End Try

        Dim conn As New SqlConnection
        Dim sql As String = ("SELECT COUNT(Id) as TotalNumber, Type 
                            FROM [Uploadtest]
                            GROUP BY Type")
        Dim cmd As New SqlCommand(sql, conn)
            cmd.Connection = conn
            conn.Open()
        Dim maxval As Integer
        Dim typing As String
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        Try
            While dr.Read
                typing = dr("type")
                maxval = dr("TotalNumber")
                If typing = "Completed" Then
                    MsgBox(maxval)
                End If
                If typing = "In Progress" Then
                    MsgBox(maxval)
                End If
                If typing = "Pending" Then
                    MsgBox(maxval)
                End If
            End While
        Finally
            dr.Close()
                conn.Close()
            End Try

[jmcilhinney]

Do you know how to code stored procedures? This seems like a good candidate.

I wouldn't have thought so. Of course, any SQL can be programmed into a stored procedure but this is so basic that there seems little specific reason to do so. If you're a database guy and you want all your SQL in your database then fair enough. As an application developer, I say keep all your code in your application if you can. If you need similar code in multiple places then extract out the commonality and put it in a method.

[TysonLPrice]

I wouldn't have thought so. Of course, any SQL can be programmed into a stored procedure but this is so basic that there seems little specific reason to do so. If you're a database guy and you want all your SQL in your database then fair enough. As an application developer, I say keep all your code in your application if you can. If you need similar code in multiple places then extract out the commonality and put it in a method.

We prefer stored procedures so if we need minor maintenance we don't have to roll code out. In our environment it is much easy to get SQL into production versus binaries. It also lets the DBA group feel like they aren't being left out

[GaryMazzone]

It also allows the reuse of query plans much better

[TysonLPrice]

It also allows the reuse of query plans much better

Good point...

Another place keeping it in code bit us a little is when we migrated from SQL 2000 to 2008. We had all kinds of applications that had SQL statements in it that we had to make changes to. A big example is how using the "With" keyword changed. We were scanning all kinds of code and rolling out lots of projects. Had all that SQL been in a database it would have been easier.

[GaryMazzone]

We have 2 main applications where I work.... One uses in-line SQL (written by Nhibernate) and the other Stored Procs that the DBAs vet. When something goes wrong in the one with in-line SQL we are still expected to figure out what went wrong with the data, not the developers who wrote the thing but someone who has no access to the code. Fun right.