Use of using statement for getting dbcontext connection object

Question

I am working on a report and wanted to generate an excel file from data. I have created a stored procedure which returns multiple datasets and because I am using EF which only supports single dataset, I opted to use below code

using (var connection = mydbcontext.Database.Connection)
{
    connection.Open();
    var command = connection.CreateCommand();
    command.CommandText = string.Format(@"EXEC prDailyRouteReport 
                                 {0}",refId);

    using (var reader = command.ExecuteReader())
    {
       //reading data and fetch next result
    }
    connection.Close()
}

Now the issue is, when I need to call two separate reports within a single web request the connection variable returns empty connection string on the second report. but I didn't get the issue if I remove the using block. I want to know why im getting empty connection string with using statement.

Do you really need EF for this? Can you just open SqlConnection and do good old SqlCommand? — trailmax
– trailmax, Commented Jul 26, 2019 at 14:00

gmiley · Accepted Answer · 2019-07-26 13:54:43Z

1

The using statement in c# can only be called when instantiating an object that implements the IDisposable interface, which means that the resultant instance can be disposed. This is what happens when code execution leaves the closing bracket of the using code block. The object instantiated within the using statement, in this case your connection variable, gets disposed, and any existing variable references will by trying to access a null reference unless it is reinstantiated first.

Additionally, you seem to be running into a threading issue where you are trying to perform operations on the same variable in different threads. The issue you are seeing is just a symptom of that. You need to address accessing your variables in multiple threads and you won't have this issue.

answered Jul 26, 2019 at 13:54

gmiley

6,6041 gold badge15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RobertBaron · Accepted Answer · 2019-07-26 13:55:53Z

This is beacause the connection is available only within the scope of the using. Just run the second report inside the using.

using (var connection = mydbcontext.Database.Connection)
{
    connection.Open();
    var command = connection.CreateCommand();
    command.CommandText = string.Format(@"EXEC prDailyRouteReport 
                                 {0}",refId);

    using (var reader = command.ExecuteReader())
    {
       //reading data and fetch next result
    }

    command = connection.CreateCommand();
    command.CommandText = string.Format(@"EXEC prDailyRouteReport2 
                                 {0}",refId);

    using (var reader = command.ExecuteReader())
    {
       //reading data and fetch next result
    }

    connection.Close()
}

Collectives™ on Stack Overflow

Use of using statement for getting dbcontext connection object

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related