MySqlExpress - Convert Rows from MySQL into Class Objects

• Download MySqlExpress.v1.7.2
• Github
• Nuget (MySqlConnector)
• Nuget (MySql.Data)

Introduction

MySqlExpress converts rows from MySql tables into class object automatically.

The purpose of existence of this class library is to bring an enchancement, an improvement on top the traditional way of handling MySQL data in C#.

Background

Let's take a look at a typical traditional way of how MySQL is handled in C#.

Here’s a 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;

Below is one of the primitive ways to get the data: by loading the data into DataTable:

using MySqlConnector;

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

Then, the DataTable will either directly be used to fill application form input:

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

Or load to a class object (data mapping) for reuse purpose and to enjoy the benefits of IDE IntelliSense while getting the data field:

// creates a class
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; }
}

// declare a book object
Book book = new Book();

// fill data from DataTable
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();

At some point, developers will soon realize a problem of this approach. It requires a lot of manual typing of the field names (or column name). It's repetitive and tedius typing. It can easily introduce human typing error.

Therefore, the idea of auto-mapping will soon arise.

The conversion work starts from retrieving the fields and properties of class object.

In C#, we can use System.Reflection to get it:

using System.Reflection;

// get all fields
var fields = typeof(Book).GetFields(BindingFlags.NonPublic |
               BindingFlags.Public | BindingFlags.Instance);

// get all properties
var properties = typeof(Book).GetProperties(BindingFlags.NonPublic |
               BindingFlags.Public | BindingFlags.Instance);

Then, we can loop through all fields, properties and DataColumn in DataTable to match the names of field/properties/column. Once they are matched, the data can be filled into that particular field/property in the Class.

DataTable dt = GetBookFromMySql();
DataRow dr = dt.Rows[0];

Book book = new Book();

// loop through all the fields
foreach (var fieldInfo in fields)
{
    foreach (DataColumn dc in dt.Columns)
    {
        // field name matches with column name (MySQL)
        if (fieldInfo.Name == dc.ColumnName)
        {
            // extract value from MySql
            object value = GetValue(dr[dc.ColumnName], fieldInfo.FieldType);

            // fill data (map data)
            fieldInfo.SetValue(book, value);

            break;
        }
    }
}

foreach (var propertyInfo in properties)
{
    // skip property that cannot be written
    if (!propertyInfo.CanWrite)
        continue;

    foreach (DataColumn dc in dt.Columns)
    {
        // field name matches with column name (MySQL)
        if (propertyInfo.Name == dc.ColumnName)
        {
            // extract value from MySql
            object value = GetValue(dr[dc.ColumnName], propertyInfo.PropertyType);

            // fill data (map data)
            propertyInfo.SetValue(book, value);

            break;
        }
    }
}

You'll notice there's a special line that is doing the data extraction:

// for field
object value = GetValue(dr[dc.ColumnName], fieldInfo.FieldType);

// for property
object value = GetValue(dr[dc.ColumnName], propertyInfo.PropertyType);

There are cases that the data returned from MySQL might not be fully compatible with the destination data type of class field or property. For example, NULL value, it will cause an exception.

Below are the extra steps to serve as a filter:

