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

Question

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

asp.net-mvc c# config dapper sql-scripts

Question

Now I using Dapper to process data

Code like:

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 will write many sql script like this above.

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

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

Gary

View more on Stack Overflow

bbsimonbb · Accepted Answer · 1/10/2017 10:21:11 PM

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.

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

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator