How to pass a list of unknown objects of type cust

Question

I am making a databasehelper class with methods to access a SQLCE database. I want to use the same method to read row(s) using different classes containing properties that match the fields in the different tables. The class to be used is determined during runtime and I want to pass a list with objects from the class on to the method and get the propertynames and use them to read the database. Would be very handy because I could use it for all my (SQLCE-)databases.

(I updated the erroneous code in order to provide the solution here)

    #region ReadData
    ///----------------------------------------------------------------------
    /// <summary>
    /// Reads datarows from database and adds them to list.
    /// </summary>
    /// <param name="data">List containing objects with properties.</param>
    /// <param name="table">Table in database.</param>
    /// <param name="search">Substring of SQL-statement that follows 'WHERE'.</param>
    /// <param name="connect">Connectionstring.</param>
    /// <returns>true if successfull</returns>
    ///----------------------------------------------------------------------
    public static bool ReadData<T>(List<T> data, string table, string search, string connect) where T : class, new()
    {
        // Return if input id missing
        if (data == null || table == "" || connect == "") return false;

        // retrieve properties from Data 
        PropertyInfo[] propinf = typeof(T).GetProperties();

        // Create string with SQL-statement
        string fields = "";
        // retrieve fields from propinf
        foreach (PropertyInfo p in propinf)
        {
            fields += fields == "" ? p.Name : ", " + p.Name;
        }
        // create SQL SELECT statement with properties and search
        string sql = "SELECT " + fields + " FROM " + table;
        sql += search == "" ? "" : " WHERE " + search;

        // Instantiate and open database
        SqlCeConnection cn = new SqlCeConnection(connect);
        if (cn.State == ConnectionState.Closed)
        cn.Open();
        data.Clear();   // just in case
        try
        {
            SqlCeCommand cmd = new SqlCeCommand(sql, cn);
            cmd.CommandType = CommandType.Text;
            SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
            if (rs.HasRows)  // Only if database is not empty
            {
                while (rs.Read()) // read database
                {
                    // instantiate single item of list Data
                    var dataitem = new T();
                    int ordinal = 0;
                    foreach (PropertyInfo p in propinf)
                    {
                        // read database and
                        PropertyInfo singlepropinf = typeof(T).GetProperty(p.Name);
                        ordinal = rs.GetOrdinal(p.Name);
                        singlepropinf.SetValue(dataitem, rs.GetValue(ordinal), null); // fill data item
                    }
                    data.Add(dataitem);  // and add it to data.
                }
            }
            else
            {
                MessageBox.Show("No records matching '" + search + "'!");
                return false;
            }
        }
        catch (SqlCeException sqlexception)
        {
            MessageBox.Show(sqlexception.Message, "SQL-error.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return false;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return false;
        }
        finally
        {
            cn.Close();
        }
        return true;
    }
    #endregion

I had two questions:

1) how do I pass this list with unknown type? Answers I found so far did not help me solve this issue.

2) how do I instantiate an object of unknown-type class (at compile-time) in order to add it to the List without causing a compile-error?

Thanks very much!

Answer 1

