How to close the database connection that is used to create the streaming result in the WCF service?

Question

I was unable to find documentation on properly closing database connections in WCF service operations. I have a service that returns a streaming response using 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 in this method or the streaming response message will be terminated. Since control returns to the WCF system after the completion of this method, I do not know how I can close this connection afterwards. Any suggestions or pointers to additional documentation would be appreciated.

Answer 1

, . , , . Microsoft Connect .

, , SqlDataReader, ExecuteReader, a CommandBehavior, CommandBehavior.CloseConnection. , , Close ( Dispose) , , SqlConnection.

, ExecuteXmlReader. SqlConnection.

- XmlReader, XmlReader, ExecuteXmlReader, .

, XmlReader XmlReader, SqlConnection. - :

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();
    }
}

SqlCommand, /. XmlReader - :

    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; }
    }

, , , . , , , SqlDataReader, CommandBehavior.CloseConnection.

, , - , :

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

, :

XmlReader xr = cmd.ExecuteXmlReader();

:

XmlReader xr = cmd.ExecuteSafeXmlReader();

. , WCF , .

( : , , , WCF . SQL-, .)

Answer 2

- Duplex Service Session. SqlConnection , Disconnect(). .Dispose() SQL.

Answer 3

, IDispose