/// <summary>
/// Executes a query against a database - returns the result for pretty displaying of the data in xslt
/// </summary>
/// <param name="args">
/// Accepts a piece of xml that can be mapped to the Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset method.
///
/// Read about it here: http://projekt.chainbox.dk/default.asp?W258
/// </param>
/// <returns>
/// The first datatable returned by the query
///
/// <root>
/// <item>
/// <fieldname>fieldvalue</fieldname>
/// <fieldname>fieldvalue</fieldname>
/// <fieldname>fieldvalue</fieldname>
/// </item>
/// <item>
/// <fieldname>fieldvalue</fieldname>
/// <fieldname>fieldvalue</fieldname>
/// <fieldname>fieldvalue</fieldname>
/// </item>
/// </root>
///
/// </returns>
public static XPathNodeIterator ExecuteDataset(XPathNodeIterator args)
{
try
{
XPathNodeIterator retval;
SqlHelperArgs sqlargs = ParseArgs(args);
DataSet ds = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(sqlargs.ConnectionString, sqlargs.CommandType, sqlargs.CommandText, sqlargs.Params.ToArray());
XmlDocument xd = new XmlDocument();
ds.DataSetName = "root";
// this is bad - the query might not return anything - but hey this is webdevelopment.
ds.Tables[0].TableName = "item";
using (StringWriter sw = new StringWriter())
{
ds.WriteXml(sw);
xd.LoadXml(sw.ToString());
}
retval = xd.CreateNavigator().Select(".");
WriteToTrace("ExecuteDataset: Returning data from:" + sqlargs.CommandText + " without using cache");
return(retval);
}
catch (Exception ex)
{
WriteToTrace(ex.ToString());
return(EmptyXpathNodeIterator(ex, "Xslt.ApplicationBlocks.Data.ExecuteDataset", true));
}
}
private static SqlHelperArgs ParseArgs(XPathNodeIterator args)
{
args.MoveNext();
XmlDocument xd = new XmlDocument();
xd.LoadXml(args.Current.OuterXml);
XmlElement argsel = (XmlElement)xd.FirstChild;
SqlHelperArgs retval = new SqlHelperArgs();
retval.CommandText = argsel.SelectSingleNode("commandtext").InnerText;
if (argsel.SelectSingleNode("commandtype").InnerText.ToLower() == "storedprocedure")
{
retval.CommandType = CommandType.StoredProcedure;
}
else
{
retval.CommandType = CommandType.Text;
}
foreach (XmlElement el in argsel.SelectNodes("parameter"))
{
string name = el.GetAttribute("name");
string value = el.InnerText;
retval.Params.Add(new SqlParameter(name, value));
}
// A connectionstring can either be specified in a custom app setting
// Be specified in the XSLT
// Or by default fall back to the umbraco connectionstring
XmlElement connectionstringel = (XmlElement)argsel.SelectSingleNode("connectionstring");
if (connectionstringel != null)
{
if (connectionstringel.HasAttribute("appkey"))
{
retval.ConnectionString = System.Configuration.ConfigurationManager.AppSettings[connectionstringel.GetAttribute("appkey")];
}
else
{
// you can specify the DBDSN directly in the XSLT file
retval.ConnectionString = connectionstringel.InnerText;
}
}
// If no connectionstring is specified
// this baby falls back to the umbraco connectionstring
else
{
retval.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["umbracoDbDSN"];
}
return(retval);
}
/// <summary>
/// Executes a query against a database without results
/// </summary>
/// <param name="args">
/// Accepts a piece of xml that can be mapped to the Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset method.
///
/// Read about it here: http://projekt.chainbox.dk/default.asp?W258
/// </param>
public static void ExecuteNonQuery(XPathNodeIterator args)
{
try
{
SqlHelperArgs sqlargs = ParseArgs(args);
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(sqlargs.ConnectionString, sqlargs.CommandType, sqlargs.CommandText, sqlargs.Params.ToArray());
}
catch (Exception ex)
{
WriteToTrace(ex.ToString());
}
}
/// <summary>
/// Executes a query against a database with a single result
/// </summary>
/// <param name="args">
/// Accepts a piece of xml that can be mapped to the Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset method.
///
/// Read about it here: http://projekt.chainbox.dk/default.asp?W258
/// </param>
/// <returns>
/// A string representation of the scalar sql result
/// </returns>
public static string ExecuteScalar(XPathNodeIterator args)
{
try
{
SqlHelperArgs sqlargs = ParseArgs(args);
return(Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteScalar(sqlargs.ConnectionString, sqlargs.CommandType, sqlargs.CommandText, sqlargs.Params.ToArray()).ToString());
}
catch (Exception ex)
{
WriteToTrace(ex.ToString());
}
return("Error executing sql - investigate trace");
}
private static SqlHelperArgs ParseArgs(XPathNodeIterator args)
{
args.MoveNext();
XmlDocument xd = new XmlDocument();
xd.LoadXml(args.Current.OuterXml);
XmlElement argsel = (XmlElement)xd.FirstChild;
SqlHelperArgs retval = new SqlHelperArgs();
retval.CommandText = argsel.SelectSingleNode("commandtext").InnerText;
if (argsel.SelectSingleNode("commandtype").InnerText.ToLower() == "storedprocedure")
{
retval.CommandType = CommandType.StoredProcedure;
}
else {
retval.CommandType = CommandType.Text;
}
foreach (XmlElement el in argsel.SelectNodes("parameter"))
{
string name = el.GetAttribute("name");
string value = el.InnerText;
retval.Params.Add(new SqlParameter(name, value));
}
// A connectionstring can either be specified in a custom app setting
// Be specified in the XSLT
// Or by default fall back to the umbraco connectionstring
XmlElement connectionstringel = (XmlElement)argsel.SelectSingleNode("connectionstring");
if (connectionstringel != null) {
if (connectionstringel.HasAttribute("appkey")) {
retval.ConnectionString = System.Configuration.ConfigurationManager.AppSettings[connectionstringel.GetAttribute("appkey")];
} else {
// you can specify the DBDSN directly in the XSLT file
retval.ConnectionString = connectionstringel.InnerText;
}
}
// If no connectionstring is specified
// this baby falls back to the umbraco connectionstring
else {
retval.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["umbracoDbDSN"];
}
return retval;
}
