Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2794
  • Last Modified:

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
0
fantasylan
Asked:
fantasylan
1 Solution
 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now