none
WCF Service must output Json RRS feed

  • Question

  • Hello.

    I have a service that looks like the following:

    <ServiceBehavior(InstanceContextMode:=InstanceContextMode.PerSession)> _
    Public Class UmbrellaMobileService
        Inherits ServiceBase
        Public serviceHost As ServiceHost = Nothing
    
        Public Shared Sub Main()
            ServiceBase.Run(New UmbrellaMobileService())
        End Sub
    
        Public Sub New()
            ServiceName = "UmbrellaMobileService"
        End Sub
    
        'Start the Windows service.
        Protected Overloads Overrides Sub OnStart(ByVal args As String())
            If serviceHost IsNot Nothing Then
                serviceHost.Close()
            End If
            serviceHost = New WebServiceHost(GetType(UmbrellaService), New Uri("http://localhost/UmbrellaMobileService"))
            serviceHost.AddServiceEndpoint(GetType(UmbrellaMobileServiceInterface), New WebHttpBinding(), "http://localhost/UmbrellaMobileService")
    
            Dim objIniFile As New UmbrellaDataManagement.IniFile("c:\Agile\Umbrella\Umbrella.ini")
            If objIniFile.ErrorMessage = "" Then
                SQLServer = objIniFile.GetString("Settings", "SERVER", "")
                UmbrellaDatabase = objIniFile.GetString("Settings", "MOBILESERVICEDATABASE", "")
                UserCode = objIniFile.GetString("Settings", "USER", "")
                UserPassword = objIniFile.GetString("Settings", "PASSWORD", "")
                If Trim(UserCode) = "" Then
                    WindowsAuthentication = True
                Else
                    WindowsAuthentication = False
                End If
            Else
                System.Diagnostics.EventLog.WriteEntry("UmbrellaMobileService", objIniFile.ErrorMessage, EventLogEntryType.Error)
                serviceHost.Close()
            End If
    
            serviceHost.Open()
        End Sub
    
        ' Stop the Windows service.
        Protected Overloads Overrides Sub OnStop()
            If serviceHost IsNot Nothing Then
                serviceHost.Close()
                serviceHost = Nothing
            End If
        End Sub
    End Class
    
    <AspNetCompatibilityRequirements(Requirementsmode:=AspNetCompatibilityRequirementsMode.Allowed)> _
    Public Class UmbrellaService
        Inherits System.Web.Services.WebService
        Implements UmbrellaMobileServiceInterface
    
        Public Function GetSQL(ByVal SQLScript As String) As XmlElement Implements UmbrellaMobileServiceInterface.GetSQL
            Dim xmlData As String = "<schema xmlns=""http://www.w3.org/2001/XMLSchema""></schema>"
            Dim XmlBodyDocument As New XmlDocument
            Try
    
                Dim ErrorMessage As String = ""
                ErrorMessage = UmbrellaDataManagementObj.OpenConnection(SQLServer, UmbrellaDatabase, UserCode, UserPassword)
                If ErrorMessage <> "" Then
                    System.Diagnostics.EventLog.WriteEntry("UmbrellaMobileService", ErrorMessage & vbNewLine & UmbrellaDataManagementObj.ConnectionString, EventLogEntryType.Error)
                    WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                    WebOperationContext.Current.OutgoingResponse.StatusDescription = ErrorMessage
                    XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                    Return XmlBodyDocument.DocumentElement
                Else
                    Dim TableName As String = "SQL"
                    Dim AllDetails As DataSet
                    AllDetails = UmbrellaDataManagementObj.GetDataSQL(SQLScript)
                    If Not AllDetails Is Nothing Then
                        AllDetails.Tables(0).TableName = TableName
                        AllDetails.DataSetName = TableName
                        XmlBodyDocument.LoadXml(AllDetails.GetXml)
    
                        UmbrellaDataManagementObj.CloseConnection()
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 200
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = "OK"
                        Return XmlBodyDocument.DocumentElement
                    Else
                        System.Diagnostics.EventLog.WriteEntry("UmbrellaMobileService", SQLScript & vbNewLine & UmbrellaDataManagementObj.ErrorMessage, EventLogEntryType.Error)
    
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = UmbrellaDataManagementObj.ErrorMessage
    
                        XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                        Return XmlBodyDocument.DocumentElement
                    End If
                End If
    
            Catch ex As Exception
                System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ex.Message, EventLogEntryType.Error)
    
                WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                WebOperationContext.Current.OutgoingResponse.StatusDescription = ex.Message
    
                XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                Return XmlBodyDocument.DocumentElement
            End Try
            Dispose()
        End Function
    
    
    
    
    
        Public Function GetProduct(ByVal ProductCode As String) As XmlElement Implements UmbrellaMobileServiceInterface.GetProduct
            Dim xmlData As String = "<schema xmlns=""http://www.w3.org/2001/XMLSchema""></schema>"
            Dim XmlBodyDocument As New XmlDocument
            Try
    
                Dim SQLSTR As String = ""
                'SQLSTR = "SELECT '' as [code], '' as [description], cast('0' as decimal(18,6)) as [quantity], '' as [uom], cast('0' as decimal(18,6)) as [price] FROM Stock Union SELECT Stock.code, Stock.description, isnull((Select SUM(StockMovement.Qty) from StockMovement where StockMovement.StockID = Stock.StockID),0.00) as [quantity], (Select UnitOfMeasure.Code from UnitOfMeasure where UnitOfMeasure.UOMID = Stock.UOMID) as [uom], isnull((Select Top 1 StockPriceList.UnitSellingPrice from StockPriceList where StockPriceList.StockID = Stock.StockID and StockPriceList.UOMID = Stock.UOMID),0.00) as [price] FROM Stock Where Stock.Code = '" & Trim(ProductCode) & "'"
                SQLSTR = "SELECT Stock.code, Stock.description, isnull((Select SUM(StockMovement.Qty) from StockMovement where StockMovement.StockID = Stock.StockID),0.00) as [quantity], (Select UnitOfMeasure.Code from UnitOfMeasure where UnitOfMeasure.UOMID = Stock.UOMID) as [uom], isnull((Select Top 1 StockPriceList.UnitSellingPrice from StockPriceList where StockPriceList.StockID = Stock.StockID and StockPriceList.UOMID = Stock.UOMID),0.00) as [price] FROM Stock Where Stock.Code = '" & Trim(ProductCode) & "'"
    
                Dim ErrorMessage As String = ""
                ErrorMessage = UmbrellaDataManagementObj.OpenConnection(SQLServer, UmbrellaDatabase, UserCode, UserPassword)
                If ErrorMessage <> "" Then
                    System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ErrorMessage & vbNewLine & UmbrellaDataManagementObj.ConnectionString, EventLogEntryType.Error)
                    WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                    WebOperationContext.Current.OutgoingResponse.StatusDescription = ErrorMessage
                    XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                    Return XmlBodyDocument.DocumentElement
                Else
                    Dim AllDetails As DataSet
                    AllDetails = UmbrellaDataManagementObj.GetDataSQL(SQLSTR)
                    If Not AllDetails Is Nothing Then
                        AllDetails.DataSetName = "products"
                        AllDetails.Tables(0).TableName = "product"
                        XmlBodyDocument.LoadXml(AllDetails.GetXml)
    
                        UmbrellaDataManagementObj.CloseConnection()
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 200
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = "OK"
                        Return XmlBodyDocument.DocumentElement
                    Else
                        System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", UmbrellaDataManagementObj.ErrorMessage, EventLogEntryType.Error)
    
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = UmbrellaDataManagementObj.ErrorMessage
    
                        XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                        Return XmlBodyDocument.DocumentElement
                    End If
                End If
            Catch ex As Exception
                System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ex.Message, EventLogEntryType.Error)
    
                WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                WebOperationContext.Current.OutgoingResponse.StatusDescription = ex.Message
    
                XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                Return XmlBodyDocument.DocumentElement
            End Try
            Dispose()
        End Function
    
        Public Function GetProducts() As XmlElement Implements UmbrellaMobileServiceInterface.GetProducts
            Dim xmlData As String = "<schema xmlns=""http://www.w3.org/2001/XMLSchema""></schema>"
            Dim XmlBodyDocument As New XmlDocument
            Try
    
                Dim SQLSTR As String = ""
                'SQLSTR = "SELECT '' as [code], '' as [description], cast('0' as decimal(18,6)) as [quantity], '' as [uom], cast('0' as decimal(18,6)) as [price] FROM Stock Union SELECT Stock.code, Stock.description,isnull((Select SUM(StockMovement.Qty) from StockMovement where StockMovement.StockID = Stock.StockID),0.00) as [quantity], (Select UnitOfMeasure.Code from UnitOfMeasure where UnitOfMeasure.UOMID = Stock.UOMID) as [uom], isnull((Select Top 1 StockPriceList.UnitSellingPrice from StockPriceList where StockPriceList.StockID = Stock.StockID and StockPriceList.UOMID = Stock.UOMID),0.00) as [price] FROM Stock Where Stock.Active = '1' Order by Stock.Code"
                SQLSTR = "SELECT Stock.code, Stock.description,isnull((Select SUM(StockMovement.Qty) from StockMovement where StockMovement.StockID = Stock.StockID),0.00) as [quantity], (Select UnitOfMeasure.Code from UnitOfMeasure where UnitOfMeasure.UOMID = Stock.UOMID) as [uom], isnull((Select Top 1 StockPriceList.UnitSellingPrice from StockPriceList where StockPriceList.StockID = Stock.StockID and StockPriceList.UOMID = Stock.UOMID),0.00) as [price] FROM Stock Where Stock.Active = '1' Order by Stock.Code"
    
                Dim ErrorMessage As String = ""
                ErrorMessage = UmbrellaDataManagementObj.OpenConnection(SQLServer, UmbrellaDatabase, UserCode, UserPassword)
                If ErrorMessage <> "" Then
                    System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ErrorMessage & vbNewLine & UmbrellaDataManagementObj.ConnectionString, EventLogEntryType.Error)
                    WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                    WebOperationContext.Current.OutgoingResponse.StatusDescription = ErrorMessage
                    XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                    Return XmlBodyDocument.DocumentElement
                Else
                    Dim AllDetails As DataSet
                    AllDetails = UmbrellaDataManagementObj.GetDataSQL(SQLSTR)
                    If Not AllDetails Is Nothing Then
                        AllDetails.DataSetName = "products"
                        AllDetails.Tables(0).TableName = "product"
                        XmlBodyDocument.LoadXml(AllDetails.GetXml)
    
                        UmbrellaDataManagementObj.CloseConnection()
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 200
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = "OK"
                        Return XmlBodyDocument.DocumentElement
                    Else
                        System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", UmbrellaDataManagementObj.ErrorMessage, EventLogEntryType.Error)
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = UmbrellaDataManagementObj.ErrorMessage
    
                        XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                        Return XmlBodyDocument.DocumentElement
                    End If
                End If
            Catch ex As Exception
                System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ex.Message, EventLogEntryType.Error)
                WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                WebOperationContext.Current.OutgoingResponse.StatusDescription = ex.Message
    
                XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                Return XmlBodyDocument.DocumentElement
            End Try
            Dispose()
        End Function
    
        Public Function GetAllResources() As XmlElement Implements UmbrellaMobileServiceInterface.GetAllResources
            Dim xmlData As String = "<schema xmlns=""http://www.w3.org/2001/XMLSchema""></schema>"
            Dim XmlBodyDocument As New XmlDocument
            Try
                XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                Return XmlBodyDocument.DocumentElement
            Catch ex As Exception
                System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ex.Message, EventLogEntryType.Error)
                WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                WebOperationContext.Current.OutgoingResponse.StatusDescription = ex.Message
                XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                Return XmlBodyDocument.DocumentElement
            End Try
        End Function
    
    
        Private Function GetMessageContentFormat(ByVal Message As Message) As WebContentFormat
            Try
                Dim format As WebContentFormat = WebContentFormat.Default
                If Message.Properties.ContainsKey(WebBodyFormatMessageProperty.Name) Then
                    Dim bodyFormat As WebBodyFormatMessageProperty
                    bodyFormat = Message.Properties(WebBodyFormatMessageProperty.Name)
                    format = bodyFormat.Format
                End If
                Return format
    
            Catch ex As Exception
                System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ex.Message, EventLogEntryType.Error)
                WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                WebOperationContext.Current.OutgoingResponse.StatusDescription = ex.Message
                Return Nothing
            End Try
        End Function
    
    End Class
    


    Imports System
    Imports System.IO
    Imports System.Web
    Imports System.Web.Services
    Imports System.Web.Services.Protocols
    Imports System.Xml.Serialization
    Imports System.Xml
    Imports System.ComponentModel
    Imports System.ServiceModel.Activation
    Imports System.ServiceModel.Web
    Imports System.Configuration
    Imports System.Configuration.Install
    Imports System.ServiceModel
    Imports System.ServiceModel.Channels
    Imports System.ServiceProcess
    Imports System.Collections
    Imports System.Collections.Generic
    Imports System.Text
    
    <RunInstaller(True)> _
    Public Class ProjectInstaller
        Inherits Installer
        Private process As ServiceProcessInstaller
        Private service As ServiceInstaller
        Private components As System.ComponentModel.Container
    
        Public Sub New()
            process = New ServiceProcessInstaller()
            process.Account = ServiceAccount.LocalSystem
            service = New ServiceInstaller()
            service.ServiceName = "UmbrellaMobileService"
            service.DisplayName = "Umbrella Mobile Service"
            service.Description = "Handels Umbrella Mobile Requests."
            Installers.Add(process)
            Installers.Add(service)
        End Sub
    End Class
    
    ' Service class which implements the service contract.
    
    <ServiceBehavior(InstanceContextMode:=InstanceContextMode.PerSession)> _
    Public Class UmbrellaMobileService
        Inherits ServiceBase
        Public serviceHost As ServiceHost = Nothing
    
        Public Shared Sub Main()
            ServiceBase.Run(New UmbrellaMobileService())
        End Sub
    
        Public Sub New()
            ServiceName = "UmbrellaMobileService"
        End Sub
    
        'Start the Windows service.
        Protected Overloads Overrides Sub OnStart(ByVal args As String())
            If serviceHost IsNot Nothing Then
                serviceHost.Close()
            End If
            serviceHost = New WebServiceHost(GetType(UmbrellaService), New Uri("http://localhost/UmbrellaMobileService"))
            serviceHost.AddServiceEndpoint(GetType(UmbrellaMobileServiceInterface), New WebHttpBinding(), "http://localhost/UmbrellaMobileService")
    
            Dim objIniFile As New UmbrellaDataManagement.IniFile("c:\Agile\Umbrella\Umbrella.ini")
            If objIniFile.ErrorMessage = "" Then
                SQLServer = objIniFile.GetString("Settings", "SERVER", "")
                UmbrellaDatabase = objIniFile.GetString("Settings", "MOBILESERVICEDATABASE", "")
                UserCode = objIniFile.GetString("Settings", "USER", "")
                UserPassword = objIniFile.GetString("Settings", "PASSWORD", "")
                If Trim(UserCode) = "" Then
                    WindowsAuthentication = True
                Else
                    WindowsAuthentication = False
                End If
            Else
                System.Diagnostics.EventLog.WriteEntry("UmbrellaMobileService", objIniFile.ErrorMessage, EventLogEntryType.Error)
                serviceHost.Close()
            End If
    
            serviceHost.Open()
        End Sub
    
        ' Stop the Windows service.
        Protected Overloads Overrides Sub OnStop()
            If serviceHost IsNot Nothing Then
                serviceHost.Close()
                serviceHost = Nothing
            End If
        End Sub
    End Class
    
    <AspNetCompatibilityRequirements(Requirementsmode:=AspNetCompatibilityRequirementsMode.Allowed)> _
    Public Class UmbrellaService
        Inherits System.Web.Services.WebService
        Implements UmbrellaMobileServiceInterface
    
        Public Function GetSalesOrders() As XmlElement Implements UmbrellaMobileServiceInterface.GetSalesOrders
            Dim xmlData As String = "<schema xmlns=""http://www.w3.org/2001/XMLSchema""></schema>"
            Dim XmlBodyDocument As New XmlDocument
            Try
    
                Dim SQLSTR As String = ""
                SQLSTR = "Select distinct SalesOrder.Number, SalesOrder.Reference, SalesOrder.CustomerOrderNumber,	SalesOrder.Date, SalesOrder.DateApproved, SalesOrder.DateCancelled, (Select Code From Companies Where Companies.InternalID = SalesOrder.CustomerID) as [Customer], (Select Name From Companies Where Companies.InternalID = SalesOrder.CustomerID) as [CustomerSite], (Select Name From Companies Where Companies.InternalID = SalesOrder.SalesmanID) as [Salesman], (Select SUM(SalesOrderDetail.TotalExcluding) from SalesOrderDetail where SalesOrderDetail.HeaderID = SalesOrder.HeaderID  ) as [TotalExcluding], (Select SUM(SalesOrderDetail.TotalVAT) from SalesOrderDetail where SalesOrderDetail.HeaderID = SalesOrder.HeaderID  ) as [TotalVAT], (Select SUM(SalesOrderDetail.TotalIncluding) from SalesOrderDetail where SalesOrderDetail.HeaderID = SalesOrder.HeaderID  ) as [TotalIncluding] FROM SalesOrder"
    
                Dim ErrorMessage As String = ""
                ErrorMessage = UmbrellaDataManagementObj.OpenConnection(SQLServer, UmbrellaDatabase, UserCode, UserPassword)
                If ErrorMessage <> "" Then
                    System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ErrorMessage & vbNewLine & UmbrellaDataManagementObj.ConnectionString, EventLogEntryType.Error)
                    WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                    WebOperationContext.Current.OutgoingResponse.StatusDescription = ErrorMessage
                    XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                    Return XmlBodyDocument.DocumentElement
                Else
                    Dim AllDetails As DataSet
                    AllDetails = UmbrellaDataManagementObj.GetDataSQL(SQLSTR)
                    If Not AllDetails Is Nothing Then
                        AllDetails.DataSetName = "salesorders"
                        AllDetails.Tables(0).TableName = "salesorder"
                        XmlBodyDocument.LoadXml(AllDetails.GetXml)
    
                        UmbrellaDataManagementObj.CloseConnection()
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 200
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = "OK"
                        Return XmlBodyDocument.DocumentElement
                    Else
                        System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", UmbrellaDataManagementObj.ErrorMessage, EventLogEntryType.Error)
    
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = UmbrellaDataManagementObj.ErrorMessage
    
                        XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                        Return XmlBodyDocument.DocumentElement
                    End If
                End If
    
            Catch ex As Exception
                System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ex.Message, EventLogEntryType.Error)
    
                WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                WebOperationContext.Current.OutgoingResponse.StatusDescription = ex.Message
    
                XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                Return XmlBodyDocument.DocumentElement
            End Try
            Dispose()
        End Function
    
        Public Function GetSalesOrder(ByVal salesorder As String) As XmlElement Implements UmbrellaMobileServiceInterface.GetSalesOrder
            Dim xmlData As String = "<schema xmlns=""http://www.w3.org/2001/XMLSchema""></schema>"
            Dim XmlBodyDocument As New XmlDocument
            Try
    
                Dim SQLSTR As String = ""
    
                Dim ErrorMessage As String = ""
                ErrorMessage = UmbrellaDataManagementObj.OpenConnection(SQLServer, UmbrellaDatabase, UserCode, UserPassword)
                If ErrorMessage <> "" Then
                    System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ErrorMessage & vbNewLine & UmbrellaDataManagementObj.ConnectionString, EventLogEntryType.Error)
                    WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                    WebOperationContext.Current.OutgoingResponse.StatusDescription = ErrorMessage
                    XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                    Return XmlBodyDocument.DocumentElement
                Else
                    Dim SalesOrderID As Long = 0
                    SalesOrderID = UmbrellaDataManagementObj.ReadFieldInteger("SalesOrder", "Top 1 HeaderID", " Where SalesOrder.Number = '" & salesorder & "'")
                    If SalesOrderID > 0 Then
                        SQLSTR = "Select distinct SalesOrder.Number, SalesOrder.Reference, SalesOrder.CustomerOrderNumber,	SalesOrder.Date, SalesOrder.DateApproved, SalesOrder.DateCancelled, (Select Code From Companies Where Companies.InternalID = SalesOrder.CustomerID) as [Customer], (Select Name From Companies Where Companies.InternalID = SalesOrder.CustomerID) as [CustomerSite], (Select Name From Companies Where Companies.InternalID = SalesOrder.SalesmanID) as [Salesman], (Select SUM(SalesOrderDetail.TotalExcluding) from SalesOrderDetail where SalesOrderDetail.HeaderID = SalesOrder.HeaderID  ) as [TotalExcluding], (Select SUM(SalesOrderDetail.TotalVAT) from SalesOrderDetail where SalesOrderDetail.HeaderID = SalesOrder.HeaderID  ) as [TotalVAT], (Select SUM(SalesOrderDetail.TotalIncluding) from SalesOrderDetail where SalesOrderDetail.HeaderID = SalesOrder.HeaderID  ) as [TotalIncluding] FROM SalesOrder Where SalesOrder.Number  = '" & salesorder & "'"
                    Else
                        SQLSTR = "Select distinct SalesOrder.Number, SalesOrder.Reference, SalesOrder.CustomerOrderNumber,	SalesOrder.Date, SalesOrder.DateApproved, SalesOrder.DateCancelled, (Select Code From Companies Where Companies.InternalID = SalesOrder.CustomerID) as [Customer], (Select Name From Companies Where Companies.InternalID = SalesOrder.CustomerID) as [CustomerSite], (Select Name From Companies Where Companies.InternalID = SalesOrder.SalesmanID) as [Salesman], (Select SUM(SalesOrderDetail.TotalExcluding) from SalesOrderDetail where SalesOrderDetail.HeaderID = SalesOrder.HeaderID  ) as [TotalExcluding], (Select SUM(SalesOrderDetail.TotalVAT) from SalesOrderDetail where SalesOrderDetail.HeaderID = SalesOrder.HeaderID  ) as [TotalVAT], (Select SUM(SalesOrderDetail.TotalIncluding) from SalesOrderDetail where SalesOrderDetail.HeaderID = SalesOrder.HeaderID  ) as [TotalIncluding] FROM SalesOrder Where SalesOrder.CustomerOrderNumber  = '" & salesorder & "'"
                    End If
                    Dim AllDetails As DataSet
                    AllDetails = UmbrellaDataManagementObj.GetDataSQL(SQLSTR)
                    If Not AllDetails Is Nothing Then
                        AllDetails.DataSetName = "salesorders"
                        AllDetails.Tables(0).TableName = "salesorder"
                        XmlBodyDocument.LoadXml(AllDetails.GetXml)
    
                        UmbrellaDataManagementObj.CloseConnection()
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 200
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = "OK"
                        Return XmlBodyDocument.DocumentElement
                    Else
                        System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", UmbrellaDataManagementObj.ErrorMessage, EventLogEntryType.Error)
    
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = UmbrellaDataManagementObj.ErrorMessage
    
                        XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                        Return XmlBodyDocument.DocumentElement
                    End If
                End If
    
            Catch ex As Exception
                System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ex.Message, EventLogEntryType.Error)
    
                WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                WebOperationContext.Current.OutgoingResponse.StatusDescription = ex.Message
    
                XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                Return XmlBodyDocument.DocumentElement
            End Try
            Dispose()
        End Function
    
        Public Function GetSalesOrderDetail(ByVal salesorder As String) As XmlElement Implements UmbrellaMobileServiceInterface.GetSalesOrderDetail
            Dim xmlData As String = "<schema xmlns=""http://www.w3.org/2001/XMLSchema""></schema>"
            Dim XmlBodyDocument As New XmlDocument
            Try
    
                Dim SQLSTR As String = ""
    
                Dim ErrorMessage As String = ""
                ErrorMessage = UmbrellaDataManagementObj.OpenConnection(SQLServer, UmbrellaDatabase, UserCode, UserPassword)
                If ErrorMessage <> "" Then
                    System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ErrorMessage & vbNewLine & UmbrellaDataManagementObj.ConnectionString, EventLogEntryType.Error)
                    WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                    WebOperationContext.Current.OutgoingResponse.StatusDescription = ErrorMessage
                    XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                    Return XmlBodyDocument.DocumentElement
                Else
                    Dim SalesOrderID As Long = 0
                    SalesOrderID = UmbrellaDataManagementObj.ReadFieldInteger("SalesOrder", "Top 1 HeaderID", " Where SalesOrder.Number = '" & salesorder & "'")
                    If SalesOrderID > 0 Then
                        SQLSTR = "Select SalesOrder.Number, SalesOrder.Reference, SalesOrder.CustomerOrderNumber,	SalesOrder.Date, SalesOrder.DateApproved, SalesOrder.DateCancelled, (Select Code From Companies Where Companies.InternalID = SalesOrder.CustomerID) as [Customer], (Select Name From Companies Where Companies.InternalID = SalesOrder.CustomerID) as [CustomerSite], (Select Name From Companies Where Companies.InternalID = SalesOrder.SalesmanID) as [Salesman], SalesOrderDetail.LineNumber, SalesOrderDetail.Note, SalesOrderDetail.LineType, case LineType when 1 then (Select Code from Account Where Account.AccountID = (Select AccountID from SubAccounts Where SubAccounts.SubAccountID = SalesOrderDetail.SubAccountID)) + '/' + (Select SubAccountCode from SubAccounts Where SubAccounts.SubAccountID = SalesOrderDetail.SubAccountID) when 2 then (Select Code from Stock Where Stock.StockID = SalesOrderDetail.ID) when 3 then (Select Code from Assets Where Assets.AssetID = SalesOrderDetail.ID) when 4 then (Select Code from Assets Where Assets.AssetID = SalesOrderDetail.ID) when 5 then '' when 6 then (Select Code from Companies Where Companies.InternalID = SalesOrderDetail.ID) + ' ' + (Select Name from Companies Where Companies.InternalID = SalesOrderDetail.ID) when 7 then (Select Code from Companies Where Companies.InternalID = SalesOrderDetail.ID) + ' ' + (Select Surname from EmployeeDetail Where EmployeeDetail.EmployeeID = SalesOrderDetail.ID) when 8 then (Select Code from Stock Where Stock.StockID = SalesOrderDetail.ID) end as [Code], SalesOrderDetail.Description, SalesOrderDetail.Weight, SalesOrderDetail.Qty, (SELECT Description FROM UnitOfMeasure WHERE (UOMID = SalesOrderDetail.UOMID)) AS UOM, SalesOrderDetail.UnitCost, SalesOrderDetail.ForeignUnitCost, SalesOrderDetail.UnitRate, SalesOrderDetail.ForeignRate, SalesOrderDetail.UnitDiscount, SalesOrderDetail.ForeignUnitDiscount, SalesOrderDetail.TotalWeight, SalesOrderDetail.TotalDiscount, SalesOrderDetail.ForeignTotalDiscount, SalesOrderDetail.DiscountPercentage, SalesOrderDetail.TotalExcluding, SalesOrderDetail.ForeignTotalExcluding, (SELECT Description FROM VATCodes WHERE (VATCodeID = SalesOrderDetail.VATCodeID)) AS VatCode, SalesOrderDetail.TotalVAT, SalesOrderDetail.TotalIncluding, SalesOrderDetail.ForeignTotalIncluding, SalesOrderDetail.RequestedDate, SalesOrderDetail.ConfirmationDate FROM SalesOrderDetail inner join SalesOrder on SalesOrder.HeaderID = SalesOrderDetail.HeaderID Where SalesOrder.Number = '" & salesorder & "' order by LineNumber"
                    Else
                        SQLSTR = "Select SalesOrder.Number, SalesOrder.Reference, SalesOrder.CustomerOrderNumber,	SalesOrder.Date, SalesOrder.DateApproved, SalesOrder.DateCancelled, (Select Code From Companies Where Companies.InternalID = SalesOrder.CustomerID) as [Customer], (Select Name From Companies Where Companies.InternalID = SalesOrder.CustomerID) as [CustomerSite], (Select Name From Companies Where Companies.InternalID = SalesOrder.SalesmanID) as [Salesman], SalesOrderDetail.LineNumber, SalesOrderDetail.Note, SalesOrderDetail.LineType, case LineType when 1 then (Select Code from Account Where Account.AccountID = (Select AccountID from SubAccounts Where SubAccounts.SubAccountID = SalesOrderDetail.SubAccountID)) + '/' + (Select SubAccountCode from SubAccounts Where SubAccounts.SubAccountID = SalesOrderDetail.SubAccountID) when 2 then (Select Code from Stock Where Stock.StockID = SalesOrderDetail.ID) when 3 then (Select Code from Assets Where Assets.AssetID = SalesOrderDetail.ID) when 4 then (Select Code from Assets Where Assets.AssetID = SalesOrderDetail.ID) when 5 then '' when 6 then (Select Code from Companies Where Companies.InternalID = SalesOrderDetail.ID) + ' ' + (Select Name from Companies Where Companies.InternalID = SalesOrderDetail.ID) when 7 then (Select Code from Companies Where Companies.InternalID = SalesOrderDetail.ID) + ' ' + (Select Surname from EmployeeDetail Where EmployeeDetail.EmployeeID = SalesOrderDetail.ID) when 8 then (Select Code from Stock Where Stock.StockID = SalesOrderDetail.ID) end as [Code], SalesOrderDetail.Description, SalesOrderDetail.Weight, SalesOrderDetail.Qty, (SELECT Description FROM UnitOfMeasure WHERE (UOMID = SalesOrderDetail.UOMID)) AS UOM, SalesOrderDetail.UnitCost, SalesOrderDetail.ForeignUnitCost, SalesOrderDetail.UnitRate, SalesOrderDetail.ForeignRate, SalesOrderDetail.UnitDiscount, SalesOrderDetail.ForeignUnitDiscount, SalesOrderDetail.TotalWeight, SalesOrderDetail.TotalDiscount, SalesOrderDetail.ForeignTotalDiscount, SalesOrderDetail.DiscountPercentage, SalesOrderDetail.TotalExcluding, SalesOrderDetail.ForeignTotalExcluding, (SELECT Description FROM VATCodes WHERE (VATCodeID = SalesOrderDetail.VATCodeID)) AS VatCode, SalesOrderDetail.TotalVAT, SalesOrderDetail.TotalIncluding, SalesOrderDetail.ForeignTotalIncluding, SalesOrderDetail.RequestedDate, SalesOrderDetail.ConfirmationDate FROM SalesOrderDetail inner join SalesOrder on SalesOrder.HeaderID = SalesOrderDetail.HeaderID Where SalesOrder.CustomerOrderNumber  = '" & salesorder & "' order by LineNumber"
                    End If
                    Dim AllDetails As DataSet
                    AllDetails = UmbrellaDataManagementObj.GetDataSQL(SQLSTR)
                    If Not AllDetails Is Nothing Then
                        AllDetails.DataSetName = "salesorders"
                        AllDetails.Tables(0).TableName = "salesorder"
                        XmlBodyDocument.LoadXml(AllDetails.GetXml)
    
                        UmbrellaDataManagementObj.CloseConnection()
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 200
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = "OK"
                        Return XmlBodyDocument.DocumentElement
                    Else
                        System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", UmbrellaDataManagementObj.ErrorMessage, EventLogEntryType.Error)
    
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = UmbrellaDataManagementObj.ErrorMessage
    
                        XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                        Return XmlBodyDocument.DocumentElement
                    End If
                End If
    
            Catch ex As Exception
                System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ex.Message, EventLogEntryType.Error)
    
                WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                WebOperationContext.Current.OutgoingResponse.StatusDescription = ex.Message
    
                XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                Return XmlBodyDocument.DocumentElement
            End Try
            Dispose()
        End Function
    
        Public Function GetPOD(ByVal Number As String) As Byte() Implements UmbrellaMobileServiceInterface.GetPOD
            Try
                Dim ErrorMessage As String = ""
                ErrorMessage = UmbrellaDataManagementObj.OpenConnection(SQLServer, UmbrellaDatabase, UserCode, UserPassword)
                If ErrorMessage <> "" Then
                    System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ErrorMessage & vbNewLine & UmbrellaDataManagementObj.ConnectionString, EventLogEntryType.Error)
                    WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                    WebOperationContext.Current.OutgoingResponse.StatusDescription = ErrorMessage
                    Return Nothing
                Else
                    Dim PODID As Long = 0
                    PODID = UmbrellaDataManagementObj.ReadFieldInteger("CustomerPODDocuments", "Top 1 CustomerPODDocumentID", " Where PODNo = '" & Trim(Number) & "' Order by CustomerPODDocumentID desc")
                    If PODID > 0 Then
                        Dim Attachment As Byte() = Nothing
                        Attachment = UmbrellaDataManagementObj.GetImage("CustomerPODDocuments", "DocumentAttached", " Where CustomerPODDocumentID = '" & PODID & "'")
                        If Not Attachment Is Nothing Then
                            Try
                                UmbrellaDataManagementObj.CloseConnection()
                                WebOperationContext.Current.OutgoingResponse.StatusCode = 200
                                WebOperationContext.Current.OutgoingResponse.StatusDescription = "OK"
                                Return Attachment
                            Catch ex As Exception
                                UmbrellaDataManagementObj.CloseConnection()
                                System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ex.Message, EventLogEntryType.Error)
                                WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                                WebOperationContext.Current.OutgoingResponse.StatusDescription = ex.Message
                                Return Nothing
                            End Try
                        Else
                            UmbrellaDataManagementObj.CloseConnection()
                            System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", UmbrellaDataManagementObj.ErrorMessage, EventLogEntryType.Error)
                            WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                            WebOperationContext.Current.OutgoingResponse.StatusDescription = UmbrellaDataManagementObj.ErrorMessage
                            Return Nothing
                        End If
                        Attachment = Nothing
                    Else
                        UmbrellaDataManagementObj.CloseConnection()
                        System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", UmbrellaDataManagementObj.ErrorMessage, EventLogEntryType.Error)
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = UmbrellaDataManagementObj.ErrorMessage
                        Return Nothing
                    End If
                End If
            Catch ex As Exception
                System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ex.Message, EventLogEntryType.Error)
                WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                WebOperationContext.Current.OutgoingResponse.StatusDescription = ex.Message
                Return Nothing
            End Try
            Dispose()
        End Function
    
        Public Function GetProduct(ByVal ProductCode As String) As XmlElement Implements UmbrellaMobileServiceInterface.GetProduct
            Dim xmlData As String = "<schema xmlns=""http://www.w3.org/2001/XMLSchema""></schema>"
            Dim XmlBodyDocument As New XmlDocument
            Try
    
                Dim SQLSTR As String = ""
                'SQLSTR = "SELECT '' as [code], '' as [description], cast('0' as decimal(18,6)) as [quantity], '' as [uom], cast('0' as decimal(18,6)) as [price] FROM Stock Union SELECT Stock.code, Stock.description, isnull((Select SUM(StockMovement.Qty) from StockMovement where StockMovement.StockID = Stock.StockID),0.00) as [quantity], (Select UnitOfMeasure.Code from UnitOfMeasure where UnitOfMeasure.UOMID = Stock.UOMID) as [uom], isnull((Select Top 1 StockPriceList.UnitSellingPrice from StockPriceList where StockPriceList.StockID = Stock.StockID and StockPriceList.UOMID = Stock.UOMID),0.00) as [price] FROM Stock Where Stock.Code = '" & Trim(ProductCode) & "'"
                SQLSTR = "SELECT Stock.code, Stock.description, isnull((Select SUM(StockMovement.Qty) from StockMovement where StockMovement.StockID = Stock.StockID),0.00) as [quantity], (Select UnitOfMeasure.Code from UnitOfMeasure where UnitOfMeasure.UOMID = Stock.UOMID) as [uom], isnull((Select Top 1 StockPriceList.UnitSellingPrice from StockPriceList where StockPriceList.StockID = Stock.StockID and StockPriceList.UOMID = Stock.UOMID),0.00) as [price] FROM Stock Where Stock.Code = '" & Trim(ProductCode) & "'"
    
                Dim ErrorMessage As String = ""
                ErrorMessage = UmbrellaDataManagementObj.OpenConnection(SQLServer, UmbrellaDatabase, UserCode, UserPassword)
                If ErrorMessage <> "" Then
                    System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ErrorMessage & vbNewLine & UmbrellaDataManagementObj.ConnectionString, EventLogEntryType.Error)
                    WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                    WebOperationContext.Current.OutgoingResponse.StatusDescription = ErrorMessage
                    XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                    Return XmlBodyDocument.DocumentElement
                Else
                    Dim AllDetails As DataSet
                    AllDetails = UmbrellaDataManagementObj.GetDataSQL(SQLSTR)
                    If Not AllDetails Is Nothing Then
                        AllDetails.DataSetName = "products"
                        AllDetails.Tables(0).TableName = "product"
                        XmlBodyDocument.LoadXml(AllDetails.GetXml)
    
                        UmbrellaDataManagementObj.CloseConnection()
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 200
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = "OK"
                        Return XmlBodyDocument.DocumentElement
                    Else
                        System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", UmbrellaDataManagementObj.ErrorMessage, EventLogEntryType.Error)
    
                        WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                        WebOperationContext.Current.OutgoingResponse.StatusDescription = UmbrellaDataManagementObj.ErrorMessage
    
                        XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                        Return XmlBodyDocument.DocumentElement
                    End If
                End If
            Catch ex As Exception
                System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ex.Message, EventLogEntryType.Error)
    
                WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                WebOperationContext.Current.OutgoingResponse.StatusDescription = ex.Message
    
                XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                Return XmlBodyDocument.DocumentElement
            End Try
            Dispose()
        End Function
    
    
        Public Function GetAllResources() As XmlElement Implements UmbrellaMobileServiceInterface.GetAllResources
            Dim xmlData As String = "<schema xmlns=""http://www.w3.org/2001/XMLSchema""></schema>"
            Dim XmlBodyDocument As New XmlDocument
            Try
                XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                Return XmlBodyDocument.DocumentElement
            Catch ex As Exception
                System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ex.Message, EventLogEntryType.Error)
                WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                WebOperationContext.Current.OutgoingResponse.StatusDescription = ex.Message
                XmlBodyDocument.Load(New System.IO.StringReader(xmlData))
                Return XmlBodyDocument.DocumentElement
            End Try
        End Function
    
    
        Private Function GetMessageContentFormat(ByVal Message As Message) As WebContentFormat
            Try
                Dim format As WebContentFormat = WebContentFormat.Default
                If Message.Properties.ContainsKey(WebBodyFormatMessageProperty.Name) Then
                    Dim bodyFormat As WebBodyFormatMessageProperty
                    bodyFormat = Message.Properties(WebBodyFormatMessageProperty.Name)
                    format = bodyFormat.Format
                End If
                Return format
    
            Catch ex As Exception
                System.Diagnostics.EventLog.WriteEntry("Umbrella Mobile Service", ex.Message, EventLogEntryType.Error)
                WebOperationContext.Current.OutgoingResponse.StatusCode = 501
                WebOperationContext.Current.OutgoingResponse.StatusDescription = ex.Message
                Return Nothing
            End Try
        End Function
    
    End Class
    

    Its implementation looks like this :

    <ServiceContract()> _
    Public Interface UmbrellaMobileServiceInterface
        <OperationContract()> _
        <WebGet(UriTemplate:="salesorders", bodystyle:=WebMessageBodyStyle.Bare, requestformat:=WebMessageFormat.Xml, responseformat:=WebMessageFormat.Json)> _
        Function GetSalesOrders() As Xml.XmlElement
    
        <OperationContract()> _
        <WebGet(UriTemplate:="salesorder/{salesorder}", bodystyle:=WebMessageBodyStyle.Bare, requestformat:=WebMessageFormat.Xml, responseformat:=WebMessageFormat.Json)> _
        Function GetSalesOrder(ByVal salesorder As String) As Xml.XmlElement
    
        <OperationContract()> _
        <WebGet(UriTemplate:="salesorderdetail/{salesorder}", bodystyle:=WebMessageBodyStyle.Bare, requestformat:=WebMessageFormat.Xml, responseformat:=WebMessageFormat.Json)> _
        Function GetSalesOrderDetail(ByVal salesorder As String) As Xml.XmlElement
    
        <OperationContract()> _
            <WebInvoke(Method:="POST", UriTemplate:="SO")> _
        Function CreateSO(ByVal request As Message) As String
    
        <OperationContract()> _
        <WebGet(UriTemplate:="SQL/{SQLScript}", bodystyle:=WebMessageBodyStyle.Bare, requestformat:=WebMessageFormat.Xml, responseformat:=WebMessageFormat.Json)> _
        Function GetSQL(ByVal SQLScript As String) As Xml.XmlElement
    
        <OperationContract()> _
        <WebGet(UriTemplate:="product/{productcode}", bodystyle:=WebMessageBodyStyle.Bare, requestformat:=WebMessageFormat.Xml, responseformat:=WebMessageFormat.Json)> _
        Function GetProduct(ByVal ProductCode As String) As Xml.XmlElement
    
        <OperationContract()> _
        <WebGet(UriTemplate:="products", bodystyle:=WebMessageBodyStyle.Bare, requestformat:=WebMessageFormat.Xml, responseformat:=WebMessageFormat.Json)> _
        Function GetProducts() As Xml.XmlElement
    
        <OperationContract()> _
        <WebGet(UriTemplate:="POD/{Number}", bodystyle:=WebMessageBodyStyle.Bare, requestformat:=WebMessageFormat.Xml, responseformat:=WebMessageFormat.Json)> _
        Function GetPOD(ByVal Number As String) As Byte()
    
        <OperationContract()> _
        <WebGet(UriTemplate:="", bodystyle:=WebMessageBodyStyle.Bare, requestformat:=WebMessageFormat.Xml, responseformat:=WebMessageFormat.Json)> _
        Function GetAllResources() As Xml.XmlElement
    End Interface
    


    As you can see, I am trying to return the data in JSON format. I am basically not sure if I am doing it right, or if there is something else needed. This is a REST service, that now has to expose data in JSON instead of XML.

    When I run this locally hosted service in my browser, all I get is:

    <schema xmlns=\"http:\/\/www.w3.org\/2001\/XMLSchema\">
    </schema>

    Now I want to know:

    Is my service code correct?

    Is this an IIS setting?

    How could I get the correct information returned in JSON?

    Any help would be appreciated,

    Hannes

    Wednesday, August 26, 2015 7:22 AM

All replies