MySqlExpress - An Easier Way To Use MySQL In C#

This article explains how MySqlExpress can save programmers time and effort in 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 read about their documentation at https://mysqlconnector.net

Before explaining what is MySqlExpress, let's take a look at the typical traditional way of handling MySQL in C#.

Here's an 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;

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 repeating typing just to map the data to the class object, or whatever fields. All fields are needed to type one by one. This 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.

In MySqlExpress, it comes with a tool called "MySqlExpress Helper", a 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:

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);
        DateTime date = ConvertInputToDate(txtDateRegister.Text);
        cmd.Parameters.AddWithValue("@date_register", date);
        decimal price = Convert.ToDecimal(txtPrice.Text);
        cmd.Parameters.AddWithValue("@price", price);

        cmd.ExecuteNonQuery();

        conn.Close();
    }
}

Using MySqlExpress

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);

using (MySqlConnection conn = new MySqlConnection(ConnString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        MySqlExpress m = new MySqlExpress(cmd);

        m.Insert("book", dic);

        conn.Close();
    }
}

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#:

using (MySqlConnection conn = new MySqlConnection(ConnString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        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);

        DateTime date = ConvertInputToDate(txtDateRegister.Text);
        cmd.Parameters.AddWithValue("@date_register", date);

        decimal price = Convert.ToDecimal(txtPrice.Text);
        cmd.Parameters.AddWithValue("@price", price);

        cmd.Parameters.AddWithValue("@id", id);
        cmd.ExecuteNonQuery();
        conn.Close();
    }
}

Here's how MySqlExpress shortens the methods (again 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);

using (MySqlConnection conn = new MySqlConnection(ConnString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        MySqlExpress m = new MySqlExpress(cmd);
        m.Update("book", dic, "id", id);

        conn.Close();
    }
}

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:

using (MySqlConnection conn = new MySqlConnection(ConnString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        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();

        conn.Close();
    }
}

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;

using (MySqlConnection conn = new MySqlConnection(ConnString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();
        MySqlExpress m = new MySqlExpress(cmd);
        m.Update("book", dic, dicCond);
        conn.Close();
    }
}

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:

string sql = @"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;";

List<MemberBook> lst = null;

using (MySqlConnection conn = new MySqlConnection(ConnString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        MySqlExpress m = new MySqlExpress(cmd);
        lst = m.GetObjectList<MemberBook>(sql);

        conn.Close();
    }
}

Performing 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");

using (MySqlConnection conn = new MySqlConnection(ConnString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        MySqlExpress m = new MySqlExpress(cmd);
        m.InsertUpdate("table1", dic, lstUpdateCol);

        conn.Close();
    }
}

There are some other minor methods available in MySqlExpress, but nonetheless, all the most significant highlight of methods have already been shown above.

You may visit the project site of MySqlExpress (Github) or download the source code attached with this article for the demo.

The Helper program is available at both the attached source code of this article and the official project site.

That's all for now, thanks for reading.

Happy Coding.

Official Project Site: Github