asked on

How to serialize data in SQLDataReader into XML

Hi

I have a function like the one below

public void somefunction(int parameter)
{
//SqlConnection defined and working.
SqlCommand cmd = new SqlCommand("SELECT * FROM TABLE WHERE ID = parameter",SQLConnection);
SqlDataReader reader = cmd.ExecuteReader();
}

How do I convert the data in the SqlDataReader into XML so it can be used for a web method in a web-service. I probably think XML Serialization is the way to go out here but I have no clue how to serialize the SqlDataReader.

Thanks

graye

ADO.Net has it's own built-in classes to support exporting to an XML document. Typically, you'd pull a complete DataTable (rather than just iterate over the entries via a DataReader), and then just use the DataTable's own WriteXML method
http://msdn.microsoft.com/en-us/library/system.data.datatable.writexml.aspx
Attached is an example in VB.Net (but heys... the concepts are the same for C#), where "ds" is a DataSet with multiple DataTables.

'
    ' Export the DataSet to an XML file (without the schema)
    '
    Private Sub tsmiDataExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsmiDataExport.Click
        ' a quick sanity check...
        If ds.SOS.Count = 0 Then
            MsgBox("There's no data to export", MsgBoxStyle.Exclamation, "Warning")
            Exit Sub
        End If
 
        SaveFileDialog1.OverwritePrompt = True
        SaveFileDialog1.Filter = "XML files (*.xml)|*.xml|All files (*.*)|*.*"
        SaveFileDialog1.FileName = ds.SOS(0).PC_Name & "_" & Now.ToString("yyyyMMdd") & ".xml"
        SaveFileDialog1.DefaultExt = ".xml"
        If SaveFileDialog1.ShowDialog() <> Windows.Forms.DialogResult.OK Then
            Exit Sub
        End If
 
        Try
            ds.WriteXml(SaveFileDialog1.FileName)
        Catch ex As Exception
            MsgBox("Could not export data to XML file" & vbCr & ex.Message, MsgBoxStyle.Exclamation, "Error")
        End Try
    End Sub
 
    '
    ' Export the DataSet to an XML file (to include the Schema)
    '
    Private Sub tsmiDataExportSchema_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsmiDataExportSchema.Click
        ' a quick sanity check...
        If ds.SOS.Count = 0 Then
            MsgBox("There's no data to export", MsgBoxStyle.Exclamation, "Warning")
            Exit Sub
        End If
 
        SaveFileDialog1.OverwritePrompt = True
        SaveFileDialog1.Filter = "XML files (*.xml)|*.xml|All files (*.*)|*.*"
        SaveFileDialog1.FileName = ds.SOS(0).PC_Name & "_" & Now.ToString("yyyyMMdd") & ".xml"
        SaveFileDialog1.DefaultExt = ".xml"
        If SaveFileDialog1.ShowDialog() <> Windows.Forms.DialogResult.OK Then
            Exit Sub
        End If
 
        Try
            ds.WriteXml(SaveFileDialog1.FileName, XmlWriteMode.WriteSchema)
        Catch ex As Exception
            MsgBox("Could not export data to XML file" & vbCr & ex.Message, MsgBoxStyle.Exclamation, "Error")
        End Try
    End Sub

Open in new window

ASKER CERTIFIED SOLUTION

deepu chandran

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial