.net应用程序的oracle连接超时
我有下面的代码尝试从 Oracle 到 SQL Server 2005 进行批量复制，但它总是超时。如何延长oracle连接超时时间？看来我不能从我在网上读到的内容。
OracleConnection source = new OracleConnection(GetOracleConnectionString());
source.Open();
SqlConnection dest = new SqlConnection(GetSQLConnectionString() );
dest.Open();
OracleCommand sourceCommand = new OracleCommand(@"select * from table");
using (OracleDataReader dr = sourceCommand.ExecuteReader())
{
using (SqlBulkCopy s = new SqlBulkCopy(dest))
{
s.DestinationTableName = "Defects";
s.NotifyAfter = 100;
s.SqlRowsCopied += new SqlRowsCopiedEventHandler(s_SqlRowsCopied);
s.WriteToServer(dr);
s.Close();
}
}
source.Close();
dest.Close();
这是我的 oracle 连接字符串：
return "User Id=USER;Password=pass;Data Source=(DESCRIPTION=" +
"(ADDRESS=(PROTOCOL=TCP)(HOST=14.12.7.2)(PORT=1139))" +
"(CONNECT_DATA=(SID=QCTRP1)));";
i have the code below trying to do a bulk copy from oracle to SQL server 2005 and it keeps timing out. how can i extend the oracle connection timeout? it seems i can not from what i read on the web.
OracleConnection source = new OracleConnection(GetOracleConnectionString());
source.Open();
SqlConnection dest = new SqlConnection(GetSQLConnectionString() );
dest.Open();
OracleCommand sourceCommand = new OracleCommand(@"select * from table");
using (OracleDataReader dr = sourceCommand.ExecuteReader())
{
using (SqlBulkCopy s = new SqlBulkCopy(dest))
{
s.DestinationTableName = "Defects";
s.NotifyAfter = 100;
s.SqlRowsCopied += new SqlRowsCopiedEventHandler(s_SqlRowsCopied);
s.WriteToServer(dr);
s.Close();
}
}
source.Close();
dest.Close();
here is my oracle connection string:
return "User Id=USER;Password=pass;Data Source=(DESCRIPTION=" +
"(ADDRESS=(PROTOCOL=TCP)(HOST=14.12.7.2)(PORT=1139))" +
"(CONNECT_DATA=(SID=QCTRP1)));";
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以设置 s.BulkCopyTimeout 选项
You can set s.BulkCopyTimeout option
在您的连接字符串中，有一个“连接生命周期”和“连接超时”参数。您可以进行相应的设置。请参阅此处获取完整参考。
顺便说一句，我知道您没有问过这个问题，但是您是否考虑过使用 ETL 工具来迁移数据库记录（例如 Informatica、FME 等）？虽然您的方法是有效的，但它的性能不会很高，因为您要将所有记录从一个数据库传输到客户端，然后将它们序列化到另一个数据库。对于小批量数据集，这不是一个大问题，但如果您要处理数十万行，您可能需要考虑使用官方 ETL 工具。
In your connection string, there is a 'Connection Lifetime' and 'Connection Timeout' parameter. You can set it accordingly. See here for the full reference.
BTW, I know you didn't ask this, but have you considered an ETL tool for migrating your DB records (e.g. Informatica, FME, etc.)? While your approach is valid, it isn't going to be very performant since you are hydrating all of the records from one DB to the client and then serializing them to another DB. For small bulk sets, this isn't a big issue, but if you were processing hundreds of thousands of rows, you might want to consider an official ETL tool.