C# Populate datagrid from MySQL

[Ro87]

Hi guys,

I have just started learning C# WPF and I am trying to fill a datagrid with the contents of a mysql table.

The code I have for a button to do this is:

        private void button1_Click(object sender, RoutedEventArgs e)
        {
            
           string MyConString =
                "SERVER=localhost;" +
                "DATABASE=vs;" +
                "UID=user;" +
                "PASSWORD=pass;";

            
            MySqlConnection myCon = new MySqlConnection(MyConString);

            try
            {
                myCon.Open();
                MessageBox.Show("MySQL connection open"); 
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            string sql = "select * from human";

            MySqlCommand myCmd = new MySqlCommand(sql, myCon);

            MySqlDataReader myReader = myCmd.ExecuteReader();

            dataGrid1.ItemsSource = myReader;
        }

When I run the app, I connect successfully but the datagrid does not show the contents of the selected table.

What am I doing wrong, is the datagrid even the correct component for this?

Thanks

 

[initroot]

assign the values from datareader to an dataset and then populate your datagrid with it.
Or use sqldataadapter.
good luck.

 

[deweyzeph]

You have to assign the datasource property of the datagrid and then you have to call the databind method of the datagrid.

So:

dataGrid1.DataSource = myReader;
dataGrid1.DataBind();

 

[Nemeses]

you not missing

myReader.Read(); ??

 

[Fuma]

Just another note, WPF is different to winforms.

You must unlearn winforms, if you used winforms before.

 

[Ro87]

assign the values from datareader to an dataset and then populate your datagrid with it.
Or use sqldataadapter.
good luck.

Hmm how do I do this with WPF C#?
I have tried creating a dataset without success, there is no code completion for the word dataset.


I know WPF is different from Winforms, from my research so far but this is proving too difficult. I feel like I'm missing something small though.

dataGrid1 has no DataBind() property so no luck there.

I tried enclosing the dataGrid1.ItemsSource = myReader in a while myReader.Read() loop.

Ag it's frustrating... But I'll keep at it.

 

[Fuma]

Try to specify your CommandType = CommandType.Text;

MySqlCommand myCmd = new MySqlCommand(sql, myCon);
[B]myCmd.CommandType = CommandType.Text;[/B]
MySqlDataReader myReader = myCmd.ExecuteReader();

and check your gridview has correct binding fields:

<ListView Height="175" HorizontalAlignment="Left" Margin="176,26,0,0" Name="GridView1" 
                      VerticalAlignment="Top" Width="481" Grid.Row="1">
	<ListView.View>
		<GridView>
			[B]<GridViewColumn Header="Heading one" DisplayMemberBinding="{Binding Path=HumanField1}"
			<GridViewColumn Header="Heading two" DisplayMemberBinding="{Binding Path=HumanField2}"[/B]

		</GridView>
	</ListView.View>
</ListView>

I prefer to create custom objects

public class Human
{

public string HumanField1 {get; set;}
public string HumanField2 {get; set;}
public string HumanField3 {get; set;}

public Human(){}

public List<Human> getHumanList()
        {
            var human  = new List<Human>();
            SqlCommand cmd = new SqlCommand("select * from human", myCon );
            SqlDataReader reader;
            cmd.CommandType = CommandType.Text;
            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                human.Add(new Human() { 
                                            HumanField1 = reader[0].ToString(), 
                                            HumanField2 = reader[1].ToString(),
                                            HumanFiedld3 = reader[2].ToString()
                });

            }

            reader.Close();

            return human;
        }
}

and on your button click

private void button1_Click(object sender, RoutedEventArgs e)
{
    var humanList = new Human().getHumanList();
    dataGrid1.ItemsSource = humanList;
}

 

[Nerfherder]

Have you made sure that your query works ?

Also try and output the result first in a message box first.

I do it like this

        public static System.Data.DataSet SQLquerySet(string query)
        {
            using (SqlConnection c = new SqlConnection(sConnectString))
            {
                c.Open();
                using (SqlDataAdapter a = new SqlDataAdapter(query, c))
                {
                   System.Data.DataSet t = new System.Data.DataSet();
                    a.Fill(t);
                    return t;
                }
            }

        }

I then call it like this:

