C# dynamic assignment of properties in a loop

I didn't quite get / see what I was hoping for and maybe missed, so I'll ask again anyhow. I have a class with fields on it. I have a data table that I'm querying from with the same columns and corresponding data types. I have a string array of all fields I am concerned about, and their specific order.

What I want to do is cycle through each element in the array and put the value from the data row of the retrieve data table resul set and populate into the field of the class structure. Something like the pseudo-code below

String WorkWithThese[] = { "MyFld1", "AnotherFld", "ThirdFld" };
DataTable oTbl = GetMySQLQuery( "Select * from MyTable where ID = 1" );
DataRow oDR = oTbl.Rows[0];

MyOtherClass oMC = new MyOtherClass();


foreach( String s in WorkWithThese )
   // NOW, what I'm looking to do...
   oMC.<the field represented by 's'> = oDR[s];

I kno开发者_如何学Pythonw the oDR[s] will work as it is a valid column by same name in the result set. My problem is the updating the individual field in the oMC.

This is intended use on a handheld device, so memory restrictions need best performance, yet dynamic for maintainability in a variety of other areas too... A gentle blend of both. I was hoping to avoid doing

oMC.MyFld1 = oDR["MyFld1"];
oMC.MyFld1 = oDR["AnotherFld"];
oMC.MyFld1 = oDR["ThirdFld"];

when there could be 50-60 fields per table reference

I've come up with a solution that allowed me an automated approach, yet helped improve performance at the same time... Hope the technique can help others too. My issue was when using System.Data (via SqlCE, but applicable for other database backends too). Each time I tried to create the SQL command object to perform insert, update, or whatever, and add the "parameters" to the sql object, get proper data types, etc was killing performance. So, I did this for the Insert/Update. On my data manager class (one per table I work with), I added to objects of IDbCommand objects, one for Insert/Update respectively. During the constructor, I would pre-query the table to get a structure of the final row object and pre-build the query and parameters (skipping over the primary key ID) something like...

private void BuildDefaultSQLInsert()
{

   // get instance to the object ONCE up front
   // This is a private property on the data manager class of IDbCommand type
   oSQLInsert = GetSQLCommand("");

   // pre-build respective insert statement and parameters ONCE. 
   // This way, when actually called, the object and their expected
   // parameter objects already in place.  We just need to update
   // the "Value" inside the parameter
   String SQLCommand = "INSERT INTO MySQLTable ( ";
   String InsertValues = "";

   // Now, build a string of the "insert" values to be paired, so
   // add appropriate columns to the string, and IMMEDIATELY add their
   // respective "Value" as a parameter
   DataTable MyTable = GetFromSQL( "Select * from MySQLTable where MyIDColumn = -1" );
   foreach (DataColumn oCol in MyTable.Columns)
   {
      // only add columns that ARE NOT The primary ID column
      if (!(oCol.ColumnName.ToUpper() == "MYIDCOLUMN" ))
      {
         // add all other columns comma seperated...
         SQLCommand += oCol.ColumnName + ",";

         InsertValues += "?,";
         // Ensure a place-holder for the parameters so they stay in synch 
         // with the string.  My AddDbParm() function would create the DbParameter
         // by the given column name and default value as previously detected
         // based on String, Int, DateTime, etc...
         oSQLInsert.Parameters.Add(AddDbParm(oCol.ColumnName, oCol.DefaultValue));
      }
   }

   // Strip the trailing comma from each element... command text, and its insert values
   SQLCommand = SQLCommand.Substring(0, SQLCommand.Length - 1);
   InsertValues = InsertValues.Substring(0, InsertValues.Length - 1);

   // Now, close the command text with ") VALUES ( " 
   // and add  the INSERT VALUES element parms
   SQLCommand += " ) values ( " + InsertValues + " )";

   // Update the final command text to the SQLInsert object 
   // and we're done with the prep ONCE
   oSQLInsert.CommandText = SQLCommand;

}

Next, When I need to actually perform the inserts for all records as I go through, I do that via my Add() function and pass in an instance of the DataRow I am working on. Since the SQLInsert object is already built with respective parameters, I can just cycle through the data row of same type as the data manager is responsible for, and just update the parameter objects with the data row's current "values"

