需求：实现远程sqlserver数据库转存到oracle中，同时实现每天的凌晨两点转一次，一次转三个月内的数据，oracle中存在时就更新，不存在时就插入，因为数据有点多，一天数据就有五百条左右，插入时性能得调整。
难点：
1、两个数据库的连接（比较简单，但是细节很重要）
2、性能好点的插入更新sql
3、怎么实现每天定时更新
4、一次性同步数据较多，会使得系统直接卡死（我那破本本，重启了四五次）
解决方案：
1、对于oracle的连接其实很简单，因为oracle是本地的所以连接很easy，连接字符串搞定就行
难点：
1、两个数据库的连接（比较简单，但是细节很重要）
2、性能好点的插入更新sql
3、怎么实现每天定时更新
4、一次性同步数据较多，会使得系统直接卡死（我那破本本，重启了四五次）
解决方案：
1、对于oracle的连接其实很简单，因为oracle是本地的所以连接很easy，连接字符串搞定就行
</pre><pre name="code" class="csharp">//oracle连接字符串
string oracleConnectStr = "Data source=数据库名;user id=登录名;password=密码";
//例如：string oracleConnectStr = "Data source=orcl;user id=liems3;password=liems3";//sqlserver连接字符串
string sqlserverConnectStr = "Data Source=远程IP;Initial Catalog=数据库;User Id=登录名;Password=密码";
//string sqlserverConnectStr = "Data Source=168.168.10.77;Initial Catalog=jamie;User Id=sa;Password=jamie";异常 System.Data.SqlClient.SqlException (0x80131904): 在与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误。未找到或无法访问服务器。请验证实例名称是否正确并且 SQL Server 已配置为允许远程连接。 (provider: Named Pipes Provider, error: 40 - 无法打开到 SQL Server 的连接)
之后就会使劲的去百度异常问题，但是有的时候可能都不是这些问题，就拿sqlserver 2000的来说吧，2000有个神马sqlserver注册组，注册组下有数据库，用查询分析器连接的时候127.0.0.1都连不进去，需要在后面加注册组中的完整地址像下面这样string sqlserverConnectStr = "Data Source=127.0.0.1\\ERP;Initial Catalog=jamie;User Id=sa;Password=jamie";3、性能好点的插入语句这里介绍merge into 根据条件插入或更新，语句简单性能好，至于详细各位自己勾搭度娘去，merge into 能很好的同步数据库中两张表的数据，对于不同数据库的数据同步，我开始也是纠结了好久，之后提出两种方案，都是可行的，一种是建一张临时表将sqlserver中查到的数据插入进去之后再更新，这种是可行的，一个同事就是用的这种，一种是直接用sql将查到的数据拼成一张表（这里不知道如何表达，其实我也是第一次见到这么神奇的东东）
sqlStr=@"select 123 as dualp_id ,'jamie' as dual_name,18 as dual_age from dual
union all
select 312 as dualp_id ,'tom' as dual_name,19 as dual_age from dual
union all
select 321 as dualp_id ,'jane' as dual_name,20 as dual_age from dual"
sqlo = string.Format(@"merge into cement_in indata using ({0}) temp on(indata.FInterID=temp.FInterID and indata.FEntryID=temp.FEntryID)
when matched then
update set indata.FDate = temp.FDate,
indata.FQty = temp.FQty,
indata.fprice = temp.fprice,
indata.famount = temp.famount,
indata.funitid = temp.funitid,
indata.FTranType =temp.FTranType,
indata.FSupplyID = temp.FSupplyID,
indata.FItemID = temp.FItemID,
indata.fnumber = temp.fnumber,
indata.itemname =temp.itemname,
indata.fmodel =temp.fmodel,
indata.supplyname =temp.supplyname,
indata.unitname =temp.unitname,
indata.typename =temp.typename
when not matched then
insert
(FInterID, FEntryID, FDate, FQty, fprice, famount, funitid, FTranType, FSupplyID,
FItemID, fnumber, itemname, fmodel, supplyname, unitname, typename)
values
(temp.FInterID,temp.FEntryID,temp.FDate, temp.FQty, temp.fprice, temp.famount, temp.funitid, temp.FTranType, temp.FSupplyID,
temp.FItemID,temp.fnumber, temp.itemname, temp.fmodel,temp.supplyname,temp.unitname,temp.typename)", sqlStr);// 组成的那个超临时表的sql语句 4、接着就是数据太多性能的问题了，一般同步300条数据的时候一秒就ok了，但是到了500以上就蛋疼了，特别是5000条的时候整个本本都game over，等了十几分钟最后只能关机重启。对于这种问题，解决方案是将数据分为300条一组数据存入到一个list，之后每300条同步一次，效果一下就很明显了，5000条数据70秒就搞定了。so easy ，大神再也不会看到我关机重启了。
5、至于每天定时运行，这个其实也很简单，我用的是一个叫jobAnt的工具，大神提供的，各种碉堡了，定时运行无压力。下面就上代码吧!
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.OracleClient;
namespace DataTransform
{
public class TransForm
{
//总共处理数据条数
private int sumData=0;
//开始时间
private DateTime startTime;
//结束时间
private DateTime endTime;
//是否是操作入库表
private bool isIn;
//一次更新条数
private int updateNumber = 500;
//oracle连接字符串
private string oracleConnectStr = "Data source=orcl;user id=liems3;password=liems3";
//sqlserver连接字符串
private string sqlserverConnectStr = "Data Source=168.168.10.77;Initial Catalog=jamie;User Id=sa;Password=jamie";
//入库表的sql
string sqlReadIn = string.Format(@"select * from cement_in where FDate between DateAdd(Month,-3,getdate()-1) and DateAdd(Month,-2,getdate()) ;");
//出库表的sql
string sqlReadOut = string.Format(@"select * from cement_out where FDate between DateAdd(Month,-3,getdate()-1) and DateAdd(Month,-2,getdate()) ;");
List<String> sqlList = null;
public void ReadFromSqlServer(bool isin)
{
this.isIn = isin;
SqlConnection sqlCon = null;
SqlCommand cmdSQL = null;
SqlDataReader dataReader = null;
OracleConnection conn = null;
OracleCommand cmdORA = null;
try
{
sqlCon = new SqlConnection(sqlserverConnectStr);
sqlCon.Open();
conn = new OracleConnection(oracleConnectStr);
conn.Open();
string sqls = null;
if (isIn)
{
sqls = sqlReadIn;
}
else
{
sqls = sqlReadOut;
}
cmdSQL = new SqlCommand(sqls, sqlCon);
dataReader = cmdSQL.ExecuteReader();
string sqlUion = "";
int num = 1;
sqlList = new List<string>();
while (dataReader.Read())
{
string strTemp;
if (isIn)
{
strTemp = string.Format(@"select
{0} as FInterID,{1} as FEntryID,
to_date('{2}','yyyy-mm-dd hh24:mi:ss') as FDate,
{3} as FQty,{4} as fprice,{5} as famount,
{6} as funitid,{7} as FTranType,'{8}' as FSupplyID,
{9} as FItemID,{10} as fnumber,'{11}' as itemname,
'{12}' as fmodel,'{13}' as supplyname,'{14}' as unitname,'{15}' as typename from dual",
Convert.ToInt32(dataReader["FInterID"]),
Convert.ToInt32(dataReader["FEntryID"]),
Convert.ToString(dataReader["FDate"]),
Convert.ToDouble(dataReader["FQty"]),
Convert.ToDouble(dataReader["fprice"]),
Convert.ToDouble(dataReader["famount"]),
Convert.ToInt32(dataReader["funitid"]),
Convert.ToInt32(dataReader["FTranType"]),
Convert.ToString(dataReader["FSupplyID"]),
Convert.ToInt32(dataReader["FItemID"]),
Convert.ToDouble(dataReader["fnumber"]),
Convert.ToString(dataReader["itemname"]),
Convert.ToString(dataReader["fmodel"]),
Convert.ToString(dataReader["supplyname"]),
Convert.ToString(dataReader["unitname"]),
Convert.ToString(dataReader["typename"])
);
}
else
{
strTemp = string.Format(@"select
{0} as FInterID,{1} as FEntryID,
to_date('{2}','yyyy-mm-dd hh24:mi:ss') as FDate,
{3} as FQty,{4} as fprice,{5} as famount,
{6} as saleprice,{7} as saleamount,'{8}' as funitid,
{9} as FTranType,{10} as FSupplyID,'{11}' as FItemID,
{12} as fnumber,'{13}' as itemname,'{14}' as supplyname,'{15}' as unitname,'{16}' as typename from dual",
Convert.ToInt32(dataReader["FInterID"]),
Convert.ToInt32(dataReader["FEntryID"]),
Convert.ToString(dataReader["FDate"]),
Convert.ToDouble(dataReader["FQty"]),
Convert.ToDouble(dataReader["fprice"]),
Convert.ToDouble(dataReader["famount"]),
Convert.ToInt32(dataReader["saleprice"]),
Convert.ToInt32(dataReader["saleamount"]),
Convert.ToInt32(dataReader["funitid"]),
Convert.ToInt32(dataReader["FTranType"]),
Convert.ToInt32(dataReader["FSupplyID"]),
Convert.ToInt32(dataReader["FItemID"]),
Convert.ToDouble(dataReader["fnumber"]),
Convert.ToString(dataReader["itemname"]),
Convert.ToString(dataReader["supplyname"]),
Convert.ToString(dataReader["unitname"]),
Convert.ToString(dataReader["typename"]));
}
sqlUion += strTemp;
if (num % updateNumber == 0)
{
sqlList.Add(sqlUion);
sqlUion = "";
}
else
{
sqlUion += " union all ";
}
num++;
}
if (!"".Equals(sqlUion))
{
string sqlu = sqlUion.Substring(0, sqlUion.Length - 10);
sqlList.Add(sqlu);
}
string sqlo = null;
foreach (var item in sqlList)
{
if (isIn)
{
sqlo = string.Format(@"merge into cement_in indata using ({0}) temp on(indata.FInterID=temp.FInterID and indata.FEntryID=temp.FEntryID)
when matched then
update set indata.FDate = temp.FDate,
indata.FQty = temp.FQty,
indata.fprice = temp.fprice,
indata.famount = temp.famount,
indata.funitid = temp.funitid,
indata.FTranType =temp.FTranType,
indata.FSupplyID = temp.FSupplyID,
indata.FItemID = temp.FItemID,
indata.fnumber = temp.fnumber,
indata.itemname =temp.itemname,
indata.fmodel =temp.fmodel,
indata.supplyname =temp.supplyname,
indata.unitname =temp.unitname,
indata.typename =temp.typename
when not matched then
insert
(FInterID, FEntryID, FDate, FQty, fprice, famount, funitid, FTranType, FSupplyID,
FItemID, fnumber, itemname, fmodel, supplyname, unitname, typename)
values
(temp.FInterID,temp.FEntryID,temp.FDate, temp.FQty, temp.fprice, temp.famount, temp.funitid, temp.FTranType, temp.FSupplyID,
temp.FItemID,temp.fnumber, temp.itemname, temp.fmodel,temp.supplyname,temp.unitname,temp.typename)", item);
}
else
{
sqlo = string.Format(@"merge into cement_out outdata using ({0}) temp on(outdata.FInterID=temp.FInterID and outdata.FEntryID=temp.FEntryID)
when matched then
update set outdata.FDate = temp.FDate,
outdata.FQty = temp.FQty,
outdata.fprice = temp.fprice,
outdata.famount = temp.famount,
outdata.funitid = temp.funitid,
outdata.FTranType =temp.FTranType,
outdata.FSupplyID = temp.FSupplyID,
outdata.FItemID = temp.FItemID,
outdata.fnumber = temp.fnumber,
outdata.itemname =temp.itemname,
outdata.saleprice =temp.saleprice,
outdata.saleamount =temp.saleamount,
outdata.supplyname =temp.supplyname,
outdata.unitname =temp.unitname,
outdata.typename =temp.typename
when not matched then
insert
(FInterID, FEntryID, FDate, FQty, fprice, famount, funitid, FTranType, FSupplyID,
FItemID, fnumber, itemname, saleprice,saleamount, supplyname, unitname, typename)
values
(temp.FInterID,temp.FEntryID,temp.FDate, temp.FQty, temp.fprice, temp.famount, temp.funitid, temp.FTranType, temp.FSupplyID,
temp.FItemID,temp.fnumber, temp.itemname, temp.saleprice,temp.saleamount,temp.supplyname,temp.unitname,temp.typename)", item);
}
cmdORA = new OracleCommand(sqlo, conn);
var val = cmdORA.ExecuteNonQuery();
if (val >= 1)
{
Console.WriteLine("insert or update count: " + val);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmdORA.Dispose();
conn.Close();
dataReader.Close();
cmdSQL.Dispose();
sqlCon.Close();
}
}
}
}<a target=_blank href="http://download.csdn.net/detail/jiangyou4/8535829">点击打开链接</a>
扫一扫
938
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
