The connection's current state is connecting

[webnegar]

Hi, im using asp.net core 2.1 and this is my sample code: `

    [HttpGet] [Route("dashboarddounatdata")]

    public IActionResult  Dashboarddounatdata()
    {
        var connection = new SqlConnection(_connectionString);
        var donutChart2 = new DonutChart2();
        try
        {
            connection.Open();  
            var parameters = new DynamicParameters();
            parameters.Add("AC1", dbType: DbType.Int32, direction: ParameterDirection.Output);
            parameters.Add("AC2", dbType: DbType.Int32, direction: ParameterDirection.Output);
            if (cnn.State == ConnectionState.Closed) cnn.Open();
            var data = cnn.Query("stp_chart_dounat_SELECT", parameters, commandType: CommandType.StoredProcedure);              
            donutChart2.AC1 = parameters.Get<int>("AC1");
            donutChart2.AC2 = parameters.Get<int>("AC2");
        }
        catch (Exception ex)
        {
            throw new Exception(String.Format("{0}.WithConnection()", GetType().FullName), ex);
        }
        finally
        {
            connection.Close();
        }
        return new OkObjectResult(donutChart2);
    }`

But sometimes (random) i get this error:

System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is connecting. at System.Data.SqlClient.SqlConnection.GetOpenTdsConnection(String method) at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) at System.Data.SqlClient.SqlCommand.ValidateCommand(Boolean async, String method) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1053 at Dapper.SqlMapper.<QueryImpl>d__1361.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.cs:line 1081 at System.Collections.Generic.List1.AddEnumerable(IEnumerable1 enumerable) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 723 at LabratoryApi.Controllers.ReportController.Getdashboardweeklycounttestconfirm() in C:\Users\New folder\Back-End\LabratoryApi\Controllers\ReportController.cs:line 213 --- End of inner exception stack trace ---

[mgravell]

Well, I can see you're calling Open() on the connection in multiple places, so this feels like an oddity in the SqlConnection itself. What are you connecting to here? Is it a regular SQL server on a LAN? Or something like SQL Azure?

On Tue, 17 Jul 2018, 08:27 Ehsan Mohsenpourian, [email protected] wrote:

Hi, im using asp.net core 2.1 and this is my sample code: ` // GET: api/values [HttpGet] [Route("dashboarddounatdata")] public IActionResult Dashboarddounatdata() { var connection = new SqlConnection(_connectionString); var donutChart2 = new DonutChart2(); try { connection.Open(); var parameters = new DynamicParameters(); parameters.Add("AC1", dbType: DbType.Int32, direction: ParameterDirection.Output); parameters.Add("AC2", dbType: DbType.Int32, direction: ParameterDirection.Output);

        if (cnn.State == ConnectionState.Closed) cnn.Open();
        var data = cnn.Query("stp_chart_dounat_SELECT", parameters, commandType: CommandType.StoredProcedure);

        donutChart2.AC1 = parameters.Get<int>("AC1");
        donutChart2.AC2 = parameters.Get<int>("AC2");

    }
    catch (Exception ex)
    {
        throw new Exception(String.Format("{0}.WithConnection()", GetType().FullName), ex);
    }
    finally
    {
        connection.Close();
    }
    return new OkObjectResult(donutChart2);
}`

But sometimes (random) i get this error:

System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is connecting. at System.Data.SqlClient.SqlConnection.GetOpenTdsConnection(String method) at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) at System.Data.SqlClient.SqlCommand.ValidateCommand(Boolean async, String method) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1053 at Dapper.SqlMapper.<QueryImpl>d__1361.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.cs:line 1081 at System.Collections.Generic.List1.AddEnumerable(IEnumerable1 enumerable) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 723 at LabratoryApi.Controllers.ReportController.Getdashboardweeklycounttestconfirm() in C:\Users\mehrparvar.ahmadreza\Desktop\New folder\Back-End\LabratoryApi\Controllers\ReportController.cs:line 213 --- End of inner exception stack trace --- at LabratoryApi.Controllers.ReportController.Getdashboardweeklycounttestconfirm() in C:\Users\mehrparvar.ahmadreza\Desktop\New folder\Back-End\LabratoryApi\Controllers\ReportController.cs:line 218 at lambda_method(Closure , Object , Object[] ) at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters) at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__12.MoveNext() --- End of stack trace from previous location where exception was thrown

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/StackExchange/Dapper/issues/1072, or mute the thread https://github.com/notifications/unsubscribe-auth/AABDsNTXZyGKTrLM9QcK4ovv04V2FN6aks5uHZHkgaJpZM4VSUeg .

