Application hangs when used with different database

[Webskater]

I have a development box running Win7 and VS2010. A User Acceptance Testing server running Windows Server 2008 / Sql Server 2008 and a Live server running Windows Server 2008 / Sql 2008.

When doing development I connect to a development database on the UAT server.

I have a problem that only occurs when I change the connection string to point to the database on the Live Server.

So, before I start describing the problem - the app works fine in development and when published to the UAT server - but there is a problem when I either publish it to the live server or, whilst on my dev box, point the connection string to the live database.

Here's an example.

I have a page with a gridview on it. In each row of the gridview there is a drop down list. All calls to the database are done through a Data Access class.

Say I have a function on an .aspx.cs page called GetDetails().

In this - to populate the Gridview I'll have something like ...

DataAccess da = new DataAccess(); //new instance of Data Access class
gvDetails.DataSource = da.GetDetails(ProductID); //returns a SqlDataReader
gvDetails.DataKeyNames = new string[] { "DetailID", "Reference", "ReferenceID" };
gvDetails.DataBind();

And, in the RowDataBound event I'll have something like this:

protected void gvDetails_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        /*
        [0]DetailID
        [1]Reference
        [2]ReferenceID
        */
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DataKey key = gvDetails.DataKeys[e.Row.RowIndex];
            DropDownList ddlReference = (DropDownList)e.Row.FindControl("ddlReference");
GF.PopulateDDL(ddlReference, key[2].ToString());
}
}

GF - is a Generic Function class. GFPopulateDDL takes a DropDownList and an ID and fetches a SqlDataReader with some data to populate the Dropdownlist.
So, as each row in the GridView is DataBound, a call to my generic function PopulateDDL - populates the DropDownList in each row.

I do this on dozens of pages in different apps and it works fine.

But, when I connect to the database on the Live Server - the application hangs when I call PopulateDDL. Within PopulateDDL it will have code like

public void PopulateDDL(DropDownList ddl, int ItemID)
{
DataAccess da = new DataAccess();
ddl.DataSource = da.GetddlItems(ItemID);
ddl.DataBind();
}

The problem occurs in the DataAccess class function that returns the datareader to populate the DropDownLists in each row. This looks like:

public SqlDataReader GetddlItems(string ItemGroup)
        {
            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(ConnectionString);
            SqlCommand myCommand = myConnection.CreateCommand();
            myCommand.CommandText = "GetddlItems";
            myCommand.CommandTimeout = 120;

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterItemGroup = new SqlParameter("@ItemGroup", SqlDbType.Int, 4);
            parameterItemGroup.Value = ItemGroup;
            myCommand.Parameters.Add(parameterItemGroup);

            // Execute the stored procedure and Return the datareader result
            myConnection.Open();
            return myCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }

