Dispose object that has been instantiated as metho

问题:

I have the following classes:

private static readonly string ConnectionString = "Dummy";
public static SqlConnection GetConnection()
{
    SqlConnection Connection = new SqlConnection(ConnectionString);
    return Connection;
}

public static SqlDataAdapter GetDataAdapter(string Query)
{
    SqlDataAdapter Adapt = new SqlDataAdapter(Query, GetConnection());
    return Adapt;
}

• How do I dispose the SqlConnection object that is instantiated when GetConnection() is passed as parameter in my SqlDataAdapter constructor?
• Will it get disposed automatically when I dispose my Adapt object in the method that called GetDataAdapter()?
• If it's not possible to dispose it, how do you suggest to proceed?

Thanks for any help.

回答1:

Description

If you dispose your SqlDataAdapter it does not dispose the SqlConnection too because its not clear if you want to use the connection again. You have to change your design to get this done.

I suggest to pass the SqlConnection to the GetDataAdapter function.

Sample

static void Main(string[] args)
{ 
    using (SqlConnection connection = GetConnection()) 
    {
        using (SqlDataAdapter adapter = GetDataAdapter("YourQuery", connection)) 
        {

        }
        // SqlDataAdapter is disposed
    }
    // SqlConnection is disposed
}

private static readonly string ConnectionString = "Dummy";
public static SqlConnection GetConnection()
{
    SqlConnection Connection = new SqlConnection(ConnectionString);
    return Connection;
}

public static SqlDataAdapter GetDataAdapter(string Query, SqlConnection connection)
{
    SqlDataAdapter Adapt = new SqlDataAdapter(Query, connection);
    return Adapt;
}

回答2:

No, the adapter does not dipose the connection. You should change it to this at least:

public static SqlDataAdapter GetDataAdapter(SqlConnection connection, string Query)
{
    SqlDataAdapter Adapt = new SqlDataAdapter(Query);
    Adapt.Connection = connection;
    return Adapt;
}

and use it like this

using (var connection = GetConnection())
using (var adapter = GetAdapter(connection, query))
{
    // do stuff
}

This way you are also more flexible by being able to pass some other connection in - in case you need it for some exceptional circustances.