win-based application (c#) needs a class for handling DB Connection

Question

Hi... I think having a class (clsConnection) which we can take advantage of it in order to execute every SQL queryies like select, insert, update, delete, .... is pretty good.

but how complete it could be? How?

Answer 1

LINQ to SQL ?

Answer 2

It sounds to me like you're just re-writing the ADO.NET SqlConnection (which already has an attached property of type SqlCommand). Or Linq to SQL (or, even, Linq to Entities).

Answer 3

You can use the DAB (SQlHelper) from the enterprise Library. This has all the methods/properties necessary for database operation. You dont need to create you own code.

Alternately you can use a ORM like LINQ or NHibernate.

Answer 4

You could use LINQ to SQL as AB Kolan suggested or, if you don't have time for the learning curve, I'd suggest taking a look at the Microsoft Enterprise Library Data Access Application Blocks.

Answer 5

When doing data access i tend to split it into 2 tiers - purely for testability.

totally seperate the logic for getting key values and managing the really low level data collection from the atomic inserts, updates, selects deletes etc.

This way you can test the logic of the low level data collection very easily without needing to read and write from a database.

this way one layer of classes effectively manages writes to individual tables whilst the other is concerned with getting the data from lookups etc to populate these tables

The Business logic layer that sits on top of these 2 dal layers obviously manages the actual business logic - this means that the datastructure is as seperated from the business logic as is realistically possible ... Ie you could replace the dal and not feel the pain so much.

the 2 routes you can take that work well are

ADO.Net

this is very powerful as you have total control, but at the same time it is time consuming and feels repetative. Also its old school so most people are bored of it hence all the linq 2 sql comments. With this you open a connection to the DB and then execute a command against it.

Basically you create a class to interface with the database and use this to use stored procedures that are in the database. The lowest level class essentially fires off the command with its parameters and then populates itself with the returned values.

and Linq 2 SQL

This is a cool system. Essentially it makes SP's redundant for 90% of cases in return for allowing strongly typed sqlesque statements in your code - save time and are more reliable. I still use 2 dal layers with this but take advantage of the fact that it will generate the basic class with properties for you and simply add functionality to actually do the atomic operations. The higher level then implements the read and write logic for multiple objects.

The nicest part is that you can generate collections of collections easily with linq 2 sql and then write all the inserts and updates with one command (altohguh in reality you tend to do things seperatley).

L2S is powerful once you start playing with it wheras generating a collection of objects from ado.net can be a real pain in comparison - especially when you have to do it again and again.

Another alternative is Linq 2 entities

I ahve had problems with this due to linked servers, also it doesn't like views much and if your tables dont have pk's or constraints then it doesn't like life much either. Id stay clear of it for a while.

Of course if you mean that you want a generic class for writing and reading data from a database I think you will be adding complexity rather than solving a problem. Really you can;t avoid writing code ;) - each bit of data access is unique, trying to genericise it past ado.net or l2s is really asking for trouble imo.

Answer 6

Small project: A singleton class (like DatabaseConnection) might be good for what you're doing.

Large project: Enterprise Library has some database code; NHibernate or Entities Framework, perhaps.

Your question wasn't specific enough to give a very definitive answer on this.