最近在做读取日志的小项目,要从日志当中读取很多的信息插入到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;
}