A while back I wrote an article on how to Cancel long running SQL Command in ASP.NET WebForm application
Webucator ASP.NET training was kind enough to turn it into video-lesson. Enjoy!
Tag Archives: Data
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 →
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 →
Correctly compare file date against TSQL stored date
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.
Speed up Box.Net uploads
Box.Net service is know for its abysmal upload speeds, it’s a horrific sight to see your files crawl at several Kb/s, feels like blast from the dial-up past. But there’s a solution – instead of using official site – use it’s less known mirror: BoxEnterprise.Net
You can use it even if you have free personal account, for normal and bulk upload needs. Login and see your gigabytes fly by.
TSQL: Mix column names and ordinals in ORDER BY
It might be a weird scenario from a parallel universe, but in infinite dimensions it is bound to happen to someone else besides me. If you’re getting results from a query where you know some columns by name and others only by their ordinal position (e.g. if SQL is built dynamically and can change based on different condition) – you add sorting to that query by both ordinal position of a column and column name and even throw an expression into the mix. Allow me to illustrate:
Let’s get back to basics, namely Northwind database. If I ran a query like this:
SELECT * FROM Customers
I will get a result, similar to this:
Now to the weird part. Let’s say I need to sort this query by by Contact’s title and Contact’s last name. I don’t know what the column name for the Contact’s title will be, but I know if will always be a static 4th column. I don’t know where in the resultset Contact’s full name be located (it could be 3rd, or 8th or whatever column) but I do know it will always be called ContactName. Continue reading →
T-SQL String DeAggregate (Split, Ungroup) in SQL Server
A while back I posted a simple way to aggregate strings in SQL Server, but what if your task is opposite – you have a character-separated string values in your fields and you need to slice them into separate rows? Consider following scenario, you have a table:
CREATE TABLE #Employees (Country varchar(50), FirstNames varchar(50))
INSERT INTO #Employees(Country,FirstNames)
VALUES ('UK','Anne,Michael,Robert,Steven')
INSERT INTO #Employees(Country,FirstNames)
VALUES ('USA','Andrew,Janet,Laura,Margaret,Nancy')
SELECT Country, FirstNames FROM #Employees
That looks like this
Country FirstNames UK Anne,Michael,Robert,Steven USA Andrew,Janet,Laura,Margaret,Nancy
and now you need to split FirstNames so that every FirstName is displayed on separate row. Continue reading →
Solution for WebHierarchicalDataGrid “DataKeyField is invalid” error
If you’re using Infragistics WebHierarchicalDataGrid and getting “DataKeyField is invalid” error after assigning DataKeyFields property for the root level:
xMyGrid.DataKeyFields = "[Key 1],[Key 2],[Key 3]"
and trying some data manipulation (like deleting rows, binding data etc.), try using GridView property of the grid instead:
xMyGrid.GridView.DataKeyFields = "[Key 1],[Key 2],[Key 3]"
The first method used to work, but somewhere around 2011 release of NetAdvantage it broke.
Totally custom filter for UltraWebGrid using LINQ
Last time I described how LINQ can be used to custom filter ADO.NET DataTable. This time I will demonstrate how this technique can replace built-in server-side filtering in Infragistics UltraWebGrid.
By default if server-side filtering is enabled in UltraWebGrid controls, it displays a small “funnel” icon in the column header, if you click this icon – a list of unique values from this column is displayed and selecting a value filters the grid by displaying only rows that have this value.
This default behavior works, sort of – it has many issues. If your grid has paging enabled – only values from the current page will be displayed. If your columns or values has characters that grid doesn’t like (commas, square brackets etc.) an exception will be thrown (this happens because under the hood grid converts your filters into a string that is passed to DataTable’s RowFilter), there’re other issues as well.
Why leave anything to chance when you can provide filtering yourself? Continue reading →
Replace DataTable RowFilter with LINQ
ADO.NET DataTable offers handy RowFilter property (via its DefaultView object), for example to filter by some string value and display filtered data in a grid a code like this is used:
odtMyData.DefaultView.RowFilter = "Field = 'value'" odgDataGrid.DataSource = odtMyData odgDataGrid.DataBind()
where odtMyData is a DataTable object and odgDataGrid is a Data Grid.
This works well, but what if you need preprocess data in the DataTable prior comparing it to the filter value – what if you need to apply a user-defined function to it? In my case I had a VB function called ProcessTags which stripped HTML tags from a string, so for example strings like this:
<a href="http://someurl">This is a sample text</a>
and this
<span style="color:red">This is a </span><label>sample text</label>
would be converted into the same text
This is a sample text
So I needed to create condition that would return DataTable rows with that have both HTML values by comparing to "This is a sample text".
How? RowFilter‘s syntax is pretty poor and external function cannot be used, so something like
odtMyData.DefaultView.RowFilter = "ProcessTags(Field) = 'This is a sample text'" odgDataGrid.DataSource = odtMyData odgDataGrid.DataBind()
would throw an error.
Continue reading →