实现功能;
从Oracle中读取数据，然后复制到SqlServer数据库中
有如下几个注意项：
1.需要在app.config中添加如下节点
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="Oracle.DataAccess" publicKeyToken="89B483F429C47342"/>
<bindingRedirect oldVersion="4.112.0.0-4.112.9999.9999" newVersion="4.122.1.0"/>
</dependentAssembly>
</assemblyBinding>
</runtime>
2.添加引用Oracle.ManagedDataAccess.dll使得OracleConnection使用引用Oracle.ManagedDataAccess.Client
3.计算机->右键（属性）->高级系统设置->环境变量->在最前面添加instantclient_12_2的文件地址(如果有客户端的话，可不添加环境变量配置)
4.bin文件夹下添加如下dll
5.完整代码在云盘链接:https://pan.baidu.com/s/1nvpBT4d 密码: 2343
前台截图如下
后台代码如下
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using Oracle.ManagedDataAccess.Client;
namespace ExportOracleToSqlServer
{
public partial class OTTImport : Form
{
private NLog.Logger logger = new NLog.LogFactory().GetCurrentClassLogger();
public string oracleCityname = string.Empty;
public OTTImport()
{
InitializeComponent();
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
string date = Convert.ToDateTime(dtpime.Text).ToString("yyyy-MM-dd");
logger.Info("DT创建完毕");
string sqlText = "select * from " + oracleCityname + " where p_day=to_date('" + date + "','yyyy-MM-dd')";
logger.Info("sql语句为：" + sqlText + "");
logger.Info("Oracleconn连接" + ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString);
try
{
DataTable dt = GetSqlTableScheme();
//先读取ORACLE的表
long count = GetCount(date);
int previousProgressValue = 0;
using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString))
{
logger.Info("Oracleconn连接" + ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString);
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
this.logger.Info("OracleCommand");
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 12 * 60 * 60;
cmd.CommandText = sqlText;
int i = 0;
using (OracleDataReader reader = cmd.ExecuteReader())
{
this.logger.Info("OracleDataReader");
while (reader.Read())
{
i++;
dt.Rows.Add(
reader["Columns1"].ToString(),
reader["Columns2"].ToString(),
reader["Columns3"].ToString(),
reader["Columns4"].ToString(),
reader["Columns5"].ToString()
);
int currentProgressValue = Convert.ToInt32(i * 100 / count);
if (currentProgressValue > previousProgressValue)
{
previousProgressValue = currentProgressValue;
this.backgroundWorker1.ReportProgress(currentProgressValue,"当前进度");
}
//每读取十万条数据，进行入库一次
if (i % 100000 == 0)
{
InsertIntoSqlServer(dt);
dt = GetSqlTableScheme();
}
}
if (dt.Rows.Count > 0)
{
InsertIntoSqlServer(dt);
}
}
}
}
}
catch (Exception)
{
throw;
}
logger.Info("Oracle数据读取完毕");
this.backgroundWorker1.ReportProgress(0, "完成入SQL库！");
}
private static DataTable GetSqlTableScheme()
{
DataTable dt = new DataTable();
dt.Columns.Add("Columns1", typeof(string));
dt.Columns.Add("Columns2", typeof(string));
dt.Columns.Add("Columns3", typeof(string));
dt.Columns.Add("Columns4", typeof(string));
dt.Columns.Add("Columns5", typeof(string));
return dt;
}
private void InsertIntoSqlServer(DataTable dt)
{
try
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
logger.Info("SqlConnection");
conn.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction))
{
bulkCopy.DestinationTableName = "dbo." + txtdboname.Text.Trim() + "";
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.BulkCopyTimeout = 12 * 60 * 60;
bulkCopy.ColumnMappings.Clear();
bulkCopy.ColumnMappings.Add("Columns1", "Columns1");
bulkCopy.ColumnMappings.Add("Columns2", "Columns2");
bulkCopy.ColumnMappings.Add("Columns3", "Columns3");
bulkCopy.ColumnMappings.Add("Columns4", "Columns4");
bulkCopy.ColumnMappings.Add("Columns5", "Columns5");
bulkCopy.WriteToServer(dt);
}
}
//logger.Info("完毕");
//this.backgroundWorker1.ReportProgress(100, "全部完成！");
}
catch (Exception)
{
throw;
}
}
private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
progressBar1.Value = e.ProgressPercentage;
if (e.UserState != null)
{
label4.Text = string.Format("{0}[{1}%]", e.UserState.ToString(), e.ProgressPercentage);
}
}
private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
if (e.Error == null)
{
MessageBox.Show("成功！");
}
else
{
MessageBox.Show(e.Error.ToString());
}
}
private void button1_Click(object sender, EventArgs e)
{
#region 设置数据表明
switch (cobCity.Text)
{
case "杭州":
oracleCityname = "HANGZHOU";
break;
case "湖州":
oracleCityname = "HUZHOU";
break;
case "金华":
oracleCityname = "JINHUA";
break;
case "嘉兴":
oracleCityname = "JIAXING";
break;
case "丽水":
oracleCityname = "LISHUI";
break;
case "宁波":
oracleCityname = "NINGBO";
break;
case "衢州":
oracleCityname = "QUZHOU";
break;
case "绍兴":
oracleCityname = "SHAOXING";
break;
case "台州":
oracleCityname = "TAIZHOU";
break;
case "温州":
oracleCityname = "WENZHOU";
break;
case "舟山":
oracleCityname = "ZHOUSHAN";
break;
default:
oracleCityname = "HANGZHOU";
break;
}
#endregion
if (txtdboname.Text == "")
{
MessageBox.Show("请输入要入库的表名");
return;
}
else
{
if (backgroundWorker1.IsBusy == false)
{
backgroundWorker1.RunWorkerAsync();
}
}
}
private void OTTImport_Load(object sender, EventArgs e)
{
cobCity.SelectedIndex = 0;
}
public long GetCount(string date)
{
long count = 0;
using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString))
{
string sqlText = "select count(0) from " + oracleCityname + " where p_day=to_date('" + date + "','yyyy-MM-dd')";
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
this.logger.Info("OracleCommand");
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 12 * 60 * 60;
cmd.CommandText = sqlText;
count = Convert.ToInt64(cmd.ExecuteScalar());
}
conn.Close();
}
return count;
}
}
}