Microsoft’s SSRS is pretty advanced reporting system with multitude of advanced features. SSRS also has charting capabilities, but it’s somewhat lacking compared to more advanced desktop or web charting suites
On the other hand FusionCharts offers very cool charting package with gazillion of chart types and very cool features. But it uses JavaScript engine and renders charts client-side only!
Microsoft’s SQL Server Reporting Services supports rendering of HTML tags, but for some reason that support stuck in 1990s – only very limited set is supported. And even using that set is problematic.
Case in point – ordered list. While officially supported – the way it is rendered is the stuff nightmares are made off. Jumble of original tags generously intermixed with DIVs and SPANs – it’s a wonder it renders at all.
It’s an all too common scenario when your ASP.NET page takes too long to load and the culprit is slow, long running SQL query. It shouldn’t come to this, you should optimize your DB stuff to minimize delays, but if you’re trying to decode feline genome or find alien live in the neighboring galaxies – that’s unavoidable. So the page is running and at some point you decide enough is enough and decide you need to cancel it. But you want to do it gracefully, for example slow page is in an IFRAME and you want to remain in the parent page and you don’t want to close/reload the whole thing.
There’s a way. The idea is, every time you create an SqlCommand – you add it to static (shared in VB.NET) list. If command runs successfully – you remove it from the list. But if it takes too long – you can issue an AJAX call from client page to cancel the command stored in that list.
SELECT Something FROM Table1 WHERE SomethingElse IN (SELECT Lookup FROM Table2)
work perfectly, but the opposite query
SELECT Something FROM Table1 WHERE SomethingElse NOT IN (SELECT Lookup FROM Table2)
doesn’t return any results – and you know for a fact that there’re results – check values returned by the subquery. Chances are there’re NULLs among those values. If that’s the case – NOT IN won’t return any results.
As a quick workaround you can add IS NOT NULL condition to the subquery:
SELECT Something FROM Table1
WHERE SomethingElse NOT IN (SELECT Lookup FROM Table2 WHERE Lookup IS NOT NULL)
This post related to the previous one, but I decided to write a separate article because it seems to be a common problem.
Sometimes when you use SqlDataReader, you would get an exception:
NullReferenceException {“Object reference not set to an instance of an object.”}
at System.Data.SqlClient.SqlDataReader.ReadColumnHeader(Int32 i)
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
And the maddening thing – it doesn’t happen often, just every once in a while. And it happens at different times too, sometimes reader would read 100 records, sometimes 200 etc.
One possible case – SqlDataReader is losing its connection. And one possible reason for that – connection goes out of scope.
Consider following scenario – you have a function that returns SqlDataReader:
Function GetTheReader() as SqlDataReader
Dim oConn As New SqlConnection("Connection String") : oConn.Open()
Dim oComm As New SqlCommand("Stored Procedure", oConn)
Dim oReader As SqlDataReader = oComm.ExecuteReader(CommandBehavior.CloseConnection)
Return oReader
End Function
And you use it like this:
Dim oReader as SqlDataReader = GetTheReader()
'Begin use reader - loop, read data etc.
The problem with this approach that connection used to create the reader is stored in a private variable inside of `GetTheReader` function and when the function exits – the variable goes out of scope. Eventually, sooner or later Garbage Collector will collect it and close and dispose of connection – and at this time your SqlDataReader will fail.
The solution? Either use SqlDataReader at the same scope level you created it, or, if you do need to use function – pass connection object into it as one of the parameters, so it would remain valid after function exits.
This is ADO.NET 101. There is one problem: DataReader loses rows. This problem has haunted me forever, extensive research and numerous suggestion didn’t help, even though the code is extremely basic:
Pass the reader to class constructor to fill Generic List (of Integer):
Public Sub New(i_oDataReader As Data.SqlClient.SqlDataReader)
m_aFullIDList = New Generic.List(Of Integer)
While i_oDataReader.Read
m_aFullIDList.Add(i_oDataReader.GetInt32(0))
End While
m_iTotalNumberOfRecords = m_aFullIDList.Count
End Sub
This problem occurs when number of rows returned by the reader is relatively large (over 600,000 records). If this happens – number of rows added to the list from the reader is inconsistent, but always less than real one. Most often “magic” number of 524289 rows is returned.
Well, this is no longer a mystery, thanks to the great people from Stack Overflow @RBarryYoung, @granadaCoder and especially @MartinSmith who was the first to point me in the right direction – and here it is.
Even though the problem is with SqlDataReader – it is happening because it is used in conjunction with Generic List. List, as you may know has a flexible Capacity for number of elements it can store. When count of elements exceeds capacity – capacity increases and always to a power of 2. E.g.
When the count exceeds 4 elements – capacity is set to 8 (2^3)
When the count exceeds 8 elements – capacity is set to 16 (2^4)
When the count exceeds 16 elements – capacity is set to 32 (2^5)
etc..
This is what makes Generic List such a powerful tool, used by many large scale .NET projects, e.g. bingogodz.com. And ordinary this is not a problem. Unfortunately this is not the case when it is used together with SqlDataReader. When count of items in the List exceeds 524,288 (2^19) and its capacity is set to 1,048,576 (2^20) – SqlDataReader’s Read method suddenly returns False even though not all records have been read. No exception is thrown, it simple stops.
The only possible workaround I’ve found so far (I am still looking for better ones) is to pre-set List capacity in advance. Since, when using DataReader, you do not know number of rows, you’re left either with hardcoding the number or running another DB query to retrieve number of rows via something like COUNT(*). Like I said, I don’t like this workaround, please let me know if you find a better one.
If you work with SQL Server that is set to use UK (British English) format you may experience a strange behavior: when you try to assign a date that is in ‘yyyy-mm-dd’ format to a DateTime variable or insert it into a DateTime column – an error is thrown
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
or similar. Or even worse – no error is thrown, but an incorrect date is inserted. This is happening because even though ‘yyyy-mm-dd’ looks like universal ISO format, a UK SQL Server interprets it as ‘yyyy-dd-mm’. So a date like ‘2011-07-21’ will throw the above error, but the date ‘2011-08-01’ will be stored as ‘January 8, 2001’ instead of expected ‘August 1, 2011’.
To alleviate this problem simple add a time part to your date – make the format ‘yyyy-mm-ddThh:mm:ss’. So the dates above will become ‘2011-07-21T00:00:00’ and ‘2011-08-01T00:00:00’ and be interpreted correctly by SQL Sercer
If you need to track whether a particular file has changed or not (and I am not talking about FileSystemWatcher, often it’s an overkill), one way is to compare file’s LastWriteTime against stored value. But if you store the date value in SQL Server you may be in for a surprise – even if file date hasn’t changed – dates will not compare as equal. Consider following example:
'Reading current system file date/time
Dim dCurrentFileDate As DateTime = File.GetLastWriteTime(sFilePath)
'Storing file date/time in SQL Server table
CMyDatabaseClass.SaveDate(sFilePath, dCurrentFileDate)
'Immediately reading date/time value back
Dim dStoredFileDate As DateTime = CMyDatabaseClass.LoadDate(sFilePath)
'Comparing stored value against current
If dCurrentFileDate = dStoredFileDate
'
End If
The If statement on Line 11 will produce False results even if the dates seem identical. The reason – millisecond part of the datetime. SQL Server and .NET disagree on how to treat it so the best thing to do is to strip milliseconds from system file date/time before storing it or comparing against stored value.
Joe from StackOverflow.com offers a very elegant solution. That solution is in C# and here it is converted to VB.NET. To apply it to the example above simple add after the second line:
dCurrentFileDate = dCurrentFileDate.Value.AddTicks(-(dCurrentFileDate.Value.Ticks Mod TimeSpan.TicksPerSecond))
This will remove milliseconds from the date/time value and next time if you compare to the same value – values will be really equal.
Comments (2) 
I have a very basic scenario:
Themocracy
