I'm writing a new project in C# with a number of SQL queries in it. Some are relatively large and complex. I want to know what the best way to store them is. Ideally I would create stored procedures on the database, but the database is used by many other applications so it's better if I can keep the procedures which are specific to my application in my application.
Options seem to be:
const string query ="Select * From MyTable"
)
QueryName.sql
As an additional thought, is there a way to easily generate strongly typed class definitions from the SQL queries?
Another option would be:
4: Create a file for each query as QueryName.sql
but make it an embedded resource in your assembly. That way, you don't have physical files on disk, but your SQL queries are nicely tucked into their own file in your Visual Studio solution, and you can easily extract the SQL text from those embedded resources into your SqlCommand
objects.
Check out these resources to get you started:
Why not simply use Entity framework or Linq-to-SQL
If you have a table named Foos yoiu end up with code like:
using(var db = new MyEntityBase()){
var selectedFoo = from foo in db.Foos
where foo.Bar > 4
select foo;
//Do stuff
}
which in turns translate to SQL like:
select * from Foos where Bar = 4
the C# code above is all strongly typed and Entity framework will create all the needed data classes for you.