lundi 27 juillet 2020

ExecuteReader in to XML in VB.net

Created a Web Service in Vb.net which reads records from the Database. Trying to convert the selected record that is read using ExecuteReader in to XML

<WebMethod()>
Public Function GetRoles(ByVal ID As String, ByVal ROLE As String) As List(Of String)

    Dim log As String = ""
    Dim sqlconn As New SqlConnection
    Dim sqlconn2 As New SqlConnection
    Dim sqlcmd As New SqlCommand
    Dim listOfficerRoles As New List(Of String)()

    Try
        sqlconn2.ConnectionString = STR_CONFIG
        Dim sqlcmd2 = New SqlCommand
        sqlcmd2.Connection = sqlconn2
        sqlconn2.Open()
        Dim sqlquery2 = "select a.LOGIN,a.USER,
                                    STUFF((select ', ' + CAST(ROLE AS  VARCHAR(1000)) from STROLE b where 
                                    b.USER = a.USER
                                    FOR XML PATH('')),1,1,'') AS ROLES 
                                    from T_USER a where ID = @ID
                                    group by a.ID, a.LOGIN"
        With sqlcmd2
            .Connection = sqlconn2
            .CommandText = sqlquery2
            .CommandType = CommandType.Text
            .Parameters.AddWithValue("@ID", ID)
        End With
        sqlcmd2.CommandText = sqlquery2
        Dim ds = New DataSet()
        Dim sqlreader2 = sqlcmd2.ExecuteReader()
        If sqlreader2 IsNot Nothing And sqlreader2.HasRows Then
            While sqlreader2.Read()

                listOfficerRoles.Add(sqlreader2("LOGIN").ToString)
                listOfficerRoles.Add(sqlreader2("ID").ToString)
                listOfficerRoles.Add(sqlreader2("ROLES").ToString)
            End While
        End If
        Return listOfficerRoles
    Catch ex As Exception
        log += Environment.NewLine + "ERROR: " + ex.Message
    Finally
        If sqlconn.State <> ConnectionState.Closed Then
            sqlconn2.Close()
            sqlconn.Close()
        End If

    End Try
    Return Nothing
End Function

I created a class namely

<Serializable()>
Public Class RolesModel

    Private LOGIN As String
    Private ID As String
    Private ROLES As String

    Public Property LOGIN1 As String
        Get
            Return LOGIN
        End Get
        Set(value As String)
            LOGIN = value
        End Set
    End Property

    Public Property ID1 As String
        Get
            Return ID
        End Get
        Set(value As String)
            ID = value
        End Set
    End Property

    Public Property ROLES1 As String
        Get
            Return ROLES
        End Get
        Set(value As String)
            ROLES = value
        End Set
    End Property
End Class

The response which i get is the following

<ArrayOfString>
   <string>ABCD</string>
   <string>1</string>
   <string>1,2,3</string>
</ArrayOfString>

WHAT I NEED IS XML

<LOGIN>ABCD</LOGIN>
<ID>1</ID>
<ROLES>1,2,3</ROLES>

There is no need to write to any file. while working with C# i created an array of class which gets populated on ExecuteReader. the default return is XML in C# but in VB.net I don't understand how to achieve it Any help is greatly appreciated.




Aucun commentaire:

Enregistrer un commentaire