static object GetValue(object ob, Type t)
{
    if (t == typeof(string))
    {
        return ob + "";
    }
    else if (t == typeof(bool))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return false;
        return Convert.ToBoolean(ob);
    }
    else if (t == typeof(byte))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return 0;
        return Convert.ToByte(ob);
    }
    else if (t == typeof(sbyte))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return 0;
        return Convert.ToSByte(ob);
    }
    else if (t == typeof(short))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return 0;
        return Convert.ToInt16(ob);
    }
    else if (t == typeof(ushort))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return 0;
        return Convert.ToUInt16(ob);
    }
    else if (t == typeof(int))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return 0;
        return Convert.ToInt32(ob);
    }
    else if (t == typeof(uint))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return 0;
        return Convert.ToUInt32(ob);
    }
    else if (t == typeof(long))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return 0L;
        return Convert.ToInt64(ob);
    }
    else if (t == typeof(ulong))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return 0L;
        return Convert.ToUInt64(ob);
    }
    else if (t == typeof(float))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return 0F;
        return Convert.ToSingle(ob);
    }
    else if (t == typeof(double))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return 0D;
        return Convert.ToDouble(ob, CultureInfo.InvariantCulture);
    }
    else if (t == typeof(decimal))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return 0m;
        return Convert.ToDecimal(ob, CultureInfo.InvariantCulture);
    }
    else if (t == typeof(char))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return Convert.ToChar("");
        return Convert.ToChar(ob);
    }
    else if (t == typeof(DateTime))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return DateTime.MinValue;
        return Convert.ToDateTime(ob, CultureInfo.InvariantCulture);
    }
    else if (t == typeof(byte[]))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return null;

        return (byte[])ob;
    }
    else if (t == typeof(Guid))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return null;

        return (Guid)ob;
    }
    else if (t == typeof(TimeSpan))
    {
        if (ob == null || ob.GetType() == typeof(DBNull))
            return null;

        return (TimeSpan)ob;
    }

    return Convert.ChangeType(ob, t);
}

All the above demonstrated the basic idea of how the data conversion (data mapping) works.

How about automates the INSERT?

For, INSERT, we can use a DICTIONARY to match the columns with data. For example:

Dictionary<string, object> dic = new Dictionary<string, object>();

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

Perform a loop on the DICTIONARY to build the SQL statement:

StringBuilder sb = new StringBuilder();

sb.Append("insert into `");
sb.Append(tableName);
sb.Append("` (");

bool isFirst = true;

// build the columns string
foreach(var kv in dic)
{
    if (isFirst)
    {
        isFirst = false;
    }
    else
    {
        sb.Append(",");
    }

    sb.Append("`");
    sb.Append(kv.Key);
    sb.Append("`");
}

sb.Append(") values(");

isFirst = true;

// building values string
foreach (var kv in dic)
{
    if (isFirst)
    {
        isFirst = false;
    }
    else
    {
        sb.Append(",");
    }

    sb.Append("@");
    sb.Append(kv.Key);
}

sb.Append(");");

cmd.CommandText = sb.ToString();

// remove all parameters
cmd.Parameters.Clear();

// adding parameters
foreach (var kv in dic)
{
    cmd.Parameters.AddWithValue($"@{kv.Key}", kv.Value);
}

cmd.ExecuteNonQuery();

This creates an INSERT statement.

How about automates the INSERT and UPDATE (2-in-1) operations?

Let's try to do it.

// get columns details of the table
DataTable dt = Select($"show columns from `{table}`;");

List<string> lstCol = new List<string>();
List<string> lstUpdateCol = new List<string>();

foreach (DataRow dr in dt.Rows)
{
    // collecting all column names
    lstCol.Add(dr[0] + "");

    // collecting all NON-Primary Key column names
    if ((dr["Key"] + "").ToUpper() != "PRI")
    {
        lstUpdateCol.Add(dr[0] + "");
    }
}

Get all the fields and properties of the class:

// get all fields
var fields = typeof(Book).GetFields(BindingFlags.NonPublic |
               BindingFlags.Public | BindingFlags.Instance);

// get all properties
var properties = typeof(Book).GetProperties(BindingFlags.NonPublic |
               BindingFlags.Public | BindingFlags.Instance);

Build the DICTIONARY for data collection:

Dictionary<string, object> dic = new Dictionary<string, object>();

// loop through all column names
foreach (var col in lstCol)
{
    // loop through all fields
    foreach (var field in fields)
    {
        // column name matches with field name
        if (col == field.Name)
        {
            // fill data into dictionary
            dic[col] = field.GetValue(book);
            break;
        }
    }

    // loop through all properties
    foreach (var prop in properties)
    {
        // column name matches with property name
        if (col == prop.Name)
        {
            // fill data into dictionary
            dic[col] = prop.GetValue(book);
            break;
        }
    }
}

