.net 连接mysql数据库方法_net8发布docker,调试怎么连接主机上的mysql数据库-CSDN博客

本文链接：https://blog.csdn.net/weixin_46867655/article/details/116044892

这篇博客介绍了如何使用.NET连接到MySQL数据库并执行数据上传操作。代码示例展示了如何建立数据库连接，开启事务，执行SQL删除和插入语句，以及处理错误和事务提交或回滚。此过程特别适用于需要批量插入大量数据的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言

.net 连接mysql数据库方法

 //同步mysql数据库
        public void UpLoadMySqlDB(string TBName, DataTable dt, String companyNo)
        {
            #region 连接MySql
            String connetStr = "Max Pool Size = 512;server=xxx.xxx.xxx.xxx;port=3308;user=root;password=xxx; database=xxx;Charset=utf8;";
            SCommBB commBB = new SCommBB();
            DV_ErrorDiaryBB errorDiaryBB = new DV_ErrorDiaryBB(commBB.Connection);
            MySqlConnection mysqlconn = new MySqlConnection(connetStr);
            try
            {
                mysqlconn.Open();//成功連接 
            }
            catch (Exception e)
            {
                errorDiaryBB.AddRecord(0, "DataCenterUploadData(For MySQL)", "数据库连接失败" + e.Message + e.StackTrace);
            }
            MySqlTransaction transaction = mysqlconn.BeginTransaction();
            MySqlCommand cmd = mysqlconn.CreateCommand();
            cmd.CommandTimeout = 3600;
            cmd.Transaction = transaction;

            string sql = string.Empty;
            #endregion

            try
            {
                #region 上传AP_VC_VaccineStock 疫苗及时库存
                if ("AAAA".Equals(TBName))
                {
                    //先删除
                    sql = "delete   from  gdm_stock";
                    cmd = new MySqlCommand(sql, mysqlconn);
                    cmd.ExecuteNonQuery();
                    //看看有没有数据
                    sql = "select count(*) from gdm_stock";
                    cmd = new MySqlCommand(sql, mysqlconn);
                    int ExistCount = 0;

                    Object result = cmd.ExecuteScalar();
                    if (result != null)
                    {
                        ExistCount = int.Parse(result.ToString());
                    }
                    if (ExistCount == 0)
                    {
                        var sqlbuilder = new StringBuilder();
                        sqlbuilder.Append("insert into gdm_stock(created_time,updated_time,icebox_name,icebox_no,vaccin_code,manufacturer_code,batch_no,validity_date," +
                            "store_num,dose,category,hospital_code,is_delete,code) values ");
                        int index = 0;
                        foreach (DataRow dr in dt.Rows)
                        {

                            DateTime? validityDt = new DateTime();
                            if (dr["validityDt"] != null && !string.IsNullOrEmpty(dr["validityDt"].ToString()))
                            {
                                validityDt = Convert.ToDateTime(dr["validityDt"]);
                            }

                            sqlbuilder.AppendFormat("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}'),",
                                Convert.ToDateTime(dr["inStockDt"]), DateTime.Now, dr["iceBoxNm"].ToString(), dr["iceBoxNo"].ToString(), dr["vaccineModelNo"].ToString()
                                , dr["supplyerNo"].ToString(), dr["batchNo"].ToString(), validityDt, Convert.ToInt32(dr["amount"]), dr["spec"].ToString(),
                                Convert.ToInt32(dr["vaccineType"]), dr["companyNo"].ToString(), Convert.ToInt32(dr["isDel"]), dr["vaccineBarcode"].ToString());
                            index = index + 1;
                            if (index == 1000)//如果超过1000，则执行一次，数据多会超时
                            {
                                sql = sqlbuilder.ToString();
                                sql = sql.Substring(0, sql.Length - 1);
                                cmd = new MySqlCommand(sql, mysqlconn);
                                cmd.CommandTimeout = 3600;
                                if (cmd.ExecuteNonQuery() > 0)
                                {
                                    index = 0;
                                    sqlbuilder = new StringBuilder();
                                    sqlbuilder.Length = 0;
                                    sqlbuilder.Append("insert into gdm_stock(created_time,updated_time,icebox_name,icebox_no,vaccin_code,manufacturer_code,batch_no,validity_date," +
                           "store_num,dose,category,hospital_code,is_delete,code) values ");
                                }
                            }
                        }
                        if (index > 0)
                        {
                            sql = sqlbuilder.ToString();
                            sql = sql.Substring(0, sql.Length - 1);
                            cmd = new MySqlCommand(sql, mysqlconn);

                            if (cmd.ExecuteNonQuery() > 0)
                            {
                                transaction.Commit();
                            }
                        }
                        else
                        {
                            transaction.Commit();
                        }

                    }
                    else
                    {
                        transaction.Rollback();
                    }
                }
                #endregion

                mysqlconn.Close();//关闭
            }
            catch (MySqlException e)
            {
                errorDiaryBB.AddRecord(0, TBName + "更新出错！", "UploadData Error." + e.Message + e.StackTrace);
                transaction.Rollback();
            }
            finally
            {
                mysqlconn.Close();
            }
        }