How can I manage my SQL query script in asp.net mvc project?

Question

I am using Dapper to process data.

Here is a code sample:

using(var connection = new SqlConnection(ConfigurationManager.AppSettings["MyConnectString"])
{
     var sql = string.Format(
         @"SELECT [Column1]
             FROM [MyTable]
            WHERE [Column3] > {0}
              AND [Column4] < {1}"
         , myValue1
         , myValue2
     );
     var result = connection.Query<long>(sql).ToList();
}

My project writes many sql scripts like the one above.

I want to write scripts to files (maybe QueryAccount.config, QueryOrder.config, (xml format), or others)

Then I can load the script from the files.

What I want is write my scripts in files, then I write the same class in file. (eg. I write all query Product scripts in Product.config, and all query Order scripts in Order.config) Then I use like:

var cmd = MyCommandManager.GetScript("QueryProduct");
cmd.SetParam("@ProductId", 123);
cmd.SetParam("@InvoicingDate", DateTime.Now(-7))
...

Script in file like:

SELECT [ProductName]
FROM [Product]
WHERE [ProductId] = @ProductId
  AND [InvoicingDate] = @InvoicingDate

Answer 1

Ignore the downvoters. SQL should always be in its own file. These files should have the extension .sql, not .config. As such, they will be edited in the VS SQL editor, a real comfort. You want one file per query I think. Nothing is gained by grouping different queries in the same file. I would advocate putting these files next to the .cs files that consume them, grouping together files you open together, and files you're likely to want to delete together one day.

Once created, right click the .sql in solution explorer, Properties --> Build Action --> Embedded resource. Then, in your MyCommandManager.GetScript() method, use GetManifestResourceStream() to access the query text. Compared to stored procedures, this has the huge advantage that your queries are compiled with the calling code, so you never have to worry about synching the version of your stored procedures and your application.

If all this seems like a lot of work, it kinda is. That's why no one does it, but they should :-) Grab QueryFirst, and it will be done for you, and a lot else besides. Disclaimer : I wrote QueryFirst.

Answer 2

If you have full access to the database you can try to implement a Stored Procedure to store the SQL text. All you would need to do is reference the Stored Proc name in the Dapper query and set the command type to StoredProcedure and you will be good to go, like so:

using(var cn = new SqlConnection("MyConnectionString"))
{
    cn.Open();
    return cn.Query<MyModel>("MyProcName", new { Parameter1 = myValue1, Parameter2 = myValue2 }, commandType: CommandType.StoredProcedure);
}

Using SQL Paramters instead of injecting the values into your query is a very smart thing to do, as it prevents SQL Injection attacks. reformatting your query like so would help:

@"SELECT [Column1]
  FROM [MyTable]
  WHERE [Column3] > @Parameter1
  AND [Column4] < @Parameter2"

Notice my parameter names match the dapper call above. When I do not use Stored Procedures, however, I usually create a private const string at the top of the class that references the query for my "storage".

public class QueryClass 
{
    private const string query = "SELECT * FROM Table1";

    public IEnumerable<MyModel> CallQuery()
    {
        // Dapper Query Details
    }
}

I subscribe to a Command/Query Pattern like this one so I never have an issue with query storage since each class usually has one query.

EDIT

If you like the Command/Query pattern, I recommend you check out MediatR as it is a nice implementation of such pattern.

SECOND EDIT

I see what you are trying to do by adding the SQL Queries to some sort of config file, however if I can I would like to advise you against that. At my last job, all SQL Queries were stored in XML files that were compiled into the application. It seemed like an effective way to manage queries, but once the application grew to even a few SQL XML files we had a hard time managing what queries could be found where, and eventually we had queries duplicated in several XML files. We also had many problems with typo's and other XML structure errors that were not caught until runtime, but I guess you can have typo's in any string so that won't necessarily go away. It ends up being a mess and causing more problems than it solves.

I believe that having the SQL query text as close to the code that requires it as possible is a better alternative, and if you are clever about namespacing and organizing query objects, you can make it easy for devs to find a query via intellisense.