Most Efficient Way To Get A Row Of Data From DB In ASP.NET

Question

I'm writing a method to return an 'asset' row from the database. It contains strings, ints and a byte array (this could be an image/movie/document).

Now for most row access I use the following method which returns a NameValueCollection as it is a light weight object, easy to use and cast int and strings.

        public static NameValueCollection ReturnNameValueCollection(Database db, DbCommand dbCommand)
    {

        var nvc = new NameValueCollection();

        using (IDataReader dr = db.ExecuteReader(dbCommand))
        {
            if (dr != null)
            {
                 while (dr.Read())
                 {
                     for (int count = 0; count < dr.FieldCount; count++)
                     {
                         nvc[dr.GetName(count)] = dr.GetValue(count).ToString();
                     }
                 }
            }
        }

        dbCommand.Dispose();
        return nvc.Count != 0 ? nvc : null;
    }

Now my apporach for this kind of data access would normally be to get a method to return a datarow.

       public static DataRow ReturnDataRow(Database db, DbCommand dbCommand)
    {
        var dt = new DataTable();

        using (IDataReader dr = db.ExecuteReader(dbCommand))
            if (dr != null) dt.Load(dr);

        dbCommand.Dispose();
        return dt.Rows.Count != 0 ? dt.Rows[0] : null;
    }

It does seem kind of wastefull to create a DataTable and then return its first datarow.

Is there better way to do this?

I'm thinking maybe a Dictionary of objects which I then manually cast each member of.

Would be interesting to see how others have tackled this. I know this kinda falls into the field of micro optimisation and as long as I'm not returning DataSets for each row query (wish I had a pound for everytime I saw that in a line of code) it should be ok.

That said this method is likely to be called for allot of data access queries on allot of sites on one box.

Cheers

Steve

Answer 1

how's it going?

Is there a reason you don't have object containers that represent a row in your database? Creating a custom object is much easier to deal with in other tiers of your solution. So, going with this approach, there are two very viable solutions to your problems.

Say you have a custom object that represents a Product in your database. You'd define the object like this:

public class Product {
    public int ProductID { get; set; }
    public string Name { get; set; }
    public byte[] Image { get; set; }
}

And you'd fill a collection of of Products (Collection) like this:

var collection = new Collection<Product>();

using (var reader = command.ExecuteReader()) {
    while (reader.Read()) {
        var product = new Product();

        int ordinal = reader.GetOrdinal("ProductID");
        if (!reader.IsDBNull(ordinal) {
            product.ProductID = reader.GetInt32(ordinal);
        }

        ordinal = reader.GetOrdinal("Name");
        if (!reader.IsDBNull(ordinal)) {
            product.Name = reader.GetString(ordinal);
        }

        ordinal = reader.GetOrdinal("Image");
        if (!reader.IsDBNull(ordinal)) {
            var sqlBytes = reader.GetSqlBytes(ordinal);
            product.Image = sqlBytes.Value;
        }

        collection.Add(product);
    }
}

Notice that I'm retrieving a value via the reader's Getx where x is the type that I want to retrieve from the column. This is Microsoft's recommended way of retrieving data for a column per http://msdn.microsoft.com/en-us/library/haa3afyz.aspx (second paragraph) because the retrieved value doesn't have to be boxed into System.Object and unboxed into a primitive type.

Since you mentioned that this method will be called many, many times, in an ASP.NET application, you may want to reconsider such a generic approach as this. The method you use to return a NameValueCollection is very non-performant in this scenario (and arguably in many other scenarios). Not to mention that you convert each database column to a string without accounting for the current user's Culture, and Culture is an important consideration in an ASP.NET application. I'd argue that this NameValueCollection shouldn't be used in your other development efforts as well. I could go on and on about this, but I'll save you my rants.

Of course, if you're going to be creating objects that directly map to your tables, you might as well look into LINQ to SQL or the ADO.NET Entity Framework. You'll be happy you did.