C#.NET
Working with databases in WPF (Visual Studio 2017)
Connecting to a Database C# - WPF and MySql
How do I add a reference to the MySQL connector for .NET?
MySQL
- Get your MySQL database up an running & create a table & store some data in it.
- Download mysql-connector-net-8.0.13.msi from Download Connector/NET and install it.
- Create new project; Visual C# > Windows Desktop > WPF App (.NET Framework)
- Once the project is created and loaded, right click References in the Solution Explorer pane.
- In the menu that is displayed, click Add Refrerence...
- Click the Browse... button.
- Browse to C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.13\Assemblies\v4.5.2 and click MySql.Data.dll" file to select it.
- Click Add
- Click OK MySQL.Data is added to the list of references.
- In Visual Studio, click Toolbox tab that resides in the left edge of the project window.
- Click the DataGrid control and drag it into the preview app window displayed in the MainWindow.xaml designer section. If the MainWindow.xaml designer section isn't displayed, open it by clicking View in the Visual Studio main menu bar then click Designer.
- In the MainWindow.xaml designer window, adjust the size and position of the DataGrid control to your liking.
Now comes the really technical part...
- In the DataGrid tag, displayed in a editor section below the MainWindow.xaml designer section; add ItemSource="{Binding}"
- in the Solution Explorer pane, click the arrow to the left of MainWindow.xaml to expand that item.
- Double click MainWindow.xaml.cs item. A tab displaying the MainWindow.xaml.cs source code opens in the project window.
WpfApp1 project
MainWindow.xaml:
<Window x:Class="WpfApp1.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:local="clr-namespace:WpfApp1"
mc:Ignorable="d"
Title="WPF TestApp" Height="500" Width="500">
<Grid>
<DataGrid Name="dataGrid1" ItemsSource="{Binding}" Height="150" Width="450" Margin="21,21,21,298"/>
</Grid>
</Window>
MainWindow.xaml.cs:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using MySql.Data.MySqlClient;
namespace WpfApp1
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
string connectionString = "SERVER=localhost; DATABASE=database name; UID=username;
PASSWORD=password;";
MySqlConnection connection = new MySqlConnection(connectionString);
MySqlCommand cmd = new MySqlCommand("select * from contacts", connection);
connection.Open();
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
// TIP: Store datatable in a DataTable variable and read that instead of reading db rows directly
// with a loop
connection.Close();
dataGrid1.DataContext = dt;
}
}
}