CODEWITHSUNDEEP
c#.netasp.netsql-serverado.net
LCJ
LCJ

Reputation: 22661

Pooling for DbDataReader

We have a DbDataReader for connecting with SQL Server 2005 from our application. I would like to know how to set the following two for the DbDataReader

1) Pooling = false

2) Connection Timeout=0;

Please share your thoughts.

    DbCommand cmd = db.GetStoredProcCommand("aspInsertZipCode");
    cmd.CommandTimeout = 0;
    dataStringToProcess.Remove(dataStringToProcess.Length - 1, 1);

    db.AddInParameter(cmd, "@DataRows", DbType.String, dataStringToProcess.ToString());
    db.AddInParameter(cmd, "currDate", DbType.DateTime, DateTime.Now);
    db.AddInParameter(cmd, "userID", DbType.Int32, UserID);
    db.AddOutParameter(cmd, "CountOfUnchangedZipCode", DbType.String, 1000);

    DbDataReader rdr = null;

    try
    {

        rdr = (DbDataReader)db.ExecuteReader(cmd);
        if (rdr.Read())
        {

            if (!String.IsNullOrEmpty(Utility.GetString(rdr, "NewZipCode")))
                strNewZipCode = strNewZipCode + "," + Utility.GetString(rdr, "NewZipCode");

        }
        rdr.NextResult();
        if (rdr.Read())
        {

            strRetiredZipCode = strRetiredZipCode + "," + Utility.GetString(rdr, "RetiredZipCode");

        }

        int TempUnchageZipCount = Convert.ToInt32(db.GetParameterValue(cmd, "CountOfUnchangedZipCode"));
        CountOfUnchangedZipCode = CountOfUnchangedZipCode + TempUnchageZipCount;
        dataStringToProcess = new StringBuilder();
        cntRec = 0;

    }
    catch
    {
        if (rdr != null && (!rdr.IsClosed))
            rdr.Close();
        throw;
    }
    finally
    {
        if (rdr != null && (!rdr.IsClosed))
            rdr.Close();
    }
    cmd.Dispose();

Thanks

Upvotes: 0

Views: 224

Answers (1)

Matthew Abbott
Matthew Abbott

Reputation: 61617

Connection pooling is handled by the specific provider. You're using a DbConnection which is the abstraction, but for instance, Sql Server handles connection pooling and timeout through the connection string, e.g.:

SERVER=127.0.0.1;Database=MyDatabase;Integrated Security=true;Timeout=0;Pooling=false;

What actually provider are you using?

On another note, what reason would you need for disabling Connection pooling and timeout? Both features are efficiency features which greatly improve how your app performs. By disabling these, and having some bad behaving code, you could end up with quite an unstable end solution, which hogs memory and may lead to unterminated connections....?

Upvotes: 4

Related Questions