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.
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?
Recursive query in SqlHelper.ExecuteReader
Hello,
I'm trying to execute a recursive query in SqlHelper. The following code works without SqlHelper:
This is just a sample, but it works. If I try this in SqlHelper I get an exception:
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
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
is working on a reply...