Question posted in
The official documentation can be found here.

Asp.net – SQL Server connection is getting established and not disposed through ADO.NET / C#

I am using the code shown here, to connect to SQL Server. This is working properly, but for the past 2 days, I am facing an issue where multiple connections (~2000) are getting opened and not getting disposed. Due to this, the CPU utilization is reaching 100% and system is unable to process any request.

Kindly help and suggest if I am doing anything wrong or any way to fix this.

DataTable dataTable = new DataTable();

try
{
    DataSet dataSet = new DataSet();

    using (var command = _dbContext.Database.GetDbConnection().CreateCommand())
    {
        command.CommandText = "usp_get_userdetails";
        command.CommandType = CommandType.StoredProcedure;

        if (_dbContext.Database.GetDbConnection().State == ConnectionState.Closed)
        {
            _dbContext.Database.GetDbConnection().Open();
        }

        command.Parameters.Add(new SqlParameter("@UserId", userid));

        var dataAdapter = new SqlDataAdapter { SelectCommand = (SqlCommand)command };
        dataAdapter.Fill(dataSet);

        dataTable = dataSet.Tables[0];
    }
}
catch (Exception ex)
{
    throw ex;
}

return dataTable;

My connection string looks like this:

server=*******;uid=username;pwd=********;database= DBName;connection timeout=0;
    Max Pool Size = 1000;Pooling = True;TrustServerCertificate=True; MultipleActiveResultSets=True
Tags:

3

Answers


  1. 0

    Avoid using DataSet, I recommend using Entity Framework and/or some ORM for connections, for example NPoco.

    https://github.com/schotime/NPoco

    https://learn.microsoft.com/es-es/ef/

    Login or Signup to reply.
  2. 0

    As I pointed out in comments, the older dataset designer is in fact a ORM, and is the precursor to EF. And the dataset designer even provides strong typed tables (of which the poster is not using).

    The simple matter, when writing such custom code, then you need/want to create a instance of the connection object in your outer using block. Then inside, another one for the command object should be created.

    Hence, your code will wind up like this:

    DataTable mytable = new DataTable();

using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
{
    using (SqlCommand cmd = new SqlCommand("usp_get_userdetails", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        conn.Open();
        cmd.Parameters.Add("@UserId", SqlDbType.NVarChar).Value =  userid;
        mytable.Load(cmd.ExecuteReader());  
    }
}

    As a FYI:
    You want to STRONG type the parameter, and hence if userid is a int, then you use this:

    cmd.Parameters.Add("@UserId", SqlDbType.Int).Value =  userid;

    You have to replace the connection string I used above with a valid connection string.

    Note how we do NOT check if the connection is open, and note how we NEVER (do not) close the connection. The "using" blocks will correctly handle the closing and disposing of the connection object. And even better, .NET will automatically "pool" the connection and re-use it for reasons of performance.

    Login or Signup to reply.
  3. 0

    You don’t say the type of dbContext, but unless it’s doing something strange, it is getting a new connection from the connection pool every time you call GetDbConnection. You need to get the connection once, attach the command to it, then dispose it afterwards.

        using (var connection = _dbContext.Database.GetDbConnection())
    {
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "usp_get_userdetails";
            command.CommandType = CommandType.StoredProcedure;

            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }

            command.Parameters.Add(new SqlParameter("@UserId", userid));

            var dataAdapter = new SqlDataAdapter { SelectCommand = (SqlCommand)command };
            dataAdapter.Fill(dataSet);

            dataTable = dataSet.Tables[0];
        }
    }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search