Hi, I have an issue with following code. When this code is executed it shows open connection in my database of Sql Server. I run the 'EXEC SP_WHO' in sql query analyzer. How to actually close reader and connection? Please help me.....

    protected void button1_Click(object sender, EventArgs e)
    {
        SqlDataReader dr;
        dr = GetReader("2");
        GridView1.DataSource = dr;
        GridView1.DataBind();
        dr.Close();

        // same code continues for different GetReader many times

    }

    private SqlDataReader GetReader(string strTran)
    {
        SqlConnection con = new SqlConnection(MyConnectionString);

        SqlCommand cmd = new SqlCommand("Reports", con);
        cmd.CommandType = CommandType.StoredProcedure;

        //do something here.

        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        return dr;
    }

Use the below which will close the connection once the data is fetchted:

SqlDataReader dr = cmd.ExecuteReaderCommandBehavior.CloseConnection);

Regards
Mike

Don't expect too much, too soon.

closing connection is necessary otherwise it will throw connection pooling problem otherwise (not very good idea), for temporary purpose, alter web.config file and write max pool size=40000 say in connection string, but closing connection is best approach.

thanks......