V
vviktorsson
Hi I hope I'm in the right group. I'm accessing a lot of data from a
WebService that returns me a DataSet. I need to insert the Data set
into an Oracle Database version 9. It's taking about an hour to loop
throug this 40.000 rows and I think thats to long. Here is how I'm
doing it:
OracleConnection _conn = new OracleConnection(this.getOracleConn());
OracleCommand _cmd = new OracleCommand("BEGIN
BLA.DATA.InsertData( :sSSN, :sYear, :nAmount, :sType ); END;", _conn);
try
{
//The call to the WebService
TheData[] tData = MyWebserviceInstance.getMyData();
_conn.Open();
for (int i = 0; i < tData.Length; i++)
{
_cmd.CommandType = CommandType.Text;
_cmd.Parameters.Add("sSSN", OracleType.VarChar);
_cmd.Parameters["sSSN"].Value = tData.SSN;
_cmd.Parameters["sSSN"].Direction =
ParameterDirection.Input;
_cmd.Parameters.Add("sYear", OracleType.VarChar);
_cmd.Parameters["sYear"].Value = tData.YEAR;
_cmd.Parameters["sYear"].Direction =
ParameterDirection.Input;
_cmd.Parameters.Add("nAmount", OracleType.Number);
_cmd.Parameters["nAmount"].Value =
tData.AMOUNT;
_cmd.Parameters["nAmount"].Direction =
ParameterDirection.Input;
_cmd.Parameters.Add("sType ", OracleType.VarChar);
if( tData.tegund == null )
_cmd.Parameters["sType "].Value = "";
else
_cmd.Parameters["sType "].Value =
tData.TYPE;
_cmd.Parameters["sType "].Direction =
ParameterDirection.Input;
_cmd.ExecuteNonQuery();
}
}
catch (System.Exception ex)
{
ex.ToString();
throw new Exception("Sorry Exception: \n" +
ex.ToString());
}
finally
{
_conn.Close();
_conn.Dispose();
_conn = null;
_cmd.Dispose();
_cmd = null;
}
It usually starts off on a good pace then the last 10-15 thousand rows
are very very slow. Does anyone out there have a better solution !?
Is there any Bulk Insert in the System.Data.OracleClient !?
Best regards
WebService that returns me a DataSet. I need to insert the Data set
into an Oracle Database version 9. It's taking about an hour to loop
throug this 40.000 rows and I think thats to long. Here is how I'm
doing it:
OracleConnection _conn = new OracleConnection(this.getOracleConn());
OracleCommand _cmd = new OracleCommand("BEGIN
BLA.DATA.InsertData( :sSSN, :sYear, :nAmount, :sType ); END;", _conn);
try
{
//The call to the WebService
TheData[] tData = MyWebserviceInstance.getMyData();
_conn.Open();
for (int i = 0; i < tData.Length; i++)
{
_cmd.CommandType = CommandType.Text;
_cmd.Parameters.Add("sSSN", OracleType.VarChar);
_cmd.Parameters["sSSN"].Value = tData.SSN;
_cmd.Parameters["sSSN"].Direction =
ParameterDirection.Input;
_cmd.Parameters.Add("sYear", OracleType.VarChar);
_cmd.Parameters["sYear"].Value = tData.YEAR;
_cmd.Parameters["sYear"].Direction =
ParameterDirection.Input;
_cmd.Parameters.Add("nAmount", OracleType.Number);
_cmd.Parameters["nAmount"].Value =
tData.AMOUNT;
_cmd.Parameters["nAmount"].Direction =
ParameterDirection.Input;
_cmd.Parameters.Add("sType ", OracleType.VarChar);
if( tData.tegund == null )
_cmd.Parameters["sType "].Value = "";
else
_cmd.Parameters["sType "].Value =
tData.TYPE;
_cmd.Parameters["sType "].Direction =
ParameterDirection.Input;
_cmd.ExecuteNonQuery();
}
}
catch (System.Exception ex)
{
ex.ToString();
throw new Exception("Sorry Exception: \n" +
ex.ToString());
}
finally
{
_conn.Close();
_conn.Dispose();
_conn = null;
_cmd.Dispose();
_cmd = null;
}
It usually starts off on a good pace then the last 10-15 thousand rows
are very very slow. Does anyone out there have a better solution !?
Is there any Bulk Insert in the System.Data.OracleClient !?
Best regards