c#从SqlServer查询数据导入Oracle数据库
int i = 0;
//查询SqlServer数据库数据
string str = "server=" + textBox3.Text + ";database="+ comboBox1.Text +";Uid=" + textBox1.Text + ";Pwd=" + textBox2.Text + ";";
SqlConnection sc = new SqlConnection(str);
try
{
sc.Open();
SqlCommand cmd = new SqlCommand("select BoardBarcode,BoardStatus from table ", sc);
SqlDataReader sdr = cmd.ExecuteReader();
while(sdr.Read())
{
richTextBox1.Text += String.Format("\n 二维码:{0} 测试结果:{1}",sdr[0],sdr[1]);
OracleConnection conn2 = new OracleConnection();
conn2.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + textBox4.Text + ")(PORT=" + textBox7.Text + ")) (CONNECT_DATA=(SERVICE_NAME=" + textBox8.Text + ")));Persist Security Info=True;User ID=" + textBox5.Text + ";Password=" + textBox6.Text + ";";
conn2.Open();
string sql = "insert into table(AOIDATE_ID,BOARDBARCODE,BOARDSTATUS,DATETIME) values (:AOIDATE_ID,:BOARDBARCODE,:BOARDSTATUS,:DATETIME)";
OracleCommand cmd2 = new OracleCommand(sql,conn2);
string aoiid = Guid.NewGuid().ToString();
DateTime datetime = DateTime.Now;
OracleParameter par1 = new OracleParameter(":AOIDATE_ID",aoiid);
OracleParameter par2 = new OracleParameter(":BOARDBARCODE", sdr[0]);
OracleParameter par3 = new OracleParameter(":BOARDSTATUS", sdr[1]);
OracleParameter par4 = new OracleParameter(":DATETIME", datetime);
cmd2.Parameters.Add(par1);
cmd2.Parameters.Add(par2);
cmd2.Parameters.Add(par3);
cmd2.Parameters.Add(par4);
int result = cmd2.ExecuteNonQuery();
int count = result + i; //统计导入的条数
i++;
if (result > 0)
{
richTextBox1.Text += String.Format("\n 成功导入数据:{0}条", count);
}
else
{
richTextBox1.Text += "\n 没有数据导入";
}
conn2.Close();
cmd2.Dispose();
}
}
catch(Exception ex)
{
richTextBox1.Text += String.Format("数据库读取错误:{0}", ex.Message);
}
finally
{
sc.Close();
}
其中 cmd2.Parameters.Add(par1);
cmd2.Parameters.Add(par2);
cmd2.Parameters.Add(par3);
cmd2.Parameters.Add(par4);
顺序要与插入的
insert into table(AOIDATE_ID,BOARDBARCODE,BOARDSTATUS,DATETIME) values (:AOIDATE_ID,:BOARDBARCODE,:BOARDSTATUS,:DATETIME)
顺序一致,否则insert的顺序会按照cmd2.Parameters.Add(par1);
cmd2.Parameters.Add(par2);
cmd2.Parameters.Add(par3);
cmd2.Parameters.Add(par4);插入实际的数据