Best Practices for Database Access in the Real World RRS feed

  • Question

  • I've been using .Net and SQL Server since about 2004, and have progressed through various methods of binding forms (and their underlying objects) to my SQL Server databases. First I used the built in "brag and drop" for data binding. This is what Microsoft showcases when you go to their conferences; they demonstrate how you can build an application in minutes by dragging and dropping form controls and binding them to the underlying database. I've yet to build a real world application that has been simple enough to use this methodology.

    One of the things I liked least about the data binding was the difficulty in using stored procedures -- it was doable, but heck of a pain. I'm a firm believer in using Stored Procedures to manipulate data for speed/performance (they're cached/"compiled"), security (prevent rogue access and injection attacks) and reliability (allows data to be validated/verified).

    I started using the MS Data Access Block when it was released and found that in most cases it worked, but sometimes the amount of legwork involved in using it was on par with manually creating the connection and micromananaging all the objects myself. In 2005 they released a new version of the DAB, but I didn't really see enough of a draw to port my code over to use it.

    Enter .Net 3.5 and SQL 2005 (or soon SQL 2008). Now there is LinQ, C# stored procedures and other new features, yet most books (even the "Pro" ones) still spend chapters describing what is essentially the "brag and drop" method that just doesn't work.

    Please, can someone provide some direction as to how to do data access in the real world at this point in time? I'd love to know the correct way of doing things when it comes to building business winforms as well as websites. Are SQL stored procedures now a depreciated technology compared to C# stored procedures? What about LinQ?

    Thanks for reading this long post, and I hope to get some input. If possible, please provide some examples, suggest books or websites to read.

    Thanks again,
    Monday, April 21, 2008 9:44 PM

All replies

  • I am sure I will be one of many that respond to your post. But, I will share my two cents.


    Personally, I never use the drag and drop features of data binding so I agree with you on that. I use a fairly sophisticated relational database that is interfaced with my application(s). I try not to put a lot of business logic in the DBMS because their is always the possibility our customers want our product on their DBMS, perhaps Oracle or something else.


    So I have pretty much created a DataLayer factory that makes change easy. But what happens if I decide to move to an Object Oriented database? That is a buzz word among some folks, but that doesn't mean I should scratch my app and start from scratch just to do something "new".


    Ask yourself the following questions. What am I creating? Web or WinForm? What are the requirements? What is likely to change over time?


    Then architect your application with all these factors in mind best you can. SQL Stored procedures is certainly not deprecated nor are triggers. I would just not rely heavily on them. But again, what are your objectives in your finished product that you can envisage? All these factors come to play when architecting.


    Try as best as you can to keep things as loosly coupled as you can to make future changes easier to implement.

    Monday, April 21, 2008 10:10 PM
  • I want to nitpick one of your comments:

    "I'm a firm believer in using Stored Procedures to manipulate data for speed/performance (they're cached/"compiled"), security (prevent rogue access and injection attacks) and reliability (allows data to be validated/verified)."

    First of all, stored procs are not cached/compiled any differently than a parameterized sql statement. Every statement you run is cached and compiled in sql server. You should use parameterized sql so that the same sql can be used with different values. If you use parameterized sql you also avoid injection attacks. Not sure what you mean about data being validated/verified but I doubt you want that kind of logic on the tier that is furthest from the user.

    In my opinion you should take a queue from the java world. Read up on DAO's and POJO's. The same things translate into the .NET world. Take a look at nhibernate, it may fit the bill for you.
    Monday, April 21, 2008 10:11 PM