How to create a generic list and populate with a Select SQL Query in c#

Question

I'm working with C # in Visual Studio Express 2012. I work exclusively with desktop applications.   As database I use MySQL

  The connection between the database and MySQL is working perfectly.

  My doubt is about making searching and return the result.

  Of all the examples I've seen, the most common is to create a method in my class MySQL enter code hereconnection that will return a list with the search result. I really do not know if this is conceptually more correct, but it seems very acceptable.

  I managed to do a search that returns all customers from my table. But my big question is: how to make this generic method?

  Eg

  My form has a button that triggers the click event:

dbConnect = new DBConnect();
dbConnect.OpenConnection();
private List<Clients> listSQLQuery; 
listSQLQuery = dbConnect.Select("select * from  clients");
datagridview.DataSource = listSQLQuery;

My method dboConnect.Select() used above:

public List<Clients> Select(string query)
{        


    //Create a list to store the result
    List<Clients> list = new List<Clients>();
   Clients clients = new Clients();


    //Open connection
    if (this.OpenConnection() == true)
    {
        //Create Command
        MySqlCommand cmd = new MySqlCommand(query, connection);
        //Create a data reader and Execute the command
        MySqlDataReader dataReader = cmd.ExecuteReader();

        //Read the data and store them in the list
        while (dataReader.Read())
        {
            clients.Id = dataReader["Id"].ToString();
            clients.Name = dataReader["Name"].ToString();
            list.Add(cliente);
        }

        //close Data Reader
        dataReader.Close();

        //close Connection
        this.CloseConnection();

        //return list to be displayed
        return list;
    }
    else
    {
        return list;
    }
}

And finally, my class Clients

public class Clients
{
    public string Id { get; set; }
    public string Name { get; set; }
}

This all works, but is very specific to a query with a Client.

  If now I make a call listSQLQuery=dbConnect.Select("select * from products") this method will not work, because it was built using as return a list of Clients and not of Products.

  I do not know if I could be clear in my question, but I wish I could make calls like without worrying if its gonna be a client, a product or a room.

Something like this:

listSQLQuery.dbConnect.Select("select * from clients");
listSQLQuery.dbConnect.Select("select * from products");
listSQLQuery.dbConnect.Select("select * from rooms");

   Is there any way to get a generic list return, without worrying about the type of listQuerySQL? I just wanna bind this list to a datagridview.

I am pretty novice in C#, so I dont know anything about LINQ, Entity Framework...

Answer 1

You can change your Select method like following and provide seperate methods to parse and create an instance of your entities from a datareader.

public List<T> Select(string query)
{
...
while(dataReader.Read())
{
     list.Add(CreateInstance<T>(dataReader)):
}
...
}

private T CreateInstance<T>(DataReader dataReader)
[
     if(T is Client)
          return CreateClient(dataReader);
     else if(T is Manager)
          return CreateManager(dataReader);
...
}

private Client CreateClient(DataReader dataReader)
{
     return new Client(dataReader["id"], dataReader["name"]);
}

EDIT: I don't agree with EF recommandations. Since you are new to C#, playing with some basic stuff makes you understand EF and some DAL patterns easier. Feeling confident about lower level parts of a system is very important when you hit a wall in a project and when you need to dive deeper to the inner workings.

If you feel yourself comfortable about DAL technologies already, you can jump to EF or NHibernate easily.