C#连接MySQL数据库实现DataGridView定时更新数据——多结果集返回

本文讨论的主要问题是C#连接MySQL数据库实现 select 多结果集返回，并将结果用DataGridView定时更新。

一、实现中遇到的问题

1、刷新DataGridView显示时出错或者出现大红叉。

2、未将对象引用设置添加到对象的实例。

出现上述问题的可能原因：

1、未能正确使用委托。

2、由于select查询语句过多，导致连接关闭数据库次数频繁。

主要难点：

1、Mysql返回多结果集java和Php的较多，C#的完整代码较少。

2、MySQL中定义的表表之间的关联性比较弱，表间只有sensorId保持一致。

二、解决办法

1、思路来源于：帖子<寻求C#中一次执行多条sql语句的方法代码>http://bbs.csdn.net/topics/340048345

2、实现来源于：文章《C# 数据表(Dataset)操作 合并 查询一箩筐》http://www.cnblogs.com/xiaofengfeng/archive/2013/02/01/2889354.html

三、实现代码

        private void getdatafrommysql(ref DataTable datatable,string VesselID, int Multifunction_ID, int ethy_ID)
        {
            string[] str=new string[5]{"123","234","345","456","567"};

            DateTime latesttime_Multifunction_ID = DateTime.Now;
            DateTime latesttime_ethy_ID = DateTime.Now;
        
            MySqlConnection mysqlcon = null;
            try
            {
                mysqlcon = this.getmysqlcon();//连接对象
                mysqlcon.Open();

                string sql_str = "SELECT * FROM temperature where sensorId=" + Multifunction_ID.ToString() + " order by id desc limit 1,1;"
                 + "SELECT * FROM carbondioxide where sensorId=" + Multifunction_ID.ToString() + " order by id desc limit 1,1;"
                 + "SELECT * FROM humidity where sensorId=" + Multifunction_ID.ToString() + " order by id desc limit 1,1;"
                 + "SELECT * FROM iiuminance where sensorId=" + Multifunction_ID.ToString() + " order by id desc limit 1,1;"
                 + "SELECT * FROM ethylene where sensorId=" + ethy_ID.ToString() + " order by id desc limit 1,1;";

                MySqlCommand mysqlcom = new MySqlCommand(sql_str, mysqlcon);
                mysqlcom.CommandType = CommandType.Text;

                MySqlDataReader mysqlread = mysqlcom.ExecuteReader(CommandBehavior.CloseConnection);

                if (mysqlread.HasRows)
                {
                    while (mysqlread.Read())
                    {
                        str[0] = mysqlread["sensorValue"].ToString();
                        latesttime_Multifunction_ID = Convert.ToDateTime(mysqlread["utcTime"]);
                    }
                    while (mysqlread.NextResult())
                    {
                        while (mysqlread.Read())
                        {
                            str[1] = mysqlread["sensorValue"].ToString();
                        }
                        while (mysqlread.NextResult())
                        {
                            while (mysqlread.Read())
                            {
                                str[2] = mysqlread["sensorValue"].ToString();
                            }
                            while (mysqlread.NextResult())
                            {
                                while (mysqlread.Read())
                                {
                                    str[3] = mysqlread["sensorValue"].ToString();
                                }
                                while (mysqlread.NextResult())
                                {
                                    while (mysqlread.Read())
                                    {
                                        str[4] = mysqlread["sensorValue"].ToString();
                                        latesttime_ethy_ID = Convert.ToDateTime(mysqlread["utcTime"]);
                                    }
                                }
                            }
                        }
                    }
                }

                datatable.Rows.Add(VesselID, Multifunction_ID, "多功能", str[0], str[1], str[2], str[3], "----", latesttime_Multifunction_ID);
                datatable.Rows.Add(VesselID, ethy_ID, "乙烯", "----", "----", "----", "----", str[4], latesttime_ethy_ID);

                mysqlcon.Close();
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }
            finally
            {
                if (mysqlcon.State == ConnectionState.Open)
                    mysqlcon.Close();
            }  
        }

四、主要参考文章有：

1、C#操作SQL Server数据库

http://www.cnblogs.com/rainman/archive/2012/03/13/2393975.html

2、C# 数据表(Dataset)操作 合并 查询一箩筐

http://www.cnblogs.com/xiaofengfeng/archive/2013/02/01/2889354.html

3、c#实现每隔一段时间执行代码（多线程）

http://www.cnblogs.com/tianzhiliang/archive/2010/08/31/1813928.html

4、如何实现DataGridView实时更新数据

http://bbs.bccn.net/thread-341646-1-1.html

5、 用多活动结果集优化ADO.NET2.0数据连接(转) 

http://blog.itpub.net/9281334/viewspace-952961/