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
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.