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...

Yes, the Linq and EF will be a good choice! Spend some time to learn it and you will never regret about this one. — Kath, Nov 24 '12 at 06:54
If You want using table columns as objects You should learn about Linq and EF. This will be very helpful in Your case. — WooCaSh, Nov 24 '12 at 06:56
possible duplicate of [C# - IDataReader to Object mapping using generics](http://stackoverflow.com/questions/1105549/c-sharp-idatareader-to-object-mapping-using-generics) — nawfal, Aug 01 '15 at 11:52

Mert Akcakaya · Answer 1 · 2012-11-24T07:13:49.540

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.

MVCKarl · Accepted Answer · 2012-11-24T07:12:49.480

0

I agree with the other commentators, if you learn EF and LINQ It will make your life much more easier.

Entity Framework Video

Introduction to LINQ Video
Introduction to LINQ Queries Webpage
Lots of LINQ examples C#

In terms of your solution perhaps you can just make 3 different Select Methods: SelectClients, SelectProduct and SelectRooms. You will need to change a bit of the code so that it only opens and closes the connection once.

edited Nov 24 '12 at 07:12

answered Nov 24 '12 at 07:04

MVCKarl

1,283
9
7

I really appreciate the help from all of you. I had thought about creating a method of searching for customers, one for products and one for rooms ... but thought I was doing something wrong in creating many similar methods. But from what I'm seeing is not a bad alternative. – user1848998 Nov 24 '12 at 07:43

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

2 Answers2