MySql的批量添加实现

来源：互联网发布：二阶滤波算法编辑：程序博客网时间：2024/05/17 06:32

最近在做读取日志的小项目，要从日志当中读取很多的信息插入到MySql中，这就涉及到批量添加的问题，现贴出代码实现过程供大家参考，同时供自己以后工作使用。

public bool AddToDataBase(Hashtable ht)
        {
            bool flag = false;
            //数据库连接
            MySqlConnection con = new DBCon().GetConn();
            con.Open();
            try
            {
                string sql = "";
                //声明List集合存储所有要插入的sql
                ArrayList sqlList = new ArrayList();
                foreach (DictionaryEntry h in ht)
                {
                    string[] arr = (string[])h.Value;
                    string time = arr[4];
                    DateTime dt = DateTime.Now;
                    if (time.Length == 16)
                    {
                         arr[3] = arr[3].Replace("'", @"\'");
                         dt = DateTime.ParseExact(time, "yyyy:MM:dd HH:mm", null);
                         sql = "insert into mail_info(From_email,To_email,Error_code,Error_msg,Error_time) values('" +
                         arr[0] + "','" + arr[1] + "','" + arr[2] + "','" + arr[3] + "','" + dt + "')";
                         sqlList.Add(sql);
                    }
                    else
                    {
                        continue;
                    }

                }

                MySqlCommand cmd = new MySqlCommand("set names gb2312", con);
                cmd.Connection = con;
                cmd.ExecuteNonQuery();
                MySqlTransaction tx = con.BeginTransaction();
                cmd.Transaction = tx;
                for (int i = 0; i < sqlList.Count; i++)
                {
                    try
                    {
                        string strsql = sqlList[i].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                        if (i == (sqlList.Count - 1))
                        {
                            //提交所做的Sql操作
                            tx.Commit();
                            flag = true;
                        }
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                //关闭数据库连接
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }

return flag;
}