How to read XML based SQL Server result set (query result) using ADO.NET

by Jagadish Pulakhandam on 8/5/2011 12:42:14 PM
Rated 0 from 0 votes
Brief: Demonstrates on retrieving XML returned by an SQL Server Query which includes FOR XML clause.
Posted to: Connected way of Querying Data using ADO.NET
Add to DiggAdd to del.icio.usAdd to FURLAdd to RedditAdd to YahooAdd to BlinklistAdd to GoogleAdd to ma.gnoliaAdd to ShadowsAdd to Technorati

This sample demonstrates on retrieving XML result set sent by SQL Server using ADO.NET.  This sample mainly uses "ExecuteXmlReader" method available in "SqlCommand" class.

Screenshot:



Source Code:

01.using System;
02.using System.Collections.Generic;
03.using System.ComponentModel;
04.using System.Data;
05.using System.Drawing;
06.using System.Linq;
07.using System.Text;
08.using System.Windows.Forms;
09.using System.Data.SqlClient;
10. 
11.namespace CSResultsInXML
12.{
13.    public partial class Form1 : Form
14.    {
15.        string CONNSTR = "Data Source=.\\sql2k8; Initial Catalog=Northwind; User Id=sa; Password=sql2008";
16. 
17.        public Form1()
18.        {
19.            InitializeComponent();
20.        }
21. 
22.        private void btnShow_Click(object sender, EventArgs e)
23.        {
24.            this.TextBox1.Text = string.Empty;
25.            using (SqlCommand cmd = new SqlCommand("SELECT OrderID, CustomerID FROM Orders FOR XML AUTO, XMLDATA", new SqlConnection(CONNSTR)))
26.            {
27.                StringBuilder sb = new StringBuilder();
28.                cmd.Connection.Open();
29.                System.Xml.XmlReader xmlr = cmd.ExecuteXmlReader();
30.                xmlr.Read();
31.                while (xmlr.ReadState != System.Xml.ReadState.EndOfFile)
32.                {
33.                    sb.AppendLine(xmlr.ReadOuterXml());
34.                }
35.                this.TextBox1.Text = sb.ToString();
36.            }
37. 
38.        }
39.    }
40.}

Join the .NET Code Central Community and join the discussion!
Signing-up is FREE and quick. Do it now, we want to hear your opinion
0

Rated 0 from 0 votes ( login  to rate)
DotnetKicks DotnetKicksDe DotNetShoutout

Attachments / Source Code
You need to Login or Join for FREE to download the following