executereader到xml的转换

rjzwgtxy  于 2021-07-29  发布在  Java
关注(0)|答案(2)|浏览(348)

在vb.net中创建了一个从数据库中读取记录的web服务。正在尝试转换使用读取的选定记录 ExecuteReader 转换为xml。

<WebMethod()>
Public Function GetRoles(ByVal ID As String)
    ..........

    With sqlcmd2
        .Connection = sqlconn2
        .CommandText = sqlquery2
        .CommandType = CommandType.Text
        .Parameters.AddWithValue("@ID", ID)
    End With
    sqlcmd2.CommandText = sqlquery2
    Dim officers As New XElement("GetAnOfficerRoles")
    Dim sqlreader2 = sqlcmd2.ExecuteReader()
    If sqlreader2 IsNot Nothing And sqlreader2.HasRows Then
        While sqlreader2.Read()
            Dim officer As New XElement("Officer")
            officers.Add(officer)
            officer.Add(New XElement("ID_LOGIN",sqlreader2("ID").ToString))
            officer.Add(New XElement("USER", sqlreader2("ID_USER").ToString))
            officer.Add(New XElement("ROLES", sqlreader2("ROLES").ToString))
        End While
    End If
    Dim settings As New XmlWriterSettings
    settings.Indent = True
    Dim mStream As New MemoryStream()
    Dim writer As XmlWriter = XmlWriter.Create(mStream, settings)
    officers.WriteTo(writer)
    writer.Flush()
    mStream.Position = 0
    Dim sReader As New StreamReader(mStream)
    Dim response As String = sReader.ReadToEnd()
    Return response

我得到的答复都是一句话。。

<anyType d1p1:type="q1:string"><?xml version="1.0" encoding="utf-8"?>
<GetAnOfficerRoles><Officer><ID>abcde</ID><USER>2</USER><ROLES> 1, 5, 9</ROLES></Officer></GetAnOfficerRoles></anyType>

我需要的是xml

HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetAnOfficerRolesResponse xmlns="http://tempuri.org/">
     <GetAnOfficerRoles>
         <Officer>
            <ID>abcded</ID>
            <USER>2</USER>
            <ROLES> 1, 5, 9</ROLES>
         </Officer>
    </GetAnOfficerRoles>
  </soap:Body>
</soap:Envelope>

非常感谢您的帮助。
我在其他项目中看到,它们使用了类和xmlserializer(gettype(classname))

<System.CodeDom.Compiler.GeneratedCodeAttribute("xsd", "4.8.3928.0"),  _
 System.SerializableAttribute(),  _
 System.Diagnostics.DebuggerStepThroughAttribute(),  _
 System.ComponentModel.DesignerCategoryAttribute("code"),  _
 System.Xml.Serialization.XmlTypeAttribute(AnonymousType:=true),  _
 System.Xml.Serialization.XmlRootAttribute([Namespace]:="", IsNullable:=false)>  _
Partial Public Class ClassName
9jyewag0

9jyewag01#

使用xml linq创建元素:

Imports System.Xml
Imports System.Xml.Linq
Imports System.IO
Module Module1

    Sub Main()

        Dim xml As String =
            "<?xml version=""1.0"" encoding=""utf-8""?>" + _
            "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">" + _
                "<soap:Body>" + _
                    "<GetAnOfficerRolesResponse xmlns=""http://tempuri.org/"">" + _
                        "<GetAnOfficerRoles>" + _
                        "</GetAnOfficerRoles>" + _
                    "</GetAnOfficerRolesResponse>" + _
                "</soap:Body>" + _
            "</soap:Envelope>"

        Dim Envelope As XDocument = XDocument.Parse(xml)

        Dim GetAnOfficerRoles As XElement = Envelope.Descendants().Where(Function(x) x.Name.LocalName = "GetAnOfficerRoles").FirstOrDefault()
        Dim ns As XNamespace = GetAnOfficerRoles.GetDefaultNamespace()

        While sqlreader2.Read()
            Dim officer As New XElement(ns + "Officer")
            GetAnOfficerRoles.Add(officer)

            officer.Add(New XElement(ns + "ID", sqlreader2("ID").ToString())
            officer.Add(New XElement(ns + "USERS", sqlreader2("USERS").ToString())
            officer.Add(New XElement(ns + "ROLES", sqlreader2("ROLES").ToString())
        End While

        Dim settings As New XmlWriterSettings
        settings.Indent = True
        Dim mStream As New MemoryStream()
        Dim writer As XmlWriter = XmlWriter.Create(mStream, settings)
        Envelope.WriteTo(writer)
        writer.Flush()
        mStream.Position = 0
        Dim sReader As New StreamReader(mStream)
        Dim response As String = sReader.ReadToEnd()
    End Sub
End Module
9avjhtql

9avjhtql2#

无需序列化类或多余代码,只需按如下方式更改getroles即可完成此操作:

<WebMethod()>
    Public Function GetRoles(ByVal ID As String, ByVal ROLE As String) As 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)()
        Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder("")

        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)
                    sb.AppendLine("<RECORDS>")
                    sb.AppendLine(String.Format("<LOGIN>{0}</LOGIN>", sqlreader2("LOGIN").ToString))
                    sb.AppendLine(String.Format("<ID>{0}</ID>", sqlreader2("ID").ToString))
                    sb.AppendLine(String.Format("<ROLES>{0}</ROLES>", sqlreader2("ROLES").ToString))
                    sb.AppendLine("</RECORDS>")
                End While
            End If

            'Return listOfficerRoles
            Return String.Format("<DOCUMENT>{0}</DOCUMENT>", sb.ToString)

    End Function

然后从客户端可以详细说明作为xml接收的字符串。

相关问题