Array vs ArrayList vs Generic List vs LinkedList in .Net

Array vs ArrayList vs Generic List vs LinkedList in .Net

Arrays
Arrays cannot shrink or grow unless you copy the array to an entirely new array of a different size.  Once the size of an array is declared that is the size it must remain without heavy overhead.

Arrays can contain objects or primitives.

ArrayLists

An ArrayList uses a dynamically expanding Array internally, so there can be a performance hit when expanding past the size of its internal Array.  The size of the internal array of an array list does not change, in order to increase performance.  When an element is added beyond the capacity of the ArrayList the internal array is copied and a new array of twice the number of elements is created.  Thus reducing the need to expand the internal array for a while.  This uses more memory, but can make it perform more quickly.  Array lists also start with an internal Array with ten elements, also to reduce the need to increase its size.

ArrayLists can only contain objects.

List

List is a generic implementation of ArrayList.  ArrayList appears to be being deprecated.

LinkedList

LinkedList can have performance issues since it can cause memory fragmentation.  There is no set bounds for a LinkedList, so data in the list can end up anywhere in memory.  This can be confusing, since we also said that ArrayLists can have performance issues due to recreating the internal Array on inserts.

You have to determine when you need to gain.  Do you need to free up memory or need to have fast inserts and deletions, then LinkedList.  Do you need fast access to items and will be doing very few inserts, then perhaps Array or List is what you want.

Memory
ArrayLists can use 100s of percent more memory than List.

Types
Under the covers ArrayList is an array of type object[].  List is an array of whatever specific type T you make it.  This can allow for better memory usage and a more precise implementation in code.

Performance
Arrays and Lists allow for very fast read since they are a fix size making each piece of data stored right next to each other in memory.

LinkedList can have performance issues since it can cause memory fragmentation.  There is no set bounds for a LinkedList, so data in the list can end up anywhere in memory.

Recursive and Nested Triggers in TSQL

We would get this error message "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)".

I was told that turning off recursive triggers would fix it.  Likely the problem could better be solved by redesigning the triggers and such, but not authorized for that right now.

Recursive Triggers is an Option that can be set by going into SQL Management Studio and right clicking on the database and clicking Properties.  In the left pane click Options.  Sort the properties alphabetically and set the option Recursive Triggers Enabled = false.

You can also set this option by:
ALTER DATABASE databasenameSET RECURSIVE_TRIGGERS OFF

The option can also be set like this:

EXEC sp_dboption 'database-name', 'recursive triggers', 'FALSE'

It appears that in different places you may see the values OFF, false, and 0, which all mean the same thing.  The inverse would be ON, true, and 1.

Once I set this option it did not fix the problem.  I ended up finding that there is another setting similar to Recursive Triggers called Nested Triggers.  It is not in the Options section with Recursive Triggers.

Nested Triggers are turned off like this:

sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

Make sure you are logged into the database for which you need to set the setting.

I don't entirely understand the difference between these two settings yet, but my guess is that Recursive Triggers controls whether a trigger is allowed to fire itself and that Nested Triggers controls whether a trigger can fire other triggers.

Sorting For Non Programmers With Report Builder

I have one primary user of the system I work on.  There are several user, but this one drives it and has a lot of need for various different reports.  It takes a long time to gather the requirements for each one, write a program to get it up on our web site, test, and deploy each one.

I have been looking for a way that he can gather and aggregate the data himself.  He can play with the data and turn it this way and that way with out getting a developer involved, other than training and supporting the product.

Another developer mentioned that he had heard about SQL Reporting Services (SSRS).  I looked into it and it still seemed like there would have to have a developer involved, since the tools are so complicated.

As I looked into SSRS I came across Report Builder.  It appeared as though each iteration of this product got a lot better.  I downloaded version 3.

It has a wizard for creating the report.  All of that is pretty straight forward, except two things.  I couldn't find a way to do sorting of the report through the wizard and the adding fields to the report seemed a little odd, but they said it is like a pivot table in Excel.  I don't know anything about pivot tables, but my user uses Excel a lot.

