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