Link to home
Start Free TrialLog in
Avatar of maqskywalker
maqskywalker

asked on

web service that returns xml

This example is using sql server 2008 and visual studio 2010.
This example uses the Employees table of the Northwind database.

I have a Web service called EmployeesWebService.asmx

This is my code:

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.Script.Services
Imports System.Collections.Generic
Imports System.Runtime.Serialization
Imports System.IO
Imports System.Text

' 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 EmployeesWebService
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function HelloWorld() As String
        Return "Hello World"
    End Function

    ' This method returns a Json type.
    <WebMethod()> _
    <ScriptMethod(UseHttpGet:=False, ResponseFormat:=ResponseFormat.Json)> _
    Public Function GetEmployees1() As List(Of Employee)

        Dim info As New List(Of Employee)()

        Dim mycon As SqlConnection
        mycon = New SqlConnection("Data Source=MyServer;Initial Catalog=Northwind;Integrated Security=True")
        mycon.Open()
        Dim query As String = "SELECT [EmployeeID],[LastName],[FirstName]  FROM [Northwind].[dbo].[Employees]"
        Dim cmd As New SqlCommand(query, mycon)
        'cmd.Parameters.AddWithValue("@id", id)

        Dim reader As SqlDataReader
        reader = cmd.ExecuteReader()
        While reader.Read()
            Dim b As New Employee()
            b.EmployeeID = reader.GetInt32(0)
            b.LastName = reader.GetString(1)
            b.FirstName = reader.GetString(2)
            info.Add(b)
        End While
        reader.Close()
        ' close the connection
        mycon.Close()

        Return info
    End Function

End Class

' Employee class
Public Class Employee
    ' Integer type
    Public Property EmployeeID() As Integer
        Get
            Return m_EmployeeID
        End Get
        Set(value As Integer)
            m_EmployeeID = value
        End Set
    End Property
    Private m_EmployeeID As String
    ' String type
    Public Property LastName() As String
        Get
            Return m_LastName
        End Get
        Set(value As String)
            m_LastName = value
        End Set
    End Property
    Private m_LastName As String
    ' String type
    Public Property FirstName() As String
        Get
            Return m_FirstName
        End Get
        Set(value As String)
            m_FirstName = value
        End Set
    End Property
    Private m_FirstName As String
End Class

Open in new window


When I run this in my web browser I get this:

User generated image
How would I modify my web service to return xml that is formatted like this:

User generated image
Can anyone help?
ASKER CERTIFIED SOLUTION
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial