Re: Database connection pool thr application - C# Discussion Boards

2-Apr-12 18:16

Hi...
In my web application, we are using oracle database. my question is connection pool based on web application. Our application end user around 500. for these which connection is better i) create new connection for every request of user. ii) single connection pool via singleton object of the class iii) session based connection pool ( each user has separate connection pool)
which principle is using for web application? can please give suggestion or valuable link to me

--Thanks

Richard MacCutchan2-Apr-12 21:26

Richard MacCutchan

2-Apr-12 21:26

I think you would get a better response by posting this in the Database forum; there is nothing in your question about C#.

Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman

Eddy Vluggen3-Apr-12 2:04

3-Apr-12 2:04

It already in the WebDevelopment[^] forum. Do we really need yet another copy of the question? $Shucks | :-\$

Bastard Programmer from Hell Suspicious | :suss:

Richard MacCutchan3-Apr-12 5:45

Richard MacCutchan

3-Apr-12 5:45

Put there after my suggestion; check the times.

Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman

Eddy Vluggen3-Apr-12 1:54

3-Apr-12 1:54

anishkannan wrote:
i) create new connection for every request of user.

That's what I'm doing; no problems with connections that are still open/closed/busy. Works quite fast, since connection-objects are returned to the pool.

Create a connection, create a command, execute.

anishkannan wrote:
ii) single connection pool via singleton object of the class

Huh?

anishkannan wrote:
iii) session based connection pool ( each user has separate connection pool)

I don't think that every user needs a pool of connections.

Bastard Programmer from Hell Suspicious | :suss:

anishkannan3-Apr-12 2:04

3-Apr-12 2:04

Thanks for reply...

Your suggested option is the open statement execution then close;

In Case, the single web request has multiple open and close; it have any problem?

i face this : If access multiple user in many days, some times got exception as maximum pool reached in database side...

Singleton : i mean that, single open at the time of first ; all statement execution then close

Pete O'Hanlon3-Apr-12 2:13

Pete O'Hanlon

3-Apr-12 2:13

If you're using the ODP.NET libraries, you could be hitting the maximum pool issue because you have unclosed connections - make sure you dispose of your connections as early as possible. Basically, the standard technique is to open the connection as late as possible and dispose of it as early as possible. We had a system with a 1000 users all hitting the Oracle server, and we never needed more than 10 connections in the pool to service them because of this simple mantra.

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

anishkannan3-Apr-12 2:30

3-Apr-12 2:30

We are using our own data access layer. which has

<pre lang="c#">
   public DataSet ExecuteProcedure(string strProcedure, Hashtable hashTableParameters, string tableName, ref DataSet datasetToFill)
        {
            try
            {
                Open();//  objConnection = new OracleConnection(strConnection);
                // Procedure Execution 
                // data fill to dataset 
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            catch (Exception e)
            {
                
            }
            finally
            {
                Close(); // objConnection.Close();
            }

            return datasetToFill;
        }

Please tell me. if anything wrong in this ( i need correct in this code if has any mistaken )

we are using oracle sql developer client software. how check the connection pool in oracle db.

Eddy Vluggen3-Apr-12 2:41

3-Apr-12 2:41

using (objConnection = new OracleConnection(strConnection))
{
  // do your stuff here
  using (objCommand = objConnection.CreateCommand())
  {
  }
}
// no need to close or dispose.

Bastard Programmer from Hell Suspicious | :suss:

anishkannan3-Apr-12 2:45

3-Apr-12 2:45

Okay thanks!!!

Eddy Vluggen3-Apr-12 2:50

3-Apr-12 2:50

My pleasure Smile | :)

Pete O'Hanlon3-Apr-12 3:10

Pete O'Hanlon

3-Apr-12 3:10

Well, I can point out a couple of things here.

anishkannan wrote:
catch (OracleException ex)
{
throw ex;
}

Completely useless code. Never catch and throw without doing something with it.

anishkannan wrote:
catch (Exception e)
{

}

You're just consuming the exception here. You have no way of knowing what's going wrong in your code now.

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

Eddy Vluggen3-Apr-12 2:39

Retrieve Value from scanner

3-Apr-12 2:39

anishkannan wrote:
In Case, the single web request has multiple open and close; it have any problem?

Not that I know of; I have an application that's opening and closing connections at a high rate, and it simply works as advertised.

anishkannan wrote:
Singleton : i mean that, single open at the time of first ; all statement execution then close

That would keep the connection alive for longer than it's required, hogging the amount of available connection-objects.

anishkannan wrote:

i face this : If access multiple user in many days, some times got exception as maximum pool reached in database side...

How long do you keep the connection "open"? If you keep the connection around too long, the pool will be empty quite fast.

Bastard Programmer from Hell Suspicious | :suss:

Reena Aleesya2-Apr-12 16:34

Reena Aleesya

2-Apr-12 16:34

Hi guys,
Currently, i've develop window based application using employee card to retrieve information and data once scan the card. The card id will merge with employee no and will display into page.
Below is my code:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
             If TextBox1.Text = True Then
           
           Dim CardID As String = TextBox1.Text
           Dim endID As String = CardID.Substring(5)
           con = New SqlConnection("Data Source=10.1.2.215;Initial Catalog=eExitPass;Persist Security Info=True;User ID=sa1;Password=sa1")
           con.Open()
           a = Val(TextBox1.Text)
           sql = "select D.CardID, A.EmpNo, convert(varchar,A.pplyDate,100) As ApplyDate, C.Status, " & _
                          "from " & _
                          "EmpDetails A left join Clock_Time D on A.EmpNo = D.EmpNo left join Status C on A. Status = C.StatusID where A.EmpNo='" & TextBox2.Text & "' "
          
           da.Fill(ds, "db1")
           MsgBox("Database is now open")

           con.Close()
           MsgBox("Database is now Closed")
       Else
           MsgBox("Incorrect Serial Number")
       End If
   End Sub

Thanks in advance
Ms Aleesya Shanngari

Abhinav S2-Apr-12 17:14

Abhinav S

2-Apr-12 17:14

Your code here is reading from a database and has nothing to do with a scanner.
Check the scanner documentation for details on the API.

Also, request you not to crosspost. A similar question has been asked in the Q&A section.

Luc Pattyn2-Apr-12 17:34

Luc Pattyn

2-Apr-12 17:34

Abhinav S wrote:
A similar question has been asked in the Q&A section

Maybe someone overthere speaks the right dialect.

Big Grin | :-D

Luc Pattyn [My Articles] Nil Volentibus Arduum

Abhinav S2-Apr-12 21:24

Abhinav S

2-Apr-12 21:24

Most likely. That does not mean the OP is going to get the answer there though.
Big Grin | :-D

Richard MacCutchan2-Apr-12 21:28

Richard MacCutchan

2-Apr-12 21:28

Reena Aleesya wrote:
Below is my code:

VB.NET and this is the C# forum!

Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman