Category Archives: ADO.NET

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 →

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

ADO.NET DataTable: Change Column DataType after table is populated with data

Sometimes there is a need to change DataType of ADO.NET DataTable column. If your table is populated as a result of some database operation – you don’t know in advance what type the columns will be. And by design you cannot change the type of the column after the table is populated. Conudrum. Catch 22. Tough luck.

But wait, there’s light at the end of the tunnel. You cannot change the type of the existing column, but you can create a new one. Continue reading →

WHDG: RowIslandsPopulating event fires multiple times

I’ve been successfully using manual load on demand in WebHierarchicalDataGrid for a while now, but recently noticed strange thing. The deeper in grid’s hierarchy I expanded the children – the slower it went.

In my case every time user clicks [+] to expand a row, VB.NET code calls an SQL Server Stored procedure to bring in child rows. I grew suspicious and fired up SQL Profiler. What I saw surprised me. Number of calls to the stored procedure increased the deeper in grid’s hierarchy I expanded the children. When I clicked [+] on the root level it resulted in 1 SP call. Clicking [+] on the child to expand grandchild – 2 calls. Expanding grandchild to see grand-grandchild rows – 3 calls, etc. Continue reading →

SQL Server stored procedure runs slow from .NET code

This has probably been discussed a lot before, but just in case here it is again, possible solution for following scenario:

You’re calling SQL Server stored procedure from your .NET code and it runs extremely slow. When you run same SP with exactly the same parameters (as captured by SQL Server Profiler) directly in SQL Server Management Studio, it runs very fast. What gives?

Chances are – that SP was executed before and query plan was cached for the specific parameters. To avoid this add WITH RECOMPILE option to your CREATE PROCEDURE or ALTER PROCEDURE statement. This will force SQL Server to create a new query plan every time SP runs, perhaps adding slight overhead, but creating an optimized path that will cover that overhead tenfold.

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)

WebHierarchicalDataGrid: Manual Load on Demand when bound to DataSet

Even though I have my issues with Infragistics WebHierarchicalDataGrid control, it has some neat features and I found that with some tweaks you can make it work.

Case in point: Manual Load on Demand. If you have hierarchical data structure, it allows you to retrieve only root level data and then when user clicks “Expand” arrow – get additional data on as needed basis:

Manual Load on Demand in action

This is achieved by handling RowIslandsPopulating grid’s event in which you can run a DB query based on parent row data, then manually create a ContainerGrid object bind it to the data and add it to parent row RowIslands collection:

Protected Sub myGrid_RowIslandsPopulating(ByVal sender As Object, ByVal e As ContainerRowCancelEventArgs) Handles myGrid_.RowIslandsPopulating

     e.Cancel = True

     Dim oData as SomeDataType = GetData()
     Dim oChildGrid As New ContainerGrid()

     e.Row.RowIslands.Add(oChildGrid)

     oChildGrid.DataKeyFields = "SOME_ID"
     oChildGrid.Level = e.Row.Level + 1
     oChildGrid.DataSource = oData
     oChildGrid.DataBind()

End Sub

For this approach to work top-level rows need to display “Expand” arrows that user can click. Continue reading →

“Cannot find column” DataTable error while grouping or sorting Infragistics UltraWebGrid

If you’re binding an ADO.NET DataTable to Infragistics UltraWebGrid and then programmaticaly sort the grid (e.g. add a column to a band’s SortedColumns collection) you may get an error:

Cannot find column My Column Name.

with stack trace starting from grid databinding and finishing in datatable’s sorting:

at System.Data.DataTable.ParseSortString(String sortString)
at System.Data.DataView.CheckSort(String sort)
at System.Data.DataView.set_Sort(String value)
at Infragistics.WebUI.UltraWebGrid.DBBinding.ProcessDataViewForFillRows(DataView dataView, RowsCollection rows)
at Infragistics.WebUI.UltraWebGrid.DBBinding.FillRows(UltraWebGrid grid, RowsCollection rows, IEnumerable datasource)
at Infragistics.WebUI.UltraWebGrid.DBBinding.BindList(IEnumerable datasource)
at Infragistics.WebUI.UltraWebGrid.DBBinding.DataBind(Object dataSource, String dataMember)
at Infragistics.WebUI.UltraWebGrid.UltraWebGrid.DataBind()

If the grid binds OK without sorting and grouping, but fails with either – most likely the culprit is one of the columns in data table. Continue reading →