ASP.NET Tutorial/ADO.net Database/to XML

Содержание

1 Retrieving XML from SQL Server sing FOR XML AUTO (C#)
2 Retrieving XML from SQL Server sing FOR XML AUTO (VB)
3 Run a query against a SQL Server database with an XML typed column. It then displays results in a tree-view.
4 The ASPX Page and XSLT to style the XML from SQL Server (C#)
- 4.1 List of Customers
5 The ASPX Page and XSLT to style the XML from SQL Server (VB)
- 5.1 List of Customers
6 XML query

Retrieving XML from SQL Server sing FOR XML AUTO (C#)

   <source lang="csharp">

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> File: Default.aspx.cs using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.IO; using System.Xml; using System.Xml.XPath; using System.Xml.Xsl; public partial class _Default : System.Web.UI.Page {

   protected void Page_Load(object sender, EventArgs e)
   {
       string connStr = "database=Northwind;Data Source=.\\SQLEXPRESS;User id=Tom;pwd=password";
       XmlDocument x = new XmlDocument();
       XPathNavigator xpathnav = x.CreateNavigator();
       using (SqlConnection conn = new SqlConnection(connStr))
       {
           conn.Open();
           SqlCommand command = new SqlCommand("select * from Customers as Customer for XML AUTO, ELEMENTS", conn);
           using (XmlWriter xw = xpathnav.PrependChild())
           {
               xw.WriteStartElement("Customers");
               using (XmlReader xr = command.ExecuteXmlReader())
               {
                   xw.WriteNode(xr, true);
               }
               xw.WriteEndElement();
           }
       }
       Response.ContentType = "text/xml";
       x.Save(Response.Output);
   }

}</source>

Retrieving XML from SQL Server sing FOR XML AUTO (VB)

   <source lang="csharp">

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> File: Default.aspx.vb Imports System.Data Imports System.Data.SqlClient Imports System.IO Imports System.Xml Imports System.Xml.XPath Imports System.Xml.Xsl Partial Class _Default

   Inherits System.Web.UI.Page
   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
          Handles Me.Load
       Dim connStr As String = "database=Northwind;Data Source=.\SQLEXPRESS;" & _
               " User id=Tom;pwd=password"
       Dim x As New XmlDocument()
       Dim xpathnav As XPathNavigator = x.CreateNavigator()
       Using conn As New SqlConnection(connStr)
           conn.Open()
           Dim command As New SqlCommand("select * from Customers as Customer " & _
               "for XML AUTO, ELEMENTS", conn)
           Using xw As XmlWriter = xpathnav.PrependChild()
               xw.WriteStartElement("Customers")
               Using xr As XmlReader = command.ExecuteXmlReader()
                   xw.WriteNode(xr, True)
               End Using
               xw.WriteEndElement()
           End Using
       End Using
       Response.ContentType = "text/xml"
       x.Save(Response.Output)
   End Sub

End Class</source>

Run a query against a SQL Server database with an XML typed column. It then displays results in a tree-view.

   <source lang="csharp">

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="Default"%> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">

   <title>XML from SQL 2005</title>

</head> <body>

       <form id="form1" runat="server">
           <asp:Button ID="GetDataButton" runat="server" Text="Go get data" 
               OnClick="GetDataButton_Click" />
               


           <asp:TreeView ID="TreeView1" runat="server">
           </asp:TreeView>
       </form>

</body> </html> File: Default.aspx.cs using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Xml;

public partial class Default : System.Web.UI.Page {

 private TreeNode Root;
 protected void GetDataButton_Click(object sender, EventArgs e)
 {
   string connString = "SERVER=(local)\\SQLEXPRESS;ATTACHDBFILENAME=|DataDirectory|mydata.mdf;INTEGRATED SECURITY=sspi"; //;USER INSTANCE=true;";
   using (SqlConnection conn = new SqlConnection(connString))
   {
     Root = new TreeNode("Families");
     TreeView1.Nodes.Add(Root);
     string cmdText = "SELECT * FROM SampleData";
     SqlCommand cmd = new SqlCommand(cmdText, conn);
     cmd.Connection.Open();
     SqlDataReader reader = cmd.ExecuteReader();
     while (reader.Read())
     {
       SqlXml xml = reader.GetSqlXml(1);
       ProcessData(xml);
     }
     reader.Close();
     cmd.Connection.Close();
   }
 }
 private void ProcessData(SqlXml xml)
 {
   XmlReader reader = xml.CreateReader();
   TreeNode familyNode=null, parentsNode=null, childrenNode=null;
   while (reader.Read())
   {
     if (reader.Depth == 1 && reader.NodeType == XmlNodeType.Element)
     {
       familyNode = new TreeNode(reader.ReadElementContentAsString());
       Root.ChildNodes.Add(familyNode);
       parentsNode = new TreeNode("Parents");
       childrenNode = new TreeNode("Children");
       familyNode.ChildNodes.Add(parentsNode);
       familyNode.ChildNodes.Add(childrenNode);
     }
     if (reader.Depth == 2 && reader.NodeType == XmlNodeType.Element)
     {
       string name = reader["name"];
       string role = reader["Role"];
       TreeNode member = new TreeNode(name);
       if (role.ToLowerInvariant().Equals("father") || role.ToLowerInvariant().Equals("mother"))
         parentsNode.ChildNodes.Add(member);
       else
         if (role.ToLowerInvariant().Equals("child"))
           childrenNode.ChildNodes.Add(member);
     }
   }
 }

}</source>

The ASPX Page and XSLT to style the XML from SQL Server (C#)

   <source lang="csharp">

<%@ Page Language="C#"

        AutoEventWireup="true" 
        CodeFile="Default.aspx.cs" 
        Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

                     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<asp:xml id="Xml1" runat="server" transformsource="~/Data.xslt"/> File: using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.IO; using System.Xml; using System.Xml.XPath; using System.Xml.Xsl; public partial class _Default : System.Web.UI.Page {

   protected void Page_Load(object sender, EventArgs e)
   {
       string connStr = "database=Northwind;Data Source=.\\SQLEXPRESS;User id=Tom;pwd=password";
       XmlDocument x = new XmlDocument();
       XPathNavigator xpathnav = x.CreateNavigator();
       using (SqlConnection conn = new SqlConnection(connStr))
       {
           conn.Open();
           SqlCommand command = new SqlCommand("select * from Customers as Customer for XML AUTO, ELEMENTS", conn);
           using (XmlWriter xw = xpathnav.PrependChild())
           {
               xw.WriteStartElement("Customers");
               using (XmlReader xr = command.ExecuteXmlReader())
               {
                   xw.WriteNode(xr, true);
               }
               xw.WriteEndElement();
           }
       }
       Xml1.XPathNavigator = xpathnav;
   }

} File: Data.xslt <?xml version="1.0" encoding="utf-8" ?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

   <xsl:output method="html"/>
   <xsl:template match="/">

List of Customers

<xsl:apply-templates select="//Customer"/>

Company Name

Contact Name

Contact Title

   </xsl:template>
   <xsl:template match="Customer">
       <tr>
           <td><xsl:value-of select="CompanyName"/></td>
           <td><xsl:value-of select="ContactName"/></td>
           <td><xsl:value-of select="ContactTitle"/></td>
       </tr>
   </xsl:template>

</xsl:stylesheet></source>

The ASPX Page and XSLT to style the XML from SQL Server (VB)

   <source lang="csharp">

<%@ Page Language="VB"

        AutoEventWireup="false" 
        CodeFile="Default.aspx.vb" 
        Inherits="_Default" %>

<asp:xml id="Xml1" runat="server" transformsource="~/Data.xslt"/> File: Data.xslt Imports System.Data Imports System.Data.SqlClient Imports System.IO Imports System.Xml Imports System.Xml.XPath Imports System.Xml.Xsl Partial Class _Default

   Inherits System.Web.UI.Page
   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
          Handles Me.Load
       Dim connStr As String = "database=Northwind;Data Source=.\SQLEXPRESS;" & _
               " User id=Tom;pwd=password"
       Dim x As New XmlDocument()
       Dim xpathnav As XPathNavigator = x.CreateNavigator()
       Using conn As New SqlConnection(connStr)
           conn.Open()
           Dim command As New SqlCommand("select * from Customers as Customer " & _
               "for XML AUTO, ELEMENTS", conn)
           Using xw As XmlWriter = xpathnav.PrependChild()
               xw.WriteStartElement("Customers")
               Using xr As XmlReader = command.ExecuteXmlReader()
                   xw.WriteNode(xr, True)
               End Using
               xw.WriteEndElement()
           End Using
       End Using
       Xml1.XPathNavigator = xpathnav
   End Sub

End Class File: Data.xslt <?xml version="1.0" encoding="utf-8" ?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

   <xsl:output method="html"/>
   <xsl:template match="/">

List of Customers

<xsl:apply-templates select="//Customer"/>

Company Name

Contact Name

Contact Title

   </xsl:template>
   <xsl:template match="Customer">
       <tr>
           <td><xsl:value-of select="CompanyName"/></td>
           <td><xsl:value-of select="ContactName"/></td>
           <td><xsl:value-of select="ContactTitle"/></td>
       </tr>
   </xsl:template>

</xsl:stylesheet></source>

XML query

   <source lang="csharp">

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="XmlQuery" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">

   <title>Untitled Page</title>

</head> <body>

   <form id="form1" runat="server">

   <asp:Literal id="XmlText" runat="server"></asp:Literal>

   </form>

</body> </html> File: Default.aspx.cs using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Web.Configuration; using System.Data.SqlClient; using System.Xml; using System.Text; public partial class XmlQuery : System.Web.UI.Page {

   protected void Page_Load(object sender, EventArgs e)
   {
   string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
   string customerQuery = "SELECT FirstName, LastName FROM Employees FOR XML AUTO, ELEMENTS";
   SqlConnection con = new SqlConnection(connectionString);
   SqlCommand com = new SqlCommand(customerQuery, con);
   StringBuilder str = new StringBuilder();
   try
   {
     con.Open();
     XmlReader reader = com.ExecuteXmlReader();
     while (reader.Read())
     {
       if ((reader.Name == "Employees") && (reader.NodeType == XmlNodeType.Element))
       {
         reader.ReadStartElement("Employees");
         str.Append(reader.ReadElementString("FirstName"));
         str.Append(" ");
         str.Append(reader.ReadElementString("LastName"));
         str.Append("
");
         reader.ReadEndElement();
       }
     }
     reader.Close();
   }
   finally
   {
     con.Close();
   }
   XmlText.Text = str.ToString();
   }

}</source>

ASP.NET Tutorial/ADO.net Database/to XML

Содержание

Retrieving XML from SQL Server sing FOR XML AUTO (C#)

Retrieving XML from SQL Server sing FOR XML AUTO (VB)

Run a query against a SQL Server database with an XML typed column. It then displays results in a tree-view.

The ASPX Page and XSLT to style the XML from SQL Server (C#)

List of Customers

The ASPX Page and XSLT to style the XML from SQL Server (VB)

List of Customers

XML query

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Инструменты

Навигация