Question Datatables or database?

[nbet]

I need some advice on if can avoid database tables and transactions. I'm reading a formatted text file into a memory-only datatable, called Input, without updating it to a database as I don't really want the overhead. I'd like to perform a SELECT query using my Input datatable and some lookup tables in a SQL db and INSERT the results into a new memory-only datatable called Output. Again, I just want my datatables Input and Output to be memory-only datatables that don't reside in a db.
But I don't know how to set up a query like that. Can anybody point me in the right direction or tell me that it's not worth it?
Here's what the query looks like using standard db tables and tableadapters. It's not exactly a simple query.

INSERT INTO Output
(RecType, CODE, AreaID, PARAMETER, MCC, Class, Style, Stock_Thickness_Gage, MCCAlt1, MCCAlt2, Comp1, Comp1Qty, Comp2, Comp2Qty, Comp3, Comp3Qty,
Comp4, Comp4Qty, Comp5, Comp5Qty, Category)
SELECT Codes.RECTYPE, Input.CODE, Codes.AREAID, Input.PARAMETER, tblMfgMtl.MCC, ['q-MCC List$'].Class, ['q-MCC List$'].Style, ['q-MCC List$'].Stock_Thickness_Gage,
tblMfgMtl.MCCAlt1, tblMfgMtl.MCCAlt2, tblMfgAssy.Comp1, tblMfgAssy.Comp1Qty, tblMfgAssy.Comp2, tblMfgAssy.Comp2Qty, tblMfgAssy.Comp3,
tblMfgAssy.Comp3Qty, tblMfgAssy.Comp4, tblMfgAssy.Comp4Qty, tblMfgAssy.Comp5, tblMfgAssy.Comp5Qty, ['q-MCC List$'].Category
FROM tblMfgAssy RIGHT OUTER JOIN
Codes INNER JOIN
Input ON Codes.CODE = Input.CODE ON tblMfgAssy.MfgAssyID = Codes.MfgAssy LEFT OUTER JOIN
['q-MCC List$'] INNER JOIN
tblMfgMtl ON ['q-MCC List$'].MCC = tblMfgMtl.MCC ON Codes.MfgMtl = tblMfgMtl.MfgMtlID
WHERE (NOT (Input.CODE LIKE ''))

 

[jmcilhinney]

Once you have a DataTable there are various ways to extract data from it. The DataTable itself has Select and Compute methods. It also has a DefaultView property, which returns a DataView. The DataView has Sort and Filter properties and a ToDataTable method. You can also use LINQ to do all sorts of things with the data, pretty much as you could on database tables with SQL.

As you're performing a join in that SQL, LINQ would be the way to go. I suggest that you do a bit of reading on the topic to get an understanding of how it works, then we can provide some help on this particular issue if you can't get it to work. As with all complex queries, you shouldn't try to code it all at once. Start out by querying a single table. Once that works, add a join. Once that works, try adding a filter. Etc, etc.

 

[nbet]

Thanks! That sounds like good advice. I did read a bit on LINQ a few months ago so I am familiar with it. But I remember reading that LINQ may not be supported in the future so I decided not to keep going. Any thoughts on that?

 

[jmcilhinney]

I think that it might have been LINQ to SQL that you read might not be supported. LINQ in general will undoubtedly be supported for a long time time to come as it's a very useful technology. There are various flavours of LINQ dedicated to certain areas of functionality though. LINQ to SQL was created as a way to access SQL Server databases without having to worry about writing any SQL code or even ADO.NET code. It's essentially been made redundant by the Entity Framework and the associated LINQ to Entities, but the story at the moment is that LINQ to SQL will continue to be supported and developed in parallel with the EF. It's hard to see how that's any more than a courtesy to those who've invested in it to this point though.

 

[jmcilhinney]

Note that, in this case, it's LINQ to DataSet that you'd be using, which has no specific connection with LINQ to SQL.

 

[cjard]

without updating it to a database as I don't really want the overhead

So you have a load of data that you want to transform, join and manipulate, and you want to do this in a client side datatable, because you "don't want the overhead" of using a database

That's a bit like saying youre gonna use a carrier pigeon to communicate with the folks back home because you don't want the overhead of typing a text message into a mobile phone

Use the right tool for the job