sql server - Execute a reader while still playing nice with the currently managed connection - Stack Overflow

admin管理员组

文章数量:1289384

When having to forego EF Core and do some ADO operations against the database, how do you safely use the EF Core managed connection? For example, I need to execute a reader. Currently this is what I do:

var cmd = DataContext.Database.GetDbConnection().CreateCommand();
cmd.CommandText = sqlToUse;
cmd.CommandType = CommandType.Text;

using (cmd)
{
    if (cmd.Connection.State == ConnectionState.Closed)
        cmd.Connection.Open();
        
    if (DataContext.Database.CurrentTransaction != null)
        cmd.Transaction = DataContext.Database.CurrentTransaction.GetDbTransaction();
        
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            // Do the things
        }
    }
}

Is there anything that I need to do with the connection afterwards whether or not I've opened the connection?

When having to forego EF Core and do some ADO operations against the database, how do you safely use the EF Core managed connection? For example, I need to execute a reader. Currently this is what I do:

var cmd = DataContext.Database.GetDbConnection().CreateCommand();
cmd.CommandText = sqlToUse;
cmd.CommandType = CommandType.Text;

using (cmd)
{
    if (cmd.Connection.State == ConnectionState.Closed)
        cmd.Connection.Open();
        
    if (DataContext.Database.CurrentTransaction != null)
        cmd.Transaction = DataContext.Database.CurrentTransaction.GetDbTransaction();
        
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            // Do the things
        }
    }
}

Is there anything that I need to do with the connection afterwards whether or not I've opened the connection?

• sql-server
• entity-framework-core
• ado
• ef-core-8.0

Share Improve this question Follow edited Feb 19 at 19:06 Thom A 96k1111 gold badges6060 silver badges9292 bronze badges asked Feb 19 at 18:47 Jason ButeraJason Butera 2,46033 gold badges3131 silver badges4848 bronze badges 1

• Related: github/dotnet/efcore/issues/7810 (a tad too long for me to read all and summarize) – Peter B Commented Feb 19 at 18:55

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

If EF is responsible for providing your connection, EF will dispose of it after use. If you close or dispose the connection, your DataContext will most likely not work as you expect.

I don't think it's necessary to check for ConnectionState == Open, I probably wouldn't unless I see problems related to it

check this link

Assuming you didn't pass an existing connection to the DbContext constructor, if you Open a connection it will remain open for the life of the DbContext and be closed with the DbContext is Disposed.

You may Close the connection in your method, and EF will re-open it as necessary, or just leave it open. Just don't Dispose() the connection.

本文标签： sql serverExecute a reader while still playing nice with the currently managed connectionStack Overflow