How to write clean sql code for .net??

[Goalie35]

Ive written database code (using vb.net) for my different projects in several ways throughout the years.

Ive created separate projects (separate DLLs) just for my database stuff, Ive written separate database components within the same project as my application, and Ive also simply placed my sql code within the page itself.

I was just wondering though if anyone knew if there was an actual "correct" way to write database code in .net. Does microsoft recomend a certain method over another? If so, does anyone know of any links to any web sites that describe which method to use?

Thanks in advance.

-Goalie35

 

[Robby]

My prefered way is to setup a DAL (Data Access Layer). It should be as generic as you can make it, this means no SQL statements, table names, parameters or even connection strings.

Create the DAL as an abstract (MustInherit) class and have your BLL (Business Layer) class inherit it. This BLL is where you would have the project-specific SQL statements etc.

You can use this DAL in any project, C#, VB.NET, WinForm and even ASP.NET.

 

[bri189a]

Professionally speaking:

You shouldnt put sql code within a page/form for a number of reason (security, change management, efficiency, reusability), and generally you want your data access to be its own seperate layer.

Keeping your data access layer seperate from your application and presentation layer is the generally accepted (and expected) method of a large scale professional product; however there is no rule saying you have to do it that way.

Keeping it seperate from application layer allows another application to use the same dll - this other application doesnt have to re-invent the wheel. Keeping the presentation layer seperate from the application layer means that an a web site, and a window application can run exactly the same because they have their own presentation layer, but have the same dll for the application itself, and that application layer uses the same data layer.

Also if the data structure changes the data dll can be recompiled and distributed as an update rather than have to re-build the whole application.

A lot of smaller projects, such as the ones I do for side cash have the application and presentation layer combined (because I know a certain thing will always be a web site - or at least the customer isnt paying enough or doesnt have the desire to make it portable to a windows application), but rarely, even on my smallest of small projects will I put the data access layer with application or the application/presentation layer... for some people that may be over kill but I have my reasoning.

You always need to plan from the beginning for growth, expansion, and change...remember you want things to be loosely coupled and highly cohesive...putting everything together in one place makes things tightly coupled and have low cohesiveness... meaning what will take me 10 minutes to change (or fix) and simply update the dll through a small download will require someone who didnt design right from the beginning days or weeks to make the same change and have to redeploy the entire application.

You might want to do some reading on the rational unified process, it changed the way I did my programming (for the better) a hundred fold.

 

[bri189a]

I just read Robbys post (wrote while I was writting mine I guess), BuisnessLayer = ApplicationLayer in my post - truthfully BuisnessLayer is better terminology...Im just use to our inter-office terminology, and while his DAL doesnt include tables, SQL, or connection strings, ours do...thats a coding style differance, Ive used his terminology at times...it just shows a differance in the way our shops operate, the important thing for you to notice is that we both say to keep the data out of the presentation layer and keep it as a seperate entity.

 

[FZelle]

Also you can check the Application Blocks supplied by MS.
There is a Data Application Block on msdn.microsoft.com

That is how MS tells the people how to do it.

In the real world many people are useing anything from hand coded SQL to
Libraries like NHybernate.

So find you own "style", like in everything else there are hundreds of todos and not todos. ;-)