Below an update to the code. Its getting close to final and has been tested in all kinds of different situations. Ideally iterations using reflection would have to be replaced by something less performance-intense but as long as database operations are way more time-consuming I guess it doesn't really matter in real life. I'm already quite happy with it.

    #region Read(Like)Data
    public static int ReadData<T>(List<T> data, string table, T search, string connect) where T : class, new()
    {
        return BaseRead(data, table, search, connect, "=");
    }
    public static int ReadLikeData<T>(List<T> data, string table, T search, string connect) where T : class, new()
    {
        return BaseRead(data, table, search, connect, "LIKE");
    }
    ///----------------------------------------------------------------------
    /// <summary>
    /// Reads datarows from database and adds them to list containing objects of type T.
    /// Note that the properties of T should match the fields of the database table.
    /// </summary>
    /// <param name="data">List containing objects of type T with properties matching fields in table.</param>
    /// <param name="table">Table in database.</param>
    /// <param name="search">Object of type T with (some) properties containing search constraints, 
    /// others should be null. Unused DateTime should be 1800-01-01.</param>
    /// <param name="connect">Connectionstring.</param>
    /// <returns>-1 if exception was thrown or the number of records (objects of type T) otherwise</returns>
    ///----------------------------------------------------------------------
    private static int BaseRead<T>(List<T> data, string table, T search, string connect, string comparer) where T : class, new()
    {
        // Abort if insufficient arguments
        if (data == null || table == "" || connect == "") return 0;
        // Make sure List<T> data is empty
        data.Clear();
        // Retrieve properties from object of type T 
        PropertyInfo[] propinfs = typeof(T).GetProperties();

        // -----------------------------------------
        // Create string that contains SQL-statement
        // -----------------------------------------
        string fields = ""; string wherestr = "";
        // Retrieve fields from propinf
        foreach (PropertyInfo p in propinfs)
        {
            fields += fields == "" ? p.Name : ", " + p.Name;
            dynamic propvalue = p.GetValue(search, null);
            // Solutions for properties of type DateTime
            long dateticks = 0; DateTime dt = new DateTime();
            Type type = propvalue != null ? propvalue.GetType() : null;
            if (propvalue != null && propvalue.GetType() == dt.GetType())
            {
                dt = propvalue;
                dateticks = dt.Ticks;
            }
            // DateTime 1800-01-01 equals null (hey, it's better than nothing...)
            if (propvalue != null && dt != DateTimeNull)
                wherestr += wherestr == "" ? p.Name + " " + comparer + " @" + p.Name.ToLower() 
                    : " AND " + p.Name + " " + comparer + " @" + p.Name.ToLower();
        }
        // Create SQL SELECT statement with properties and search
        string sql = "SELECT " + fields + " FROM " + table;
        sql += wherestr == "" ? "" : " WHERE " + wherestr;

        // -------------------
        // Database operations
        // -------------------
        SqlCeConnection cn = new SqlCeConnection(connect);
        if (cn.State == ConnectionState.Closed) cn.Open();
        try
        {
            SqlCeCommand cmd = new SqlCeCommand(sql, cn);
            cmd.CommandType = CommandType.Text;
            // Add propertyvalues to WHERE-statement using reflection
            foreach (PropertyInfo p in propinfs)
            {
                dynamic propvalue = p.GetValue(search, null);
                // Except for DateTime values 1800-01-01 (defined as null)
                if (propvalue != null && !(propvalue.GetType() is DateTime && propvalue != DateTimeNull))
                {
                    if (comparer == "LIKE") propvalue = "%" + propvalue + "%";
                    cmd.Parameters.AddWithValue("@" + p.Name.ToLower(), propvalue);
                }
            }
            SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
            if (rs.HasRows)  // Only if database is not empty
            {
                while (rs.Read()) // Read next row in database
                {
                    // Instantiate single item of List data
                    var dataitem = new T();  // Object to put the field-values in
                    foreach (PropertyInfo p in propinfs)
                    {
                        // Read database fields using reflection
                        PropertyInfo singlepropinf = typeof(T).GetProperty(p.Name);
                        int ordinal = rs.GetOrdinal(p.Name);
                        dynamic result = rs.GetValue(ordinal);
                        // Conversion to null in case field is DBNull
                        if (result is DBNull)
                        {
                            if (singlepropinf.PropertyType.Equals(typeof(DateTime)))
                            {
                                singlepropinf.SetValue(dataitem, DateTimeNull, null); // Fill data item with datetimenull
                            }
                            else
                            {
                                singlepropinf.SetValue(dataitem, null, null); // Fill data item with null
                            }
                        }
                        else
                        {
                            singlepropinf.SetValue(dataitem, result, null); // Or fill data item with value
                        }
                    }
                    data.Add(dataitem);  // And add the record to List<T> data.
                }
            }
            else
            {
                //MessageBox.Show("No records matching '" + wherestr + "'!");
                return 0;
            }
        }
        catch (SqlCeException sqlexception)
        {
            MessageBox.Show(sqlexception.Message, "SQL-error.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return -1;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return -1;
        }
        finally
        {
            cn.Close();
        }
        // Return number of objects (should equal number of retrieved records)
        return data.Count();
    }
    #endregion

Answer 2

1: a list of unknown type could be the non-generic IList, or ArrayList, or List<object>

2: Activator.CreateInstance(type)

Alternatively, look at writing a generic method, ideally something like:

ReadData<T>(List<T> data, ...) where T : class, new()

and use new T() to create new items, and typeof(T) to talk about the Type. With a generic method the caller supplies the T - often implicitly. Note that there is no need for the ref in your example.