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
fantasylanAsked:
Who is Participating?
 
deepu chandranCommented:
hi,
refer the Below EE Link
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_20959743.html


Otherwise you can use SQlDataAdaptor for this, This will return a Dataset, that can be directly converted to XML

Regards
Deepu
0
 
grayeCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.