OledbConnection - Multiple Users Trying To Connect Same time

[ahmarshi]

Hi,

In my web application I am using OledbConnection to connect to Oracle DB.

Problem I am facing is when more than one users reach OledbDataAdapter.Fill statement, one of them get failed and .net throws an error saying

Datareader needs Open connection. Connection State is Open, Execute

My Code is

conn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyApp;User ID=aaaaa;Password=aaaaaaa"

if (conn.State != ConnectionState.Open)
{
if (conn.State != ConnectionState.Closed)
conn.Close();
conn.Open();
}

dtdptr = new OleDbDataAdapter(mySQL, conn);
dtSetMyAuctionToday = new DataSet();

dtdptr.Fill(dtSetMyAuctionToday);

I don't know how to resolve it. I think it is connection Pooling issue, I don't know how to use pooling.

Any help will be appreciated. My application is already in a production environment.

Thanks & Best Regards,

[Cristian]

I think that is not the problem, because each time that an user enter to your web site a new session will be open. then a new connection is used and if you are filling a dataset with a connection2, the connection cannot be open.
Maybe the solution can be to use a stored procedure, because if the time to fill the dataset is too much the connection can be busy

[ahmarshi]

Hi Cristian,

Yes, you are right, when first user is getting served by Fill statement (connection is bussy) and at same time another user reaches Fill statement he gets error.

1. I declared my connection variable as shared, do you think still then a new variable will be declared?

2. Is there any other workaround for this problem, coz my limitation is I can not create stored procedures.

Thanks,

[mendhak]

Don't use a shared connection.

[ahmarshi]

Hi,

Thanks for the reply, I didn't do it yet, I will definitely try this workaround.

We used a shared connection variable to avoid too many connections to the DB. All users should only use one connection path, coz rightnow 20-30 users use this application, if this number gorws to 100-200 then there will be so many connections in case of concurrent database access.

What is your openion about it?

Thanks & Best Regards,

[mendhak]

Oracle is a large, scalable database that was meant to work with a huge number of connections. It should do the connection pooling automatically for you, you don't need to do it. The same applies to SQL Server.

In your code, you should only open the connection when you need it, then close it when you're done with it. This way you can avoid many simultaneous open connections.

[ahmarshi]

Hi,

Thanks for the comments.

Actually the application I am working on, is not the only application connecting to Oracle DB. There are so many other larger applications using it.

That is why I wanted to use limited number of connections from my application ( I think this is what we can control through Pooling but I don't know how to do it for Oledb).

2nd reason for using limited number of connections is DBA Team. We've to give them clear explanation of how many connections will my application use.

Can you provide me some code snippet or steps how can I implement conn. pooling for Oledb.

Thanks & Best Regards,

[ahmarshi]

Any comments?

thanks,