Letting ADO.NET Write Your SQL
Letting .NET DO Some of the Heavy Lifting
Many years ago, while working on a Delphi project, a team member made an assertion about his code relative to everyone else's. He said that his code represented best practices, and anyone who wasn't coding the way he was was unprofessional. In short, "everyone but me is stupid." He got fired. Well, that is, after two or three unbiased third parties looked at his code and came to the determination that his code was crap, he got fired.
Why tell you this? Because very few people are absolutely sure that the way they do things is right and is the only right way to do things. And most of those who do believe in a universal "right way" to code are cocky or, at a minimum, suffering from excess hubris.
Best practices is an evolutionary standard, and you, my friends, are part of the standards team. Some standards are to sell products. Some standards are intended to help. And some standards are hooey.
A dozen or so years ago it was very common for everyone to write SQL and embed it directly in code. A while after that, stored procedures were invented and the standard was to use stored procedures. Now, with LINQ on the horizon and improved performance in technologies like ADO.NET, inline SQL and SQL- like code (LINQ) are "okay again."
In many ways it really doesn't matter what standard you have, as long as you have one. I like using stored procedures because it's a very clean way to delineate work; embedded SQL is unsightly. Hence, my standard is to use stored procedures most of the time. That said, using what will get the job done is more important than an arbitrary standard, or maybe even critical to accomplishing your goal.
A good toolbox with a lot of tools is a pretty good thing to have around. In this article I'll demonstrate how you can use the SQL Command Builder (SqlCommandBuilder) to let ADO.NET write your SQL for you. Technically, SQL created by the SqlCommandBuilder is embedded SQL, but you don't have to see it in your code. And, more importantly, you don't have to write it.