[josh2323]

I'm working on a program that frequently needs to query a database and then load some fields into objects (in memory). I find myself writing a lot of duplicate code, namely switch statements, where I'm basically naming which database fields should be assigned to which data members. It seems like it should be a common task, so if there's a way "everyone" does it, please let me know.

My idea is to create a class "DataReader" with a method "Read". The parameters will be an Array of "database field names", a corresponding ArrayList of "local data members" to copy into, and the querystring.

Cars aCar = new Cars();
ArrayList localObjects = new ArrayList{aCar.Model, aCar.Color, aCar.Year};
string [] dbFieldNames = new string [3] {"carModel", "carColor", "carYear"};
DataReader reader = new DataReader(connectionString);
DataReader.Read(dbFieldNames, localMembers, carsQueryString);

My issue is, when objects are added to an ArrayList, really what's added is their value, not the memory location of the objects themselves. So, when changes are made to the objects in the ArrayList, these changes are not reflected on the original objects which were added.

I'm pretty sure I could add pointers to the Arraylist instead, but I'd rather not resort to this if there's a better way. Much appreciated for any help!

This is an ASP.NET program, but the code I've posted is all C#. So, if it needs to be moved, sorry about posting in the wrong section.

[overtech]

I'm not sure I understand the purpose of the ArrayList. If you already have a custom class "Cars" in which to hold the "Model, Color and Year" properties, why do you need the ArrayList?

- Dave

[josh2323]

Thanks for taking a look at this Dave. I guess what you're saying is "why not just pass Cars to the method instead of passing the ArrayList containing all of Car's data members?".

If I pass Cars, there's no way for the method to know which database fields to assign to which of Car's data members. I could explicitly state this with a switch statement specific to the Car's class, but that wouldn't make the method reusable for other objects. My idea is to have the ArrayList indexed in the same order as the Array of database field names so there's a direct correlation between the two.

Here's a basic idea of the method I'd like to call. Now that I think about it, maybe "Read" isn't really the best title for it, but I'll stick with it for this example.

public class Datatype
{
    private Hashtable dbFields = new Hashtable();

    //key / value pairs are read into dbFields from a database

    public void Read(string[] dbFieldNames, ArrayList localObjects)
    {
        for (int i = 0; i < dbFieldNames.Length; i++)
        {
            //finds value based on dbFieldNames[i] and assigns it to localObjects[i]
            localObjects[i] = dbFields[dbFieldNames[i]];
        }
    }
}

overtech, on 05 May 2010 - 07:34 PM, said:

I'm not sure I understand the purpose of the ArrayList. If you already have a custom class "Cars" in which to hold the "Model, Color and Year" properties, why do you need the ArrayList?

- Dave

[overtech]

How about this, just store your database fields in an ArrayList of strings and make your property names the same as the database field names. Then you could use the "SetValue" method to set the each property value in class "Cars" that you retrieved from the database.

Later, when you need to know which database fields to set, just read the field names from the ArrayList and get the actual property values from the Cars class using the "GetProperty" method.

That way you aren't making a copy of class property values that you have to maintain in parallel with the actual class properties...

For that matter, the ArrayList of field names (or even a Dictionary if you wanted KeyValuePairs) could be a property of class "Cars" itself. And the set method of the ArrayList property could set the other individual properties based on the contents of the ArrayList or Dictionary. Then the get method could update the value of each property and return a Dictionary containing the fieldnames/values. (hope that made sense, it did in my head)

I've never needed anything like that myself so I'm just kicking around ideas off the top of my head, but I think it could be worked out...

- Dave

[josh2323]

Thanks Dave! Although you didn't explicitly say it, you're right, I need to use reflection. The GetProperty and GetProperties methods specifically. I'll post the code when I'm done in case anyone else is trying to solve this same problem.

Thanks again!

[josh2323]

Here's the code I ended up with. I actually ended up creating a class that inherits the List<T> object so I can read in all of the rows at once with one query.

