Bind a DataGrid to a DataSet holding multiple tables at runtime in C#

Bind a DataGrid

This example shows how you can bind a DataGrid control to a DataSet loaded from an MDB file at runtime. When the form loads, the following code builds the DataSet and binds it to the form’s DataGrid control.

// The DataAdapters and the DataSet.
private OleDbDataAdapter DaAddresses, DaTestScores;
private DataSet DsContacts;

private void Form1_Load(object sender, EventArgs e)
{
    const string SELECT_ADDRESSES = "SELECT * FROM Addresses";
    const string SELECT_TEST_SCORES = "SELECT * FROM TestScores";

    // Get the database file name.
    // This assumes the database is in the executable directory.
    string db_name = Application.StartupPath + "\\Contacts.mdb";

    // Compose the connection string.
    string connect_string =
        "Provider=Microsoft.ACE.OLEDB.12.0;" +
        "Data Source=" + db_name + ";" +
        "Persist Security Info=False";

    // Create a DataAdapter to load the Addresses table.
    DaAddresses = new OleDbDataAdapter(SELECT_ADDRESSES,
        connect_string);

    // Create a DataAdapter to load the Addresses table.
    DaTestScores = new OleDbDataAdapter(SELECT_TEST_SCORES,
        connect_string);

    // Create and fill the DataSet.
    DsContacts = new DataSet("ContactsDataSet");
    DaAddresses.Fill(DsContacts, "Addresses");
    DaTestScores.Fill(DsContacts, "TestScores");

    // Bind the DataGrid to the DataSet.
    dgContacts.DataSource = DsContacts;
}

The code composes a database connect string and then creates two OleDbDataAdapter objects to select data from the database’s Addresses and TestScores tables. It creates a new DataSet and uses the data adapters to load their tables into it. Finally the code sets the DataGrid control’s DataSource property to the DataSet. The DataGrid automatically lets the user open either table and edit their values.

Note that this doesn’t work with the DataGridView control. The DataGrid is more powerful. It not only can display a multi-table DataSet, but it also lets the user navigate between the tables if they are linked with foreign key relationships. (I’ll demonstrate that in another post.)

When the user closes the form, the following code saves any changes in the data back to the database.

// Save changes to the data.
private void Form1_FormClosing(object sender,
    FormClosingEventArgs e)
{
    // Use a CommandBuilder to make the INSERT,
    // UPDATE, and DELETE commands as needed.
    OleDbCommandBuilder command_builder;
    command_builder = new OleDbCommandBuilder(DaAddresses);
    command_builder = new OleDbCommandBuilder(DaTestScores);

    // Update the database.
    try
    {
        DaAddresses.Update(DsContacts, "Addresses");
        DaTestScores.Update(DsContacts, "TestScores");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

This code creates two OleDbCommandBuilder objects to generate database commands as needed while updating the data. (When you create a command builder, it is attached to the data adapter you pass into the constructor. That’s how the adapter can later generate the commands it needs.)

The code then calls each data adapter’s Update method to update the database. (At that point the Update method uses the OleDbCommandBuilder objects to generate the commands it needs. It’s all a bit mysterious.)


Download Example   Follow me on Twitter   RSS feed   Donate




This entry was posted in controls, database and tagged , , , , , , , , , , . Bookmark the permalink.

11 Responses to Bind a DataGrid to a DataSet holding multiple tables at runtime in C#

  1. Ed Lyons says:

    Good example. Thanks.!

  2. rajshree says:

    Hey actually in my case I have read XML file into dataset in that dataset there are totally 4 tables where two are main table and another two table are inside this one main table…
    I want to convert this into excel sheet where I want to read in such fashion that 1st table row should repeat till last table row do not finished…

  3. Rod Stephens says:

    Sorry but I don’t think I understand what you need. Do you want to make a program convert the data loaded from XML into an Excel workbook? If so, then this example may help:

    Load a comma-separated value (CSV) in Excel in C#

  4. Jolle says:

    Hi Rod! I don’t have a DataGrid in my VS2012 Toolbox. There is the DataGridView only. So I have created and added a new DataGrid() in code and set width and height. I can’t find a possibility to define columns. Because of there are no columns defined, I have to choose between displaying Adresses or TestScores by clicking on a little plus sign and then one of two links, but I don’t see both merged in one grid. Any suggestions?

    • RodStephens says:

      That’s just the way this control behaves with two unrelated tables such as these. You can’t really put them on a single grid at the same time because they have different columns.

      If the data was in a database, you could use a SELECT statement to select data from multiple tables and display that, but that’s not what this example does.

  5. Jolle says:

    Ah! I figured out, I can set the DataMember to either Adresses or TestScores, but there is no merged member “Contacts”. Shouldn’t be there a relation between the two tables? How does the Dataset know how to combine the data from both tables?

    • RodStephens says:

      If you set DataMember to the name of a table, you only see that table. If you don’t set DataMember, then you can view either table at run time. Download the example to see the details.

      The control can display both tables but only one at a time because they don’t have the same columns. If you want to view both tables at the same time, you could use two DataGrid controls.

  6. Jolle says:

    Oops! Sorry! I think the most important sentense for me has to be “(I’ll demonstrate that in another post.)” 😎 Did you posted this already?

Leave a Reply

Your email address will not be published. Required fields are marked *