Build the SQL statement:

StringBuilder sb = new StringBuilder();

sb.Append("insert into `");
sb.Append(table);
sb.Append("`(");

bool isFirst = true;

// build the columns string
foreach (KeyValuePair<string, object> kv in dic)
{
    if (isFirst)
        isFirst = false;
    else
        sb.Append(",");

    sb.Append("`");
    sb.Append(kv.Key);
    sb.Append("`");
}

sb.Append(") values(");

isFirst = true;

// build the values string
foreach (KeyValuePair<string, object> kv in dic)
{
    if (isFirst)
        isFirst = false;
    else
        sb.Append(" , ");

    sb.Append("@v");
    sb.Append(kv.Key);
}

// build the update statement
sb.Append(") on duplicate key update ");

isFirst = true;

// match the data with all non-primary key columns
foreach (string key in lstUpdateCols)
{
    if (isFirst)
        isFirst = false;
    else
        sb.Append(",");

    sb.Append("`");
    sb.Append(key);
    sb.Append("`=@v");
    sb.Append(key);
}

sb.Append(";");

cmd.CommandText = sb.ToString();

// remove parameters
cmd.Parameters.Clear();

// adding parameters
foreach (KeyValuePair<string, object> kv in dic)
{
    cmd.Parameters.AddWithValue("@v" + kv.Key, kv.Value);
}

// execute the INSERT UPDATE statement
cmd.ExecuteNonQuery();

This demonstrates the basic idea of how INSERT + UPDATE are being automated.

Using the Code

MySqlExpress is built based on the above idea.

Below introduces some of the pre-built methods that perform the automation of SELECT, INSERT, UPDATE.

Declare a standard code block for using MySqlExpress:

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

        MySqlExpress m = new MySqlExpress(cmd);

        // starts here

        conn.Close();
    }
}

To Get Single Row (Object)

var dicParam = new Dictionary<string, object>();
dicParam["@id"] = 1;
string sql = "select * from book where id=@id";
Book book = m.GetObject<Book>(sql, dicParam);

To Get Multiple Rows (List of Objects)

List<book> lst = m.GetObjectList<Book>("select * from book;");

To Save (INSERT + UPDATE) a Class Object

// Saving single object
m.Save("book", book);

// Saving list of objects
m.SaveList("book", lstBook);

To Save (INSERT only) with DICTIONARY

Dictionary<string, object> dic = new Dictionary<string, object>();

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

To Save (UPDATE only) with DICTIONARY - Single Primary Key

Dictionary<string, object> dic = new Dictionary<string, object>();

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

To Save (UPDATE only) with DICTIONARY - Multiple Primary Key

// 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 single row
m.Update("book", dic, dicCond)
m.Update("book", dic, dicCond, true);

// updating multi rows
m.Update("book", dic, dicCond, false);

To Delete or Execute Any SQL Statement

m.Execute("delete from book where id=1");
m.Execute("update book set status=1;");

To Get the Data from First Row and First Column

string name = m.ExecuteScalar<string>("select title from book where id=1;");
int total = m.ExecuteScalar<int>("select count(id) from book;");

To Execute Query With Parameters

var dicParam = new Dictionary<string, object>();
dicParam["@id"] = 1;

m.Execute("delete from book where id=@id;", dicParam);
string name = m.ExecuteScalar<string>("select title from book where id=@id;", dicParam);

MySqlExpress Helper

The helper app is part of the MySqlExpress project. It will generate the class from MySQL table. It loads the columns of specific table and converts them into C# class fields and properties. Thus, it helps developers to create C# class at ease.

It can also generate the DICTIONARY entries based on the columns.

For info on the details of how the helper can be worked with MySqlExpress, you may read [this article] and [this article].

Okay, that's all for this article. Cheers guys! Happy coding!

History

• 8^th January, 2023: Initial version (Release of v1.7.2)