Sushil,
Here's the code i ran for the test. I got past this issue (the
ImportRow) by using a LoadDataRow, but now, i have a new issue.
Using the test code below, it works. However, when i switch this to a
production table (it has 5 or 6 times as many blobs, numbers, and
varchars...Update hangs indefinitely! No errors...just hanging
forever.
I noticed another post on the 19th..someone has the same problem.
There's definitely a big nasty bug in the Oracle data adapter Update!
// TEST CODE
private void Test()
{
string connectionStringFrom = v4Context_.ConnectionString;
OracleConnection conFrom = new
OracleConnection(connectionStringFrom);
string connectionStringTo = v5Context_.ConnectionString;
OracleConnection conTo = new OracleConnection(connectionStringTo);
try
{
v4Context_.ExecuteNonQuery("drop table migrationtest");
}
catch {}
v4Context_.ExecuteNonQuery("create table migrationtest ( name
varchar(50), stuff blob, some number, large varchar(2000), constraint
PK_Temp primary key (name) )");
IDataParameter blobParam = v4Context_.CreateDataParameter("blob",
(new System.Text.ASCIIEncoding()).GetBytes("foobar"),
DbType.Object);
v4Context_.ExecuteNonQuery("insert into migrationtest values(
'foobar', :blob, 1, 'large')", blobParam);
try
{
v5Context_.ExecuteNonQuery("drop table migrationtest2");
}
catch {}
v5Context_.ExecuteNonQuery("create table migrationtest2 ( name
varchar(50), stuff blob, some number, large varchar(2000), constraint
PK_Temp primary key (name) )");
// Open the connection
conFrom.Open();
conTo.Open();
// Populate DataSet and close the Database Connection
DataSet dsFrom = new DataSet();
string sqlMoleculeFrom = "select * from migrationtest";
OracleDataAdapter daFrom = new OracleDataAdapter (sqlMoleculeFrom,
conFrom);
daFrom.Fill(dsFrom,"migrationtest");
DataSet dsTo = new DataSet();
string sqlMoleculeTo = "select * from migrationtest2";
OracleDataAdapter daTo = new OracleDataAdapter (sqlMoleculeTo,
conTo);
daTo.Fill(dsTo,"migrationtest2");
// Close the connections
conFrom.Close();
// conTo.Close();
if ( dsFrom.Tables["migrationtest"].Rows.Count > 0)
{ //there are molecules to migrate for this document
//add a unique constraint to prevent dups
UniqueConstraint uc = new UniqueConstraint
( new DataColumn[]
{
dsTo.Tables["migrationtest2"].Columns["Name"]
}
);
dsTo.Tables["migrationtest2"].Constraints.Add(uc);
DataTable t = dsTo.Tables["migrationtest2"];
foreach( DataRow row in dsFrom.Tables["migrationtest"].Rows)
{
//first, update the document guid
try
{
//importrow doesnt work..trying loaddatarow
object [] row2 = row.ItemArray;
t.BeginLoadData();
t.LoadDataRow(row2, false);
t.EndLoadData();
}
catch(Exception ex)
{
log.Warn("Row already found in R_Molecule", ex);
};
}
string test = dsTo.GetXml(); //test
OracleCommandBuilder cb = new OracleCommandBuilder(daTo);
log.Debug( string.Format("Molecule update command {0}",
cb.GetUpdateCommand().CommandText));
// Update Orcale from the DataSet
int r = 0;
try
{
r = daTo.Update(dsTo,"migrationtest2");
dsTo.AcceptChanges();
}
catch(Exception e)
{
dsTo.RejectChanges();
log.Info( "Migrated for R_Molecule failed", e);
}
finally
{
log.Info( string.Format("Migrated {0} rows to R_Molecule", r));
conTo.Close();
conTo.Open(); //test
string sql = "select count(*) from migrationtest2";
OracleCommand cmd = new OracleCommand(sql, conTo);
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
int x = dr.GetInt32(0);
conTo.Close();
}
}
}
