7

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ForbesLindesay
  • 10,482
  • 3
  • 47
  • 74
  • If you want to use stored procedures, why are you using an ORM? – Oded Aug 15 '11 at 11:07
  • Can you not create the SPs in an application specific schema? – kͩeͣmͮpͥ ͩ Aug 15 '11 at 11:08
  • You've tagged your question with "entity-framework" - are you using that? You shouldn't need "raw" SQL - just query the entity framework context. – Graham Clark Aug 15 '11 at 11:10
  • @Oded at the moment I'm not, although I was considering whether something like EF or LINQ to SQL might make more sense. In the past I've always stuck to pure SQL as I've found it hard to make performant and bug free LINQ to SQL. – ForbesLindesay Aug 15 '11 at 11:13
  • @David Am I right in thinking that would need to go in the database? If so it would mean updating all the customer's (separate) databases, which I'm not keen to do if it's not necessary. – ForbesLindesay Aug 15 '11 at 11:15
  • @Tuskan360 - you can do this kind of thing with a script – kͩeͣmͮpͥ ͩ Aug 15 '11 at 11:18
  • 2
    Possible duplicate of [Executing a SQL script stored as a resource](https://stackoverflow.com/questions/1379195/executing-a-sql-script-stored-as-a-resource) – Michael Freidgeim Dec 02 '17 at 06:21

3 Answers3

21

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:

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
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.

Doctor Jones
  • 21,196
  • 13
  • 77
  • 99
Rune FS
  • 21,497
  • 7
  • 62
  • 96
  • If I'm using EF, how do I go about things like Data Annotations in MVC? Where do you add attributes? – ForbesLindesay Aug 15 '11 at 11:31
  • 1
    OK, should've googled that first [MetadataTypeAttribute](http://blogs.oosterkamp.nl/blogs/jowen/archive/2008/10/16/metadatatype-attribute.aspx) if anyone else wants to know – ForbesLindesay Aug 15 '11 at 12:40
  • @ForbesLindesay you shouldn't feed your entity directly to MVC, that's for mappers and UiClasses are made for (ViewModels if you like) – Bogdan Mart Jan 31 '16 at 16:49
  • 1
    @Rune FS There are lot of things, that can't be done uing EF, like complex aggreagates and Recursive CTE. I'm using _context.Database.SqlQuery("SQL QUERY") for this – Bogdan Mart Jan 31 '16 at 16:50
  • @BogdanMart I agree you can't do everything with EF however since you can extent linq-to-sql to suit your needs you can do everything in linq-to-sql that you can do in SQL. You can even do more. We once integrated an external authorization into our linq queries. Is it necessarily worth the effort? Well that pretty much depends on the specific case but doable it definately is – Rune FS Jan 31 '16 at 18:48
  • @RuneFS that's interesting, thanks for a Tip, I'll look at extending IQueriable But I'm not using EF across whole project, i'm rather using it insire Repository, and complex SQL which can be moved into LINQ is called from single method, which should be covered with Integration Tests. Is it possible to extend LINQ to the point that it will understand Recursive CTE? I think it is, but I'll need to integrate tightly with EF core. Can you give me a reference, how Query is generated by framework? – Bogdan Mart Feb 06 '16 at 08:06
  • @BogdanMart I thikn you should write a seperate question detailing your context and giving an example – Rune FS Feb 06 '16 at 10:07
0

I woud go for any ORM like EF or Subsonic.

Yohann Canu
  • 167
  • 1
  • 8