dataGridView2.DataSource = SQLquerySet(sSqlquery);

 

[initroot]

XAML I include the DataGrid:

    <WpfToolkit:DataGrid
        Grid.Row="4" Grid.Column="0"
        ItemsSource="{Binding Path=GridData, Mode=OneWay}" >
    </WpfToolkit:DataGrid>

In my view model I expose a DataView:

  public DataView GridData
  {
     get
     {
        DataSet ds = new DataSet("MyDataSet");

        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
           SqlCommand cmd = conn.CreateCommand();
           cmd.CommandType = CommandType.Text;
           cmd.CommandText = "SELECT * FROM HumanResources.Employee";

           SqlDataAdapter da = new SqlDataAdapter(cmd);
           da.Fill(ds);
        }

        return ds.Tables[0].DefaultView;
     }
  }

Please note I copied this, and didn't check it myself too busy now.

Hmm how do I do this with WPF C#?
I have tried creating a dataset without success, there is no code completion for the word dataset.


I know WPF is different from Winforms, from my research so far but this is proving too difficult. I feel like I'm missing something small though.

dataGrid1 has no DataBind() property so no luck there.

I tried enclosing the dataGrid1.ItemsSource = myReader in a while myReader.Read() loop.

Ag it's frustrating... But I'll keep at it.

 

[Ro87]

Hi guys thanks for all the helpful replies. Ok I have tested the query and sent the output to a listbox successfully:

private void button1_Click(object sender, RoutedEventArgs e)
        {
            string MyConString = "SERVER=localhost;" +
                "DATABASE=vs;" +
                "UID=root;" +
                "PASSWORD=l33t;";
            MySqlConnection connection = new MySqlConnection(MyConString);
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader Reader;
            command.CommandText = "select * from human";
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                string thisrow = "";
                for (int i = 0; i < Reader.FieldCount; i++)
                    thisrow += Reader.GetValue(i).ToString() + ",";
                listBox1.Items.Add(thisrow);
                dataGrid1.Items.Add(thisrow);
                
            }
            connection.Close();
        }

This is the result when I click the button:

As you can see in the pic, the listbox above is populated but the datagrid just shows three lines.

I think there is a property of the datagrid that I'm not setting correctly?

 

[Lounger]

Hi guys thanks for all the helpful replies. Ok I have tested the query and sent the output to a listbox successfully:

private void button1_Click(object sender, RoutedEventArgs e)
        {
            string MyConString = "SERVER=localhost;" +
                "DATABASE=vs;" +
                "UID=root;" +
                "PASSWORD=l33t;";
            MySqlConnection connection = new MySqlConnection(MyConString);
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader Reader;
            command.CommandText = "select * from human";
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                string thisrow = "";
                for (int i = 0; i < Reader.FieldCount; i++)
                    thisrow += Reader.GetValue(i).ToString() + ",";
                listBox1.Items.Add(thisrow);
                dataGrid1.Items.Add(thisrow);
                
            }
            connection.Close();
        }

This is the result when I click the button:

As you can see in the pic, the listbox above is populated but the datagrid just shows three lines.

I think there is a property of the datagrid that I'm not setting correctly?

It isn't a good idea to hard code database connection details. It also means you would have to track them and recompile if you use a different database. Rather put them in a separate file - database.properties - and read it in with a paramater.

 

[Fuma]

Hi guys thanks for all the helpful replies. Ok I have tested the query and sent the output to a listbox successfully:

private void button1_Click(object sender, RoutedEventArgs e)
        {
            string MyConString = "SERVER=localhost;" +
                "DATABASE=vs;" +
                "UID=root;" +
                "PASSWORD=l33t;";
            MySqlConnection connection = new MySqlConnection(MyConString);
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader Reader;
            command.CommandText = "select * from human";
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                string thisrow = "";
                for (int i = 0; i < Reader.FieldCount; i++)
                    thisrow += Reader.GetValue(i).ToString() + ",";
                listBox1.Items.Add(thisrow);
                dataGrid1.Items.Add(thisrow);
                
            }
            connection.Close();
        }

This is the result when I click the button:

As you can see in the pic, the listbox above is populated but the datagrid just shows three lines.

I think there is a property of the datagrid that I'm not setting correctly?

Show us your datagrid code