CODEWITHSUNDEEP
c#asp.netsql-server
Michael Heribert
Michael Heribert

Reputation: 69

HTTP 500: SQL connection string causing error

I am working on an API using ASP.NET. At one point, I need a method to call another one. Both of those use stored procedures:

public async Task<IHttpActionResult> GetTeamById(string TeamId)
{       
        DataTable Result = new DataTable();

        SqlCommand Command = new SqlCommand("GetTeam", ConnectionString);
        Command.CommandType = CommandType.StoredProcedure;
        Command.Parameters.AddWithValue("@TeamId", TeamId);

        using (ConnectionString) /defined above
        {
            try
            {    
                ConnectionString.Open();

                using (SqlDataReader DataReader = Command.ExecuteReader())
                {
                    Result.Load(DataReader);
                    return Ok(Result);
                }
            }
            catch
            {
               return InternalServerError();
            }
        }
    }

    public async Task<IHttpActionResult> UpdateTeam(string TeamId)
    {
        SqlCommand Command = new SqlCommand("UpdateTeam", ConnectionString);
        Command.CommandType = CommandType.StoredProcedure;               
        Command.Parameters.AddWithValue("@Id", TeamId);

        using (ConnectionString)
        {
            try
            {
                ConnectionString.Open();

                int returnvalue = (int) Command.ExecuteScalar();            

                if (returnvalue == 1) 
                   return BadRequest();
                else 
                   await GetTeamById(TeamId);
            }
            catch
            {
                return InternalServerError();
            }
        }                  
    }

The problem I face is that this code throws a 500 Internal Server Error when the UpdateTeam method is called. I figured out that the problem occurs at the line ConnectionString.Open() in the GetTeamById function.

I thought that that might be due to the connection still being opened? But isn't that just why one uses the using()? How can I work around this problem?

Thanks for your support!

Upvotes: 0

Views: 1426

Answers (1)

Hamletkrita
Hamletkrita

Reputation: 247

I always follow this standard when i connect to a procedure:

using (var conn = new SqlConnection(connectionString))
using (var command = new SqlCommand("ProcedureName", conn) { 
                           CommandType = CommandType.StoredProcedure }) {
   conn.Open();
   command.ExecuteNonQuery();
   conn.Close();
}

Upvotes: 3

Related Questions