Proper .net datareader setup suggestion

Question

I have a data reader in a static class that allows me to get single or multiple records from a database. This is used in an ASP.net application. However, we've been running into some strange issues lately and I'm wondering if my current method structure might be the cause of the problem. On occasion I get an error stating that the data reader is still open. Other times a server time out occurs.

This is an example of one of my static classes that gets data from the database. For the sake of brevity I reduced the amount of methods in this example to my "get" structure. So if I wanted to return a List of type Reference I would call the following form an aspx.cs file.

List<Reference> ref = ReferenceAdapter.GetAllByType(......);

Any insight is greatly appreciated.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using DbProvider;
using MyBusinessModelLayer;

namespace MyDataAccessLayer
{
    public static class ReferenceAdapter //: IReferenceAdapter
    {
    static IDbProvider _dbProvider;

    static ReferenceAdapter()
    {
        Initialize();
    }

    private static void Initialize()
    {
        _dbProvider = new SqlDbProvider(Config.SqlConnectionString);
    }

    public static List<Reference> GetAllByType(ReferenceType referenceType)
    {
        return GetAllByType(referenceType, false);
    }

    public static List<Reference> GetAllByType(ReferenceType referenceType, bool isIncludeDeleted)
    {
        Hashtable hIn, hOut;
        string sp;

        sp = "[ReferenceSelectAllByReferenceType]";

        hIn = new Hashtable();
        hIn.Add("@ReferenceType", referenceType.ToString());
        if (!isIncludeDeleted)
        {
            hIn.Add("@Deleted", false);
        }

        hOut = new Hashtable();
        hOut.Add("@RTNStatus", 0);

        return GetMultipleFromDb(sp, hIn, hOut, CommandType.StoredProcedure);
    }

       private static List<Reference> GetMultipleFromDb(string sSql, Hashtable hInputParameters)
    {
        return GetMultipleFromDb(sSql, hInputParameters, new Hashtable(), new CommandType());
    }

    private static List<Reference> GetMultipleFromDb(string sSql, Hashtable hInputParameters, CommandType cType)
    {
        return GetMultipleFromDb(sSql, hInputParameters, new Hashtable(), cType);
    }

    private static List<Reference> GetMultipleFromDb(string sSql, Hashtable hInputParameters, Hashtable hOutputParameters, CommandType cType)
    {
        IDbCommand dbCommand = _dbProvider.Select(sSql, hInputParameters, hOutputParameters, cType);
        var lo = new List<Reference>();

        using (IDataReader dr = dbCommand.ExecuteReader())
        {
            while (dr.Read())
                lo.Add(GetObjectFromDataRecord(dr));
        }
        dbCommand.Connection.Close();
        return lo;
    }// GetMultipleFromDb()

    private static Hashtable GetParametersFromObject(Reference o)
    {
        Hashtable h = new Hashtable();
        h.Add("@ReferenceShortName", o.ShortName);
        h.Add("@ReferenceLongName", o.LongName);
        h.Add("@ReferenceDescription", o.Description);
        h.Add("@ReferenceType", o.Type.ToString());
        h.Add("@ReferenceSortKey", o.SortKey);

        return h;
    }// GetParametersFromObject()

    private static Reference GetObjectFromDataRecord(IDataRecord dr)
    {
        Reference o = new Reference();

        o.ReferenceId = dr["ReferenceID"].DBNullToInt();
        o.ShortName = dr["ReferenceShortName"].ToString();
        o.LongName = dr["ReferenceLongName"].ToString();
        o.Description = dr["ReferenceDescription"].ToString();
        o.Type = (ReferenceType)Enum.Parse(typeof(ReferenceType), dr["ReferenceType"].ToString(), true);
        o.SortKey = dr["ReferenceSortKey"].DBNullToInt();
        o.Created = dr["CreatedDate"].DBNullToDateMinVal();
        o.CreatedBy = dr["CreatedBy"].DBNullToInt();
        o.Updated = dr["UpdatedDate"].DBNullToDateMinVal();
        o.UpdatedBy = dr["UpdatedBy"].DBNullToInt();
        o.Deleted = Convert.ToBoolean(dr["Deleted"]);
        o.Deletable = GetAssetReferenceCount(o.Type, o.ReferenceId);

        return o;
    }// GetObjectFromDataRecord()
}

}

Answer 1

Rewrite your code so that there are no static, shared objects. Most probably, as your site is used by more and more users, you have concurrency issues caused by the fact that your static methods reuse static resources.

Instead of

List<Reference> ref = ReferenceAdapter.GetAllByType(......);

you could have

using ( var adapter = new ReferenceAdapter() )
{
   List<Reference> ref = adapter.GetAllByType();
}

Not only that would create a new instance so that there are no concurrency issues but also you could make sure resources are disposed properly.

Are you sure that reinventing things by writing your own data access layer is a right way to follow? Considering your lack of experience, using an existing framework (like an orm or a micro-orm) sounds like a much safer choice. People ate their teeth on similar issues and are years ahead of you and they share the experience by desiging ready-to-use libraries.