Is it possible to use class's properties not knowing anything about it (even a type and name)?

Question

I want to try to not write all methods and classes with a similar code with SQL statements and follow the DRY principle. And now I need to get access to properties of a not known class.

public T Get(string pathToClass, string sqlStatement)
{
    SqlConnection connection = new SqlConnection();
    connection.ConnectionString = connectionString; //connectionString is field of class.
    connection.Open();

    SqlCommand command = new SqlCommand(sqlStatement);

    SqlDataReader reader = command.ExecuteReader();

    List<Type> types = new Reflect(pathToClass).GetTypes();   //Reflection is my class library
    List<string> names = new Reflect(pathToClass).GetNames(); //it only reads the classes and returns properties's names and types.

    object entity = new object(); //idk is it right or not, but seems it is.
    for(int i = 0; reader.Read(); i++)
    {
        entity.SomeProperty = types[i].Parse(reader[names[i]].ToString()); //There is a wrong point, where I'm confused
    }

    connection.Close();

    return (T)entity;
}

Very unclear what you trying to do - if you know nothing about the property you really can't get its value... Indeed you can get names of all properties and get values of each property by the name... but that does not seem to be what you are asking... — Alexei Levenkov, Aug 25 '20 at 18:39
This approach looks like it would force you to build sql in a way that's crazy vulnerable to injection attacks. — Joel Coehoorn, Aug 25 '20 at 18:56

Joel Coehoorn · Accepted Answer · 2020-09-11T16:40:30.457

If not using an ORM, the common approach here is returning raw DataRow or IDataRecord objects, with a static factory method with each/all of your individual types that knows how to construct an instance of that type given an IDataRecord or DataRow input.

For example:

//Initial class might look like this:
public class Employee
{
    public string FirstName {get;set;}
    public string LastName {get;set;}
    public int ID {get;set;}
    public string Email {get;set;}

    public static Employee FromDataRow(DataRow row)
    {
        return new Employee
        {
            ID = row["ID"],
            FirstName = row["FirstName"],
            LastName = row["LastName"],
            Email = row["Email"]
        };
    }
}

//Data access might look like this:
public class DB
{
    private static string ConnectionString { get;} = "connection string here";
    public static IEnumerable<DataRow> FindEmployeeRecords(string LastName)
    {
        string sql = "SELECT ID, FirstName, LastName, Email FROM Employee WHERE LastName LIKE @LastName + '%'";
        using (var cn = new SqlConnection(ConnectionString))
        using (var cmd = new SqlCommand(sql, cn))
        using(var da = new SqlDataAdapter(cmd))
        {
            cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 80).Value = LastName;
            var result = new DataSet();
            da.Fill(result);
            return results.Tables[0].Rows;
        }
    }
}

// and then you could use it like this:
var employees = DB.FindEmpoyeeRecords("Smith").Select(Employee.FromDataRow);
foreach (var employee in employees) 
{
   //...
}
// or you could bind employees to a datasource

Of course, you could use reflection to map the DataRow fields to the object. You can even abstract part of that into a utility method. However, you still need the factory method in each type (it's just a little less code), reflection is slow (like, really slow), and you lose the flexibility of how the mapping might occur, if you want a class field to be a bit different from a table column.

We can also reduce boilerplate code in the database layer. However, this requires a better understanding of functional programming.

Is it possible to use class's properties not knowing anything about it (even a type and name)?

1 Answers1