Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Jeroen Breuer 4908 posts 12265 karma points MVP 5x admin c-trib
    Apr 04, 2011 @ 10:48
    Jeroen Breuer
    0

    Recursive query in SqlHelper.ExecuteReader

    Hello,

    I'm trying to execute a recursive query in SqlHelper. The following code works without SqlHelper:

    string sql = string.Format(@"
    WITH UmbracoResults (nodeId, alias, masterContentType) AS
    (
    -- Base case
    SELECT c.nodeId, c.alias, c.masterContentType FROM cmsContentType c
    WHERE nodeId = 1062

    UNION ALL

    -- Recursive step
    SELECT c.nodeId, c.alias, c.masterContentType
    FROM cmsContentType c
    INNER JOIN UmbracoResults -- Note the reference to CTE table name
    ON c.nodeId = UmbracoResults.masterContentType
    )

    SELECT * FROM UmbracoResults");

    //Get the data from the database.
    SqlConnection sqlConn = new SqlConnection(GlobalSettings.DbDSN);
    sqlConn.Open();
    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, sqlConn);
    DataSet dataSet = new DataSet();
    dataAdapter.Fill(dataSet);
    sqlConn.Close();

    This is just a sample, but it works. If I try this in SqlHelper I get an exception:

    string sql = string.Format(@"
    WITH UmbracoResults (nodeId, alias, masterContentType) AS
    (
    -- Base case
    SELECT c.nodeId, c.alias, c.masterContentType FROM cmsContentType c
    WHERE nodeId = 1062

    UNION ALL

    -- Recursive step
    SELECT c.nodeId, c.alias, c.masterContentType
    FROM cmsContentType c
    INNER JOIN UmbracoResults -- Note the reference to CTE table name
    ON c.nodeId = UmbracoResults.masterContentType
    )

    SELECT * FROM UmbracoResults");

    using (IRecordsReader dr = SqlHelper.ExecuteReader(sql))
    {
    while (dr.Read())
    {
    //Do stuff
    }
    }

    This give the following exception:

    Exception: {"Umbraco Exception (DataLayer): SQL helper exception in ExecuteReader"}
    InnerException:    {"Incorrect syntax near '('."}    System.Exception {System.Data.SqlClient.SqlException}

    I would like this query to work with SqlHelper because it's for a package and the code using the first sample propably won't work on all databases.

    Jeroen

  • Jeroen Breuer 4908 posts 12265 karma points MVP 5x admin c-trib
    Apr 04, 2011 @ 11:47
    Jeroen Breuer
    0

    In this recursive query I also want to build a string by concating all the found values. I know this can be done in a stored procedure, but can it also be done in a query which is executed in C# like the above samples?

    Jeroen

Please Sign in or register to post replies

Write your reply to:

Draft