Last post Feb 24, 2011 04:25 AM by swapna.anu
Feb 21, 2011 10:25 AM|swapna.anu|LINK
I am developing a web application with back end as Oracle. I call the procedures from web application when ever some update, create or delete for the records needs to be performed. How to maintain the connection in these scenarios.
Can I open the connection and maintain it through out the application till user logs out?
Thanks in advance.
Feb 21, 2011 11:36 AM|Shuvo Aymon|LINK
The best way to communicate with db is : open a connection then perform the operation and immediately close the connection.
Why you want a conncetion always open? Whenever you need open it then close after your purpose is served.
Feb 21, 2011 05:39 PM|swapna.anu|LINK
Thanks for your quick reply. Currently I am doing the same thing but what I can see is everytime when I open a conn and close, it makes an entry into the oracle session table and its not deleting the entry. So my team wants to open only one conn and maintain
it till its logged out.
Its because the entries in the oracle session table may create memory problem.
Thanks in advance
Feb 21, 2011 06:27 PM|Lannie|LINK
Open, do your work, and close, or you will really have scaling problems!
My understanding is those entries are dynamically managed with Oracle, and are there to speed repeat use of the same query by another user, kind of like a cache. I watch oracle manage my session and sql code, and I see it working in auto mode, growing and
You can also leave more technical questions that Oracle engineers might answer at the Oracle ODP.NET forum.
Feb 22, 2011 05:47 AM|swapna.anu|LINK
Can you tell me is it required to dispose the connection object everytime we open and close?
thanks in advance.
Feb 22, 2011 12:28 PM|Shuvo Aymon|LINK
Please use using key word. I think it will do the dispose related work itself.
using(OracleConnection connection = new OracleConnection(connectionString); )
//Create a command object
using(OracleCommand command = new OracleCommand(sql, connection))
using(OracleDataReader reader = cmd.ExecuteReader())
Feb 23, 2011 12:55 AM|mamun22s|LINK
Try this code for create connection
public OracleConnection getConnection()
OracleConnection con = null;
String uId = "dbsuername";
String pass = "abc";
String ip = "192.168.0.1";
String port = "1521";
String sId = "dbsidname";
con = new OracleConnection();
if (con != null)
String pooling = "Max Pool Size=10;Min Pool Size=5;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=1; Decr Pool Size=1";
//String pooling="Pooling = false;";
con.ConnectionString = "User Id=" + uId + ";Password=" + pass + ";Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + ip + ")(PORT=" + port + ")))(CONNECT_DATA=(SID=" + sId + ")));" + pooling;
catch (Exception oe)
Then call the connection for
public ItemDs getItem()
ItemDs itemDs = new ItemDs();
OracleConnection conn = new OracleConnection();
conn = new Connection().getConnection();
OracleCommand cmd = new OracleCommand("", conn);
cmd.CommandText = "SELECT I.* FROM ITEM I WHERE ISGROUP='False'" + Where + " ORDER BY I.NAME";
cmd.CommandType = CommandType.Text;
OracleDataReader reader = cmd.ExecuteReader();
OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
Feb 23, 2011 04:36 AM|swapna.anu|LINK
I am currently using 'using ' key word but that doesn't help in removing the records from oracle session.
So along with using key word I am disposing the connection object. This is not posing new entries each time I open and close but uses the same connection which is used for the first time. This resolves the issue what I am facing with oracle but still its
posting 2 entries for one connection. I verified all the code but I am not able to find where I missed the call to dispose.
Is there any way to find from where the other entry is posted in to oracle
Feb 23, 2011 08:52 AM|Shuvo Aymon|LINK
Feb 23, 2011 12:10 PM|Shuvo Aymon|LINK
Feb 24, 2011 04:25 AM|swapna.anu|LINK
I am not creating the session table, its an inbuilt table in PL/SQL with name v$session. I am checking it whenever my code is run.