最近需要从Oracle库中将大量(百万级)的数据用程序导入到mysql中程序每次跑到一百万多一点就出现这样的异常,不知道如何解决.主要代码很简单,请大家帮忙看看,如何解决,谢谢.
public void Oracle2Mysql()
{
GetConn();
GetMySqlConn();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "select s.email,s.createdate,s.iforder,s.updatedate from s_subscriber s"; conn.Open();
mysqlconn.Open(); OracleDataAdapter oracleDAP = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
oracleDAP.Fill(ds, "table");
conn.Close();
try
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
string EmailSupplier = GetEmailSupplier(dr["email"].ToString());
string EmailSupplierCode = GetEmailSupplierCode(EmailSupplier.ToString()); ArrayList para = new ArrayList();
para.Add(dr["email"].ToString());
para.Add(EmailSupplierCode.ToString());
para.Add(dr["iforder"].ToString());
para.Add(dr["createdate"].ToString());
para.Add(dr["updatedate"].ToString());
InputData(para);
Console.WriteLine("Email:{0}", para[0]);
Console.WriteLine("EmailSupplierCode:{0}", EmailSupplierCode.ToString());
Console.WriteLine("IfOrder:{0}", para[2]);
Console.WriteLine("CreateDate:{0}", para[3]);
Console.WriteLine("UpdateDate:{0}", para[4]);
Console.WriteLine("*************"); }
ds.Clear();
ds.Dispose();
mysqlconn.Close();
Console.WriteLine("Complete");
}
catch (Exception ex)
{
Log(ex.ToString());
Console.WriteLine(ex.Message);
} }
public void Oracle2Mysql()
{
GetConn();
GetMySqlConn();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "select s.email,s.createdate,s.iforder,s.updatedate from s_subscriber s"; conn.Open();
mysqlconn.Open(); OracleDataAdapter oracleDAP = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
oracleDAP.Fill(ds, "table");
conn.Close();
try
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
string EmailSupplier = GetEmailSupplier(dr["email"].ToString());
string EmailSupplierCode = GetEmailSupplierCode(EmailSupplier.ToString()); ArrayList para = new ArrayList();
para.Add(dr["email"].ToString());
para.Add(EmailSupplierCode.ToString());
para.Add(dr["iforder"].ToString());
para.Add(dr["createdate"].ToString());
para.Add(dr["updatedate"].ToString());
InputData(para);
Console.WriteLine("Email:{0}", para[0]);
Console.WriteLine("EmailSupplierCode:{0}", EmailSupplierCode.ToString());
Console.WriteLine("IfOrder:{0}", para[2]);
Console.WriteLine("CreateDate:{0}", para[3]);
Console.WriteLine("UpdateDate:{0}", para[4]);
Console.WriteLine("*************"); }
ds.Clear();
ds.Dispose();
mysqlconn.Close();
Console.WriteLine("Complete");
}
catch (Exception ex)
{
Log(ex.ToString());
Console.WriteLine(ex.Message);
} }
,这个时候已经跑了一百零几万个数据了.System.IndexOutOfRangeException: 索引超出了数组界限。
在 EmailInitialize.Initialize.Oracle2Mysql() 位置 D:\code\EmailInitialize\EmailInitialize\Initialize.cs:行号 127
是个oracle的连接函数 GetConn();//127行
{
try
{
conn = new OracleConnection(GetConnString());
return conn;
}
catch (Exception ex)
{
Log("读取Oracle数据库连接错误:\n" + ex.ToString());
return null;
}
} public static string GetConnString()
{
try
{
XmlDocument xml = new XmlDocument();
xml.Load(Environment.CurrentDirectory + "\\SysConfig.xml");
return xml.DocumentElement.SelectNodes("appSetting").Item(0).SelectNodes("add").Item(0).Attributes.GetNamedItem("value").InnerText.Trim();
}
catch (Exception ex)
{
Log("读取数据库连接字符串错误:\n" + ex.ToString());
return String.Empty;
}
}
ArrayList para = new ArrayList();//优化:这句话应放在循环外面
{
try
{
string query = "insert into T_Subscriber(email,EmailSupplierCode,IfOrder,CreateDate,UpdateDate) values('" + para[0] + "','" + para[1] + "','" + para[2] + "','" + para[3] + "','" + para[4] + "');";
MySQLCommand cmd = new MySQLCommand(query.ToString(), mysqlconn);
cmd.ExecuteNonQuery();
Log(query.ToString());
}
catch (Exception ex)
{
Log(ex.ToString());
Console.WriteLine(ex.Message);
}
}
那么不是只有一个para了吗?数组会不会被撑的不行?
这样每次循环重新生成一个有问题吗?
请指教
namespace EmailInitialize
{
class ClassMain
{
static void Main(string[] args)
{
Initialize email = new Initialize();
email.Oracle2Mysql(); Console.ReadLine();
} }
}
不知道是c#的问题还是有可能是oracle的问题~~~
貌似无法解决了~~晕~~~
只需在select语句后加上行数控制,如:rownumber>100 and rownumber<201
相信此法能解决,为测试,如果可以说一声。