(I didn't write that - but it works)

As soon as you hit myConnection.Open() - the application hangs. No error messages - nothing. As you step through the code you get returned to the browser and it just sits there forever with the progress bar going nowhere. BUT, it only happens in the specific situation of populating the dropdownlists in each row of the Gridview. On the page I am having problems with - PopulateDDL has already been called a couple of times, successfully, to populate DropDownLists outside of the Gridview.

Now, you might think this is a Connection issue - that a Connection is opened to populate the SqlDataReader that populates the Gridview and that, as you loop through the gridview, more connections are opened and closed to populate the DropDownLists. But, all functions that return SqlDataReaders are closed with
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
so Connections are definitely closed.

So ... sit there on my dev box and point at the database on UAT server - and it works fine. Point at the database on the Live server and, at that specific point (described above), it falls over. (Earlier calls to live database all work fine).

Same database (SQL Server 2008) - same operating system - Windows Server 2008 R2.

So, why does it hang on the live database on that particular myConnection.Open but not on the UAT or development database? And why does it only do it in that specific situation. Most database calls work fine. And why no error messages? You just get moved back to the browser - which has hung. On my development box I am having to restart IIS sometimes - one error message that does appear sometimes is to do with something (damn, can't remember the terminology) like - a debugger is already attached - something like that.

Any help much appreciated. At the end of my second day on this now.

By the way - it isn't anything to do with errors in the code above - I just typed that from memory - the actual code works fine on dozens and dozens of pages - it's just this issue with the different databases that is behind this.

[gep13]

Hey,

The only thing that springs to mind is permissions on certain tables in the database. In the calls that are working, compared to the ones that aren't, are you hitting the same tables? Even if this were the case though, I would expect to get some form of error message back from ADO.Net.

Another thought...

Does the live server have the same data as the UAT one? Could it be that there is just much more data in the live server, and it is taking longer to reply? Can you take the query that you are running, and execute it directly against the live server? Does it work?

Gary

[Webskater]

Hey,

The only thing that springs to mind is permissions on certain tables in the database. In the calls that are working, compared to the ones that aren't, are you hitting the same tables? Even if this were the case though, I would expect to get some form of error message back from ADO.Net.

Another thought...

Does the live server have the same data as the UAT one? Could it be that there is just much more data in the live server, and it is taking longer to reply? Can you take the query that you are running, and execute it directly against the live server? Does it work?

Gary

Hi and thanks for the reply.

The tables generally have no permissions - all access to the tables is via stored procedures so the only permissions are execute permissions on the SPs.

The live server has virtually no data in the tables being used by this app. The database is used by 5 separate applications but each app pretty much has its own tables - apart from common ones like the ones containing organisations and people. At the moment I'm trying to get the live app to work. As far as queries go - no problems executing on the live server. Any page which is dishing up problems I have run the queries over and over. All execute in milliseconds.

Another bit of evidence ... on a couple of pages that were playing up I noticed I had not explicity disposed of a SqlDataReader.

So, this code, on a .aspx.cs page ...

DataAccess da = new DataAccess();
SqlDataReader dr = da.GetDetails(ItemID);
gvDetails.DataSource = dr;
gvDetails.DataBind();

... works perfectly when retrieving data from a Win 2008 R2 Server with SQL Server 2008, but fails when retrieving data from another Win 2008 R2 Server with SQL Server 2008.

If I dispose of the data reader ...

DataAccess da = new DataAccess();
SqlDataReader dr = da.GetDetails(ItemID);
gvDetails.DataSource = dr;
gvDetails.DataBind();
dr.Dispose();

.... it suddenly starts working on the Live server as well.

My understanding is that the normal garbage collection takes care of SqlDataReaders you forget to explicitly dispose of and, even if it doesn't, it certainly does when the UAT server is being used - in all 5 of the other apps.

So ... there is some difference between the UAT server and the Live server that means that when the front end receives a SqlDataReader from a DataAccess class that definitely returns SqlDataReaders with ...

return myCommand.ExecuteReader(CommandBehavior.CloseConnection);

... and that SqlDataReader is not explicity closed - it causes a problem if the data comes from one database but not if it comes from another database. Which baffles me. The SqlDataReader passed to the font end is not connected to the database in any way yet, when retrieved from one database server it works fine (without explicit disposal) but, when retrieved from another database server it causes the application to hang with no error messages of any size or shape.

Obviously, the error described above is easily fixed but the original error I have described won't go away. A way round it is to fill a dataset with the data for the dropdownlists and just use that to populate each dropdownlist in each row of the GridView. I tend to do this anyway if the GridView is likely to have a large number of rows - as only one call to the database is needed to get the data - as opposed to a call every row filling a SqlDataReader (I have only done this through laziness when I have known a Gridview is not likely to have more than a few rows)

One last thing - I have used SQLCompare to make sure UAT and Live databases are identical. And I have to persist and find an answer as there are still some situations where everything is absolutely correct but still it hangs against one database but not the other.

Thanks again for your input.

[Webskater]

As an aside - is it IE 8.0 that means I have to log in every time this page posts back?

[brin351]

Is the Live server a dedicated one that you control or is your database on a shared server. I ask because you could examine the sql & server logs to see if anything is reported there.

If I understand you correctly the problem is making the connection between your local machine and the remote/live server and then only sometimes mainly when rapidly repeated connections are made. If thats right and if the connections are over the internet (not on the same network) then the communication lag may be your primary problem.

You could write a bit of test code looping through connections and or dataReaders etc to see if you hit the same problem. If you don't get the same problem then it may be a code issue. Bugs without error messages are a pain in the ass.

[Webskater]

Is the Live server a dedicated one that you control or is your database on a shared server. I ask because you could examine the sql & server logs to see if anything is reported there.

If I understand you correctly the problem is making the connection between your local machine and the remote/live server and then only sometimes mainly when rapidly repeated connections are made. If thats right and if the connections are over the internet (not on the same network) then the communication lag may be your primary problem.

You could write a bit of test code looping through connections and or dataReaders etc to see if you hit the same problem. If you don't get the same problem then it may be a code issue. Bugs without error messages are a pain in the ass.

Thanks for your reply.

Both the UAT and Live Server are on a network - in the same rack as it happens. The weird thing is the live server already runs 5 apps that I have written and - of course - the code is similar throughout.

The big difference is that the existing apps were developed on an XP box with VS 2005 and .Net Framework 2.0. The new app is being developed on a Win 7 box and being deployed on a different server (Win 2008 R2) from the existing 5 apps which are on a Win 2003 server.

But, in a sense, none of that should matter because, temporarily, my new app is being deployed to UAT and (notionally) live to the same server. The difference is that the UAT one is getting it's data from a database on Database Server 1 whereas the live one is pointing to Database Server 2. The operating systems - service packs etc. are identical on Database Servers 1 and 2 and, as I say, the app, whether live or UAT is on the same server.

The logs show nothing.

[gep13]

I am at a loss to suggest much else. The only other obvious things to check would be the connection pooling setup on both machines. Could be that ones has plenty of available connections, while the other is waiting for a connection to become available.

As a side note, you could use a "using" statement in your code, so you don't have to worry about explictly disposing of an object. I would want to refactor my code first, to ensure that this was done.

Gary

[Webskater]

I am at a loss to suggest much else. The only other obvious things to check would be the connection pooling setup on both machines. Could be that ones has plenty of available connections, while the other is waiting for a connection to become available.

As a side note, you could use a "using" statement in your code, so you don't have to worry about explictly disposing of an object. I would want to refactor my code first, to ensure that this was done.

Gary

Hi Gary and thanks again for your help.

What does 'refactor my code' mean? Cheers.

[gep13]

Hello,

The act of refactoring your code is essentially going through it and changing it:

http://en.wikipedia.org/wiki/Code_refactoring

In your case, the Refactor would be to implement the "using" statement on all of your disposable objects.

Gary

[Webskater]

Hello,

The act of refactoring your code is essentially going through it and changing it:

http://en.wikipedia.org/wiki/Code_refactoring

In your case, the Refactor would be to implement the "using" statement on all of your disposable objects.

Gary

Ahhh, I had this idea in the back of my mind that I had seen 'refactor' on a menu somewhere.

[gep13]

Ahhh, I had this idea in the back of my mind that I had seen 'refactor' on a menu somewhere.

You do get some built in refactoring within Visual Studio, for instance, renaming all instances of a variable in your solution is a "refactor" and available through the right click menu.

There are other tools out there, such as ReSharper from JetBrains, and CodeRush from DevExpress that allow you to do more complicated refactoring automatically. However, some refactoring, like the one I suggested to you, would be a manual refactor of the code.

Gary