private void Page_Load(object sender, System.EventArgs e)
{
StringBuilder sb = new StringBuilder();
XmlDocument doc = new XmlDocument();
XmlNode root, fields, rows;
SqlConnection con = null;
SqlDataReader rdr = null;
SqlCommand cmd;
ArrayList parameters = new ArrayList();
int i;
//
// Connect to SQL.
//
try {
con = new Arena.DataLib.SqlDbConnection().GetDbConnection();
con.Open();
cmd = con.CreateCommand();
//
// Do some custom replacement.
//
cmd.CommandText = SQLQuerySetting.ReplaceNonCaseSensitive("@@PersonID@@", (ArenaContext.Current.Person != null ? ArenaContext.Current.Person.PersonID.ToString() : "-1"));
//
// Put in all Query Parameters configured.
//
foreach (String qp in QueryParametersSetting)
{
if (!String.IsNullOrEmpty(qp))
{
String[] opts = qp.Split('=');
String o, v = null;
o = opts[0];
if (opts.Length == 2)
{
v = opts[1];
}
if (Request.QueryString[o] != null)
{
cmd.Parameters.Add(new SqlParameter(String.Format("@{0}", o), Request.QueryString[o]));
}
else
{
cmd.Parameters.Add(new SqlParameter(String.Format("@{0}", o), (v == null ? (object)DBNull.Value : (object)v)));
}
}
}
//
// Execute the reader.
//
rdr = cmd.ExecuteReader();
//
// Start creating the XML output.
//
root = doc.CreateElement("sql");
doc.AppendChild(root);
//
// Put in all the field names under a fields element.
//
fields = doc.CreateElement("fields");
root.AppendChild(fields);
for (i = 0; i < rdr.FieldCount; i++)
{
XmlNode field;
if (!SuppressColumnsSetting.Contains(rdr.GetName(i)))
{
field = doc.CreateElement("field");
field.InnerText = rdr.GetName(i).Replace("_", " ");
fields.AppendChild(field);
}
}
//
// Load in each row of data under a rows element.
//
rows = doc.CreateElement("rows");
root.AppendChild(rows);
while (rdr.Read())
{
XmlNode row;
row = doc.CreateElement("row");
rows.AppendChild(row);
//
// Each row is comprised of one or more field name elements.
//
for (i = 0; i < rdr.FieldCount; i++)
{
XmlNode node;
if (!SuppressColumnsSetting.Contains(rdr.GetName(i)))
{
node = doc.CreateElement(rdr.GetName(i));
node.InnerText = rdr[i].ToString();
row.AppendChild(node);
}
}
}
//
// Prepare the translator to convert the XML via XSLT.
//
XPathNavigator navigator = doc.CreateNavigator();
XslCompiledTransform transform = new XslCompiledTransform();
XsltArgumentList argsList = new XsltArgumentList();
transform.Load(base.Server.MapPath(XsltUrlSetting));
argsList.AddParam("ControlID", "", this.ClientID);
foreach (String p in XSLTParametersSetting)
{
try {
String[] s = p.Split('=');
if (s.Length == 1)
{
if (Request.QueryString[s[0]] != null)
{
argsList.AddParam(s[0], "", Request.QueryString[s[0]]);
}
}
else
{
argsList.AddParam(s[0], "", s[1]);
}
}
catch (System.Exception ex)
{
}
}
//
// Translate and store the data.
//
transform.Transform((IXPathNavigable)navigator, argsList, new StringWriter(sb));
ltContent.Text = sb.ToString();
//
// If RawXmlSetting is True, output only XML.
//
if (RawXmlSetting == true)
{
Response.Write(sb.ToString());
Response.ContentType = "application/xml";
Response.End();
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
//
// Close all our SQL connections.
//
if (rdr != null)
{
rdr.Close();
}
if (con != null)
{
con.Close();
}
}
}
