<%@Page Debug="false"%> <%@Import Namespace="System"%> <%@Import Namespace="System.Data"%> <%@Import Namespace="System.Data.SqlClient"%> <%@Import Namespace="System.IO"%> <script language="c#" runat="server"> private string m_sConnStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=MySQLServer\\MyInstance"; private string m_sXML = ""; //_______________________________________________________________________________________________________ private void Page_Load(object sender, System.EventArgs e) { //Grab whatever XML string we have hidden in the form m_sXML = lblHidden.Text.Trim(); if (!Page.IsPostBack) { //Only do this the first time in (in order to //save trips to the database every time) BindDataGridToDataSet(m_sConnStr); } } //_______________________________________________________________________________________________________ private void btnDataSetToXML_Click(object sender, System.EventArgs e) { //Display XML DisplayXMLFromDataSet(ref m_sXML, m_sConnStr); //Reset hidden XML values lblHidden.Text = m_sXML; } //_______________________________________________________________________________________________________ private void btnXMLToDataSet_Click(object sender, System.EventArgs e) { //Display DataGrid DisplayGridFromXML(ref m_sXML, m_sConnStr); //Reset hidden XML values lblHidden.Text = m_sXML; } //_______________________________________________________________________________________________________ private void DisplayXMLFromDataSet(ref string sXML, string sConnStr) { try { //If the XML variable is zero-length, get some XML from a DataSet if (sXML.Trim()==string.Empty) sXML = GetXMLFromDataSet(sConnStr); //Toggle visibility dgMain.Visible = false; lblXML.Visible = true; //Display the XML string lblXML.Text = FormatXMLToHTML(sXML); } catch(Exception exp) { HandleGeneralErrors("cbDefault.DisplayXMLFromDataSet()", exp); } } //_______________________________________________________________________________________________________ private void DisplayGridFromXML(ref string sXML, string sConnStr) { DataSet ds = new DataSet(); StringReader rdr; try { //If the XML variable is zero-length, get some XML from a DataSet if (sXML.Trim()==string.Empty) sXML = GetXMLFromDataSet(sConnStr); //Create a StringReader to hold the XML rdr = new StringReader(sXML); //Stuff XML into a DataSet ds.ReadXml(rdr); //Toggle visibility dgMain.Visible = true; lblXML.Visible = false; //Bind DataGrid to show what was in the XML string dgMain.DataSource = ds; dgMain.DataBind(); } catch(Exception exp) { HandleGeneralErrors("cbDefault.DisplayGridFromXML()", exp); } } //_______________________________________________________________________________________________________ private string GetXMLFromDataSet(string sConnStr) { string sXML = ""; try { //Load up the XML variable DataSet ds; ds = GetCustOrderHistDataSet(sConnStr); sXML = ds.GetXml(); } catch(Exception exp) { HandleGeneralErrors("cbDefault.GetXMLFromDataSet()", exp); } return sXML; } //_______________________________________________________________________________________________________ private void BindDataGridToDataSet(string sConnStr) { DataSet ds; try { //Grab a DataSet ds = GetCustOrderHistDataSet(sConnStr); //Bind it to the DataGrid dgMain.DataSource = ds; dgMain.DataBind(); } catch(Exception exp) { HandleGeneralErrors("cbDefault.BindDataGridToDataSet()", exp); } } //_______________________________________________________________________________________________________ private DataSet GetCustOrderHistDataSet(string sConnStr) { DataSet ds = new DataSet(); SqlConnection cnn = new SqlConnection(); SqlCommand cmd = new SqlCommand(); SqlDataAdapter da; try { //Create and open a connection object cnn.ConnectionString = sConnStr; cnn.Open(); //Set up command object cmd.Connection = cnn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "CustOrderHist"; cmd.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.NChar, 5, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Current, "ALFKI")); //Fill up DataSet da = new SqlDataAdapter(cmd); da.Fill(ds); } catch(Exception exp) { HandleGeneralErrors("cbDefault.GetCustOrderHist()", exp); } finally { //Clean up cmd = null; cnn.Close(); cnn = null; } //Send back the DataSet return ds; } //_______________________________________________________________________________________________________ private string FormatXMLToHTML(string sXML) { sXML = sXML.Replace("<", "&lt;").Replace(">", "&gt;").Replace("\n", "<br />"); return sXML; } //_______________________________________________________________________________________________________ private void HandleGeneralErrors(string sSender, Exception exp) { //Display error message lblError.Text = "<b>" + sSender + "</b><br />" + exp.ToString().Replace("\n", "<br />"); } //_______________________________________________________________________________________________________ </script> <HTML> <HEAD> <title>XML/DataGrid Example</title> <style> .FontNormal { font-size: 10pt; font-family: arial,helvetica; } .FontError { font-size: 8pt; color: #cc0000; font-family: arial,helvetica; } .FontXML{ font-size: 8pt; font-family: courier new,helvetica; color: #000099; } .ButtonNormal{ font-family: arial,helvetica; font-size: 8pt; background-color: #f7f7aa; border: 1px solid #6b696b; color: #000000; cursor: hand; } </style> </HEAD> <body> <form id="frmMain" method="post" runat="server"> <!-- Used to store the XML string --> <asp:Label Runat="server" ID="lblHidden" Visible="False" /> <table cellSpacing="0" cellPadding="3" border="0"> <tr> <td> <asp:Button Runat="server" ID="btnDataSetToXML" Text="DataSet To XML" CssClass="ButtonNormal" onClick="btnDataSetToXML_Click" /> <asp:Button Runat="server" ID="btnXMLToDataSet" Text="XML To DataSet" CssClass="ButtonNormal" onClick="btnXMLToDataSet_Click" /> </td> </tr> <tr> <td> <asp:datagrid id="dgMain" runat="server" AutoGenerateColumns="False" BorderColor="#6B696B" BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="4" GridLines="Vertical" ForeColor="Black" CssClass="FontNormal" > <SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#CE5D5A" /> <AlternatingItemStyle BackColor="White" /> <ItemStyle BackColor="#F7F7DE" /> <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#6B696B" /> <FooterStyle BackColor="#CCCC99" /> <Columns> <asp:BoundColumn DataField="ProductName" HeaderText="Product Name" /> <asp:BoundColumn DataField="Total" HeaderText="Total"> <ItemStyle HorizontalAlign="Right" /> </asp:BoundColumn> </Columns> </asp:datagrid> </td> </tr> <tr> <td> <asp:Label Runat="server" ID="lblXML" CssClass="FontXML" /> </td> </tr> </table> <p /> <asp:label id="lblError" runat="server" CssClass="FontError"></asp:label> </form> </P> </body> </HTML>