Question Querying a dataset?

[ccbryan]

Hi folks. Here's what I'm trying to do:

1. Pull lots of detail transaction records into a datatable, including blank columns for codes.
2. Loop through those records and populate the code fields based on values in other tables.
3. Create a summary datatable based on the coding in step 2.

This should be a piece of cake I know. Steps 1 and 2 are no problem but I'm completely stuck on step 3. Is there a way to do a 'normal' SQL query on a datatable?

(I'm not able to modify the database itself.)

Any help would be greatly appreciated!

Chandler

 

[jmcilhinney]

There are a few options. If you were using .NET 3.5 then LINQ to DataSet would be the obvious choice. If you're using .NET 2.0 then you may have to be a bit more manual depending on your needs. You haven't actually told us what your needs are though, so I can't really be more specific.

 

[ccbryan]

I apologize for the vagueness of my question. Here's a bit more detail...

There is a table of transactions, including fields for customer, salesman, etc.

The goal is to calculate incentive pay based on transactions. So I'm going to pull the relevant transactions into a datable DT and then examine each transaction to decide whether it results in a bonus or a penalty, and find the incentive dollar value of the transaction. I'm then writing these results to the datatable DT (i.e. popluating "type" and "value" fields). As I said, this part is no problem.

But once that's done I need to query DT to find the number of bonus and penalty transactions per customer, as well as the total incentive per salesman. In essence, I need to run queries on DT, because the data to be summarized as well as the data on which it is grouped does not exist in the original database transaction table.

So basically, I need to do this...

SELECT custid, type, count(transno) FROM ds.tables("DT") ORDER BY custid, type GROUP BY custid, type

...with the result going to another datatable or something similar that I can either report from or send to a CSV. Surely this is something that is done all the time? Or is the whole way I'm going about this hopelessly outdated?

 

[jmcilhinney]

What you want to do is very simple using LINQ because it can do pretty much what SQL can do but in .NET code rather than in a database. I want to confirm your .NET version because, if it's 3.5 or later, LINQ will make this operation almost trivial.

If you are using .NET 2.0 then it's a bit more difficult. The DataTable and DataView classes do give you some options but they can't combine all the different parts of a query into one and they can't do grouping for you automatically. In that case you will have to do some looping and manual calculations.

 

[ccbryan]

Thanks jmcilhinney for directing me to LINQ... it does look like exactly what I need. However... (of course there's a 'however')

My project is in framework 3.5. I have added a project reference to System.Data.Linq, and System.Linq is an 'imported namespace' and checked. In my form where this code is running, I have Imports System.Data.Linq and Imports System.Linq. Still, this code...

Imports IncentivePayVB
Imports System.Text
Imports System.Linq
Imports System.Data.Linq
Imports System.Data.SqlClient
Imports System.IO
Imports System.Data
Imports System.Collections

Public Class Form2

    Public startdate As String
    Public enddate As String
    Public startdateint As Int32
    Public dsCU01S As New DataSet
    Public dtCU01S As New DataTable

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim connstr5INC As String = My.Settings.connstr5INC
        Dim connstrSEDC As String = My.Settings.connstrSEDC
        Dim connstrSQL As String = My.Settings.connstrSQL
        startdate = "20110601"
        enddate = "20110630"

          'Get all rows from CU01S where either start or disconnect date is within date range
        Dim cnnCU01S As SqlConnection = New SqlConnection(connstr5INC)
        Dim cmdCU01S As SqlCommand = cnnCU01S.CreateCommand
        cnnCU01S.Open()
        cmdCU01S.CommandType = CommandType.Text
        cmdCU01S.CommandText = "Select CU01S_CUST_ID, CU01S_SIM, CU01S_PRODUCT, CU01S_USOC, " _
                    & "CU01S_TYPE, CU01S_START_DAT, CU01S_DISC_DATE, CU01S_BUBY, CU01S_BUDT, CU01S_UPBY, " _
                    & "'        ' AS TRANSTYPE, '     ' as ONT, 00.0000 AS INDINCENT, " _
                    & "'                         ' AS CUSTNAME, '    ' AS DISCREASON " _
                    & "from BTEDAT.CU01S where " _
                    & "((CU01S_START_DAT >= '" & startdate & "' and " _
                    & "CU01S_START_DAT <= '" & enddate & "') or " _
                    & "(CU01S_DISC_DATE >= '" & startdate & "' and " _
                    & "CU01S_DISC_DATE <= '" & enddate & "')) and (" _
                    & "CU01S_TYPE = 'CTV' or " _
                    & "CU01S_TYPE = 'DSRV' or " _
                    & "CU01S_TYPE = 'PLNE' or " _
                    & "CU01S_TYPE = 'VNL01') " _
                    & "ORDER BY CU01S_UPBY "
        Dim daCU01S As SqlDataAdapter = New SqlDataAdapter()
        daCU01S.SelectCommand = cmdCU01S
        daCU01S.Fill(dsCU01S, "InvData")

'Processing... populating transtype, etc.


        Dim Sales = ds.Tables("InvData").AsEnumerable()
        Dim SalesByCust = From s In [COLOR="#FF0000"]Sales[/COLOR] _
                         Order By s.CustID, s.transtype _
                         Group By Customer = s.CustID, TransType = s.transtype _
                         Into CustSales = Group, Count() _
                         Order By Customer, Transtype

...gives me the "Expression of type 'Object' is not queryable" error on the red 'sales'. According to Microsoft, this error is a result of not having the LINQ references in place. It sure seems to me as if I do... any ideas?

Chandler

 

[ccbryan]

Got it!

Option Infer = on.

 

[JohnH]

Turn on Option Infer, if not non-declared variables will be type Object. You should also turn on Option Strict.
In any case you don't have to convert the DataTable AsEnumerable (Of DataRow), it already is that.

 

[JohnH]

Option Infer = on.

I was just late by posting that then Type inference is a key element in Linq, where types are sometimes anonymous (not declarable) and sometimes rather complex and generic, if you'd have to be explicit about that in best case it could become an unreadable mess of type declaration all over the place.