Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Imports System.ServiceModel.Web
Imports System.Web.Script.Serialization
Imports System.Web.Script.Services

<System.Web.Script.Services.ScriptService()> _
<ToolboxItem(False)> _
Public Class reportdata
    Inherits System.Web.Services.WebService
    Dim connectionString = "redacted"


    <WebMethod()> _ 'This is the old function
    <WebGet(ResponseFormat:=WebMessageFormat.Json)> _
    Public Function rptPendingServerRequestsOld() As DataSet
        Dim connection As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim sql As String
        sql = "redacted"

        connection = New SqlConnection(connectionString)

        Try
            connection.Open()
            command = New SqlCommand(sql, connection)
            adapter.SelectCommand = command
            adapter.Fill(ds)
            adapter.Dispose()
            command.Dispose()
            connection.Close()
            Return ds

        Catch ex As Exception
        End Try
    End Function

    <WebMethod()> _ 'this is the new one that I want to return JSON
    <ScriptMethod(ResponseFormat:=ResponseFormat.Json)> _
    Public Function rptPendingServerRequests() As Generic.List(Of request)
        Dim _conn As SqlConnection = New SqlConnection(connectionString)
        Dim _dr As SqlDataReader
        Dim Sql As String = String.Empty
        Sql += "redacted"

        Try
            Dim _cmd As SqlCommand = New SqlCommand(Sql, _conn)
            _conn.Open()
            _dr = _cmd.ExecuteReader(CommandBehavior.CloseConnection)

            If _dr.HasRows Then
                Dim s As request
                Dim c As New Generic.List(Of request)
                While _dr.Read
                    s = New request
                    With s
                        .requestID = _dr("request_id")
                        .status = _dr("status")
                        .requester = _dr("req_by_user_id")
                        .assignee = _dr("user_id")
                        .nextAction = _dr("description")
                    End With
                    c.Add(s)
                End While

                Return c
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            _conn.Close()
        End Try

    End Function

End Class