Passing around open readers

Discussion in 'ASP .Net' started by blue, Jan 23, 2004.

  1. blue

    blue Guest

    We often get connection pooling errors saying that there are no available
    connections in the pool.

    I think the problem is that we are passing around open readers all over the
    place. I am planning on changing this in our code and I expect this to fix
    our problem.

    We have our connection pooling set to the default number of connections
    open. We probably have about 3-7 users concurrently using our web site.
    So, the problem isn't that we have too many users.

    If passing open readers to methods is causing our problem, my question is
    why? Is it making a copy of the reader when it gets passed around and the
    copy isn't getting closed?

    We are doing this:

    public void method1()
    {
    SqlDataReader reader = SqlHelper.ExecuteReader(...);

    method2(reader);

    reader.Close();
    }

    We are closing the reader on the calling side but I suspect that a new copy
    of the open reader is staying open in the called method.

    What do you think? Am I on the right track?

    Thanks,

    blue
     
    blue, Jan 23, 2004
    #1
    1. Advertising

  2. blue,
    Did you close the SqlConnection object as well?


    Tu-Thach

    ----- blue wrote: -----

    We often get connection pooling errors saying that there are no available
    connections in the pool.

    I think the problem is that we are passing around open readers all over the
    place. I am planning on changing this in our code and I expect this to fix
    our problem.

    We have our connection pooling set to the default number of connections
    open. We probably have about 3-7 users concurrently using our web site.
    So, the problem isn't that we have too many users.

    If passing open readers to methods is causing our problem, my question is
    why? Is it making a copy of the reader when it gets passed around and the
    copy isn't getting closed?

    We are doing this:

    public void method1()
    {
    SqlDataReader reader = SqlHelper.ExecuteReader(...);

    method2(reader);

    reader.Close();
    }

    We are closing the reader on the calling side but I suspect that a new copy
    of the open reader is staying open in the called method.

    What do you think? Am I on the right track?

    Thanks,

    blue
     
    =?Utf-8?B?VHUtVGhhY2g=?=, Jan 23, 2004
    #2
    1. Advertising

  3. blue

    Sharon Guest

    In C# the default for mathod parameter is "by value".
    This means that the reader is copyed and holds the connection open.
    To pass the parameter "by reference" add ref to the method definition:
    public void method2(ref SqlDataReader dr)
    Hope this helps.
    Sharon.

    "blue" <> wrote in message
    news:...
    > We often get connection pooling errors saying that there are no available
    > connections in the pool.
    >
    > I think the problem is that we are passing around open readers all over

    the
    > place. I am planning on changing this in our code and I expect this to

    fix
    > our problem.
    >
    > We have our connection pooling set to the default number of connections
    > open. We probably have about 3-7 users concurrently using our web site.
    > So, the problem isn't that we have too many users.
    >
    > If passing open readers to methods is causing our problem, my question is
    > why? Is it making a copy of the reader when it gets passed around and the
    > copy isn't getting closed?
    >
    > We are doing this:
    >
    > public void method1()
    > {
    > SqlDataReader reader = SqlHelper.ExecuteReader(...);
    >
    > method2(reader);
    >
    > reader.Close();
    > }
    >
    > We are closing the reader on the calling side but I suspect that a new

    copy
    > of the open reader is staying open in the called method.
    >
    > What do you think? Am I on the right track?
    >
    > Thanks,
    >
    > blue
    >
    >
     
    Sharon, Mar 6, 2004
    #3
  4. blue

    Teemu Keiski Guest

    Hi,

    Reference types (which SqlDataReader is) passed as parameter are always
    passing a copy of reference to the method, despite do you have ref keyword
    or not With reference type the ultimate result is the same if you call
    methods or change properties, though ref keyword has impact on if you can
    reassign the original reference (without ref keyword you can impact on the
    object by accessing members but you can't reassign the reference e.g it has
    no effect outside the method, with ref keyword, you can do that too).

    In this case it means that the SqlDataReader object is *not* copied but the
    reference to it is. When accessing the copied reference, the copy is
    indistinguishable from the original reference. So if you call Close on
    another reference, it is closed for all references (as it is one and the
    same underlying object). So it shouldn't be the reason here.

    --
    Teemu Keiski
    MCP, Microsoft MVP (ASP.NET), AspInsiders member
    ASP.NET Forum Moderator, AspAlliance Columnist

    "Sharon" <> wrote in message
    news:...
    In C# the default for mathod parameter is "by value".
    This means that the reader is copyed and holds the connection open.
    To pass the parameter "by reference" add ref to the method definition:
    public void method2(ref SqlDataReader dr)
    Hope this helps.
    Sharon.

    "blue" <> wrote in message
    news:...
    > We often get connection pooling errors saying that there are no available
    > connections in the pool.
    >
    > I think the problem is that we are passing around open readers all over

    the
    > place. I am planning on changing this in our code and I expect this to

    fix
    > our problem.
    >
    > We have our connection pooling set to the default number of connections
    > open. We probably have about 3-7 users concurrently using our web site.
    > So, the problem isn't that we have too many users.
    >
    > If passing open readers to methods is causing our problem, my question is
    > why? Is it making a copy of the reader when it gets passed around and the
    > copy isn't getting closed?
    >
    > We are doing this:
    >
    > public void method1()
    > {
    > SqlDataReader reader = SqlHelper.ExecuteReader(...);
    >
    > method2(reader);
    >
    > reader.Close();
    > }
    >
    > We are closing the reader on the calling side but I suspect that a new

    copy
    > of the open reader is staying open in the called method.
    >
    > What do you think? Am I on the right track?
    >
    > Thanks,
    >
    > blue
    >
    >
     
    Teemu Keiski, Mar 6, 2004
    #4
  5. blue

    Teemu Keiski Guest

    Just to add, that I still wouldn't pass DataReaders through methods just
    like that, because it might make things more complicated from error handling
    standpoint (reader and db connection must be closed in error situations as
    well). Therefore at least try...catch...finally block would be good when
    passing the DataReader to the method and make sure that Close is called in
    finally.

    --
    Teemu Keiski
    MCP, Microsoft MVP (ASP.NET), AspInsiders member
    ASP.NET Forum Moderator, AspAlliance Columnist


    "Teemu Keiski" <> wrote in message
    news:...
    Hi,

    Reference types (which SqlDataReader is) passed as parameter are always
    passing a copy of reference to the method, despite do you have ref keyword
    or not With reference type the ultimate result is the same if you call
    methods or change properties, though ref keyword has impact on if you can
    reassign the original reference (without ref keyword you can impact on the
    object by accessing members but you can't reassign the reference e.g it has
    no effect outside the method, with ref keyword, you can do that too).

    In this case it means that the SqlDataReader object is *not* copied but the
    reference to it is. When accessing the copied reference, the copy is
    indistinguishable from the original reference. So if you call Close on
    another reference, it is closed for all references (as it is one and the
    same underlying object). So it shouldn't be the reason here.

    --
    Teemu Keiski
    MCP, Microsoft MVP (ASP.NET), AspInsiders member
    ASP.NET Forum Moderator, AspAlliance Columnist

    "Sharon" <> wrote in message
    news:...
    In C# the default for mathod parameter is "by value".
    This means that the reader is copyed and holds the connection open.
    To pass the parameter "by reference" add ref to the method definition:
    public void method2(ref SqlDataReader dr)
    Hope this helps.
    Sharon.

    "blue" <> wrote in message
    news:...
    > We often get connection pooling errors saying that there are no available
    > connections in the pool.
    >
    > I think the problem is that we are passing around open readers all over

    the
    > place. I am planning on changing this in our code and I expect this to

    fix
    > our problem.
    >
    > We have our connection pooling set to the default number of connections
    > open. We probably have about 3-7 users concurrently using our web site.
    > So, the problem isn't that we have too many users.
    >
    > If passing open readers to methods is causing our problem, my question is
    > why? Is it making a copy of the reader when it gets passed around and the
    > copy isn't getting closed?
    >
    > We are doing this:
    >
    > public void method1()
    > {
    > SqlDataReader reader = SqlHelper.ExecuteReader(...);
    >
    > method2(reader);
    >
    > reader.Close();
    > }
    >
    > We are closing the reader on the calling side but I suspect that a new

    copy
    > of the open reader is staying open in the called method.
    >
    > What do you think? Am I on the right track?
    >
    > Thanks,
    >
    > blue
    >
    >
     
    Teemu Keiski, Mar 6, 2004
    #5
  6. blue

    Sharon Guest

    Thanks for the important correction.

    "Teemu Keiski" <> wrote in message
    news:...
    > Hi,
    >
    > Reference types (which SqlDataReader is) passed as parameter are always
    > passing a copy of reference to the method, despite do you have ref keyword
    > or not With reference type the ultimate result is the same if you call
    > methods or change properties, though ref keyword has impact on if you can
    > reassign the original reference (without ref keyword you can impact on the
    > object by accessing members but you can't reassign the reference e.g it

    has
    > no effect outside the method, with ref keyword, you can do that too).
    >
    > In this case it means that the SqlDataReader object is *not* copied but

    the
    > reference to it is. When accessing the copied reference, the copy is
    > indistinguishable from the original reference. So if you call Close on
    > another reference, it is closed for all references (as it is one and the
    > same underlying object). So it shouldn't be the reason here.
    >
    > --
    > Teemu Keiski
    > MCP, Microsoft MVP (ASP.NET), AspInsiders member
    > ASP.NET Forum Moderator, AspAlliance Columnist
    >
    > "Sharon" <> wrote in message
    > news:...
    > In C# the default for mathod parameter is "by value".
    > This means that the reader is copyed and holds the connection open.
    > To pass the parameter "by reference" add ref to the method definition:
    > public void method2(ref SqlDataReader dr)
    > Hope this helps.
    > Sharon.
    >
    > "blue" <> wrote in message
    > news:...
    > > We often get connection pooling errors saying that there are no

    available
    > > connections in the pool.
    > >
    > > I think the problem is that we are passing around open readers all over

    > the
    > > place. I am planning on changing this in our code and I expect this to

    > fix
    > > our problem.
    > >
    > > We have our connection pooling set to the default number of connections
    > > open. We probably have about 3-7 users concurrently using our web site.
    > > So, the problem isn't that we have too many users.
    > >
    > > If passing open readers to methods is causing our problem, my question

    is
    > > why? Is it making a copy of the reader when it gets passed around and

    the
    > > copy isn't getting closed?
    > >
    > > We are doing this:
    > >
    > > public void method1()
    > > {
    > > SqlDataReader reader = SqlHelper.ExecuteReader(...);
    > >
    > > method2(reader);
    > >
    > > reader.Close();
    > > }
    > >
    > > We are closing the reader on the calling side but I suspect that a new

    > copy
    > > of the open reader is staying open in the called method.
    > >
    > > What do you think? Am I on the right track?
    > >
    > > Thanks,
    > >
    > > blue
    > >
    > >

    >
    >
    >
     
    Sharon, Mar 6, 2004
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Andrew Kidd
    Replies:
    3
    Views:
    495
    Andrew Kidd
    Apr 22, 2004
  2. Jesse Liberty
    Replies:
    2
    Views:
    334
  3. Jesse Liberty

    Alpha Readers Wanted

    Jesse Liberty, Apr 7, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    355
    Jesse Liberty
    Apr 7, 2005
  4. Piet
    Replies:
    0
    Views:
    587
  5. Network/Software Buyer
    Replies:
    0
    Views:
    441
    Network/Software Buyer
    May 23, 2010
Loading...

Share This Page