Best approach to bind a datagridview to database entity/ies

Question

Is this aproach the best that exist to databind a datagridview?

I've seen many people with problems binding datagridviews and after a lot of work I found this as the best method. I hope it helps other people, and someone can add an improvement.

Step 1) Create the dataGridView and its columns with the editor.

Step 2) Create an object that represents a row in the datagridview.

This object may have as many instances of database entities as you need. Here's an example with two objects (two columns in the datagridview)

public class ObjectToShow
{
    MyDatabaseObject myDatabaseObject = new MyDatabaseObject();

    public ObjectToShow(MyDatabaseObject myDatabaseObject)
    {
        this.myDatabaseObject = myDatabaseObject;
    }

    public string Data1 //to asign to a datagridview column
    {
        get { return myDatabaseObject.data1; }
        set { myDatabaseObject.data1 = value; NotifyPropertyChanged("Data1")}
    }

    public string Data2 //to asign to another datagridview column
    {
        get { return myDatabaseObject.data2; }
        set { myDatabaseObject.data2 = value; NotifyPropertyChanged("Data2"); }
    }

    //This is to notify the changes made to the object directly and not from the control. This refreshes the datagridview.
    public event PropertyChangedEventHandler PropertyChanged;
    private void NotifyPropertyChanged(String propertyName)
    {
        if (PropertyChanged != null)
        {
            PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
    }

}

Step 3) In the Form create a BindingList of ObjectToshow and a bindingSource as follow

BindingList<ObjectToshow> ObjectToShow_list = new BindingList<ObjectToshow>();
BindingSource bindingSource = new BindingSource();

Step 4) Create the bindings this way

//if we don't put this, each public property in ObjectToshow will generate a new column in the datagridview
//I think it's best to create the columns from the editor.
dataGridView1.AutoGenerateColumns = false;

//database -> <- bindingList -> <- bindingSource -> <- datagridview <- user
bindingSource.DataSource = ObjectToShow_list;
dataGridView1.DataSource = bindingSource;

dataGridView1.Columns["Column_Data1"].DataPropertyName = "Data1";
dataGridView1.Columns["Column_Data2"].DataPropertyName = "Data2";

Step 5) query the database

//Example bringing all the data from a database table. This should be done in a controller class.
My_DBModel DB_context = new My_DBModel();    
List<myDatabaseObject> myDatabaseObject_list = DB_context.myDatabaseObject.ToList();

//Clear de previous data    
ObjectToShow_list.Clear();

//Add the queried elements to the bindingList
foreach (myDatabaseObject item in myDatabaseObject_list)
{
    ObjectToshow objectToshow = new ObjectToshow(item);
    ObjectToShow_list.Add(objectToshow);
}

Step 6) Modify the data from the bindingList or the datagridview as you want. Then DB_context.saveChanges().

To add data, add it directly to DB_context.myDatabaseObject.Add(new ...) and query the database again; If you want to add it from the datagridview I think you have to handle the event and add it to the context anyway.

This is what I do and it works, but I'm not sure if it's the best way. Thanks in advance.

Answer 1

So, in order to explain my thoughts better I'll write an answer (as it doesn't restrict me with the character count) but I want to make it clear that I only add my thoughts about how your initial code could be made better and not how it must be done. Having said that let's get into the topic. I'll start with Step 2) Create an object that represents a row in the datagridview. because I think this is what it's all about. Your database model (the tables and the columns for each table) sometime will reflect your business model, but sometimes in your business logic you'll need to use information from 2 or more database tables and I think that the most common approach is the one you've chosen - create a new class that represents the business logic needs. Even here you are given some options like using anonymous objects or [NotMapped] properties but I'll leave these to someone that actually prefers any of these other options since I also would go with a new class. I'll skip step 3 and 4 since I don't think I have something of value to write about that and go straight to Step 5) query the database. The first thing that I think should be reconsider is the responsibilities that you give to each Model, View and Controller. As I wrote in one of my comments, in my opinion having another project dedicated to handle the data access is as far the best approach I've found. Why I prefer this approach? Well - first, as I wrote, your database model will most probably not reflect your business model. Let say in your project you have Model folder and you put there all the entities plus the business objects. It's confusing, even with a fairly small application you will find yourself with many classes and it will be difficult even for you at some point to tell which class represents a database tables (entity) and which you use in your business logic. It will be even harder for the man after you to find out those things, or even you, after a few months later. So it's a simple thing that can make your code much more readable which alone is not a small achievement. And after some more reading about how to decouple an application if we decide that indeed a separate project for data access is a good approach, then it makes a lot of sense to put the logic for getting that data in this project. The way I like to do that (keep in mind I don't have much experience, I'm learning as I'm writing this stuff) is to use Repository pattern. You can read a lot about this pattern and the different ways it's used, but just to show you the advantage of using repository instead of

