better way to store long SQL strings in C#

Question

I have searched around for a while for this and have not found anything.

I am storing some pretty long SQL select strings (a shorter one like this:)

      string mySelectQuery = "select distribution_stop_information.unique_id_no as stop_unique_id_no,distribution_line_items.unique_id_no as line_unique_id_no, stop_name, stop_address,route_code AS RouteCode, customer_reference," +
            "distribution_line_items.datetime_created, rma_number from distribution_stop_information join distribution_line_items on " +
            "distribution_line_items.unique_id_no = distribution_stop_information.unique_id_no " +
            "where distribution_line_items.datetime_created > '2/22/2017' and customer_no = '91000'";

then passing them by

var Ourstops = (List<stop_data>)db.Query<stop_data>(mySelectQuery);

This is cumbersome and produces hard to read/debug code.

What are some better ways of doing this?

Just a point of clairification - on this project I am not allowed to create any sprocs. Strictly a query only use type, and using postgresql (not that that matters that much here)

Answer 1

This is my preferred formatting, just one guy's opinion:

string mySelectQuery = @"
    select 
        distribution_stop_information.unique_id_no as stop_unique_id_no
        ,distribution_line_items.unique_id_no as line_unique_id_no, stop_name
        ,stop_address,route_code AS RouteCode, customer_reference
        ,distribution_line_items.datetime_created, rma_number 
    from 
        distribution_stop_information 
        join distribution_line_items on distribution_line_items.unique_id_no = distribution_stop_information.unique_id_no
    where 
        distribution_line_items.datetime_created > '2/22/2017' and customer_no = '91000'
";

Benefits

• Copy and paste right into sql management studio
• Using the @ (verbatim literal) eliminates all the quotes and concatenations
• Easy to also use $ for string interpolation
• commas in front make commenting lines out easier

But do be mindful of SQL Injection and use parameters as much as possible- which is pretty much always. (Edit from comments)

Answer 2

• Stored procs or views (SQL specific)
• Resources
• Use of @ to allow line breaks
• Configuration files
• Content management system (if you have one, and even then not too sure about that)
• Entity Framework so you don't have SQL (I'm not a fan BTW. I'd go SPs)

Answer 3

My favourite question! Like you perhaps, I find SQL in string literals the weirdest thing in programming. So weird that I went off and wrote QueryFirst, a visual studio extension for working intelligently with SQL. Your SQL lives in a .sql file, like god intended. You edit it with the marvellous TSQL editor, connected to the database with intellisense for tables and columns and syntax validation. Every time you save the file, QueryFirst checks that the query runs, then (re)generates the C# wrapper that lets you use it. Behind the scenes, QueryFirst compiles your SQL into the binary, and accesses it with GetManifestResourceStream. All your data access is continually integration tested and working, and you have no more SQL hanging around in string literals.