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!