.net mvc 4 web api service connection to SQL Server issue

Question

The setup

We have a .net mvc 4 web api service layer, that is accessed via a web app, which gets data from a SQL Server 2008 database.

The problem

When more than 5 GET requests are being made to the web service at the same time, a 500 Interal Server Error is returned to the web app. When I look closer at the .net error I see the following:

An error has occurred.
The connection was not closed. The connection's current state is open

The C# service code where the exception is being thrown:

public class ImageLinkRepository : IImageLinkRepository
{
  private SqlConnection cnnSQL = new SqlConnection(...);

  public IEnumerable<ImageLink> Get(String userId)
        {

            ImageLinks = new List<ImageLink>();

            // open connection to DB
            cnnSQL.Open();

            try
            {
                SqlCommand cmmSQL = new SqlCommand("nVision_select_lcimagelinks_sp", cnnSQL);
                cmmSQL.CommandType = System.Data.CommandType.StoredProcedure;

                SqlParameter prmSQL = cmmSQL.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@LCIMGLINKUSERID",
                    Value = userId
                });

                SqlDataReader rdrSQL = cmmSQL.ExecuteReader();

                if (rdrSQL.HasRows)
                {
                    while (rdrSQL.Read())
                    {
                        ImageLinks.Add(new ImageLink 
                        { 
                            // set new ImageLink object's properties    
                            imageTopicId = DBReader.SQLString(rdrSQL, "LCIMGLINKIMGTOPICID"), 
                            id = DBReader.SQLInt(rdrSQL, "LCIMGLINKPK"),
                            recordId = DBReader.SQLString(rdrSQL, "LCIMGLINKRECORDID"),
                            text = DBReader.SQLString(rdrSQL, "LCIMGLINKTEXT"),
                            topicId = DBReader.SQLString(rdrSQL, "LCIMGLINKTOPICID"),
                            topicItem = DBReader.SQLString(rdrSQL, "LCIMGLINKTOPICITEM"),
                            url = DBReader.SQLString(rdrSQL, "LCIMGLINKURL"),
                            user = DBReader.SQLString(rdrSQL, "LCIMGLINKUSERID")
                        });
                    }
                }    
            }

            // close connection to DB
            cnnSQL.Close();

            return ImageLinks;
        }
}

I have tried to manually close the connection if it is open and I have tried to only open the connection if its closed, neither of them work.

I must be doing something wrong, surely, the service should be able to handle simultaneous requests. What am I missing?

STW · Accepted Answer · 2013-08-30 15:41:06Z

2

You're likely exhausting the SQL Connection Pool. To start with you should be wrapping your SqlCommand, SqlConnection, and SqlReaders with using() statements to ensure they are correctly disposed of and closed.

The SqlConnection being constructed as a field means that your controller has to be responsible for Disposing of it. It's easier to construct it within the method, where it can be wrapped in a using() as well (otherwise you're class should implement IDisposable(), calling .Dispose() on the Connection in turn).

try
{
  using (var cnnSQL = new SqlConnection(...))
  {
    using (var cmmSQL = new SqlCommand("nVision_select_lcimagelinks_sp", cnnSQL))
    {
      cmmSQL.CommandType = System.Data.CommandType.StoredProcedure;

      SqlParameter prmSQL = cmmSQL.Parameters.Add(new SqlParameter
      {
          ParameterName = "@LCIMGLINKUSERID",
          Value = userId
      });

      using (var rdrSQL = cmmSQL.ExecuteReader())
      {
      ...
      }
    }
  {
} ...

edited Aug 30, 2013 at 15:41

answered Aug 30, 2013 at 15:35

STW

46.8k17 gold badges109 silver badges170 bronze badges

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

2 Comments

Zengineer Over a year ago

Adding the 'using' block fixed the problem. Since some service classes multiple methods defined, it seemed to make more sense to declare the connection outside of each method and have them share the same SQLConnection object. I just didnt know about implementing IDispoasable(). I added the Dispose() method call after I closed the connection but it always return the 500 Error back to the web app. In any event, thanks!

STW Over a year ago

Read up on .NET's SQL Connection Pooling, under the hood it actually reuses connections--even though it appears to be creating new ones entirely from scratch.

Collectives™ on Stack Overflow

.net mvc 4 web api service connection to SQL Server issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related