CODEWITHSUNDEEP
c#asp.netsql-server-2005oracle-databasedatabase-connection
kacalapy
kacalapy

Reputation: 10164

.net application's oracle connection timing out

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)));";

Upvotes: 3

Views: 19012

Answers (2)

boa7-2011
boa7-2011

Reputation: 31

You can set s.BulkCopyTimeout option

Upvotes: 3

Jordan Parmer
Jordan Parmer

Reputation: 37224

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.

Upvotes: 0

Related Questions