CODEWITHSUNDEEP
c#stored-proceduresado.net
Yuvi Dagar
Yuvi Dagar

Reputation: 649

Converting a COMPLEX stored procedure into C# & ADO.Net

This is NOT a question on whether SPs are good or bad. Or whether writing SQL statements in C# is good or bad.

Very soon we start work on a new project which is a typical inventory/billing management system. This will be developed using .Net and C# as the language. The database is not yet finalized.

Use of stored procedures is NOT ALLOWED in this application and it will have medium to complex database operations. So logic that I could have easily written using and SP, is now something I will have to write using C# and ADO.Net.

Now taking a very HYPOTHETICAL USE CASE where a customer has selected 5 item and ready to get the bill generated at the counter …. this will typically have the following database operations:

Now looking at this scenario this could have ALL been done easily in an SP. This contains a lot of queries, IF condition, possible loops etc and looks like a good contender for a SP. What I wanted from the experts is:

  1. Are any best practices for writing such code in C# & ADO.Net. In C# we can typically recreate the same code using ADO.Net…. BUT IS THIS THE ONLY WAY OR THE CORRECT WAY?
  2. I have read a lot of people saying stored procedures are bad…. but when considering a complex scenario like this… don’t you feel that stored procedures are better suited in this case.

Upvotes: 5

Views: 3918

Answers (1)

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

Are any best practices for writing such code in C# & ADO.Net. In C# we can typically recreate the same code using ADO.Net…. BUT IS THIS THE ONLY WAY OR THE CORRECT WAY?

So I think based off of your question you would be much happier using something like Dapper for your database access. Dapper, in case you don't know, is an extremely lightweight database access library that supports even some of the more complex needs (like returning more than one result set in a single round-trip and then deserializing those results into POCO classes).

In short, with Dapper, you have the flexibility to query the database exactly how you want and it's ridiculously fast.

Now, for a more concrete example, Dapper extends the IDbConnection interface, so it's not going to matter what database you choose:

The database is not yet finalized.

it's easily parameterized, so it's not subject to SQL Injection:

var parms = new { ID = 1, Bar1 = "Hello" };
var foo = connection.Query<Foo>(
    "SELECT Bar1, Bar2 FROM Foo WHERE ID = @ID AND Bar1 = @Bar1",
    parms);

and it supports transactions, so you can manage the operation as a single transaction still:

var tran = connection.BeginTransaction();
var parms = new { ID = 1, Bar1 = "Hello" };
var foo = connection.Query<Foo>(
    "SELECT Bar1, Bar2 FROM Foo WHERE ID = @ID AND Bar1 = @Bar1",
    parms, tran);

and to be perfectly honest, though the entity frameworks are really getting popular, and Microsoft is pouring a ton of money into theirs, I don't buy into them anymore after using Dapper. And here's why. Generally speaking it is much better to leverage the database for what it's best at, and Dapper allows that, all it's doing is giving you a mechanism by which you can drastically reduce the boiler plate code required to get data from and execute against a database in ADO.NET.

Now, this is a good way to get into the argument of stored procedures.

I have read a lot of people saying stored procedures are bad…. but when considering a complex scenario like this… don’t you feel that stored procedures are better suited in this case.

Stored procedures are not bad. And most people that state that are probably coming from one of two angles:

  1. They are just jumping on the bandwagon.
  2. They are ignorant to the purpose and benefits of a stored procedure. PLEASE NOTE: I did not say stupid, I said ignorant.

Stored procedures, despite popular belief, serve a wonderful purpose. First and foremost, the execution plan is compiled. Secondly, they are easily parameterized to make more complex and multi-level queries faster - whereas views and direct SQL are not. Third, they are more easily secured for DBA's.

I had a query, it was a direct SQL query because the company I worked for at the time thought the exact same thing, and I increased it's performance by 98% by making it a stored procedure. That saved the company $2,000 a month in additional hardware they were ready to buy!

As I stated before, use the database for what it's good at. I think this is often lost because I see a lot of people trying to sort 500,000 objects using C# and the .NET framework and they can't figure out why it's not fast enough - they should have sorted them on the database server! That's one thing it's really good at!

Don't let people lead you down the path that a technology is useless, and bad, because many will tell you triggers are bad, but when used for the right reason they are perfect!

Upvotes: 9

Related Questions