目录
介绍
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智能感知的优势:
// 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将列与数据匹配。例如:
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自动化的基本思想。
使用代码
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;");
保存(插入 + 更新)类对象
// Saving single object
m.Save("book", book);
// Saving list of objects
m.SaveList("book", lstBook);
使用字典保存(仅插入)
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<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);
使用字典保存(仅更新)——多个主键
// 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 Helper
帮助程序应用是MySqlExpress项目的一部分。它将从MySQL表生成类。它加载特定表的列,并将它们转换为C#类字段和属性。因此,它可以帮助开发人员轻松创建C#类。
它还可以根据列生成DICTIONARY条目。
有关如何使用MySqlExpress帮助程序的详细信息,您可以阅读[本文]和[本文]。
https://www.codeproject.com/Articles/5351510/MySqlExpress-Convert-Rows-From-MySQL-into-Class-Ob