[webnegar]

Im using SQL Server 2014 (with always-on enabled) in LAN.

[mgravell]

Ah, what is the difference between cnn and connection here? the connection you're opening is connection, but the connection you're using for the command is cnn, which I can't see declared.

edit: minor note, but using is usually preferable than a try/finally - even if you're explicitly trying to catch the exception, and: dapper will actually open (and re-close) the connection itself if it finds it is closed - you don't actually need to call Open when dapper is involved (unless you specifically want to have a connection open for multiple queries); the biggest confusion for me is simply: cnn vs connection

[jboarman]

I may or may not be encountering a similar issue that comes up only when I use the same connection multiple times, which happens when I call a service that receives an injected IDbConnection in its constructor to make a single call to QuerySingleOrDefault().

foreach (var sale in sales)
{
            _salesService.AddSale(sale);
}

The service code does NOT have any open/close/dispose handling since Dapper takes care of that for me as I understand it.

I don't see any threading concerns in my scenario as all the code in this situation is synchronous. However, it is strange that the code works fine if there is only 1 item but fails reliably so far when there are 3 or so items.

The resulting exception stack trace looks like this:

System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is connecting.
   at System.Data.SqlClient.SqlConnection.GetOpenTdsConnection(String method)
   at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
   at System.Data.SqlClient.SqlCommand.ValidateCommand(Boolean async, String method)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1053
   at Dapper.SqlMapper.QueryRowImpl[T](IDbConnection cnn, Row row, CommandDefinition& command, Type effectiveType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1179
   at Dapper.SqlMapper.QuerySingleOrDefault[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 803

[longletian]

Is there a solution to this problem？ I met it, too {"ExecuteReader requires an open and available Connection. The connection's current state is connecting."}

[mgravell]

If the status is opening, it sounds like perhaps OpenAsync or BeginOpen was called and not awaited, and then the incomplete/open connection has handed down. Can you show your connection opening code?

[OuttaSpaceTime]

for me the problem was that is was using a lambda foreach which was not running truely async and obscured the connections. some other issue could come when you try to use the same connection within one method when it is passed with IDbConnection as Service.

[bang75]

I am also started to get exactly this problem (on Azure) without (what I can think of) any code changes except maybe a Dapper update. Also reusing IDbConnection and doing lambdas. Is this an error somewhere or is the problem in my code? If so, what do I need to change / be aware of?

[macamedyousuf-lang]

try { if(con.State!=ConnectionState.Open) con.Open();

            string searchValue = txtsearch.Text.Trim();
            string query = "select *from Orders WHERE OrderID = @search OR OrderID LIKE @searchName";

            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@search", searchValue);
            cmd.Parameters.AddWithValue("@searchName", "%" + searchValue + "%");
            
            SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                txtorderID.Text = dr["OrderID"].ToString();
                dtbOrderDate.Text = dr["OrderDate"].ToString();
                txtcustomerID.Text = dr["CustomerID"].ToString();
                txtproductID.Text = dr["ProductID"].ToString();
                txtquantity.Text = dr["Quantity"].ToString();
                txtCustomerName.Text = dr["customername"].ToString();
                txtProductName.Text = dr["productname"].ToString();
                txtPrice.Text = dr["price"].ToString();
                txtTotal.Text = dr["Total"].ToString();
            }

            else
            {
                MessageBox.Show("No record found.");
            }

            dr.Close();
            con.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error: " + ex.Message);
        }