How do you embed sql queries in c#

Question

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:

1. a string literal (const string query ="Select * From MyTable")
   • Pros: simple, short
   • Cons: no Syntax highlighting, messy for long queries
2. Create a file for each query as QueryName.sql
   • Pros: syntax highlighting, neater for large, complex queries
   • Cons: lots of files for lots of queries (one query per file), maybe slower to read query from content file?
3. Any other ideas?

As an additional thought, is there a way to easily generate strongly typed class definitions from the SQL queries?

Answer 1

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:

• How to: Create Embedded Resources
• Understanding Embedded Resources in Visual Studio .NET
• How to Embed Resource Files in .NET Assemblies

Answer 2

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.