DataReader Problems Boxing and Unboxing

After I researched the problems and implemented the solutions in this post http://brookssoftwaredev.blogspot.com/2014/01/net-datareader-not-closing-connection.html I found that I was still getting timeouts while trying to obtain a connection from the pool.

I researched more and found that doing normal conversion like

int.Parse(reader["FieldName"]);

caused boxing and unboxing of the values.  I was doing this on several values for many records.

I did some research and found that the boxing and unboxing causes the garbage collector to bog down.  I found that the DataReader has special methods to avoid the boxing and unboxing.  These methods are things like GetChar, GetDouble, GetDateTime, and GetInt32.  From what I read these stop the boxing and unboxing and prevent the garbage collector from thrashing.

Once I implemented those methods my timeouts on the connection pool went away.  It appears as though the garbage collector couldn't clean up the connections, since it was busy with all of the rogue objected from the DataReader boxing and unboxing.

I realized that using a DataReader left the connection open and there needed to be precautions taken, but there are several other things that you must deal with when using a DataReader, so use caution.

.Net DataReader Not Closing Connection After Closed

As I research using the .Net DataReader I kept seeing information online that said things like "When the command is executed, the associated Connection object is closed when the associated DataReader object is closed."

There had been concerns that the data reader would tie up the connection too long.  There was also concern that we were closing the connection explicitly, especially since the creation of the reader was abstracted into another class.  The class using the reader would not have access to the command or connection that created it.

In various different places I have worked we have used this and were confident that the DataReader would clean itself and its connection up, based upon what we had read.

We did a small test page recently and it turns out that by default it leaves the connection open.

We opened a connection and created a command tied to that connection and then used that command to create a DataReader.  We did command.ExecuteReader() to create the reader.

As we researched further we found that ExecuteReader has an optional parameter of a CommandBehavior enumeration.  If you pass CommandBehavior.CloseConnection then it closes the connection when the reader closes.

Dynamically Created Control Not Firing Events in ASP.Net

I have an aspx page where when someone changes the value in a Drop Down List the database is queried and records are displayed in an HTML table.  As the table is created I add a Button control to each row.  I have tried this with a CheckBox as well with similar results.  I also wire up a click event for each button when it is created.  All the events for the Buttons point to the same event.

The grid is created properly, with the button on each line.  When I click one of the buttons the Page_Load event fires, but the click event does not.

When I dynamically create a button outside the grid from the Drop Down List's SelectedIndexChanged event it also does not fire the click event.

When I move the button creation code to the Page_Load event wrapped in an If (!IsPostback) block the Click event works properly.

It appears that if the control is not created in Form_Load or Form_Init the dynamic control's events do not fire.  I have not found a solution to be able to do what I want, but I thought what I found in my research so far might be useful to someone.

How to Get SQL Report Services Report Built With Report Builder to Display No Page Breaks on Screen, But Still Break When Printed

I created a report with Report Builder 3.  My users are us to seeing reports displayed on screen with no page breaks.  It looks like the default is to have page breaks.  I found that in the report properties there are a couple of properties to manage page breaks.

In Report Builder, right click outside the report body and click Report Properties.  In the properties window there is a page section when you have it sorted by property category.  In that section there is a property called InterActiveSize.  If you set the height and the width of this property both to be 0 the report is displayed on screen in one long page with no breaks.

The other property is PageSize.  This has to do with the printed page.  It is right below the margin property where you can control margins.  I think this only controls them for the printed page.

Data Bound GridView and Asp.Net Ajax UpdatePanel

I tried loading a couple of grids through an update panel with a timer to try to get the rest of the page to display to the user quickly and do the heavy lifting of these two grids in the background.

I wrapped the two grids in an update panel and added a timer.  I put code in the tick event of the timer.  I loaded the page and it came up quickly and moments later the grids filled in.  Moments after that all of my dynamic data i have created with template fields was erased.  The main data that was being bound was still there.

As I researched it I found that the RowCreated event was firing repeatedly.  I viewed the call stack and saw that it was being fired by system code that I could not see and not by my code.

I tried using session variables to get it to routed around the multiple calls, but the damage was being done before it got to the RowCreated event.  The event also was getting null values for ID fields I was using to populate the grid correctly, which is why the dynamic data was not being displayed.

I did a couple of hours of research, but found nothing specific to my problem, but I did start stumbling upon two properties of the update panel, ChildrenAsTriggers and UpdateMode.  Honestly I don't entirely understand UpdateMode yet, but ChildrenAsTriggers got me thinking that maybe the grid was triggering a refresh of its own somehow after I got everything set the way I wanted it.

I set ChildrenAsTriggers="false".  It required that I have UpdateMode="Conditional".  I think this also required that I have an explicit Trigger tag for the update panel that was set to the Tick event of my timer.

After all that, it appears to be working.

IIS Routing and Paths With 404 in Them

We had a problem today with a path routing.  We had a path similar to this http://localhost:54612/mydir/invoice/40414

It appeared that this path was never getting to the code, but being bounced back by IIS.  The URL http://localhost:54612/mydir/40399 worked just fine.

After further research we found that it was number at the end with 404 in it that was breaking us.  http://localhost:54612/mydir/30404 also broke.

The code we were using was

RouteTable.Routes.Add(new RegexRoute(@"^/mydir/invoice/(?\d+)/?$", new PathRouteHandler("~/myotherdir/file.aspx")));

Once we changed it to

RouteTable.Routes.Add(new Route(@"mydir/invoice", new PathRouteHandler("~/myotherdir/file.aspx")));

it worked.

We had to change the catching code to check the query string instead of the route path, but it worked.

We have yet to discover why it was behaving badly with invoice numbers containing 404.  I would love to read your thoughts if you have run across such a thing.

There is already an open DataReader associated with this Command which must be closed first

Every so often in our ASP.Net website we would get the error, "There is already an open DataReader associated with this Command which must be closed first" referring to a SqlDataReader.  We would tweak code here and there and get it to go away.  Later another code tweak would bring it back.  We have no idea what is causing it.  

It is in a block of code that creates a SQLCommand, creates a SQLDataReader, and destroys the SQLDataReader and SQLCommand.  We are not sure how it is possible that another reader is executing on that command.

As a work around until we can find the issue we have added this attribute

MultipleActiveResultSets=True

to our connection string and it has seemed to have bypassed the problem.  I know it likely causes other problems, but due to the environment we work it that's what we have to do.