This article is first published at c-sharpcorner.com on Dec 20, 2022.
This article explain how MySqlExpress saves a programmer’s time and effort is handling MySQL data.
This article assumes that you have already understood the basics of using MySQL connectors, MySqlExpress is worked on top of a library called MySqlConnector (MIT). You may about their documentations at https://mysqlconnector.net
Before explaning the what is MySqlExpress, let’s look at the typical traditional way of handling MySQL in C#.
Here’s an usual example of MySQL table:
CREATE TABLE `book` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`barcode` VARCHAR(45),
`title` VARCHAR(300),
`author` VARCHAR(300),
`publisher` VARCHAR(300),
`date_register` DATETIME,
`price` DECIMAL(12,2),
PRIMARY KEY (`id`)
) ENGINE = InnoDB;To load rows from MySQL table into C#, here is one of the typical traditional method:
The connector loads the data into DataTable:
DataTable dt = new DataTable();
using (MySqlConnection conn = new MySqlConnection(ConnString))
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = "select * from book where id=@id";
cmd.Parameters.AddWithValue("@id", 1);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);
conn.Close();
}
}The most primitive way is to retrieve the data directly from DataTable and DataRow, by direct accessing the DataRow like this:
DataRow dr = dt.Rows[0];
lbId.Text = dr["id"] + "";
txtBarcode.Text = dr["barcode"] + "";
txtTitle.Text = dr["title"] + "";
txtAuthor.Text = dr["author"] + "";
txtPublisher.Text = dr["publisher"] + "";
DateTime dateRegister = Convert.ToDateTime(dr["date_register"]);
txtDateRegister.Text = dateRegister.ToString("yyyy-MM-dd");
decimal price = Convert.ToDecimal(dr["price"]);
txtPrice.Text = price.ToString("0.00");In order to enjoy the benefits of IDE IntelliSense, the DataTable or DataRow will be converted into Class Objects, which becomes reusable. Code once, use everywhere.
Preparing the Class Object:
public class Book
{
public int Id { get; set; }
public string Barcode { get; set; }
public string Title { get; set; }
public string Author { get; set; }
public string Publisher { get; set; }
public DateTime DateRegister { get; set; }
public decimal Price { get; set; }
}Loading (or converting, mapping) data into the Class Object:
Book book = new Book();
book.Id = Convert.ToInt32(dr["id"]);
book.Barcode = dr["barcode"] + "";
book.Title = dr["title"] + "";
book.Author = dr["author"] + "";
book.Publisher = dr["publisher"] + "";
book.DateRegister = Convert.ToDateTime(dr["date_register"]);
book.Price = Convert.ToDecimal(dr["price"]);Then the class can be reused like this:
txtBarcode.Text = book.Barcode;
txtTitle.Text = book.Title;
txtAuthor.Text = book.Author;
txtPublisher.Text = book.Publisher;
txtDateRegister.Text = book.DateRegister.ToString("yyyy-MM-dd");
txtPrice.Text = book.Price.ToString();The main problem here is the manual typing for accessing the data from DataRow:
Convert.ToInt32(dr["id"]);
dr["barcode"] + "";
dr["title"] + "";
dr["author"] + "";
dr["publisher"] + "";
Convert.ToDateTime(dr["date_register"]);
Convert.ToDecimal(dr["price"]);It requires the programmer to type the column name (field name) manually to access the data in DataRow. For table that has lots of columns, this process will take a lot of time and many lines of typing just to map the data to the class object, or whatever fields. Manual typing of field names also introduces the potential risk of human typo error.
Yes, every table, the same tedious work is needed to be repeated, and this is so unflexible and definitely going to take lots of time.
This is where MySqlExpress comes in. It automates the conversion from DataTable to Class Object.
Here is how MySqlExpress does:
Book book = null;
var dicParam = new Dictionary<string, object>();
dicParam["@id"] = 1;
using (MySqlConnection conn = new MySqlConnection(ConnString))
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
conn.Open();
MySqlExpress m = new MySqlExpress(cmd);
book = m.GetObject<Book>("select * from book where id=@id", dicParam);
conn.Close();
}
}Just one line of code:
book = m.GetObject<Book>("select * from book where id=@id", dicParam);It’ll replace all the data typing conversion work of DataRow to Class Fields.
For MySqlExpress, it comes with a tool called “MySqlExpress Helper“, a C# Class Object generator. It automates the fields creation for C#.
The Helper program will load a list of all MySQL tables. By clicking on the table’s name, it will generate all the fields in the combination of private fields and public properties.
Here’s an example of generated fields and properties:
public class Book
{
int id = 0;
string barcode = "";
string title = "";
string author = "";
string publisher = "";
DateTime date_register = DateTime.MinValue;
decimal price = 0m;
public int Id { get { return id; } set { id = value; } }
public string Barcode { get { return barcode; } set { barcode = value; } }
public string Title { get { return title; } set { title = value; } }
public string Author { get { return author; } set { author = value; } }
public string Publisher { get { return publisher; } set { publisher = value; } }
public DateTime DateRegister { get { return date_register; } set { date_register = value; } }
public decimal Price { get { return price; } set { price = value; } }
}Just like that, with a single click, the class object’s fields are generated.
It accelerates the coding speed and eliminates the risk of typo error.
The Using of Private Fields and Public Properties
Private fields are used to match the column names of MySQL table.
MySQL naming convention specifies that all column names should be in lowercase and keywords should be separated by underscore.
On the other hand, the C# naming convention specifies that any variable exposed outside of Class should be in the form of Public Properties and using Pascal Case.
This is why as the default mode, the class object is generated in the form of combination of private fields and public properties.
Saving Data Into MySQL
Let’s compare the typical traditional way with MySqlExpress way of handling data saving.
INSERT
The typical traditional way:
DateTime date = ConvertInputToDate(txtDateRegister.Text);
decimal price = Convert.ToDecimal(txtPrice.Text);
using (MySqlConnection conn = new MySqlConnection(ConnString))
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = @"insert into book (barcode, title,
author, publisher, date_register, price)
values (@barcode, @title, @author,
@publisher, @date_register, @price);";
cmd.Parameters.AddWithValue("@barcode", txtBarcode.Text);
cmd.Parameters.AddWithValue("@title", txtTitle.Text);
cmd.Parameters.AddWithValue("@author", txtAuthor.Text);
cmd.Parameters.AddWithValue("@publisher", txtPublisher.Text);
cmd.Parameters.AddWithValue("@date_register", date);
cmd.Parameters.AddWithValue("@price", price);
cmd.ExecuteNonQuery();
conn.Close();
}
}Using MySqlExpress
There are 3 ways to perform saving (inserting / updating) data in MySql.
- Save Object
- Insert (by using Dictionary)
- Update (by using Dictionary)
- Insert Update (by using Dictionary and list of columns to be updated)
1. Save Class Objects
A combination of “INSERT” and “UPDATE”. This method will first attempt to perform an INSERT. If the primary key of the data has already existed in MySQL table, then it will perform an UPDATE.
// Syntax
m.Save(tablename, class);
m.SaveList(tablename, List<class>);
// Example:
// Saving single object
m.Save("player", player);
// Saving list of objects
m.SaveList("player", lstPlayer);2. Insert (by using Dictionary)
Open the MySqlExpress Helper program, change the output type to “Generate Dictionary Entries“, then click on the table name.
The C# dictionary entries will be generated, copy the text, and paste it to the following code block with MySqlExpress as shown below.
MySqlExpress runs the operation of INSERT like this:
Dictionary<string, object> dic = new Dictionary<string, object>();
//dic["id"] = // delete this line
dic["barcode"] = txtBarcode.Text;
dic["title"] = txtTitle.Text;
dic["author"] = txtAuthor.Text;
dic["publisher"] = txtPublisher.Text;
dic["date_register"] = ConvertInputToDate(txtDateRegister.Text);
dic["price"] = Convert.ToDecimal(txtPrice.Text);
m.Insert("book", dic);The dictionary entries will be converted into parameters during execution. Since the dictionary entries are generated by the helper program with one click,  this encourages faster code completion and reduce typo errors of field names.
If the MySQL tables has lots of columns (like a lots), the helper program will come in handy and could save a lot of time and effort in typing the field names.
Next,
UPDATE
The typical traditional way of doing UPDATE in C#:
DateTime date = ConvertInputToDate(txtDateRegister.Text);
decimal price = Convert.ToDecimal(txtPrice.Text);
cmd.CommandText = @"update book set barcode=@barcode,
title=@title, author=@author, publisher=@publisher,
date_register=@date_register, price=@price
where id = @id;";
cmd.Parameters.AddWithValue("@barcode", txtBarcode.Text);
cmd.Parameters.AddWithValue("@title", txtTitle.Text);
cmd.Parameters.AddWithValue("@author", txtAuthor.Text);
cmd.Parameters.AddWithValue("@publisher", txtPublisher.Text);
cmd.Parameters.AddWithValue("@date_register", date);
cmd.Parameters.AddWithValue("@price", price);
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();MySqlExpress Update – Single Row (by using Dictionary)
Here’s how MySqlExpress shortens the methods. (The dictionary entries are generated by using the Helper tool):
Dictionary<string, object> dic = new Dictionary<string, object>();
//dic["id"] = // delete this line
dic["barcode"] = txtBarcode.Text;
dic["title"] = txtTitle.Text;
dic["author"] = txtAuthor.Text;
dic["publisher"] = txtPublisher.Text;
dic["date_register"] = ConvertInputToDate(txtDateRegister.Text);
dic["price"] = Convert.ToDecimal(txtPrice.Text);
m.Update("book", dic, "id", id);The dictionary entries will be converted into parameters during execution.
How about updating a row with multiple primary keys?
Let’s find it out.
The typical traditional way:
cmd.CommandText = @"update book set publisher=@publisher,
date_register=@date_register, price=@price
where title=@title and author=@author;";
cmd.Parameters.AddWithValue("@publisher", publisher);
cmd.Parameters.AddWithValue("@date_register", date_register);
cmd.Parameters.AddWithValue("@price", price);
cmd.Parameters.AddWithValue("@title", title);
cmd.Parameters.AddWithValue("@author", author);
cmd.ExecuteNonQuery();MySqlExpress Update – Multi Rows (using Dictionary)
For MySqlExpress, use the Helper program to generate dictionary entries and separate them into 2 groups. First group will be the data, second group will be the condition. For example:
The dictionary that holds the data:
var dic = new Dictionary<string, object>();
dic["publisher"] = txtPublisher.Text;
dic["date_register"] = ConvertInputToDate(txtDateRegister.Text);
dic["price"] = Convert.ToDecimal(txtPrice.Text);The dictionary that holds the conditions:
var dicCond = new Dictionary<string, object>();
dic["title"] = txtTitle.Text;
dic["author"] = txtAuthor.Text;Combining above groups of dictionaries, here is how it works with MySqlExpress execution:
// the data
var dic = new Dictionary<string, object>();
dic["publisher"] = txtPublisher.Text;
dic["date_register"] = ConvertInputToDate(txtDateRegister.Text);
dic["price"] = Convert.ToDecimal(txtPrice.Text);
// the condition
var dicCond = new Dictionary<string, object>();
dic["title"] = txtTitle.Text;
dic["author"] = txtAuthor.Text;
// updating multi rows
m.Update("book", dic, dicCond, false);
// updating single row
m.Update("book", dic, dicCond, true); // or
m.Update("book", dic, dicCond)MySqlExpress Insert Update
Here is an example of SQL syntax that executes INSERT UPDATE:
insert into table1 (col1, col2, col3,col4)
values(data1, data2, data3, data4)
on duplicate key update col3=data3, col4=data4;The SQL is trying to insert a new row into MySQL table, if all the primary keys already exist, it will update the row, so called “INSERT UPDATE”.
Here is an example of table structure that has 2 primary keys, both primary keys are not auto-increment columns.
CREATE TABLE `test11`.`table1` (
`col1` INTEGER UNSIGNED NOT NULL,
`col2` VARCHAR(45) NOT NULL,
`col3` VARCHAR(45),
`col4` VARCHAR(45),
PRIMARY KEY (`col1`, `col2`)
);For coding with MySqlExpress, open the Helper program, first, select the output type as “Generate Dictionary Entries“. This is what you’ll see:
and the generated dictionary entries code:
Dictionary<string, object> dic = new Dictionary<string, object>();
dic["col1"] =
dic["col2"] =
dic["col3"] =
dic["col4"] =Next, select the output type as “Generate Update Column List“, then click on the table name and this will what you’ll see:
and this is the generated code:
List<string> lstUpdateCol = new List<string>();
lstUpdateCol.Add("col3");
lstUpdateCol.Add("col4");Combining these two blocks of code, here is how MySqlExpress performs the operation of “INSERT” and “UPDATE“:
Dictionary<string, object> dic = new Dictionary<string, object>();
dic["col1"] = data1;
dic["col2"] = data2;
dic["col3"] = data3;
dic["col4"] = data4;
List<string> lstUpdateCol = new List<string>();
lstUpdateCol.Add("col3");
lstUpdateCol.Add("col4");
m.InsertUpdate("table1", dic, lstUpdateCol);Generating Customized Class Object For Complex SQL Join Statement
Aside from handling fixed columns from MySQL tables, the Helper program can also generates class object field entries based on customized complex SQL join statement.
Let’s take the following two MySQL tables as example:
CREATE TABLE `member` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`barcode` varchar(45),
`name` varchar(45),
PRIMARY KEY (`id`)
);
CREATE TABLE `member_book` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`date_borrow` datetime,
`date_return` datetime,
`status` int unsigned,
`member_id` int unsigned,
`book_id` int unsigned,
PRIMARY KEY (`id`)
);Let’s have a SQL join statement that joins 3 tables together, which is the tables of “book“, “member” and “member_book“.
select a.*, b.name 'member_name', b.barcode 'member_barcode',
c.title 'book_title', c.barcode 'book_barcode'
from member_book a
inner join member b on a.member_id=b.id
inner join book c on a.book_id=c.id
order by a.date_borrow;Open MySqlExpress Helper program, enters the SQL join statement into the textbox next to the button called “Generate Customized SQL Object“, then click on the same button. The customized fields for C# Class Object is generated.
At C#, creates a new class:
public class MemberBook{}Paste the generated fields into the class:
public class MemberBook
{
int id = 0;
DateTime date_borrow = DateTime.MinValue;
DateTime date_return = DateTime.MinValue;
int status = 0;
int member_id = 0;
int book_id = 0;
string member_name = "";
string member_barcode = "";
string book_title = "";
string book_barcode = "";
public int Id { get { return id; } set { id = value; } }
public DateTime DateBorrow { get { return date_borrow; } set { date_borrow = value; } }
public DateTime DateReturn { get { return date_return; } set { date_return = value; } }
public int Status { get { return status; } set { status = value; } }
public int MemberId { get { return member_id; } set { member_id = value; } }
public int BookId { get { return book_id; } set { book_id = value; } }
public string MemberName { get { return member_name; } set { member_name = value; } }
public string MemberBarcode { get { return member_barcode; } set { member_barcode = value; } }
public string BookTitle { get { return book_title; } set { book_title = value; } }
public string BookBarcode { get { return book_barcode; } set { book_barcode = value; } }
}Then, as usual, here’s the code for getting the data from MySQL with MySqlExpress. Note that the data is now loaded in a C# List, not a single object:
List<MemberBook> lst = null;
lst = m.GetObjectList<MemberBook>(@"select a.*,
b.name 'member_name', b.barcode 'member_barcode',
c.title 'book_title', c.barcode 'book_barcode'
from member_book a
inner join member b on a.member_id=b.id
inner join book c on a.book_id=c.id
order by a.date_borrow;");There are some other minor methods available in MySqlExpress, but nonetheless, all the most significant highlight of methods have already been shown above.
MySqlExpress Helper is available for download at the project site:
https://github.com/adriancs2/MySqlExpress
That’s all for now, cheers and thanks for reading.