Should I Close SQLConnection For All Transactions in Webservice?

Question

I am using ASP.Net WebserviceApplication for my application and it is communicating with my SQL Server. Should I close SQLConnection after all user's sql transactions or it should be open everytime?

For example;

   public void Connection()
    {
        if (connection == null)
            connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Conn"].ToString());

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

   [WebMethod]
    public long GetUsersRankMonthly(string userName)
    {
        Connection();
        SqlCommand command = new SqlCommand("Select scores.Rank From (SELECT ROW_NUMBER() OVER(ORDER BY Score DESC) AS Rank,Score,NickName,ID FROM teatalay.TopScoresGeneral) scores Where UserName = @userName", connection);
        command.Parameters.Add(new SqlParameter("@userName", userName));

        var result = (long?)command.ExecuteScalar();

        return result.HasValue ? result.Value : -1;
    }

Thank you.

Answer 1

Wrap your transactions in a using statement when using a sql command. Let ASP.NET take care of SQL Connection pooling. It is a bit more refined at it than your code. Keep everything as condensed as possible and only modify if you notice that the number of connections to your server are what are the source of your performance issues.

Edit

using (var cnn = new SqlConnection("connection string here")){
    using (var cmd = new SqlCommand("SProc or parametized text", cnn)){
        cnn.Open();
        // do stuff
        cnn.Close();
    }
}