What is the best way to query data from a MS SQL Server in C#?

I know that it is not good practice to have an SQL query in the code.

Is the best way to create a stored procedure and call it from C# with parameters?

using (var conn = new SqlConnection(connStr))
using (var command = new SqlCommand("StoredProc", conn) { CommandType = CommandType.StoredProcedure }) {
  • 8
    "I know that it is not good practice to have an SQL query in the code." - imo, thats nonsense. – GrandmasterB Jan 21 '12 at 19:53
  • 4
    You don't have to call conn.Close() if you created it in a using block. The whole point of the using block is to mimic the C++ style of destructor clean up. An elegant clean up from a more civilized age. Not as random or clumsy as the try-catch style. – Lord Tydus Jan 22 '12 at 16:05
  • 2
    FYI. Stored procedures are also "code". The whole point of stored procs was to allow mixing procedural code with set-based style SQL. So you'll have a query in the code regardless. For extremely high volume apps sometimes it's better to have logic performed outside the database to allow horizontal scaling by adding servers. If you can maintain a single DB, but muli-server for the logic then scaling becomes much easier. – Lord Tydus Jan 22 '12 at 16:10
  • @GrandmasterB we have a substantial amount of SQL inline in C# (our C# LOC is near 2 million now) - and 6 years later it's coming back to bite us because we now need to hunt down that inline SQL (we recently hired a SQL expert - so we are doing performance tweaks). Trust me: you never know how big your app will get and how things will change in the future. Keep different languages in different files - even if you just delegate it off to manifest resources. You could also // SQLCODE it - but you need to remember to do that. – Jonathan Dickinson Jan 24 '12 at 17:08
  • 1
    @JonathanDickinson, Use stored procs if you want. I've said many times they are useful when you have disparate code bases operating against the same database. But just because they are useful in some circumstances doesnt automatically make not using them 'bad practice' all the time. If using SQL statements directly isnt causing a problem, then its not a 'bad practice' for that app. – GrandmasterB Jan 24 '12 at 19:54

Using stored procedures is one way, and has been in widespread use for many years.

A more modern way to interact with SQL Server databases from C# (or any .NET language) is to use Entity Framework. The advantage of Entity Framework is that it provides a higher level of abstraction.

To quote from Microsoft (https://msdn.microsoft.com/en-us/data/jj590134 ):

The ADO.NET Entity Framework enables developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications. Entity Framework applications provide the following benefits:

  • Applications can work in terms of a more application-centric conceptual model, including types with inheritance, complex members, and relationships.
  • Applications are freed from hard-coded dependencies on a particular data engine or storage schema.
  • Mappings between the conceptual model and the storage-specific schema can change without changing the application code.
  • Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in different database management systems.
  • Multiple conceptual models can be mapped to a single storage schema.
  • Language-integrated query (LINQ) support provides compile-time syntax validation for queries against a conceptual model.

The use of an ORM vs Stored Procedures involves tradeoffs, particularly in terms of security and where the logic resides.

The "classic" approach to development with SQL Server is to have the application logic reside in stored procedures and programs only given security rights to execute stored procedures, not update tables directly. The concept here is that stored procedures are the business logic layer for the application(s). While the theory is sound, it has tended to fall out of favor for various reasons, being replaced by implementing the business logic in a programming language like C# or VB. Good applications are still implemented with a tiered approach, including separation of concerns etc. but are more likely to follow a pattern like MVC.

One downside of implementing logic in the ORM rather than the database is ease of debugging and testing data integrity rules by those responsible for the database (DA or DBA). Take the classic example of transferring money from your checking to savings account, it is important that this be done as an atomic unit of work, in other words sandwiched in a transaction. If this sort of transfer is only allowed to be done through a stored procedure it is relatively easy for the DA and auditors to QA the stored procedure.

If on the other hand this is done via an ORM like Entity Framework and in production it is discovered that on rare occasions money is taken from checking but not put into savings debugging may be far more complex, particularly if multiple programs are potentially involved. This would most likely be an edge case, perhaps involving peculiar hardware issues that need to occur in a particular sequence etc. How does one test for this?

| improve this answer | |
  • Or other ORM. There are many of them, with various advantages and disadvantages when compared with EF. – svick Jan 22 '12 at 13:34
  • 8
    Listing disadvantages of ORMs would make this answer more useful. – Den Jan 23 '12 at 11:46

Actually the basic assertion is debatable - there are trade-offs either way between having SQL in the code or having code in the database (which is where you're heading with stored procedures).

The upshot of which is that there is no single "best", this is not something you can generalise because whichever way you go you are making a compromise (you gain benefits but also introduce constraints).

If there is a one to one correspondence between your application and you database then it doesn't really matter. If, on the other hand, you have a large core database shared by a significant number of applications enforcing consistency within the database between those applications becomes much more important.

What's more important is to worry about the architecture and layering of your application - given an appropriate data access layer whether using an ORM like Entity Framework or NHibernate or doing something more direct should isolate most of your application from this decision regardless of whether you build queries or use stored procedures.

I will freely I have tended to work on relatively small projects with small teams (1-3 devs) - using stored procedures is, for me, more trouble than its worth because given that nature of our applications (and my skillset?) deploying new code is generally much easier than updating the schema (even allowing that I have code that makes updating the schema relatively straightforward) and I am able to enforce business rules by use of common data access code. This is clearly a classic example of "Your Mileage May Vary".

| improve this answer | |
  • 2
    +1 for "no single best", -1 for deploying code is easier than deploying stored proc changes, +1 for choosing an approach that makes sense for your app and ensures DAL isolation - So +1. – Joel Brown Jan 21 '12 at 16:57
  • I did qualify the deploying bit with "for me" because that has for me consistently been the case (especially for web applications) - for all that it might bear a bit of a rewrite in that area. – Murph Jan 21 '12 at 18:36
  • +1, whats best depends on the application & situation. – GrandmasterB Jan 21 '12 at 19:54
  • 1
    I guess it depends on the conditions you work in. If you don't have DBAs locking you out of production, dropping a replacement stored proc into the database can be frighteningly easy. For that matter, without production controls dropping new markup, script and even new compiled code into a centralized app can also be much too easy. – Joel Brown Jan 21 '12 at 23:03
  • @JoelBrown - Exactly - I suppose, no, I'm certain I made a whole lump of assumptions there. Firstly my schema is version controlled (in a crude but effective fashion), I'm no dba but I'm smart enough to know that one's schema needs to be consistent. I work on mostly on web apps and you can't get to the databases except by visiting the server, whereas I have (just) reduced deployment of apps to (more or less) a single button click... integrating schema updates into said deployment is on the list, but I have always found schema updates more stressful than code updates (ease of rollback?) – Murph Jan 22 '12 at 10:37

As long as you have parametrized your inputs any approach is valid. Much of the don't have queries in code arguments came from the bad old days when many of the libraries forced you to string-append your statements together and that is where sql injection attacks came from.

| improve this answer | |
  • 1
    Is parameterising enough? Don't you need to sanitise as well? – StuperUser Jan 25 '12 at 9:52
  • that depends on your source and your purpose. I was taking his question literally, he is read-only with some parameters. in common cases the worst you would do with dirty input if properly parametrized is get a type exception or no results with bad input. Inserting is different and usually needs validation unless you treat the source as authoritative. – Bill Jan 25 '12 at 23:54

Best practice is really overblown here -- there are lots of good ways to do it and the one you pick really should depend on what your app is and what you need to do. That said, there are only two things you can do really wrong:

  • As @Bill points out, you should always parameterize your queries. String building is an easy vector for SQL injection as well as all manner of hard to track down bugs. Much smarter people figured out how to tupelize and escape sql so you don't need to figure it out yourself.

  • Close your connections. The bestest way is to wrap everything a using statement, but try / catch / finally is cool too if that floats your boat. But always make sure you use a connection like a cheap car -- drive it hard and fast and get rid of it quick.

The other practice I would argue vehamently for is that you should make sure to concentrate your data access code in as few places as possible. We do not allow front-end web apps to hold a direct reference to System.Data.SqlClient to help enforce this caveat.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.