C#

Michiel wrote:

The thread who is pooling the database

// create SQL Connection
while(true)
{  
  // create SQL Command
  // Open SQL Connection
  // Execute SQL Command
  // Close SQL Connection

  // sleep for 1/5 sec...
}

PeterTheSwede wrote:

If I remember correctly, the current SQL Native Client (preferred when talking to SQL Server 2005) doesn't support connection pooling. This means you get a performance hit if you solve your problems by creating, opening and closing a separate connection each time you touch the database (each time you will cause negotiations between the client and SQL Server before you start doing actual work). If connection pooling was supported, this wouldn't be the case, meaning it would be the simplest solution and wouldn't be a performance issue (connection pooling makes it extremely fast to open/close new connections as a number of "closed" connections are kept open in the background, ready to use by the next attempt to open a new connection).

SqlConnection sqlConn1, sqlConn2, sqlConn3;
SqlDataReader sqlReader1, sqlReader2, slqReader3; // also three?
SqlCommand sqlComm1, sqlComm2, sqlComm3; // also three?

private string myConnectionString; // initialize this somewhere!

private SqlConnection GetConnection()
{
    SqlConnection cn = new SqlConnection(myConnectionString);

    try
    {
        cn.Open();
        return cn;
    }
    catch
    {
        cn.Dispose();
        throw;
    }
}

private SqlCommand GetCommand(SqlConnection cn, string sqlquery, params object[] parms)
{
    SqlCommand cmd = cn.CreateCommand();

    try
    {
        cmd.CommandText = sqlquery;
        if (parms != null)
        {
            for (int i = 0; i < parms.Length; i++)
            {
                // note: in the query, parameters should be named @p0, @p1...
                // note: we must send DbNull.Value instead of null, or bad things happen.
                cmd.Parameters.AddWithValue(
                    String.Format("@p{0}", i), parms[i] ?? DbNull.Value);
            }
        }
        return cmd;
    }
    catch
    {
        cmd.Dispose();
        throw;
    }
}

using (SqlConnection cn = GetConnection())
{
    string query = "SELECT * FROM MyTable WHERE x = @p0 AND y = @p1";

    using (SqlCommand cmd = GetCommand(cn, query, 1, 2))
    {
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                DoSomethingWithTheseFields(rdr["field1"], rdr["field2"]);
            }
        }
    }
}