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 }) {
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
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:
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?
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".
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.
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.
// SQLCODEit - but you need to remember to do that.