<![CDATA[
<%@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("<", "<").Replace(">", ">").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>
]]>