reusing oracleconnection do not reflect structure change?

Richard

Hi,

I am writing a C# program involves of an Oracle database using Oracle.DataAccess.Client -> OracleConnection

Though, I have a question about reusing the same connection to fetch data from the same database. Here's what I've done.

string OracleConString = "User Id=user;Password=pass;Data Source=source";
OracleConnection OracleCon = new OracleConnection(OracleConString);

OracleCon.Open();

Then I use that connection to fetch data from the database into an OracleDataAdapter

Adapter.SelectCommand = new OracleCommand("select * from test", OracleCon);

Then I add a column into the database using the connection

string alterColSQL = "alter table test add ID number(11)";
OracleCommand alterCmd = new OracleCommand(alterColSQL, OracleCon);
alterCmd.ExecuteNonQuery();

Then I try to use the connection to fetch data again using OracleDataReader

string ReadSQL = "select * from test";
OracleCommand ReadCmd = new OracleCommand(ReadSQL, OracleCon);
OracleDataReader GISReader = GISReadCmd.ExecuteReader();
DataTable ReaderTable = new DataTable();
ReaderTable.Load(Reader);

But when I try to look for the new column that I found, it said the table doesn't have that new column I've just added, though it still has the old table structure. So I did many...many tests and found that:

1) If I use a new connection, I can fetch the newly added column (using select * from test)
2) Or I can fetch the newly added column using the old connection (using select FID from test), (select * from test) will not have the new column 'ID' that I've added
3) Or if I didn't fetch the data at the beginning (select * from test), I can fetch the newly added column after adding the table column (select * from test)

It seems like the memory hold on to the first data structure and giving the same stuff at the 2nd fetch, so I wonder why this is happening, is that a way that I can release the memory of the connection? This is just my guess-imate.

Can anyone explain why this is happening? Thanks!

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suthish Nair15y ago
Put a breakpoint and check each Dataset, datatable values.
Richard15y ago
I tried again, and it just doesn't work, you can try to run my code

string OracleConString = "User Id=user;Password=pass;Data Source=source;Statement Cache Size=0;Connection Lifetime=0";

DataSet ds_CvnDataset = new DataSet("ds_CvnDataset");
OdbcDataAdapter CvnAdapter = new OdbcDataAdapter();
DataTable dt_CvnData = new DataTable();

CvnAdapter.Fill(ds_CvnDataset, "ds_CvnDataset");
dt_CvnData = ds_CvnDataset.Tables["ds_CvnDataset"];

DataSet ds_CopyToGISSet = new DataSet("CopyToGISSet");
OracleDataAdapter CopyToGISAdapter = new OracleDataAdapter();
DataTable dt_CopyToGISTable = new DataTable();

OracleCommandBuilder CopyToGISBuilder = new OracleCommandBuilder(CopyToGISAdapter);
CopyToGISAdapter.SelectCommand = new OracleCommand("select * from pole_database_bk", OracleCon);
CopyToGISAdapter.Fill(ds_CopyToGISSet, "CopyToGISSet");
dt_CopyToGISTable = ds_CopyToGISSet.Tables["CopyToGISSet"];

string alterColSQL = "alter table pole_database_bk add FID number(11)";
OracleCommand alterCmd = new OracleCommand(alterColSQL, OracleCon);
alterCmd.ExecuteNonQuery();

string GISReadSQL = "select * from pole_database_bk";
OracleCommand GISReadCmd = new OracleCommand(GISReadSQL, OracleCon);

OracleDataReader GISReader = GISReadCmd.ExecuteReader();
DataTable GISReaderTable = new DataTable("GISReader");
GISReaderTable.Load(GISReader);
MPdataset.Tables.Add(GISReaderTable);

oDataGrid.SetDataBinding(MPdataset, "GISReader");
Suthish Nair15y ago
Richard,

Its working for me..

New column get added.

Can also fetch the new column.

using System.Data.OracleClient;

string OracleConString = "Password=111;User id=1111;Data Source=hello;Connection Lifetime=0";

OracleConnection OracleCon = new OracleConnection(OracleConString);
OracleCon.Open();
OracleDataAdapter Adapter = new OracleDataAdapter();
Adapter.SelectCommand = new OracleCommand("select * from test1", OracleCon);
DataSet ds = new DataSet();
Adapter.Fill(ds);

string alterColSQL = "alter table test1 add ID number(11)";
OracleCommand alterCmd = new OracleCommand(alterColSQL, OracleCon);
alterCmd.ExecuteNonQuery();

string ReadSQL = "select * from test1";
OracleCommand ReadCmd = new OracleCommand(ReadSQL, OracleCon);
OracleDataReader GISReader = ReadCmd.ExecuteReader();
DataTable ReaderTable = new DataTable();
ReaderTable.Load(GISReader);
ds.Tables.Add(ReaderTable);

OracleCon.Close();
Mahesh Chand15y ago
It looks like your connection is somehow being cached. Can you give a try to ChangeDatabase method and pass the same connection string? If that does not work, I wonder if a call of the Dispose method would work.
Richard15y ago
yeah, I've checked that the columns are already created in the database when it throws the error.
Mahesh Chand15y ago
That's strange. Did you check the database if the table had new column or not?
Richard15y ago
anyone?

I did more research and suspected it may be the statement caching in the connection string but give me the same result.
Richard15y ago
yeah I have try to use OracleCon.close(); to close and reopen the connection, but same result :(
Guest User15y ago
Have you tried closing and re-opening the connection after the alter statement? (Just trying to eliminate possible causes...)
Richard15y ago
don't think that is the case because like i mentioned, i can use the same connection to get the altered table strcuture (only if i didn't include the first fetch), so it's not a problem of transaction commit.

plus the 'alter' command should be auto commit during transaction.
Guest User15y ago
At a quick glance it looks like there's an implicit transaction that needs to be committed.