To encourage rapid application development with MySQL in C# by converting rows in class objects and saving data by passing class or dictionary.
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:
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; }
}
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();
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;
var fields = typeof(Book).GetFields(BindingFlags.NonPublic |
BindingFlags.Public | BindingFlags.Instance);
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();
foreach (var fieldInfo in fields)
{
foreach (DataColumn dc in dt.Columns)
{
if (fieldInfo.Name == dc.ColumnName)
{
object value = GetValue(dr[dc.ColumnName], fieldInfo.FieldType);
fieldInfo.SetValue(book, value);
break;
}
}
}
foreach (var propertyInfo in properties)
{
if (!propertyInfo.CanWrite)
continue;
foreach (DataColumn dc in dt.Columns)
{
if (propertyInfo.Name == dc.ColumnName)
{
object value = GetValue(dr[dc.ColumnName], propertyInfo.PropertyType);
propertyInfo.SetValue(book, value);
break;
}
}
}
You'll notice there's a special line that is doing the data extraction:
object value = GetValue(dr[dc.ColumnName], fieldInfo.FieldType);
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;
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;
foreach (var kv in dic)
{
if (isFirst)
{
isFirst = false;
}
else
{
sb.Append(",");
}
sb.Append("@");
sb.Append(kv.Key);
}
sb.Append(");");
cmd.CommandText = sb.ToString();
cmd.Parameters.Clear();
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.
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)
{
lstCol.Add(dr[0] + "");
if ((dr["Key"] + "").ToUpper() != "PRI")
{
lstUpdateCol.Add(dr[0] + "");
}
}
Get all the fields and properties of the class:
var fields = typeof(Book).GetFields(BindingFlags.NonPublic |
BindingFlags.Public | BindingFlags.Instance);
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>();
foreach (var col in lstCol)
{
foreach (var field in fields)
{
if (col == field.Name)
{
dic[col] = field.GetValue(book);
break;
}
}
foreach (var prop in properties)
{
if (col == prop.Name)
{
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;
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;
foreach (KeyValuePair<string, object> kv in dic)
{
if (isFirst)
isFirst = false;
else
sb.Append(" , ");
sb.Append("@v");
sb.Append(kv.Key);
}
sb.Append(") on duplicate key update ");
isFirst = true;
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();
cmd.Parameters.Clear();
foreach (KeyValuePair<string, object> kv in dic)
{
cmd.Parameters.AddWithValue("@v" + kv.Key, kv.Value);
}
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);
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
m.Save("book", book);
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
var dic = new Dictionary<string, object>();
dic["publisher"] = txtPublisher.Text;
dic["date_register"] = ConvertInputToDate(txtDateRegister.Text);
dic["price"] = Convert.ToDecimal(txtPrice.Text);
var dicCond = new Dictionary<string, object>();
dic["title"] = txtTitle.Text;
dic["author"] = txtAuthor.Text;
m.Update("book", dic, dicCond)
m.Update("book", dic, dicCond, true);
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
- 8th January, 2023: Initial version (Release of v1.7.2)