Displaying Graphs with an Object Oriented Approach using Silverlight SDK and Visual Studio 2008 - Understanding the ASP.NET 3.5 Web Service: continued
(Page 3 of 5 )
To hold multiple rows from the “Products” table, a new collection class named “Products.vb” is developed (using generics) as follows:
Imports System.Collections.Generic
Public Class Products
Inherits List(Of Product)
End Class
Finally, the web service contains only three methods as follows:
GetProductInfo
GetProductList
GetTotalSaleValue
“GetProductInfo” simply returns the information about a particular product. “GetProductList” returns a collection of “Product” objects (say, multiple product information). “GetTotalSaleValue” returns the sale value of all products in the Northwind database.
The entire source code for the web service is as follows:
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data.SqlClient
' To allow this Web Service to be called from script, using ASP.NET AJAX,
uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding
(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class ProductService
Inherits System.Web.Services.WebService
<WebMethod()> _
Public Function GetProductInfo(ByVal ProductID As Integer) As Product
Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings
("cnNorthwind").ConnectionString)
Using cmd As New SqlCommand("SELECT ProductID, ProductName, UnitPrice,
UnitsInStock, UnitsOnOrder,(SELECT sum(UnitPrice * Quantity) FROM dbo.
[Order Details] WHERE ProductID = a.ProductID) as SaleValue FROM
dbo.Products as a WHERE ProductID=" & ProductID, cn)
cmd.Connection.Open()
Using rdr As SqlDataReader = cmd.ExecuteReader
If rdr.Read() Then
Return New Product With {.ProductID = rdr(rdr.GetOrdinal("ProductID")),
.ProductName = rdr(rdr.GetOrdinal("ProductName")) & "", .UnitPrice = rdr
(rdr.GetOrdinal("UnitPrice")) & "", .UnitsInStock = rdr(rdr.GetOrdinal
("UnitsInStock")) & "", .UnitsOnOrder = rdr(rdr.GetOrdinal
("UnitsOnOrder")) & "", .SaleTillToDate = rdr(rdr.GetOrdinal("SaleValue"))
& ""}
Else
Return Nothing
End If
End Using
cmd.Connection.Close()
End Using
End Using
End Function
<WebMethod()> _
Public Function GetProductList() As Products
Dim lstProducts As New Products
Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings
("cnNorthwind").ConnectionString)
Using cmd As New SqlCommand("SELECT ProductID, ProductName, UnitPrice,
UnitsInStock, UnitsOnOrder,(SELECT sum(UnitPrice * Quantity) FROM dbo.
[Order Details] WHERE ProductID = a.ProductID) as SaleValue FROM
dbo.Products as a ORDER BY 6 DESC", cn)
cmd.Connection.Open()
Using rdr As SqlDataReader = cmd.ExecuteReader
While rdr.Read()
lstProducts.Add(New Product With {.ProductID = rdr(rdr.GetOrdinal
("ProductID")), .ProductName = rdr(rdr.GetOrdinal("ProductName")) & "",
.UnitPrice = rdr(rdr.GetOrdinal("UnitPrice")) & "", .UnitsInStock = rdr
(rdr.GetOrdinal("UnitsInStock")) & "", .UnitsOnOrder = rdr(rdr.GetOrdinal
("UnitsOnOrder")) & "", .SaleTillToDate = rdr(rdr.GetOrdinal("SaleValue"))
& ""})
End While
End Using
cmd.Connection.Close()
End Using
End Using
Return lstProducts
End Function
<WebMethod()> _
Public Function GetTotalSaleValue() As Double
Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings
("cnNorthwind").ConnectionString)
Using cmd As New SqlCommand("SELECT SUM(UnitPrice * Quantity) as SaleValue
FROM dbo.[Order Details]", cn)
cmd.Connection.Open()
Using rdr As SqlDataReader = cmd.ExecuteReader
If rdr.Read Then
Return rdr(rdr.GetOrdinal("SaleValue"))
End If
End Using
cmd.Connection.Close()
End Using
End Using
End Function
End Class
Next: Displaying the Silverlight graph for a particular product >>
More ASP.NET Articles
More By Jagadish Chaterjee
