There is any way to make this C# form DataGridView with MySQL, setup more simpler? In this example project i 'm using a myDB database person table to fill the DataGridView, and handling CRUD methods.
This simple project is handling the persons in the database, you can add new person to the database, you can delete, update or read. I made the project as compact as I could, but I think it could be shorter and easier to understand.
In this case dt is my DataGridView name, I made a Query function to make it clearer and more understandable, and I'm updating the the mysql database with the help of the adapter and the command builder's update function.
I'm iterating through the DataSet's tables and updating every table. The name of the query will be the name of the table's name in the DataSet.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySqlConnector;
namespace WPF_crud
{
public partial class Form1 : Form
{
const string con = "server=localhost;database=myDB;username=root;";
MySqlConnection connection= new MySqlConnection(con);
//act as a bridge between a DataSet and a MySQL database for retrieving and saving data
MySqlDataAdapter adapter;
//DataSet is storing the data what he gets from the adapter (in correct variable string,int,bool stb.) it can store multiple tables from a query
DataSet data = new DataSet();
//automatically generates SQL commands (such as SELECT, INSERT, UPDATE, DELETE)
MySqlCommandBuilder command = new MySqlCommandBuilder();
public void Query(string query)
{
// Create a MySqlDataAdapter to fill a DataSet
adapter = new MySqlDataAdapter(query, connection);
command = new MySqlCommandBuilder(adapter);
data.Clear();
//fill the DataSet
adapter.Fill(data, "table");
//fill the DataGridView with the DataSet specified table
dt.DataSource = data.Tables["table"];
}
public Form1()
{
InitializeComponent();
//only for design not required
dt.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dt.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
Query("SELECT * FROM person where 1");
}
private void btn_Save_Click(object sender, EventArgs e)
{
adapter.Update(data, "table");
Query("SELECT * FROM person where 1");
}
private void btn1_Click(object sender, EventArgs e)
{
Query("SELECT * FROM person where age < 50");
}
private void btn2_Click(object sender, EventArgs e)
{
Query("SELECT * FROM person where age > 50");
}
private void btn3_Click(object sender, EventArgs e)
{
Query("SELECT * FROM person where name like '%a%'");
}
}
}
Because of connection pooling, it is not correct to re-use the same connection object. The connection objects you see in code are already a thin/lightweight wrapper over a much heavier object managed via a pool, and when you keep the same connection object you optimize this thin wrapper at the expense of the much "heavier" real connection. In other words, you should only save/share the connection string, and you really do want a brand new connection object for most queries.
Even worse, the Query() method shown here will force you to write code that is crazy-vulnerable to SQL injection attacks... that kind of thing where you find out next year you were hacked six months ago, and were lucky to make it even that far. In fact, about the only thing worse you could do for security is save passwords in plain-text (you're not saving passwords in plain-text, right?)
This is important enough you should not even let it slide for practice, learning, or proof of concept work.
Instead, you need a mechanism to {strike}allow{/strike}force passing parameters to the queries separate from the SQL string itself (read up on parameterized queries).
But also, after fixing the Query() method to use parameters, I'd also change it to return the data as a result. So showing the data on the grid is more like this:
dt.DataSource = Query("SOME SQL", new string[} {"some parameter", "some other parameter"});
Once we get this far, you'll also find it often makes sense to put this stuff in it's own static class:
public static class DB
{
private static readonly string connectionString = "connection string here";
// you can still have a Query() method, but make it PRIVATE to the class
// and have it ask for an argument for query parameters.
private static DataTable Query(string sql, IEnumerable<(string, string)> parameters=null)
{
using var cn = new MySqlConnection(connectionString);
using var cmd = new MySqlCommand(sql, cn);
using var da = new MySqlDataAdapater(cmd);
if (parameters is object)
{
foreach(var parameter in parameters)
{
cmd.AddWithValue(parameter.Item1, parameter.Item2);
}
}
var result = new DataTable();
da.Fill(result);
return result;
}
// now have a PUBLIC method for each query:
public static DataTable MaxAge(int age)
{
string sql = "SELECT * FROM person where age < @MaxAge";
return Query(sql, new (string, string)[] {("@MaxAge", age.ToString())});
}
public static DataTable MinAge(int age)
{
string sql = "SELECT * FROM person where age > @MaxAge";
return Query(sql, new (string, string)[] {("@MaxAge", age.ToString())});
}
public static DataTable NameContains(string name)
{
// LIKE queries with a leading wildcard are SLOOOOW! Avoid when possible!
string sql = "SELECT * FROM person where name like '%' + @Name + '%'";
return Query(sql, new (string, string)[] {("@MaxAge", name)});
}
}
Now I know this seems like I made things more complicated, rather than less, but look what the button click methods will look like now:
// Give these buttons meaningful names!
private void btn1_Click(object sender, EventArgs e)
{
dt.DataSource=DB.MaxAge(50);
}
private void btn2_Click(object sender, EventArgs e)
{
dt.DataSource=DB.MinAge(50);
}
private void btn3_Click(object sender, EventArgs e)
{
dt.DataSource = DB.NameContains("a");
}
Finally, I see this:
I'm updating the the mysql database with the help of the adapter and the command builder's update function.
You're also almost always better off either writing these INSERT/UPDATE/DELETE queries yourself, or getting a real ORM solution.