Question
I'm currently creating a new C# project that has many SQL queries. Some are rather substantial and intricate. What is the most effective method for storing them? The database is utilized by many other apps, so although it would be ideal if I created stored procedures there, it would be great if I could retain the procedures that are unique to my application inside my program.
Choices seem to be:
- a literal string (
const string query ="Select * From MyTable")- advantages: straightforward and concise
- Cons: Long queries are messy without syntax highlighting.
- Make a file for each query as follows:
QueryName.sql- Advantages: neater for huge, complicated searches with syntax highlighting
- Cons: Multiple files for many queries (one query per file); maybe reading the query from the content file takes longer?
- Other suggestions?
I also wonder whether there is a simple approach to create strongly typed class definitions from the SQL queries.
Accepted Answer
An additional choice is:
Create a file for every query as follows:QueryName.sql
but use it as a resource into your assembly. With your SQL queries neatly tucked away in their own file inside your Visual Studio solution, you can quickly extract the SQL text from those embedded resources and avoid having actual files on disk.SqlCommand
objects.
To help you started, look at these resources:
Popular Answer
Why not just use Linq-to-SQL or Entity Framework?
If your table is called Foos, your code will look like this:
using(var db = new MyEntityBase()){
var selectedFoo = from foo in db.Foos
where foo.Bar > 4
select foo;
//Do stuff
}
It then results in SQL like:
select * from Foos where Bar = 4
Strong typing is used throughout the whole block of C# code above, and Entity Framework will generate all the data classes you need.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
