I use this query to insert into Oracle database from a dataset in ASP.NET.
ds = getMyData(sqlspRemoteDBsource);
OracleCommand cmdInsertProd;
String f1;
String f2;
String f3;
for (int x = 0; x < ds.Tables[0].Rows.Count; x++)
{
try
{
myCon.Open();
}
catch
{
}
f1 = ds.Tables[0].Rows[x][0].ToString().Replace("'", "''"); //string
f2 = ds.Tables[0].Rows[x][1].ToString(); //number
f3 = ds.Tables[0].Rows[x][2].ToString(); //date
insertStr = "insert into Mytab ( " +
"DBf1, " +
"DBf2, " +
"DBf2, " +
" values ('" + f1 + "', '" +
"NULLIF('" + f2 + "', '') , " +
"to_date('" + f3 + "', 'mm/dd/yyyy hh:mi:ss AM'), " + "')";
try
{
cmdInsertProd = new OracleCommand(insertStr, myCon); ;
cmdInsertProd.ExecuteNonQuery();
sconn.Close();
}
catch
{
String errorText = "GOT ERROR!!";
}
} // for loop
The above tables long time to execute as the dataset is huge. Is there any efficient way to insert to the database? I found that the bulkcopy is not an option if the target database is not a sql server database.
Also I am opening the connection in each for loop and closing it? Is it right practice?
Also I do NULLIF and to_date to take care of the NULL in number column and to format the dates. Else the insert string does not work.
Also is it a good practice to build huge number of insert strings via the for loop and then open the database and insert all at once? If so, how could I do that?
Also could I directly insert the dataset to the Oracle table via some other way?
Also what could be a suggested way to handle the error if the execution fails halfway because of a network error or so? Like a rollback or notification...?
Suggestion will be very helpful.
Thanks.