ADO.net locks out SQL2000 user account under load

[Mr Jen Withers]

This is a strange problem to describe so you will have to bear with me! I have developed a web application using c#.net that utilises server side ASPX pages that connect to a SQL2000 database. I have been load testing my application with the Microsoft Application Test Center that comes with Visual Studio 2003, with simulated loads of 10 - 400 users. When I run a test against my web application for upwards of 10 concurrent users I start to get strange results from the database for what should be routine queries. I execute a simple query looking for a row in a table which I know is there (100% certain!) and the query returns the results to a dataset. I then get the first datatable from the dataset and check that the dataset.rows.count variable is greater then 0. Which it always is! I then proceed to look for a column that exists in the table which I specifically selected and an exception is thrown telling me the column doesnt exist in the datatable. This is actually impossible the data is DEFINATLEY there! I can log into SQL Query analyser as the same SQL logon that the ASPX page uses to connect and run the same query and it returns the expected results without error or anaomoly. Once an error of this type has occured, I can replicated it upto 10 minutes after it first has happened. During this 10 minute period I can switch the SQL logon the ASPX uses and it runs fine. As soon as I switch it back to the user on which the error occured it demonstrates the same problem again. After 10 minutes the problem disappears and wont happen again until I apply a heavy load to the server. As an important note, I have now managed to replicate an identical problem with just 2 users trying to concurrently login. Here is the snippet of code iam using to query the database:

    public static DataSet execute\_DataTable(String SQL\_Command\_String)
    {
        
        String connectionString;
        
        connectionString = getConnectionString(@"..\\xml\_Settings\\DB\_Connect.xml");
        makeConnection(connectionString);

        DataSet dsResults = new DataSet();
        try
        {
            SqlDataAdapter dbAdapter = new SqlDataAdapter(SQL\_Command\_String, dbConn);
            dbAdapter.Fill(dsResults, "Results");
        }
        catch (Exception exp)
        {
            throw new Exception(exp.Message + " " + exp.StackTrace, exp);
        }
        finally
        {
            dbConn.Close();

[Mark Churchill]

Without looking too closely, it sounds like with liberal use of the static keyword you've ended up with a whole bunch of non-thread-safe code. Have a second look at your data layer and imagine how multiple simultaneous calls will affect it. If you are dealing with trees, try querying for the entire subtree in a single recordset to speed things up. Common Table Expressions are useful for recursively pulling out data in a single query.

Mark Churchill Director Dunn & Churchill Free Download:
Diamond Binding: The simple, powerful, reliable, and effective data layer toolkit for Visual Studio.

[Mr Jen Withers]

Hey There thanks for the advice, I think I could do with reading up on thread safe code! Would it be worth while turning my Database connection code into an instanciable object and totally removing the static keywords to try and avoid these issues? Thanks Mike