Static Data Access Methods

Question

I know that creating a custom data access layer is not a very good idea unless you: 1) Know exactly what you're doing, and/or 2) Have a very specific need. However, I am maintaining some legacy code that uses a custom data access layer where each method looks something like this:

    using (SqlConnection cn = new SqlConnection(connectionString))
{
    using (SqlDataAdapter da = new SqlDataAdapter("sp_select_details", cn))
    {
        using (DataSet ds = new DataSet())
        {
            da.SelectCommand.Parameters.Add("@blind", SqlDbType.Bit).Value = blind;
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.CommandTimeout = CommandTimeout;
            da.Fill(ds, "sp_select_details");
            return ds;
        }
    }
}

Consequently, the usage looks something like this:

    protected void Page_Load(object sender, EventArgs e) {
    using (Data da = new Data ("SQL Server connection string")) {
        DataSet ds = da.sp_select_blind_options(Session.SessionID); //opens a connection
        Boolean result = da.sp_select_login_exists("someone");//opens another connection
    }
}

I am thinking that using Microsoft's Enterprise Library would save me from setting up and tearing down, namely, the connection to SQL Server every method call. Am I correct in this thinking?

Absolutely nothing wrong with a custom data access layer. Non custom access layers written by people who don't waht they are doing and don't know waht they need, now there's a heck of a lot wrong with taht. — Tony Hopkinson, Apr 05 '12 at 21:23

Tigran · Answer 1 · 2012-04-05T21:20:52.743

Yes, it will definitely save your time, but you will pay in terms of performance and flexibility.

So creating a custom DataLayer is also a very good idea to gain a performance and flexibility.

Considering that you're talking about legacy code, that, I suppose, works, I wouldn't change it to something modern (but less performant) only for having something fresh in my code.

Solid, workable DataLayer is a best choice ever over any other new technology you should implement in legacy code.

In short, change it only if you have really seriouse reasons to do that. I understand your willingness to change the stuff, cause it's always hard to understand the code written by someone else, but believe me, very often not changing old legacy code is a best choice for the project.

Good luck.

score 0 · Answer 2 · answered Apr 05 '12 at 21:19

0

I've used Enterprise Library in the past very successfully, and Enterprise Library would hide some of the messy details from you, but essentially it would be using the same code internally as that demonstrated in your example.

As @tigran says, I wouldn't recommend trying to change an existing codebase unless there are fundamental issues with it.

answered Apr 05 '12 at 21:19

Phil

42,255
9
100
100

The main thing is I would like to use a previously opened connection and instead of data sets used data readers. As I worked my way through the code I also noticed that there are tons of instances where the data access class is re-instantiated over and over within a calling method. From how I was taught, this is a no-no. – Jim Apr 06 '12 at 15:37
Ah, good point about DataSets. I always used DataReader and converted to types. – Phil Apr 06 '12 at 15:48

score 0 · Answer 3 · answered Apr 05 '12 at 21:21

0

Yep, by default connection pooling will be on. The application domain basically maintains a list of connections, and when you issue a call to create a connection, it returns an unused one from the pool, if it exists or creates one if not.

So when your connection cn goes out of scope in teh using statement and get's disposed, what actually happens is it goes back in to the pool, ready for the next request and hang around in there based on various optimisation parameters.

Google ADO connection pooling for more details, there's a lot in there.

answered Apr 05 '12 at 21:21

Tony Hopkinson

20,172
3
31
39

OK. Thanks. So, what benefits what I get from using something like the Microsoft Enterprise Library for data access, over the above. I would prefer using ORM since our database schema is pretty much nailed down, but that's out of the question from the powers that be. – Jim Apr 08 '12 at 18:13
Connection pooling is underneath either one given ADO.net is being used , so it has no impact on choosing the architecture for a DAL – Tony Hopkinson Apr 08 '12 at 20:58

Static Data Access Methods

3 Answers3