I searched and searched online for how to change the sorting and nothing was steering me into something simple for the user.  The best thing I found in my exploration was just adding an Order By clause to the SQL it generates.  That didn't seem great for a user, but I was almost willing to deal with the training for that.

Then I went through the program right clicking on things.  I found that I could pop up the properties window for the table that was the report and in there was a property called Sort Expression.  I had to right click on the grid and choose to select the grid and then I had to go to the View menu and select the properties check box.  Then that brought up the properties box.

It appears to be done in the report itself and not the SQL, because it doesn't appear to change the underlying SQL.

Authentication Popup When Trying to Access Files In Reports Folder

We are in the process of upgrading to a new server.  Our old one was a Go Daddy dedicated server, which we managed ourselves.  We are having Go Daddy migrate our web site to a newer, faster dedicated server.

As we went through the testing of the new server we found that pages that were in a directory called reports were popping up an authentication box in the browser whenever we would try to browse to them.

We tried checking the permissions on that folder and files and everything seemed fine.  We added permissions to try to help, but it did not fix it.

We tried moving the files to another existing folder and that worked, but we didn't want those files there.

We tried creating a new folder, moving the files there, deleting the old folder, and renaming the new one to reports.  We thought that perhaps the directory had gotten corrupted or something, but it didn't fix it.

We renamed the folder to report and that fixed it.  At first we thought the server didn't like the folder name reports.  They we got to thinking maybe it doesn't like the routing.  We are routing requests that come into server/reports/ReportName to server/reports/ReportName.aspx.

We tried just changing the routing, but that did not work.

We have now left it alone, but our newest theory is that maybe the new server has Reporting Services installed on it and it grabs requests to server/reports for itself.

Update 1-29-2013
I started getting the Windows Authentication popup again today.  This time what I had done was copy our website to another fold, create a new web site, and point the new site to the copy.  When I tried to bring up the home page I got the Window log in box.  I tried various different settings in IIS and also security settings on the folder with not luck.

I finally looked at the Authentication section of the website in IIS.  You get to this by clicking on the website in the left pain of IIS Manager, then clicking Authentication under the IIS section in the right pain.

I looked and saw that Anonymous Authentication was enabled, which seemed like the right value, so I left it alone for a while.  I went back to it later and right clicked on it.  Turns out there is an edit sub menu.  I clicked it and it gave me a screen to pick which user's identity to run under.  It was set to specific user and had a user name selected.  I'm guessing I could've picked another user or went somewhere else and given that user more rights.

Instead what I did was told it to use the application pool identity.  Once I did that I tried my website again and no more being asked for login credentials.  Not sure what the ramifications of this are, so if you find that it is insecure to do that, then maybe the picking a specific user or giving the default user sufficient rights is what you want to do.

Server Error 500.0 ASP.Net 4, Windows Server 2008 R2, IIS 7.5

I set up a new server to be a test environment for our production server.  When I installed it and tried to run the web site I got a .Net error that was telling me that my config file wanted version 4 of the .Net frame work.

Unrecognized attribute 'targetFramework'. Note that attribute names are case-sensitive.

I found that I needed to go to the application pool and set its >Net framework version to 4.0. It was set to 2.0

When the page loaded I got "HTTP Error 500.0 - Internal Server Error" in the browser.

I checked the event log and got "An application has reported as being unhealthy."

I found solutions online that said stop and restart the application pool.  That did not help.

I found solutions that talked about running a command from the .Net folder that would repair .Net.  I think the program they suggested running was aspnet_regiis.exe.  That did not help.

I looked and saw that Microsoft .Net Framework 4 Extended was missing in Programs and Features in Control Panel.  I downloaded and installed the full version of the .Net Framework.  I think I may have only installed the Client Profile.  Once I did the install it added Microsoft .Net Framework 4 Extended, but did not fix the problem.

After more research I uninstalled both Microsoft .Net Framework 4 Extended and also Microsoft .Net Framework 4 Client Profile and reinstalled the full version of the .Net Framework version 4.

