Example #1
0
        /// <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));
            }
        }
Example #2
0
        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);
        }
Example #3
0
 /// <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());
     }
 }
Example #4
0
 /// <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;
        }