Calling SQL Datasets

Topics: General, Troubleshooting
Mar 10, 2011 at 3:40 PM

I am new to composite and want to start using it more.  How do I call in SQL datasets and then use it on a page rendered by composite.  Are there any whitepapers on this? 

Right now I do all of my dynamic calls using ASP and ASP.net in VB.

 

Thanks

Mar 10, 2011 at 3:52 PM

You can write all the .net code you want and execute it in the context of a page. The easiest is to use UserControls (.ascx), Master pages (.master) or the the MVC pattern to write your code and execute it.

Mar 10, 2011 at 5:01 PM

If you want the data to show up in Composite C1 as a first class citizen look into making a data provider that expose your data - check http://docs.composite.net/C1/Data/CustomDataProviders.aspx

If you just need to push your data our on pages, then Web Form Controls or MVC that talk directly to your data is the fastest way to get there (like burningice suggest). Check http://docs.composite.net/C1/ASP-NET.aspx

If you would like to expose your data to XSLT Functions (so you do the layout logic using XSLT) then look into the C# Functions feature - create functions that grab the data and return XElement, IEnumerable<XElement> etc. and then call them from your XSLT Functions. Check http://docs.composite.net/C1/XSLT.aspx for XSLT info.

Yet another way would be to create a "Inline C# Function" like the one below - and emit the markup you want from C#. This function will automatically be available to insert on pages etc.

using System.Xml.Linq;
using Composite.Core.Xml;

namespace Mawtex
{
  public static class InlineMethodFunction
  {
    public static XhtmlDocument Sample()
    {
      var doc = new XhtmlDocument();
      doc.Body.Add(
        new XElement( Namespaces.Xhtml + "h1", "Sample" ),
        new XElement( Namespaces.Xhtml + "p", "If you like mixing this up a lot..." )
        );
      return doc;
    }
  }
}

 

Mar 10, 2011 at 5:14 PM

Thanks....

I am looking at the SQL Functions and what is the Connection String format?  I entered a typical connections string for VB and it failed.... 

Mar 10, 2011 at 5:19 PM

A standard .NET connection string should work just fine - check http://www.connectionstrings.com/sql-server-2008#p1 - if not please provide details about the format / what error you get.

The SQL Functions will grab data based on your query and make it available as XML (for XSLT developers).

Mar 10, 2011 at 6:24 PM

Ok I got the function to work and my output is below...

<root>

     <NewDataSet>
         <Table>
            <First_Name>Drew</First_Name>
            <Last_Name>Beattie</Last_Name>
            <User_ID>16</User_ID>
        </Table>
         <Table1>
            <_x0040_AVAIL_TEAM>Red Lion Christian</_x0040_AVAIL_TEAM>
        </Table1>
         <Table2>
            <Return_x0020_Value>0</Return_x0020_Value>
        </Table2>
    </NewDataSet>
</root>
Now I created a a xslt function, but how do I call the values from the SQL function above?
Help please!
Mar 11, 2011 at 9:40 AM

If you want to use SQL function output in XSLT I recommend you to use next SQL function settings:

- on the "Settings" tab check option "Return result as XML- Yes, the command returns XML"

- on the "SQL command" tab at the end of your SELECT query use "FOR XML AUTO", something like this: SELECT * FROM MyTable FOR XML AUTO

 

Then you can call this SQL function in XSLT ("Function Calls" tab) and  process output result.