Winform/Csharp中连接Mysql并执行指定sql,查询结果通过反射映射为对象的List

悠悠 2023-09-30 20:55 10阅读 0赞

## 场景 ##

Winform中连接Mysql8并查询表中数据进行显示：

[Winform中连接Mysql8并查询表中数据进行显示\_winform连接mysql\_霸道流氓气质的博客-CSDN博客][Winform_Mysql8_winform_mysql_-CSDN]

在上面连接Mysql的基础上，如果需要连接Mysql并执行指定的sql语句，并且将

查询结果转换为对象的list数据。

sql语句执行的查询结果

![7b6c7041c52c49d29308ce9b0f879b46.png][]

查询并映射后的结果

![9451859c63b24cde9bc4280c8eea0942.png][]

注：

博客：  
[霸道流氓气质的博客\_CSDN博客-C\#,架构之路,SpringBoot领域博主][CSDN_-C_SpringBoot]

## 实现 ##

1、代码比较简单，直接附代码

private void button3_Click(object sender, EventArgs e)
            {
                connetStr = "server= 127.0.0.1;UserId=root;password=123;database=test"; 
                mySqlConnection = new MySqlConnection(connetStr);
                try
                {
                    mySqlConnection.Open(); //连接数据库
                    MessageBox.Show("数据库连接成功", "提示", MessageBoxButtons.OK);
                    string searchStr = "SELECT mail.car_number,mail.driver_number ,driver.name as driver_name FROM bus_mail_list mail LEFT JOIN bus_driver_info driver on mail.driver_number = driver.account_number";
                    DataTable dataTable = getMySqlRead(searchStr);
                    mailList = DataTableToList<BusMailList>(dataTable);
                    mailList.ForEach(mail =>
                    {
                        Console.WriteLine(mail.driver_name + mail.driver_number);
                    });
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK);     //显示错误信息
                }         
            }

2、其中连接数据的字符串中配置ip、用户名密码等信息。

需要提前声明一些变量

List<BusMailList> mailList = new List<BusMailList>();
    
            string connetStr = String.Empty;
    
            MySqlConnection mySqlConnection = null;

然后新建要反射后的对象

public class BusMailList
        {
            public long binding_time { get; set; }
            public string car_number { get; set; }
            public string driver_name { get; set; }
            public string driver_number { get; set; }
            public int id { get; set; }
            public long update_time { get; set; }
    
        }

注意这里要与数据库查询结果字段对应一致。

查询结果只需要三个字段，实体对象可以有多个属性，但是实体对象的三个属性需要与查询结果的字段一致。

然后注意的是，这里实体对象没有重写构造方法，如果重写了构造方法的话，必须再声明无参的构造方法。

举个其他实体对象的例子

class CarInfoVo
        {
            public string car_Num { get; set; }
            public string car_Type { get; set; }
    
            public CarInfoVo()
            {
    
            }
    
            public CarInfoVo(string carNum, string carType)
            {
                car_Num = carNum;
                car_Type = carType;
            }
        }

其中用到的两个方法是创建MysqlDataReader对象的getMySqlRead方法

public DataTable getMySqlRead(string M_str_sqlstr)
            {  
                MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mySqlConnection);
                MySqlDataAdapter mda = new MySqlDataAdapter(mysqlcom);
                DataTable dt = new DataTable();
                mda.Fill(dt);
                mySqlConnection.Close();
                return dt;
            }

另一个是利用反射将DataTabel转换为对象list的DataTableToList

public List<T> DataTableToList<T>(DataTable dt)
            {
                //确认参数有效
                if (dt == null)
                    return null;
    
                List<T> list = new List<T>();
    
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //创建泛型对象
                    T _t = Activator.CreateInstance<T>();
                    //获取对象所有属性
                    PropertyInfo[] propertyInfo = _t.GetType().GetProperties();
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        foreach (PropertyInfo info in propertyInfo)
                        {
                            //属性名称和列名相同时赋值
                            if (dt.Columns[j].ColumnName.ToUpper().Equals(info.Name.ToUpper()))
                            {
                                if (dt.Rows[i][j] != DBNull.Value)
                                {
                                    info.SetValue(_t, dt.Rows[i][j], null);
                                }
                                else
                                {
                                    info.SetValue(_t, null, null);
                                }
                                break;
                            }
                        }
                    }
                    list.Add(_t);
                }
                return list;
         }

[Winform_Mysql8_winform_mysql_-CSDN]: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/120395988
[7b6c7041c52c49d29308ce9b0f879b46.png]: https://img-blog.csdnimg.cn/7b6c7041c52c49d29308ce9b0f879b46.png
[9451859c63b24cde9bc4280c8eea0942.png]: https://img-blog.csdnimg.cn/9451859c63b24cde9bc4280c8eea0942.png
[CSDN_-C_SpringBoot]: https://blog.csdn.net/badao_liumang_qizhi