Performance tips/hints for .NET webservice providing info from sql server

Question

Hi,

I have a rather simple web-service which exposes data from sql server. It will be used to synchronize data between 2 different databases (SQL Server and Lotus Notes). We're in the stage of testing the web-service and poll it with 20 req./min., the first 2 minutes it goes OK, but after the second, we're getting an exception, obviously the connection (to the database) can't be opened(timeout).

Do you have any tips/recommendations on what to do or where to look at? The web-service has been programmed using C#/.NET, the connection to the db is opened during construction of (web-service) object and closed when the object is disposed.

I've considered using global.asax to "share" the connection but after some googling I found out most people find that a bad idea and I'm looking for a different solution.

ps. the service is pooled in a synchronous way, no 2 requests exist at the same time

-edit- (after first 2 anwsers about pooling) This is current code:

public class DataService : System.Web.Services.WebService
{
    private SqlConnection conn = new SqlConnection("Data Source=ip;database=database;uid=user;pwd=secret;");
    public DataService () 
    {
            try
            {
                conn.Open();

            }
            catch (Exception dbconn)
            {
                throw new SoapException("Couldn't open connection to database:" + dbconn.Message + " More info at: " + dbconn.HelpLink, errorCode);
            }
            //Uncomment the following line if using designed components 
            //InitializeComponent(); 
    }
    ~DataService()
    {
         conn.Close();
    }
    [WebMethod(Description="Gets all Person changes(from last week)")]
    public Person[] GetPerson()
    { 
            Person[] Personen = null;
            SqlCommand sqlcmd = conn.CreateCommand();

            sqlcmd.CommandText = "SELECT * FROM Person";
            SqlDataReader Rows = sqlcmd.ExecuteReader();
            while (Rows.Read())
            {
                    //doSomething
            }

            Rows.Close();
            return Personen;
    }

}

Answer 1

Sounds like you've exhausted the connection pool -- best option is to wrap you SQL calls with using blocks, loosely thus:

using( SqlConnection con = new SqlConnection( "MyConnectionString" ) )
{
    con.Open();

    using( SqlCommand cmd = new SqlCommand( "MyStoredProcedure", con ) )
    {
        // Do stuff with the Command
    }
}

This will allow you to serve concurrently the same number of requests as the size of your connection pool.

So, the code after your edit becomes:

public class DataService : System.Web.Services.WebService
{
    [WebMethod(Description="Gets all Person changes(from last week)")]
    public Person[] GetPerson()
    { 
        Person[] Personen = null;

        using( SqlConnection conn = new SqlConnection("Data Source=ip;database=database;uid=user;pwd=secret;") )
        {
            using( SqlCommand sqlcmd = conn.CreateCommand() )
            {
                sqlcmd.CommandText = "SELECT * FROM Person";
                SqlDataReader Rows = sqlcmd.ExecuteReader( CommandBehavior.CloseConnection ); // This will close the DB connection ASAP
                while (Rows.Read())
                {
                    //doSomething
                }

                Rows.Close();
            }
        }

        return Personen;
    }
}

Answer 2

See in this link Best Practices for Using ADO.NET, in it you will find a link to SQL Server Connection Pooling (ADO.NET) - make sure you really are using a connection pool correctly.