Fill dataGrid from MySQL database in C# WPF

Question

I want to fill a dataGrid in my WPF application.

My XAML:

<DataGrid AutoGenerateColumns="True" Height="200" HorizontalAlignment="Left" 
Margin="102,72,0,0" Name="dataGrid1" VerticalAlignment="Top" Width="848" />

My code behind:

  public void FillGrid()
    {
        string MyConString =    
        "SERVER=myserver.com;" +
        "DATABASE=mydatabase;" +
        "UID=myuserid;" +
        "PASSWORD=mypass;";

        string sql = "SELECT clientnr, name, address FROM clients ORDER BY name";

        MySqlConnection connection = new MySqlConnection(MyConString);
        MySqlCommand cmdSel = new MySqlCommand(sql, connection);
        DataTable dt = new DataTable();
        MySqlDataAdapter da = new MySqlDataAdapter(cmdSel);
        da.Fill(dt);
        dataGrid1.DataContext = dt;
    }

I'm sure that the MySQL part is correct, it does not give any errors. VS10 express doesn't give any errors. But if i execute the method my dataGrid won't be filled.

What I'm doing wrong?

Thanks in advance!

Solution 1

Set your DataGrid's binding:

<DataGrid ItemsSource="{Binding }" />

Solution 2

You definitely want it to be bound to the DataTable and not the Adapter, as Rachel suggested (the adapter's job is to populate the DataTable). Also, it's good to enclose connections and commands in usings to make sure everything is cleaned up, like this:

public void FillGrid()
{
    string MyConString =
    "SERVER=myserver.com;" +
    "DATABASE=mydatabase;" +
    "UID=myuserid;" +
    "PASSWORD=mypass;";

    string sql = "SELECT clientnr, name, address FROM clients ORDER BY name";

    using (MySqlConnection connection = new MySqlConnection(MyConString))
    {
        connection.Open();
        using (MySqlCommand cmdSel = new MySqlCommand(sql, connection))
        {
            DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter(cmdSel);
            da.Fill(dt);
            dataGrid1.DataContext = dt;
        }
        connection.Close();
    }
}

Solution 3

Replace

dataGrid1.DataContext = dt; 

with

dataGrid1.ItemsSource = dt.DefaultView;

Solution 4

Just call the method FillGrid() after InitializeComponents() in your code behind. I just did that and it runs perfectly.

Solution 5

I always use this code to display/fill data in my Datagrid:

SqlConnection con = new SqlConnection("your connection string");
    
private void LoadGrid()
    {
        SqlCommand cmd = new SqlCommand("Select * From XXX;", con);
        DataTable dt = new DataTable();
        con.Open();
        SqlDataAdapter sdr = new SqlDataAdapter(cmd);
        sdr.Fill(dt);
        dataGrid.ItemsSource = dt.DefaultView;
        con.Close();
    }

View more solutions

Comments

[Rachel]

@Lars See the update to set the DataGrid binding. Also, verify your FillGrid method is getting run.

[Rachel]

@HaiderKhattak I'd suggest posting a new question with the relevant code. The most likely cause is your DataContext isn't set correctly.

[user1034912]

details pleaseeeee

[lurker]

This should explain why the replacement is recommended.