Class: (I haven't implemented all of the possible DataBaseTypes yet, only MySql since that's what I'm using right now)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using MySql.Data.MySqlClient;
using System.Reflection;

namespace JoshTools
{
    /// <summary>
    /// A list of objects that will be read in from a database
    /// </summary>
    /// <typeparam name="DataType">The type of object that will be used in this list</typeparam>
    public class DataTypeList<DataType> : List<DataType> where DataType : new()
    {
        public enum DatabaseType { MySql, Sql, ODBC };

        private IDbConnection connection;
        private IDbCommand command;
        private IDataParameter parameter;
        private DatabaseType theDatabaseType;

        public DataTypeList(DatabaseType dbType)
        {
            theDatabaseType = dbType;
            switch (dbType)
            {
                case DatabaseType.MySql:
                    connection = new MySqlConnection();
                    command = new MySqlCommand();
                    parameter = new MySqlParameter();
                    break;
                default:
                    break;
            }
        }

        public void Connect(string connectionString)
        {
            connection.ConnectionString = connectionString;
            connection.Open();
        }

        public void Disconnect()
        {
            connection.Close();
        }

        /// <summary>
        /// Sets the query that will read in the objects' information from the database.
        /// This method assumes there is only one parameter in the query string.
        /// </summary>
        /// <param name="query">The query string</param>
        /// <param name="parameterName"></param>
        /// <param name="parameterValue"></param>
        public void SetLoadQuery(string query, string parameterName, object parameterValue)
        {
            command.Connection = connection;
            command.CommandText = query;
            parameter.ParameterName = parameterName;
            parameter.Value = parameterValue;
            command.Parameters.Add(parameter);
        }

        /// <summary>
        /// Sets the query that will read in the objects' information from the database.
        /// Takes multiple parameters.
        /// </summary>
        /// <param name="query">The query string</param>
        /// <param name="parameterNames"></param>
        /// <param name="parameterValues"></param>
        public void SetLoadQuery(string query, List<string> parameterNames, List<object> parameterValues)
        {
            //need to implement
        }

        /// <summary>
        /// Reads in the dbfields, assigns them to the corresponding localProperties, then adds the object with these properties
        /// to the this list
        /// </summary>
        /// <param name="localProperties">A list of the local properties to be read into ordered the same as their
        /// corresponding dbfields</param>
        /// <param name="dbfields">A list of the dbfields to read in</param>
        public void Load(List<string>localProperties, List<string> dbfields)
        {
            switch(theDatabaseType)
            {
                case DatabaseType.MySql:
                    Load<MySqlDataReader>(localProperties, dbfields);
                    break;
                //need to implement other databasetypes
                default:
                    break;
            }
        }

        /// <summary>
        /// Reads in the dbfields, assigns them to the corresponding localProperties, then adds the object with these properties
        /// to the this list
        /// </summary>
        /// <typeparam name="DataReader">The type of datareader to be used ex. MySqlDataReader, SqlDataReader, etc.</typeparam>
        /// <param name="localProperties">A list of the local properties to be read into ordered the same as their
        /// corresponding dbfields</param>
        /// <param name="dbfields">A list of the dbfields to read in</param>
        private void Load<DataReader>(List<string>localProperties, List<string> dbfields)
            where DataReader : IDataReader
        {
            DataType localObject = new DataType();
            PropertyInfo localProperty;
            DataReader aReader;
            aReader = (DataReader)command.ExecuteReader();
            while (aReader.Read())
            {
                localObject = new DataType();
                //inner loop reads in values for one datatype
                for (int i = 0; i < aReader.FieldCount; i++)
                {
                    string dbFieldName = aReader.GetName(i);
                    int index = dbfields.IndexOf(dbFieldName);
                    string localName = localProperties[i];
                    localProperty = localObject.GetType().GetProperty(localName);
                    localProperty.SetValue(localObject, aReader.GetValue(i), null);
                }

                //outer loop adds the datatypes to the list
                Add(localObject);
            }
        }
    }
}

An implementation of the class where FieldGroup is a type that holds header information about groups of "InputFields" to be displayed on a webpage. FieldGroup has two fields: name & description. If this is a little too abstract, imagine the "FieldGroup" object is instead called "Car" or "Product". It would work the same way.

private void ReadFieldGroups(string productCatagory)
    {   
        //create a list of FieldGroups
        DataTypeList<FieldGroup> inputFields = new DataTypeList<FieldGroup>(DataTypeList<FieldGroup>.DatabaseType.MySql);
        inputFields.Connect(GlobalSettings.ConnectionString);

        //load FieldGroups
        inputFields.SetLoadQuery(loadFieldGroupQuery, "@productCatagory", productCatagory);
        List<string> fieldGroupProps = new List<string>{"Name", "Description"};
        List<string> dbFields = new List<string>{"Name", "Description"};
        inputFields.Load(fieldGroupProps, dbFields);
        
        inputFields.Disconnect();
    }