My_DBModel DB_context = new My_DBModel();    
List<myDatabaseObject> myDatabaseObject_list = DB_context.myDatabaseObject.ToList();

Let's say you have two entities - User and Order. And you have a GenericRepository that implements the basic methods that you will use while manipulating data:

public class GenericRepository<TEntity> : IRepository<TEntity> where TEntity : class
{
    internal MyDbContext context;
    internal DbSet<TEntity> dbSet;

    public GenericRepository(MyDbContext context)
    {
        this.context = context;
        this.dbSet = context.Set<TEntity>();
    }

    public virtual IQueryable<TEntity> GetAll()
    {
        return dbSet;
    }

    public virtual IQueryable<TEntity> GetBy(Expression<Func<TEntity, bool>> predicate)
    {
        return dbSet.Where(predicate);
    }

    public virtual TEntity GetById(long id)
    {
        return dbSet.Find(id);
    }
    //And so on...

and you have also UserRepository and OrderRepository which both inherit from the GenericRepository so already for each entity you have all basic methods implemented so you don't have to repeat yourself every time when you want to perform Delete or Update.And why I don't like My_DBModel DB_context = new My_DBModel();? Well, imagine that you use some method, let's say GetOrdersBySomething() and you use this method on several places in your code by querying the database. What will happen if someone decides to write a stored procedure which will return this information from now on - you have to find all places where you actually use this method and change the logic. Let's say that few months later you have to use data from a web service too.. each change force you to rewrite the same logic on different places in your application. But if you use repository you will just have GetOrdersBySomething() in your OrdersRepository and each time when you have to make a change you gonna make it only here and nowhere else.

Also, if I understood your post correctly, the main topic is about being able to collect data from several tables in the database and bind it as a datasource. So what kind of problems this may cause. Even if you are dealing with relatively small amount of data, if you first query each table separately and then try to populate your business object on the server side this may cause a big performance issue. If you have to use let say 3 tables each with 10columns, that makes 30 columns total. If you need only 15 of them, then what you want is the database server to do it's work and return those columns in the way you need them, so the work on the server side is as little as possible. Which lead me to the next topic that I point out - the expression trees. I won't write much about them cause I don't think I have some deep understanding about them, but here is the official msdn page about the topic http://msdn.microsoft.com/en-us/library/bb882637.aspx where you can read more about what expression tree is, what is the idea behind it. And when you get the idea of what exactly expression tree is, then it will be more clear why I think that your example where you query against only one table is not the best one, because this approach really shines when you execute the appropriate query.

Answer 2

You could just bind the DataGridView to the DataSource with AutoGenerateColumns on something like

dataGridView1.DataSource = [yourDataSource]

Then loop through the columns and set their properties in the loop which would allow you to hide columns you don't want to see, name headers etc.

This is how I do mine

foreach (DataGridViewColumn col in dgvReconciledItems.Columns)
{
    switch (col.Name)
    {
        case "Column1":
            col.HeaderText = "Header1";
            col.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            col.FillWeight = 30;
            break;
        case "Column2":
            col.HeaderText = "Header2";
            col.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            col.FillWeight = 10;
            break;

        default:
            col.Visible = false;
            break;
    }
}

Any questions let me know