Tag Archives: dotnet

Cancel long running SQL Command in ASP.NET WebForm application

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.

Thanks Arsalan Tamiz for posting this solution to my question on StackOverflow. His demo project was in C# (you can download it from the above link). but since most of my projects are in VB.NET – I did a conversion with some adjustments.
Continue reading →

Have you tried DotNet Fiddle?

You might be familiar with JS fiddle – an awesome tool that allows you to interactively test and share your JavaScript code along with gazillion of libraries and CSS classes.

You may even know non less awesome SQL Fiddle using which you can build custom schemas, tables, views and run and share your SQL queries in various RDBMS.

But have you heard about .NET Fiddle? This is a great interactive compiler, using IDE familiar to other fiddlers users it allows you to run and share your .NET code in both C# and VB.NET.

From the developers of .NET Fiddle:

We are a group of .NET developers who are sick and tired of starting Visual Studio, creating a new project and running it, just to test simple code or try out samples from other developers.

Give it try!

Solution for SqlDataReader.ReadColumnHeader NullReferenceException

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.

When SqlDataReader is missing rows

I have a very basic scenario:

  1. Execute TSQL Stored procedure
  2. Return a DataReader
  3. Read data from the Reader

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:

Get the reader:

m_dbSel.CommandType = CommandType.StoredProcedure
m_dbSel.CommandText = "SP_Name"
oResult = m_dbSel.ExecuteReader()

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.

UPDATE: Finally figured it out: http://stackoverflow.com/a/18520609/961695

ASP.NET: Delete file from server after TransmitFile command

It’s a common scenario: user needs to download a file from the server by clicking link or a button in your ASPX page. The server-side code for this is pretty straightforward and looks something like this:

Sub DownloadFile(ByVal i_sServerPath As String, ByVal i_sDisplayName As String)
   Dim oFile As FileInfo = New FileInfo(i_sServerPath)

   With Response
      .Clear()
      .ClearHeaders()
      .AddHeader("Content-Length", oFile.Length.ToString())
      .ContentType = ReturnContentType(oFile.Extension.ToLower())
      .AddHeader("Content-Disposition", "attachment; filename=" & i_sDisplayName)
      .TransmitFile(oFile.FullName)
      .End()
   End With

End Sub

The code accepts 2 parameters: i_sServerPath – full path to the file on the server and i_sDisplayName – file name that will be displayed to the user in the “Save As” dialog. Code also populates response headers based on file information. I use this handy function to populate ContentType based on file extention:

Function ReturnContentType(ByVal i_sfileExtension As String) As String
   Select Case i_sfileExtension
      Case ".htm", ".html", ".log" : Return "text/HTML"
      Case ".txt" : Return "text/plain"
      Case ".doc" : Return "application/ms-word"
      Case ".tiff", ".tif" : Return "image/tiff"
      Case ".asf" : Return "video/x-ms-asf"
      Case ".avi" : Return "video/avi"
      Case ".zip" : Return "application/zip"
      Case ".xls", ".csv" : Return "application/vnd.ms-excel"
      Case ".gif" : Return "image/gif"
      Case ".jpg", "jpeg" : Return "image/jpeg"
      Case ".bmp" : Return "image/bmp"
      Case ".wav" : Return "audio/wav"
      Case ".mp3" : Return "audio/mpeg3"
      Case ".mpg", "mpeg" : Return "video/mpeg"
      Case ".rtf" : Return "application/rtf"
      Case ".asp" : Return "text/asp"
      Case ".pdf" : Return "application/pdf"
      Case ".fdf" : Return "application/vnd.fdf"
      Case ".ppt" : Return "application/mspowerpoint"
      Case ".dwg" : Return "image/vnd.dwg"
      Case ".msg" : Return "application/msoutlook"
      Case ".xml", ".sdxl" : Return "application/xml"
      Case ".xdp" : Return "application/vnd.adobe.xdp+xml"
      Case Else : Return "application/octet-stream"
   End Select
End Function

It’s all good, but what if you need to delete the file from the server immediately after user downloaded it? Continue reading →

Solution for ASP.NET access to temporary folder denied error

Scenario: You’re trying to run an ASP.NET application when suddenly an error is thrown similar to this:

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0016: Could not write to output file ‘c:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\……..\………\some_code.dll’ — ‘Access is denied. ‘

You gave that folder all possible permissions imaginable and still the error persist. What the? Continue reading →

Cheat for “These columns don’t currently have unique values” error

Ordinary when you create a parent-child relationship between DataTables in a DataSet – there is a requirement that all values of the parent columns need to be unique. If they’re not – you will get an error: “These columns don’t currently have unique values“.

But there’re times when you need to make the relationship work even if those values are not unique. The solution is not to create constrain when creating the relationship. This can be done for example by passing FALSE as value for createConstrains parameter of Dataset.Relations.Add method:

oMyDataSet.Relations.Add("MyRel", _
oMyDataSet.Tables("TheParent").Columns("ParentColumn"), _
dtSet.Tables("TheChild").Columns("ChildColumn"), _
False)

Error using Crystal Reports 2008 in ASP.NET application on 64bit server

If you’re using Crystal Reports 2008 in your ASP.NET application, and after deploying to a 64bit server getting following error:

An error has occurred while attempting to load the Crystal Reports runtime. Either the Crystal Reports registry key permissions are insufficient, or the Crystal Reports runtime is not installed correctly. Please install the appropriate Crystal Reports redistributable (CRRedist*.msi) containing the correct version of the Crystal Reports runtime (x86, x64, or Itanium) required. Please go to http://www.businessobjects.com/support for more information.

then switch your application to 32bit mode. In case of Windows 2003/IIS6 entire server will have to be switched, in case of Windows 2008/IIS7 a dedicated 32bit application pool can be established for your application.

WARP, UltraWebGrid and ScriptManager glitch in IE6 and IE7

This is probably a very obscure situation, but it happened to me, it could happen to someone else. Scenario: an ASP.NET page with Infragistics UltraWebGrid inside of a WARP panel. A button outside the WARP serves as a trigger for partial postback. First click on the button causes expected partial postback, but on the second click page does full postback and is screwed after that. The issue happens only in IE6/7, page works correctly in IE8.

Another condition – page contains ASP.NET AJAX ScriptManager control with ServiceReference path pointing to an ASMX WebService.

Turned out the issue was caused by project being left in debug mode (in web.config debug=”true”). Which caused WebService page to be loaded with parameter “jsdebug” in query string. Which apparently IE6 and 7 didn’t like very much. Switching to debug=”false” in web.config solved the problem.

ASP.NET Chart Control is not rendering image

If you’re using MS Chart Control for .NET Framework 3.5 SP1 (in .NET Framework 4.0 it comes as a part of a framework), you may experience a strange behavior when chart images aren’t rendered on the page:

Chart image isn't rendering

If you’re using HTTP Handler to serve chart images (image URL looks something like “…/ChartImg.axd?i=chart_24dae5cb1f024c4a89f4fe492f05cc59_0.png“) missing mapping in IIS configuration could be to blame Continue reading →