Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports system.Data.SqlClient

<WebService(Namespace:="http://www.geoffsnowman.net/mytestsvc")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class Service
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function GetProducts() As Product()
        Dim rdr As SqlDataReader
        Dim cmd As SqlCommand
        Dim conn As New SqlConnection("Persist Security Info=True;Integrated Security=SSPI;database=AdventureWorks;server=gsnowmanevo;Connect Timeout=30")
        Try
            conn.Open()
            cmd = New SqlCommand("SELECT SUM(Sales.SalesOrderDetail.LineTotal) AS TotalSales, Production.Product.Name, Production.ProductSubcategory.Name AS SubCat, " & _
                      "Production.ProductCategory.Name AS Cat FROM Production.Product INNER JOIN " & _
                      "Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID INNER JOIN " & _
                      "Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID INNER JOIN " & _
                      "Sales.SalesOrderDetail ON Production.Product.ProductID = Sales.SalesOrderDetail.ProductID " & _
                      "GROUP BY Production.Product.Name, Production.ProductSubcategory.Name, Production.ProductCategory.Name", conn)
            rdr = cmd.ExecuteReader()
            Dim results(300) As Product
            Dim i As Integer = 0
            While (rdr.Read And i < 300)
                results(i) = New Product()
                results(i).psales = rdr.GetDecimal(0)
                results(i).pname = rdr.GetString(1)
                results(i).psubcat = rdr.GetString(2)
                results(i).pcat = rdr.GetString(3)
                i = i + 1
            End While
            Return results
        Catch ex As Exception
            Throw ex
        Finally
            conn.Close()
        End Try

    End Function

    Public Class Product
        Public pname As String
        Public pcat As String
        Public psubcat As String
        Public psales As Decimal
    End Class

End Class