Does SqlCommand.ExecuteReader ever automatically open the db connection?

I am noticing some odd behavior on my web page. I have a WCF Data service that serves up JSON to fill up a jqGrid. The service is called using javascript/ajax.

Then I have some server-side code that also calls the same WCF service to get data.

Inside my WCF Service I run cmd.ExecuteReader() without a prior open connection, and it doesn't complain about the connection in certain cases -- it seems to be when I'm calling the data service from javascript. However when I call the service from code-behind, ie server-side, I get the error that says "ExecuteReader requires an open and available connection".

Does anyone know about this issue? Ive narrowed it down as much as possible. It appears the only difference is whether I call the service from client side or server side. Here is my code:

[ServiceContract(Namespace = "")]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class JsonService
{
    static String _connection = ConfigMgr.ConnectionStrings["MyConnStr"];
    static DomainEntities dbContext = new DomainEntities(_connection);

    [OperationContract]
    [WebInvoke(Method = "POST", BodyStyle = WebMessageBodyStyle.WrappedRequest,
                         RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json)]
    public JsonGrid Get()
    {
        return new JsonGridContract(dbContext.Products.ToJson()); 
    }
}

Below is where ExecuteReader() is called. It intercepts the provider, converts the expression tree to SQL, and then executes it parsing the results to string-based JSON rather than the domain objects.

    public static List<JsonRow> ToJson(this IQueryable queryable)
    {
        Expression expression = queryable.Expression;
        expression = Evaluator.PartialEval(expression);

        if !(queryable.Provider is JsonQueryProvider)
              throw new InvalidOperationException("Provider is invalid");

        String table = (queryable.Provider as JsonQueryProvider).Table; 

        SqlConnection connection = (queryable.Provider as JsonQueryProvider).Connection; 

        Type elementType = TypeSystem.GetElementType(expression.Type);

        TranslateResult result = new QueryTranslator(table).Translate(expression);

        SqlCommand cmd = connection.CreateCommand();

        cmd.CommandText = result.CommandText;

        SqlDataReader reader = cmd.ExecuteReader();

        List<JsonRow> jsonResult = new List<JsonRow>(); 

        while (reader.Read())
            {
                JsonRow instance = new JsonRow(reader.FieldCount); 

                for (int i = 0, n = reader.FieldCount; i < n; i++)
                {
                    var items = instance.Items; 

                    if (reader.IsDBNull(i))
                    {
                        items[i] = string.Empty;  
                    }
                    else
                    {
                        items[i] = reader[i].ToString(); 
                    }
                }

                jsonResult.Add(instance); 
            }

        reader.Close(); 

        return jsonResult; 
    }

As noted before this method executes fine even though I never open the connection. I am using AJAX client-side,

  $.ajax(
            {
                type: "POST",
                contentType: "application/json; charset=utf-8",
                url: "Services/JsonService.svc/Get",
                data: {},
                dataType: "json",
                success: function (data, textStatus) {
                    if (textStatus == "success") {
                        var thegrid = $("#jqGrid")[0];
                        thegrid.addJSONDat开发者_运维技巧a(data.d);
                    }
                },
                error: function (data, textStatus) {
                    alert('An error has occured retrieving data.');
                }
            });
        }

First thing to exclude, is that you don't dispose any of your objects. Your SqlCommand, SqlConnection, and SqlDataReader should all be in using stmt blocks. You don't need your explicit close then. See if this then fails across the board. Im curious if its just that the garbage collector hasn't collected yet to kill your connection object.

using(SqlConnection connection = new SqlConnection(...))
{
...
}

The issue is that your SqlReader will keep the Connection occupied while it is in use. You can't use the connection for anything else (such as executing another reader, or even another statement). Until the reader is closed, your connection is essentially locked.

Typically, like Adam says, wrap your statements in a using block, and your issue will be solved. However, I notice you seem to be grabbing your connection from a cache of some sort (queryable.Provider). If you are attempting to use your connection repetitively, or caching it, you will run into the same issue.

If you want to take advantage of ADO.NET's connection pooling abilities, you need to create a new Connection instance inside your using block, and only reuse the EXACT same connection string. ADO.NET will pool the underlying connection resources automatically.