In C#, how do you incorporate SQL queries?

c# entity-framework sql

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:

  1. a literal string (const string query ="Select * From MyTable" )
    • advantages: straightforward and concise
    • Cons: Long queries are messy without syntax highlighting.
  2. 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?
  3. Other suggestions?

I also wonder whether there is a simple approach to create strongly typed class definitions from the SQL queries.

1
7
8/15/2011 12:07:05 PM

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:

21
8/15/2011 12:08:27 PM

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





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow