今天做了一个需要将一个oracle数据库里的数据导到sqlServer里面去,具体的需求就是一次下载十万条左右的数据
提到了增加便想到了效率的问题,如何实现有效的增加呢,以前几百条的时候可以用while来循环一条一条加入,如果数据量增大,效率就出现问题,面对怎么大的数据量让我想到了直接用DataSet来insert,带着这个问题找了点资料,MS已经给我们提供了这方面的类库,我们直接拿来用就OK了,那我就在着做个记号方便以后查看.
![](/Images/OutliningIndicators/ContractedBlock.gif)
Code
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
/// 下载远程信息线程
/// </summary>
private static Thread _thread;
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
/// 开始下载帐户信息,下载的前提条件是本地库里的行数小于规定的行数
/// </summary>
public static void StartDownAccount()
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
if (int.Parse(System.Configuration.ConfigurationManager.AppSettings["LocalDBCount"]) > count)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
count = int.Parse(System.Configuration.ConfigurationManager.AppSettings["LocalDBCount"]) + 1;
_thread = new Thread(new ThreadStart(SyncPassword_By_Oracle));
if (!_thread.IsAlive)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
_thread.Start();
}
}
}
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
/// 批量下载远程数据库到本地
/// </summary>
private static void SyncPassword_By_Oracle()
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DataTable OraTable = DbHelperOra.Query(SQL_SELECT_ACCOUNT_BY_ALL_ORACLE).Tables[0];
using (System.Data.SqlClient.SqlBulkCopy sqlbulk = new SqlBulkCopy(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSql"]))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sqlbulk.DestinationTableName = "ACCOUNT";
sqlbulk.ColumnMappings.Add(0, "ACCOUNT");
sqlbulk.ColumnMappings.Add(1, "SNO");
sqlbulk.ColumnMappings.Add(2, "QUERYPIN");
DbHelperSQL.ExecuteSql(SQL_TRUNCATE_ACCOUNT);
sqlbulk.WriteToServer(OraTable);
sqlbulk.Close();
//if (Convert.ToInt32(DbHelperOra.GetSingle(SQL_SELECT_ACCOUNT_BY_COUNT_SQL)) < Convert.ToInt32(DbHelperSQL.GetSingle(SQL_SELECT_ACCOUNT_BY_COUNT_SQL)))
// DbHelperSQL.ExecuteSql(SQL_DELETE_DISTINCT_TABLE);
_thread.Abort();
}
}
catch
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
}
}
![](/Images/OutliningIndicators/ContractedBlock.gif)
废弃修改配置文件#region 废弃修改配置文件
![](/Images/OutliningIndicators/InBlock.gif)
//private static void SaveXML()
//{
// #region
// XmlDocument doc = new XmlDocument();
// doc.Load(AppDomain.CurrentDomain.BaseDirectory + "Web.Config");
// XmlNode node = doc.SelectSingleNode("/configuration/appSettings/add[@key='IsFirst']");
// if (node.Attributes["value"].Value == "false")
// return;
// node.Attributes["value"].Value = "false";
// doc.Save(AppDomain.CurrentDomain.BaseDirectory + "Web.Config");
// #endregion
//}
#endregion
提到了增加便想到了效率的问题,如何实现有效的增加呢,以前几百条的时候可以用while来循环一条一条加入,如果数据量增大,效率就出现问题,面对怎么大的数据量让我想到了直接用DataSet来insert,带着这个问题找了点资料,MS已经给我们提供了这方面的类库,我们直接拿来用就OK了,那我就在着做个记号方便以后查看.
![](/Images/OutliningIndicators/ContractedBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)