WPF project that uses MySql

  • Article

Previous Related Blog                                                     Next Related Blog

If you are like me, MySql is somewhat a strange land, hopefully you will find that the concepts shown in this blog will help you understand how to use databinding, XAML, and MySql. 

The source file is at the bottom of the blog in case you want to download it for investigation.

Source:

  1. Download MySQL Installer, use the smaller of the two if you have a fast or fat connection to the internet.
  2. MySQL Connector/Net Developer Guide
  3. 4.2 Making a Connection
  4. 5.1.1 The MySqlConnection Object (Following this tutorial will yield a successful connection, but you have to make sure you have your references or nugget's set correctly)
  5. Various readings

Discussion:

WPF is XAML.  Forms of any sort are sort of trailing edge technology, but the use of XML is considered the cool thing to do.

So open a WPF C# application and make some changes, you also will need to add a reference to the MySQL Data, add the salika database to your project’s Data Server.

  1. Select New Project and then C# WPF
    • image
  2. Once your WPF Application opens add the salika MySQL database to the your server explorer:
    • image
  3. In your Visual Studio 2013 open the MainWindows.xaml, from the toolbox, you may need to add the toolbox to your IDE, do this by pressing Ctrl+Alt+x
    • In the default form add a DataGrid, TextBlock (for a title), and a Button from the Toolbox, bring up the Toolbox by pressing Ctrl+Alt+x, just like you might do if you were using Windows Forms (if you got that in school, etc.)
    • You can either name the DataGrid in the XAML or use Properties like in Windows Forms.  If you use the Properties box point of view then the XAML will automatically add the x:Name=”DataGrid”, so make sure to look at your XAML in the IDE to see what that looks like.
      • image
    • Do the same process you used in step 2 to name the other two controls.  Using Bindings correctly will eventually mean you won’t need to use the control names, but for now let’s name the controls, it makes easier for me to explain it to you.

Add References

  1. Add the appropriate references for MySQL, you can use Nuget if you are familiar with it, but for this blog I will show you how to use reference:
    • Add References:
      • image

 

      • In the Reference Manager Select MySql.Data
      • image

 

  • Modify the using directives at the top of the MainWindow.xaml.cs:
    • You can now successfully add the namespaces MySQL.Data and MySQL.Data,
      • image      << Adding using MySql.Data;

 

      • image  <<Adding using MySql.Data.Databinding;

 

      • image<<Adding using System.Data;
      • FInally add using System.Data
      • Your “using” should look like the following:

 

Set up your Button and Click Event

  • We will need to make our code work so that when you click the button you populate the data grid control. 
      • Button Configuration using Properties:
      • image

To add an event you can either double click the button or click the small lightning bolt on the properties page, we will work with the click event a little later, but it is now set up to have the code added to it.  The properties box, after you click the little lightning symbol shown in the red box below.  We won’t use this process, but it is important that you are aware of it.

And your code will have a event method that looks like the following.  If you double click the button, see the second image

    • If you use the properties box in the event mode your click event is shown btnLoadDataGrid, without the expected _Click at the end

 

If you double-click the button, then the code will look like the following:

    • image
    • We will add the code that will fill the DataGrid with information later.  So keep scrolling down!

TextBlock Modification

    • Now modify your TextBlock to give a title to your applicationimage

 

    • Now you need to connect the DataGrid up to the MySQL database.  This requires that you use the XAML (there is a designer you can use, but it doesn’t really save any time).  This is somewhat complex if you haven’t used XAML.  But once you have worked with XAML for a period of time, you will get used to implementing various designs, in fact you should take a look at Jerry Nixon’s blog  at: https://blog.jerrynixon.com/, he is quite the UI designer, and if you want to understand how to be a good UI designer, then check out his blog.
    • Adding the DataGrid code, the code below should be added to the default MainWindow.xaml, I have put the code into a vertical style, normally it is horizontal and things are ordered unnaturally if you are not used to working with XAML:
    • After this code in the default MainWindow.xaml, your code will be different, you will likely have different Height and Widths, your x:Class will be different, and you can always change the title.  After the “greater than” symbol add the DataGri

 

    • Now add the following directly after the greater than symbol or close tag (scroll down to see how the code will look in the IDE).  Let’s discuss what is going on in the code:
      • {Binding Path=customer_id} points to the MySQL database sakila table named actor column named customer_id.  ItemsSource refers to the LoadDataBinding generated by the code we will add later for the button click.  Here is the XAML code just for the datagrid.  If you are used Forms, for whatever reason, the approach is different, but much easier to maintain and to expand on.  The format I used will work in XAML, but again, normally it is horizontal and not inline, I did this to conserve space on the website.
      • <DataGrid Name="dataGridCustomers"
                   ItemsSource="{Binding LoadDataBinding}"
                   AutoGenerateColumns="False"
                   HorizontalAlignment="Left"
                   Margin="14,55,0,46"
                   Width="496"
                   CanUserResizeRows="False"
                   >
             <!--Columns in the Data Grid-->
             <DataGrid.Columns>
                 <DataGridTextColumn Binding="{Binding Path=customer_id}"
                                     Header="Customer #"
                                     Width="75"
                                     IsReadOnly="True" />
                 <DataGridTextColumn Binding="{Binding Path=first_name}"
                                     Header="First"
                                     Width="100"
                                     IsReadOnly="True" />
                 <DataGridTextColumn Binding="{Binding Path=last_name}"
                                     Header="Last"
                                     Width="100"
                                     IsReadOnly="True" />
                 <DataGridTextColumn Binding="{Binding Path=email}"
                                     Header="Email"
                                     Width="150"
                                     IsReadOnly="True" />
             </DataGrid.Columns>
        </DataGrid>

Below is how your code may look when you have completed the XAML, it won’t look exactly like this, you may not have moved around the XAML to have a horizontal appearance.  For example,  you may want to add two buttons to connect to two different database tables or views, etc. 

  • Your MainWindow.xaml may look like the following, items that might be different are the x:Class, Height and Width:

<Window x:Class="DataGridBinding1.MainWindow"
        xmlns="https://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="https://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <!--Grid-->
    <Grid Background="#FFD1F9EE" Margin="10,0,0,-1.2" HorizontalAlignment="Left" Width="505" >
        <TextBlock Text="Customers"
                       Name="textBlockHeading"
                       Height="32" HorizontalAlignment="Left" Margin="16,15,0,0"
                       VerticalAlignment="Top" Width="310"  FontSize="20" FontStretch="Normal"/>
        <Grid HorizontalAlignment="Left"
                  VerticalAlignment="Top"
                  Height="310"
                  Width="505" Margin="0,2,0,0">
            <!--This starts the DataGrid XAML-->
            <DataGrid Name="dataGridCustomers"
                      ItemsSource="{Binding LoadDataBinding}"
                      AutoGenerateColumns="False"
                      HorizontalAlignment="Left"
                      Margin="14,55,0,46"
                      Width="496"
                      CanUserResizeRows="False"
                      >
                <!--Columns in the Data Grid-->
                <DataGrid.Columns>
                    <DataGridTextColumn Binding="{Binding Path=customer_id}"
                                        Header="Customer #"
                                        Width="75"
                                        IsReadOnly="True" />
                    <DataGridTextColumn Binding="{Binding Path=first_name}"
                                        Header="First"
                                        Width="100"
                                        IsReadOnly="True" />
                    <DataGridTextColumn Binding="{Binding Path=last_name}"
                                        Header="Last"
                                        Width="100"
                                        IsReadOnly="True" />
                    <DataGridTextColumn Binding="{Binding Path=email}"
                                        Header="Email"
                                        Width="150"
                                        IsReadOnly="True" />
                </DataGrid.Columns>
            </DataGrid>
            <!--The Button XAML-->
            <Button Content="Load Data Grid"
                    Name="btnLoadDataGrid"                   
                    Height="25"
                    HorizontalAlignment="Left"
                    Margin="226,10,0,0"
                    VerticalAlignment="Top"
                    Width="100" Click="btnLoadDataGrid_Click" />
        </Grid>
    </Grid>
</Window>

  • Now run your code by pressing F5, you will see your controls like the textblock, button and datagrid. 
    • Nothing happens if you press the button.
  • The LocalBinding has not been formed by the code behind.  MVC would handle this quite a bit different, the MainWindow.XAML and MainWindow.XAML.cs would be the view, and only the bindings would used to connect to the MVC classes that model or control.

Adding the btnLoadDataGrid_Click event code

  • The btnLoadDataGrid_Click event code is used instead of the normal MVC approach to keep things simple, but in a way more complex if you were going to do anything with more programming.
  • You will need to make a connection to the MySQL database, and set up the LocalBinding so that the data can be displayed.  To do that, you should have added the using MySql.Data and using MySql.Data.MySqlClient.
  • Now you need to add the following code (or similar) to the Button Click event created earlier:

private void btnLoadDataGrid_Click(object sender, RoutedEventArgs e)
{
string password = "9Gidget0";
string connStr =
"server=localhost;user=root;database=sakila;port=3306;password=" + password;
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT customer_id,first_name,last_name,email FROM customer", conn);
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds, "LoadDataBinding");
dataGridCustomers.DataContext = ds;
conn.Close();
} 

MySQLDatabindingUI.zip