Fast and Efficient Async SQL Process

rodmcleay · November 9th, 2007, 03:45 AM

Hi All,
I would like to know the best way to use a SQL connection for a web site.
The situation is I have a one page site that has AJAX calls to update the GUI.
Each page page update writes one record to SQL 2000 and does not retrieve any data.
I would like to know the best approach to usitlise my connection to reduce the amount of time to open/close the connection and write the one record.
I feel as thought opening and closing the connection for each nonquery command is an overhead, is this the case?
How can i ensure that my commands are all executed in the least amount of time.
I have considered opening a connection leaving it open, locking the code to execute the command, is this a bad Idea?
Currently I have coded a static method to execute the commands

Code:

    public static void ExecuteTextNonQuery(string commandText)
    {
        SqlCommand cmd = new SqlCommand(commandText);
        cmd.CommandType = CommandType.Text;
        cmd.Connection = Connection;
        lock (connlock)
        {
            if (Connection.State != ConnectionState.Open) { Connection.Open(); }
            cmd.ExecuteNonQuery();
            Connection.Close();
        }
    }

Any advise or direction in this would be much appreciated.

Speed is the essence, I need to be able to serve this page to 1000's of concurrent users.

======================================
"They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad."
--Shakespeare
======================================