After that i got a different error, but I think that has to do with the configuration of my code.  At least now it appears to be at least getting to .Net and .Net is trying to process the page.  I think the previous error was indicating that IIS was not able to pass off to .Net.

A friend of mine has the theory that I had installed .Net before IIS and that is why it was confused.  He said that IIS needs to be installed first for ASP.Net to get configured correctly.  Not sure if that is what happened here or not, but it does make sense what he is saying.

Stripping the Time Off of a DateTime Field in SQL Server

I find myself occasionally having to return just the date portion of a DateTime.  Every time I have to remember what I did the last time.

Here are a few solutions I have tried.  You can apply them to whatever your need is.

Let's say you have a field in your table called DateOfExecution and that date in one record is 2012-09-01 15:14:55.540

Method 1 - Convert to a NVarChar and Format It.
CONVERT(nvarchar(20),  DateOfExecution  , 101)

This method returns 09/01/2012.  This lets you just compare the date by itself, but you have to compare it as a string.

Method 2 - Cast It to a Float, Take Off the Decimal Portion With Floor, and Cast It Back to a DateTime
CAST( FLOOR( CAST(  DateOfExecution   AS FLOAT ) ) AS DATETIME )

This method returns 2012-09-01 00:00:00.000, leaving a time portion there, but the time portion is all zeros.  This can be useful if you want to get two dates into the same time and compare the entire DateTime.


Method 3 - Pull the Year, Month, and Day Out of the Date as Strings, Concatenate Them Together, and Cast Them Back to a DateTime
CAST(
(
STR( YEAR(  DateOfExecution   ) ) + '/' +
STR( MONTH(  DateOfExecution   ) ) + '/' +
STR( DAY(  DateOfExecution   ) )
)
AS DateTime
)

This method is more complicated and also returns 2012-09-01 00:00:00.000.

Method 4 - Pull the Year, Month, and Day Out of the Date as Strings, Trim the Individual Parts, and Concatenate Them Together

rtrim(ltrim(STR( YEAR(  DateOfExecution   ) ))) + '/' +
rtrim(ltrim(STR( MONTH(  DateOfExecution    ) ))) + '/' +
rtrim(ltrim(STR( DAY(  DateOfExecution   ) )))

This method returns a string that looks like this 2012/9/1.  You could apply some formatting and/or concatenate the values in a different order to make it look the way you want it to.

Sharing SQL Server (TSQL) Temp Tables Across Stored Procedures

I had a query that I created for a report.  Another report needed to display that report and use the data from it to display some other reports on the same page.

In order to pull in data from different sources and aggregate it at the end and to remove duplicates I dumped all the records I was accumulating into a temp table.  The problem I had was getting the information from that temple table to the other stored procedure that was creating the other reports.

At first I was using a local temp table using the #TableName syntax.  This was only available to the current stored procedure, so I had to change that to a global temp table using the ##TableName syntax.  After that the second stored procedure could use the temp table information.  Unfortunately, since it was global, even one who ran it was using the same information and were stepping all over each other.

I next decided that I would not drop the table at the top of the first store procedure, but create the records storing SPID in field in the temp table, so that I would only see my records and not some else's.  As I thought about it more I thought it might be best to use the UserId of the currently logged in user.  This would allow me to delete all of my records from previous runs without disturbing others' records.

From what I have seen the global temp table drops itself after some period of inactivity, so that is good too, but as a precaution I am considering also storing a datetime field on the records I am creating as well as the user name, so that I can delete records older that a certain number of minutes, since the report is run and looked at and the temp data is no longer needed.

So that I can dynamically create the temple table from a query I put in code to check for the temp table existing.

IF OBJECT_ID('tempdb..##NewSales') IS NOT NULL

If it does not exist then I do

INSERT INTO tempdb..##NewSales

SELECT {FieldList}

If it does exist then I do

SELECT  {FieldList} INTO tempdb..##NewSales