How do I close a database connection being used to produce a streaming result in a WCF service?

Question

I have been unable to find any documentation on properly closing database connections in WCF service operations. I have a service that returns a streamed response through the following method.

public virtual Message GetData()
{
    string sqlString = BuildSqlString();
    SqlConnection conn = Utils.GetConnection();
    SqlCommand cmd = new SqlCommand(sqlString, conn);
    XmlReader xr = cmd.ExecuteXmlReader();

    Message msg = Message.CreateMessage(
        OperationContext.Current.IncomingMessageVersion,
        GetResponseAction(),
        xr);

    return msg;
}

I cannot close the connection within the method or the streaming of the response message will be terminated. Since control returns to the WCF system after the completion of that method, I don't know how I can close that connection afterwards. Any suggestions or pointers to additional documentation would be appreciated.

Answer 1

Good question, actually. Unfortunately, one for which I believe there is no good answer. There's actually an active Microsoft Connect ticket about this very issue.

Normally, if you want to stream results and you just need a regular SqlDataReader, you can use the ExecuteReader overload that takes a CommandBehavior, and specifically CommandBehavior.CloseConnection. If a reader is created using this command behavior, then when you Close (or Dispose) the reader, it also closes the underlying connection, so you never have to worry about disposing the SqlConnection.

Unfortunately, there's no equivalent overload of ExecuteXmlReader. You have to dispose the SqlConnection explicitly.

One way around this would be to implement your own XmlReader descendant, wrapping the real XmlReader obtained from ExecuteXmlReader and forcing the connection closed on close.

The basic idea is just to derive from XmlReader and wrap both the real XmlReader and the SqlConnection itself. Something like this:

class SqlXmlReader : XmlReader
{
    private SqlConnection connection;
    private XmlReader reader;

    public SqlXmlReader(SqlCommand cmd)
    {
        if (cmd == null)
            throw new ArgumentNullException("cmd");
        this.connection = cmd.Connection;
        this.reader = cmd.ExecuteXmlReader();
    }

    public override void Close()
    {
        reader.Close();
        connection.Close();
    }
}

This takes the connection and the reader directly from the SqlCommand so there's no chance of a connection/reader mismatch. You need to implement the rest of the XmlReader methods and properties too - it's just a lot of boring method proxying:

    public override int AttributeCount
    {
        get { return reader.AttributeCount; }
    }

    public override string BaseURI
    {
        get { return reader.BaseURI; }
    }

    public override int Depth
    {
        get { return reader.Depth; }
    }

    public override bool EOF
    {
        get { return reader.EOF; }
    }

    public override string GetAttribute(int i)
    {
        return reader.GetAttribute(i);
    }

    public override string GetAttribute(string name, string namespaceURI)
    {
        return reader.GetAttribute(name, namespaceURI);
    }

    public override string GetAttribute(string name)
    {
        return reader.GetAttribute(name);
    }

    public override bool HasValue
    {
        get { return reader.HasValue; }
    }

    public override bool IsEmptyElement
    {
        get { return reader.IsEmptyElement; }
    }

    public override string LocalName
    {
        get { return reader.LocalName; }
    }

    public override string LookupNamespace(string prefix)
    {
        return reader.LookupNamespace(prefix);
    }

    public override bool MoveToAttribute(string name, string ns)
    {
        return reader.MoveToAttribute(name, ns);
    }

    public override bool MoveToAttribute(string name)
    {
        return reader.MoveToAttribute(name);
    }

    public override bool MoveToElement()
    {
        return reader.MoveToElement();
    }

    public override bool MoveToFirstAttribute()
    {
        return reader.MoveToFirstAttribute();
    }

    public override bool MoveToNextAttribute()
    {
        return reader.MoveToNextAttribute();
    }

    public override XmlNameTable NameTable
    {
        get { return reader.NameTable; }
    }

    public override string NamespaceURI
    {
        get { return reader.NamespaceURI; }
    }

    public override XmlNodeType NodeType
    {
        get { return reader.NodeType; }
    }

    public override string Prefix
    {
        get { return reader.Prefix; }
    }

    public override bool Read()
    {
        return reader.Read();
    }

    public override bool ReadAttributeValue()
    {
        return reader.ReadAttributeValue();
    }

    public override ReadState ReadState
    {
        get { return reader.ReadState; }
    }

    public override void ResolveEntity()
    {
        reader.ResolveEntity();
    }

    public override string Value
    {
        get { return reader.Value; }
    }

Dull, dull, dull, but it works. This reader will close the connection for you when it's done, same as a SqlDataReader opened with CommandBehavior.CloseConnection.

Last thing to do would be to create an extension method to make this easier to use:

public static class SqlExtensions
{
    public static XmlReader ExecuteSafeXmlReader(this SqlCommand cmd)
    {
        return new SqlXmlReader(cmd);
    }
}

Once you have this, instead of writing:

XmlReader xr = cmd.ExecuteXmlReader();

You write:

XmlReader xr = cmd.ExecuteSafeXmlReader();

That's it. Now when WCF closes your reader, it will automatically close the underlying connection.

(Disclaimer: This hasn't officially been tested, but I can see no reason why it wouldn't work, unless WCF does not actually close the reader. Be sure to actually test this against a live SQL connection to make sure that it really doesn't leak connections.)

Answer 2

You could try some form of a Duplex Service or Session based service. This would allow you to make the request in one call and keep the SqlConnection opened until a Disconnect() style call is made. This disconnect could .Dispose() of the related SQL objects.

Answer 3

You might look into making your service class implement the IDispose