public Boolean AddMyRecord(DataRow oDR)
{
   // the parameter name was set based on the name of the column, 
   // so I KNOW there will be a match, and same data type
   foreach (IDbDataParameter oDBP in oSQLInsert.Parameters)
      oDBP.Value = oDR[oDBP.ParameterName];

   ExecuteMySQLCommand( oSQLInsert );
}

With some timed-trials against the handheld device, the before and after times to run / validate about 20 queries and 10 inserts from about 10 seconds down to 2.5 seconds. The technique was similar for that of doing SQLUpdate, but forcing the WHERE clause to the primary ID column of the table at the end of the building string / object cycle. It works great. Now, if I ever need to expand the structure or column sequences of the table, I dont have to change ANY code for the insert, update processes.

Agree with the others about the speed of using reflection but something like this might work:

        public static void SetProperty(object myObject, string name, string valueString)
        {
            try
            {
                Type type = myObject.GetType();
                PropertyInfo property = type.GetProperty(name);

                if (property != null && property.CanWrite)
                {
                    object value = null;
                    if (property.PropertyType == typeof(double))
                        value = Convert.ToDouble(valueString);
                    else if (property.PropertyType == typeof(int))
                        value = Convert.ToInt32(valueString);
                    else if (property.PropertyType == typeof(bool))
                        value = Convert.ToBoolean(valueString);
                    else if (property.PropertyType == typeof(DateTime))
                        value = DateTime.Parse(valueString);
                    ...
                    else
                        Debug.Assert(false, property.PropertyType.AssemblyQualifiedName + " not handled");

                    property.SetValue(myObject, value, null);
                }
            }
            catch (FormatException)
            {
                //Unable to set the property '{0}' to '{1}', name, valueString
            }
            catch (NullReferenceException)
            {
                //Property not defined (or even deprecated)
            }
        }

Haven't tried on CompactFramework, but you could try to use reflection to find the property to set my name (=> the column name from the database). You could then set the property using reflection.

This, however, is the least performant way...

It is possible to do it using reflection but it is extremely slow. I would recommend either:

• to fill fields manually (no that much work)
• use codegeneration to write that code for you
• use ORM (best solution!)

Using relection is certainly not the way to go when you are looking for performance in handheld device. Although there are some ORM tools out there like EntitySpaces but i am sure you would get better performance by following manual approach may be with the help of some Code generation tool.

Here is my solution, using reflection, I don't know is suitable in performace but you can resolve may problems in a click...

    /// <summary>
    /// Riempie una Entità con i valori presenti in un DataRow automaticamente.
    /// L'automatismo funzione solo se i nomi delle colonne (campi del DataBase) corrispondono ai nomi 
    /// delle properties se una Property non ha colonna del DataRow semplicemente è valorizzata al Default
    /// (come dopo una istanzizione dell'oggetto mediante "new"). Se una colonna esiste ma non c'è la 
    /// corrispondente proprietà la segnalazione dell'eccezione dipenderà dal flag: columnCanNotCorrespond
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="dr"></param>
    /// <param name="columnCanNotCorrespond">Se true, non scatta eccezione se c'è una colonna che non è corrispondente ad una Property</param>
    /// <returns></returns>
    public static T GetEntity<T>(DataRow dr, bool columnCanNotCorrespond)
    {
        Type entityType = typeof(T);
        T entity = (T)entityType.Assembly.CreateInstance(entityType.FullName);

        if (columnCanNotCorrespond)
        {
            foreach (DataColumn dc in dr.Table.Columns)
            {
                object columnValue = dr[dc.ColumnName];                 
                if (entity.GetType().GetProperty(dc.ColumnName) != null) //La Property Esiste?
                    entity.GetType().GetProperty(dc.ColumnName).SetValue(entity, columnValue, null);

            }
        }
        else //Scatterà eccezione se la Property non corrisponde alla colonna!
        {
            foreach (DataColumn dc in dr.Table.Columns)
            {
                object columnValue = dr[dc.ColumnName];                 
                entity.GetType().GetProperty(dc.ColumnName).SetValue(entity, columnValue, null);
            }

        }
        return (T)entity;
    }