Writing data from sql database in an xml file without dataset-CodePudding

I want to write data from a SQL database in an XML file. I know that it is possible with dataset, but I don't want to use it, because I want to format my own XML file with XmlTextWriter.

I will give you some references.

With my connections string (name, password...) u build a new SqlConnection. Then i build a String where my SQL query is in. Then I open the connection, it works. But my problem is, that i do not know how I can write the value of the query in my XML File.

connection = New SqlConnection(connetionString)

SQL query

Dim city As String = "SELECT City FROM info WHERE No = '1'"

Code how i build my XML File.

Dim xmlfile As String = "path name"
        If IO.File.Exists(xmlfile) = True Then
            IO.File.Delete(xmlfile)
        End If
        Dim enc As New System.Text.UTF8Encoding
       Dim XMLbg As New Xml.XmlTextWriter(xmlfile, enc)
        With XMLbg
            .Formatting = Xml.Formatting.Indented
            .Indentation = 4
            .WriteStartDocument()
            .WriteStartElement("Data")              
   --------------------------------------------------------
            .WriteElementString("City", **'here must be the Data for the City'** )
     
            .WriteEndElement() 'Data
            '--------------------------------------------------------
            XMLbg.Close()
        End With
    Catch ex As Exception
        MessageBox.Show(ex.Message.ToString, "Exception ", MessageBoxButtons.OK, MessageBoxIcon.Error)

Maybe someone know how to do it. Thank you :)

CodePudding user response：

Consider using entity framework. No dataset, no SQL on code.

CodePudding user response：

OKay, no DataSet, just a DataTable.

I am not up on XML formats so I wasn't sure if the For Each belongs inside or outside the WriteStartElement...WriteEndElement. I am sure you know how it works.

Private Sub WriteXMLFile()
    Dim dt = GetDataForXMLFile()
    Dim xmlfile As String = "path name"
    If IO.File.Exists(xmlfile) = True Then
        IO.File.Delete(xmlfile)
    End If
    Dim enc As New System.Text.UTF8Encoding
    Dim XMLbg As New Xml.XmlTextWriter(xmlfile, enc)
    With XMLbg
        .Formatting = Xml.Formatting.Indented
        .Indentation = 4
        .WriteStartDocument()
        For Each row As DataRow In dt.Rows
            .WriteStartElement("Data")

            .WriteElementString("City", Convert.ToString(row(0)))

            .WriteEndElement() 'Data
        Next
        XMLbg.Close()
    End With

End Sub

Private connectionString As String = "Your connection string"

Private Function GetDataForXMLFile() As DataTable
    Dim dt As New DataTable
    Dim city As String = "SELECT City FROM info WHERE No = '1'"
    Using connection As New SqlConnection(connectionString),
        cmd As New SqlCommand(city, connection)
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function