Best way to store queries outisde of program in C#

Question

I have some connection strings and queries that I need to store outside my C# program (can't hardcode them). Right now I'm using the application configuration file of Visual Studio. Unfortunately the queries require variables (chosen at run-time by the user) from my program to run.

My current workaround is to break up the query into pieces in the configuration file and reassemble them inside the program. I'd prefer to avoid this, since it makes it hard to read the query from the configuration file. Does anyone have a more elegant solution?

why not use stored procedures? – Jethro Aug 25 '11 at 19:17 — Jethro, Aug 25 '11 at 19:17

David · Accepted Answer · 2011-08-25T19:24:15.650

6

Stored procedures on the server that accept the parameters would be my preference. That protects you fairly well from sql injection (unless you construct them improperly), and is the commonly accepted best practice.

Otherwise, use parameterized queries. They can be stored in the config file. (But I'd be darn sure to encrypt the .config file if you continue to store sensitive data like connection strings and helpful things like workable queries in a plain-text config file.)

edited Aug 25 '11 at 19:24

answered Aug 25 '11 at 19:17

David

72,686
18
132
173

my exact thoughts. Nice one. +1 – Jethro Aug 25 '11 at 19:18
+1 for stored procedures. Best practice security wise and it's also an elegant solution to this problem. – Michael Aug 25 '11 at 19:18
Also, look into an O/R mapper like Entity Framework or NHibernate - this is slightly orthogonal to your question, but it bears on it. – Chris B. Behrens Aug 25 '11 at 19:22

score 1 · Answer 2 · answered Aug 25 '11 at 19:16

1

A common approach would be using placeholders and replacing them at run time.

answered Aug 25 '11 at 19:16

Sascha

10,231
4
41
65

score 1 · Answer 3 · answered Aug 25 '11 at 19:24

If you must have strings out of your program and can not use Stored Procedures, the easiest way would be to store string with already paramaters, something like this:

select * from Country where city = @City"

Load this string and add a parameter to your query. Always use parameters on query generation.

This is simple, straightforward and more SQL native approach that come in my mind now in your case.

Hope this helps.

Best way to store queries outisde of program in C#

3 Answers3