Today I needed to do a simple thing: Combine selected values of .NET CheckListBox control into a comma separated string. I am lazy, so I decided to Google for a ready-to-use piece of code. Sure enough there’re tons of those. But all of them involve looping through control items, checking IF item is selected, then adding values.. Boring, routine stuff.
People! We live in the 21st century, age of inspiration! Uhm.. sorry, got carried away. Bottom line: I didn’t like any of those solutions and being a fan of LINQ I put together one of my own. Continue reading 'CheckListBox to comma-separated string'»
Linq2Sql has a great use of stored procedures – it converts them into methods which you can easily call using standardized .NET syntax. For example if you have SP:
ALTER PROCEDURE MyProcedure(MyParam int) ...
after dragging it into Linq2Sql designer you can call it in your .NET code like this:
Dim aResults = MyDbContext.MyProcedure(2011)
but there are 2 caveats. Continue reading 'Stored Procedure in LINQ2SQL query'»
In the past I described how to perform string aggregates in T-SQL. In this post I will show how strings can be concatenated in LINQ.
I am using ADO.NET data table as a source for the query, but LINQ being LINQ can pull data pretty much from anything so this example can easily be adjusted.
First things first, let’s create the source. As in T-SQL post I am using ol’ faithful Northwind database and getting data from the Employees table:
Dim oConn As New SqlConnection(sMyConnStr) : oConn.Open()
Dim oComm As New SqlCommand("SELECT Country, FirstName FROM Employees ORDER BY Country, FirstName", oConn)
Dim oAd As New SqlDataAdapter(oComm) : Dim oTable As New DataTable : oAd.Fill(oTable)
This will fill the datatable with employees’ first names and countries
Country FirstName
UK Anne
UK Michael
UK Robert
UK Steven
USA Andrew
USA Janet
USA Laura
USA Margaret
USA Nancy
And now we want to group this by the country, combining first names into comma separated string. Continue reading 'String Aggregate in LINQ'»
If you’re using Infragistics classic UltraWebGrid with LoadOnDemand not set and paging enabled, getting column filters to work can be tricky. By default clicking on Filter icon will display column data from current page only, ignoring other pages. To make it work you have to take matter in your own hands – populate filter data in code.
The best place to do it is in InitializeLayout event. There you can loop thru all the columns, calling function to populate column filters:
Protected Sub xMyGrid_InitializeLayout(ByVal sender As Object, ByVal e As LayoutEventArgs) Handles xMyGrid.InitializeLayout
For Each ugColumn As UltraGridColumn In e.Layout.Grid.Columns
GatherFilterDataForColumn(ugColumn)
Next
End Sub
Continue reading 'Showing ALL filters in UltraWebGrid with paging'»
Often you have to operate with flattened data that in reality contains multiple levels of hierarchy. For example it can come as a result of several SQL JOIN statement and look like this:
In this example data consist of static root column, region, site, type and state. And the data has clearly defined hierarchy (e.g. Region “India” has site “Bangalore”, site “Bangalore” has types “Application” and “Area”, type “Application” has states “N/A” and “Testing”).
To load this data into Infragistics UltraWebTree I put together a small procedure: Continue reading 'Using LINQ to bind flat data to Infragistics UltraWebTree'»
LINQ is truly integrated into VB.NET. This allows not only to use LINQ-specific query language in a standard VB.NET code, but use VB.NET code in a LINQ query. Consider function from the previous post. To make it universal we can pass one more parameter
“Aggregate Type” and based on that parameter return Min, Max, Avg, Sum or Count
Function GroupBy(ByVal i_sGroupByColumn As String, ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable, i_iAggregateType as Integer) As DataTable
dim aQuery = From row In i_dSourceTable Group By Group1 = row(i_sGroupByColumn) Into Group Select Group1, Aggr = Choose(i_iAggregateType, Group.Min(Function(row) row(i_sAggregateColumn)), Group.Max(Function(row) row(i_sAggregateColumn)), Group.Sum(Function(row) row(i_sAggregateColumn)), Group.Avg(Function(row) row(i_sAggregateColumn)), Group.Count(Function(row) row(i_sAggregateColumn)))
return aQuery.toDataTable
End Function
In this example VB.NET function Choose is used inside of a LINQ query’s Select Statement. If i_iAggregateType parameter is equal 1 – Minimum value, will be calculated, 2 – Maximum etc.
I’ve described before how to group and aggregate data in ADO.NET data table using standard .NET 2.0 features. But if you happen to use .NET 3.5 or above and Visual Studio 2008 or above – that entire block of code can be replaced with a single LINQ query:
Function GroupBy(ByVal i_sGroupByColumn As String, ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable
dim aQuery = From row In i_dSourceTable Group By Group1 = row(i_sGroupByColumn) Into Group Select Group1, Aggr = Group.Count(Function(row) row(i_sAggregateColumn))
return aQuery.toDataTable
End Function
The query at line 3 is a LINQ to DataSet query, so reference to System.Data.Linq has to be added to your project. The Group.Count aggregate can be replaced with Group.Max, Group.Min, Group.Sum or Group.Avg to perform respectful function.
There is one caveat though. Continue reading 'Grouping ADO.NET DataTable using LINQ'»
I was running a basic LINQ 2 SQL statement:
From role In db.user_role _
Where role.USER_ID = Session("user_id") Select role
when I encountered following error message:
Method ‘System.Object CompareObjectEqual(System.Object, System.Object, Boolean)’ has no supported translation to SQL.
After a little research I found the solution.
Continue reading 'Method ‘System.Object CompareObjectEqual(System.Object, System.Object, Boolean)’ has no supported translation to SQL. Solution to error'»
Comments (0) 
Themocracy