A question regarding c# interfaces and maybe generics

Question

Hi,

We have built an internal tool that generates the whole data access, each table has a class the represents it's data and all the common operations.(think lightweight Entity framework).

These DataAccess objects always have a constructor that receives a connection string, and a Load function that receives a SqlDataReader.

Something like this:

class Customer
{
    public string ConnStr;
    public int Id;
    public string Name;

    Public customers(string connStr)
    {
        ConnStr = connStr;
    }

    public Customer Load(SqlDataReader)
    {
        if(reader.Read())
        {
            Id = reader["Id"].ToString();
            Name = reader["Name"].ToString();
        }   
    }
}

I want to write a utility Data Access static method that will allow me to write my SQL and get a list of objects in return, following that previous object example:

string SQL = "SELECT * FROM Customers WHERE Name=@Name";
List<Customer> customers = GetList<Customer>(connStr, SQL, new SqlParameters("@Name", "John"));

I somehow can't figure how to deal with it, I have tried interfaces but they don't allow constructors or static methods, and using generics - i can't call the methods i need to init the object(constructor + load), here is my latest try, commented out the section that doesn't work:

---

public static List<T> GetList<T>(string connStr, string SQL, params SqlParameter[] prms)
        {
            List<T> list = new List<T>();

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand(SQL, conn);
                foreach (SqlParameter param in prms)
                {
                    cmd.Parameters.Add(param);
                }

                using (SqlDataReader reader = cmd.ExecuteReader())
                {

                    //T item = new T(connStr);
                    //item.Load(reader);
                    //list.Add(item);
                }
            }

            return list;
        }

---

Btw, we are interested in open sourcing our DataAccess generator, it's amazing - it allow very effecient access to DB objects + creates a javascript data access layer that gives you FULL CONTROL of your DB from javascript(ofcourse this has security implications which can be managed).

If anyone here knows how to "open source" a project like this or any company that wants to join the development of this product - please feel free contacting me: [email protected]

Thanks in advance, Eytan

Answer 1

The Load is easy enough - you could have:

interface IDataEntity {
    void Load(SqlDataReader reader);
}

then:

public static List<T> GetList<T>(string connStr, string SQL,
      params SqlParameter[] prms) where T : IDataEntity, new()
{
    .... 
    T item = new T();
    item.Load(reader);
    list.Add(item);
}

The new T(connStr) is trickier - does it really need this value? A public property would be easier:

interface IDataEntity {
    void Load(SqlDataReader reader);
    string ConnectionString {get;set;}
}
class Customer : IDataEntity 
{ ... }

etc. There isn't any inbuilt (language) support for parameterised generic constructors. You can hack around it, but in many cases the parameterised forms of Activator.CreateInstance are fast enough (when compared to data-access over a network, reflection is negligible). If you need a parameterised version it can be done with Expression etc (let me know if you want an example).

Answer 2

Why not passing constr to a method:

T item = new T();
item.SetConn(connStr);

And do not forget:

public static List<T> GetList<T>(string connStr, string SQL, params SqlParameter[] prms)
  where T : new
{

Answer 3

This will not work as you wish. SQL classes for .net are not strongly typed. It it impossible to say from query like SELECT * FROM Customers... object of what class has to be constructed. This is possible if all your entities derive from single class that declares:

public virtual Entity Load(SqlDataReader reader)

and your generic method has constraint:

public static List<T> GetList<T>(string connStr, string SQL, params SqlParameter[] prms)
   where T : Entity, new()

then you could do:

T entity = new T();
entity.Load(reader);

Answer 4

You need to go to reflection, unless you want to change how your classes work in the way of constructors.

You can do this:

while (reader.Read())
{
    T item = Activator.CreateInstance(typeof(T), new object[] { connStr }) as T;
    item.Load(reader);
    list.Add(item);
}

(note here, I made it the responsibility of the above loop to call .Read on the reader)

Note that unless you expose the Load method through an interface, you need to call that through reflection as well.

I would do this:

public interface IDataObject
{
    void Load(IDataReader reader);
}

And then implement this for your Customer:

public class Customer : IDataObject
{
    ...

And then add a constraint to your GetList method:

public List<T> GetList<T>(string connStr, string sql, params SqlParameter[] parameters)
    where T : IDataObject