MySqlExpress - 将 MySQL 的行转换为类对象

adriancs

4.64/5 (6投票s)

2023年1月8日

CPOL

3分钟阅读

10632

168

将 MySQL 行转换为 C# 类对象。通过传递类或字典执行 INSERT/UPDATE

引言

MySqlExpress 自动将 MySql 表中的行转换为类对象。

这个类库存在的目的是为了增强和改进 C# 中处理 MySQL 数据的传统方式。

背景

让我们看一下 C# 中处理 MySQL 数据的典型传统方法。

这是一个常见的 MySQL 表的示例

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;

以下是一种获取数据的原始方法：将数据加载到 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();
    }
}

然后，DataTable 将直接用于填充应用程序表单输入

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

或者加载到类对象（数据映射）中，以便重用并享受 IDE IntelliSense 在获取数据字段时的优势

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

然后，该类可以这样重用

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

在某些时候，开发人员会很快意识到这种方法的问题。它需要手动输入大量的字段名称（或列名称）。这是重复且繁琐的输入。很容易引入人为的输入错误。

因此，自动映射的想法很快就会出现。

转换工作从检索类对象的字段和属性开始。

在 C# 中，我们可以使用 System.Reflection 来获取它

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

然后，我们可以循环遍历 DataTable 中的所有字段、属性和 DataColumn，以匹配字段/属性/列的名称。一旦它们匹配，数据就可以填充到类中的特定字段/属性中。

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

你会注意到有一条特殊的行正在进行数据提取

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

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

在某些情况下，从 MySQL 返回的数据可能与类字段或属性的目标数据类型不完全兼容。例如，NULL 值，它会导致异常。

以下是作为过滤器的额外步骤

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

以上所有内容都演示了数据转换（数据映射）如何工作的基本思想。

如何自动化 INSERT？

对于 INSERT，我们可以使用 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);

对 DICTIONARY 执行循环以构建 SQL 语句

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

这将创建一个 INSERT 语句。

如何自动化 INSERT 和 UPDATE（二合一）操作？

让我们尝试一下。

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

构建用于数据收集的 DICTIONARY

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

构建 SQL 语句

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

这演示了 INSERT + UPDATE 如何自动化的基本思想。

Using the Code

MySqlExpress 是基于上述想法构建的。

下面介绍了一些预构建的方法，这些方法可以自动执行 SELECT、INSERT、UPDATE。

声明一个使用 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();
    }
}

获取单行（对象）

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

获取多行（对象列表）

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

保存（INSERT + UPDATE）一个类对象

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

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

使用 DICTIONARY 保存（仅 INSERT）

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

使用 DICTIONARY 保存（仅 UPDATE）- 单个主键

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

使用 DICTIONARY 保存（仅 UPDATE）- 多个主键

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

删除或执行任何 SQL 语句

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

从第一行和第一列获取数据

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

执行带参数的查询

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 助手

助手应用程序是 MySqlExpress 项目的一部分。它将从 MySQL 表生成类。它加载特定表的列，并将它们转换为 C# 类字段和属性。因此，它可以帮助开发人员轻松创建 C# 类。

它还可以基于列生成 DICTIONARY 条目。

有关助手如何与 MySqlExpress 一起使用的详细信息，您可以阅读[这篇文章]和[这篇文章]。

好了，这篇文章就到此为止。祝大家愉快！编码愉快！

历史

2023 年 1 月 8 日：初始版本（发布 v1.7.2）