How to insert a query into another query in C# using Microsoft Visual Studio?

Question

Is it possible to insert a query into another query in C#? Currently I am connecting to an access database and when I insert the query into another query in SQL view on Microsoft Access, the data I want is shown. However when I insert the same code into C#, the query is underlined. Basically this query is to include the data from the query produced in "Day" view to the FROM part in query for "Month" view.

How do I modify the query to be able to run in C#?

The query in SQL view on Microsoft Access for "Day view" is :

SELECT outbound.[Receipt date], outbound.[Receive plant], Avg(outbound.[Aging Days]) AS [AvgOfAging Days] FROM outbound GROUP BY outbound.[Receipt date], outbound.[Receive plant] HAVING (((outbound.[Receipt date]) Is Not Null));

The query in SQL view on Microsoft Access for "Month view" is:

SELECT Format([Receipt date],"mm/yyyy") AS [Month], Avg(Day.[AvgOfAging Days]) AS [AvgOfAvgOfAging Days] FROM  (SELECT outbound.[Receipt date], outbound.[Receive plant], Avg(outbound.[Aging Days]) AS [AvgOfAging Days] FROM outbound WHERE (outbound.[Receipt date] > #4/1/2015#) GROUP BY outbound.[Receipt date], outbound.[Receive plant] HAVING (((outbound.[Receipt date]) Is Not Null))) AS [Day] WHERE Day.[Receive plant] = "2300" GROUP BY Format([Receipt date],"mm/yyyy"), Day.[Receive plant] HAVING (((Format([Receipt date],"mm/yyyy")) Is Not Null));

(For the date and plant I have hard coded values to test the query but my actual program should read the user selection)

The code that has been modified to fit the C# application for "Month" view is:

string cmdQuery = "SELECT Format([Receipt date],\"mm/yyyy\") AS [Month], Avg(Day.[AvgOfAging Days]) AS [AvgOfAvgOfAging Days] "
                                + "FROM ("SELECT outbound.[Receipt date], outbound.[Receive plant], Avg(outbound.[Aging Days]) AS [AvgOfAging Days] " + "FROM outbound " + "WHERE ([Receipt date] >= #" + dateFrom + "#) AND ([Receipt date] <= #" + dateTo + "#) AND ((outbound.[Receive plant]) ='" + plant[i] + "')" + "GROUP BY outbound.[Receipt date], outbound.[Receive plant] " + "HAVING (((outbound.[Receipt date]) Is Not Null)); ) AS [Day] "

                                + "WHERE ((Day.[Receive plant]) ='" + plant[i] + "') "
                                + "GROUP BY Format([Receipt date],\"mm/yyyy\"), Day.[Receive plant] "
                                + "HAVING (((Format([Receipt date],\"mm/yyyy\")) Is Not Null)) ";

How do I edit the query above so that this query will be able to run in C#?

Thanks.

Answer 1

I think this will work - but I would strongly suggest using a more maintainable way...

  string cmdQuery = "SELECT Format([Receipt date],\"mm/yyyy\") AS [Month], Avg(Day.[AvgOfAging Days]) AS [AvgOfAvgOfAging Days] "
                            + "FROM ( "+ "SELECT outbound.[Receipt date], outbound.[Receive plant], Avg(outbound.[Aging Days]) AS [AvgOfAging Days] " + "FROM outbound " + "WHERE ([Receipt date] >= #" + dateFrom + "#) AND ([Receipt date] <= #" + dateTo + "#) AND ((outbound.[Receive plant]) ='" + plant[i] + "')" + "GROUP BY outbound.[Receipt date], outbound.[Receive plant] " + "HAVING (((outbound.[Receipt date]) Is Not Null)); ) AS [Day] "

                            + "WHERE ((Day.[Receive plant]) ='" + plant[i] + "') "
                            + "GROUP BY Format([Receipt date],\"mm/yyyy\"), Day.[Receive plant] "
                            + "HAVING (((Format([Receipt date],\"mm/yyyy\")) Is Not Null)) ";

Answer 2

1. Switch to parameterized values / placeholders (SQL Command Parameters); and then

2. Use string.Format to insert the sub-query, which should be a valid query by itself.

Simply add the inner parameters to the final SQL Command object, along with any parameters for the outer query. That is to say, the string building is only for the query body and not for any data.

To make life easier the inner query should also be located in a derived-table location surrounded by parenthesis and given an alias name.

For example:

var innerQuery = "SELECT .. WHERE name = @name";
var finalQuery = string.Format("SELECT * FROM ({0}) AS T ..", innerQuery);

var cmd = conn.NewCommand();
cmd.CommandText = finalQuery;
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = "Fred";

If this approach is followed then the only thing of concern is avoiding name clashes, which is easy if defining (and following) relevant rules.

---

Alternatively, use one of the many ORM/Mappers/LINQ providers that allow constructing queries, including embedded queries, in type-safe ways.

If continuing to write raw SQL in such a manner also consider using verbatim string literals.