none
LINQ, CSV, Tab Delimited and DataSets/Datatables RRS feed

  • Question

  • My scenario is that I receive several text files either in CSV or Tab delimited format. The number of fields in each may vary, but I only need two columns from each; part number and quantity, and I know where those two fields are in each file. ( I use a control SQL table to keep track of file locations, vendor name and number, fields with partno and qty, etc.)  The end result will be comparing the part number and quantity to a master list we keep and updating that master list with changed quantities only. The text files range in size from a few K to a few MBs and there are about 30 files. Not all parts_no's are in our master list. So filtering BEFORE we start updating would be nice.

    I'm looking for the most efficient way to get the individual file information into either multiple datatables in a dataset or one datatable that is compared to the master list. We want to minimize hits to any database and only look at information that's changed. I'm seeing a few ways to do this:

    1. Read individual files to their own datatable in a single dataset then loop through the datatables and update the master list (most realistic and already implemented and pretty traditional)

    2. Read individual files to ONE datatable then update the master list (my boss likes this one)

    3. Some way to use LINQ and skip the whole datatable/dataset scenario (not sure I like this, but it may be the most efficient)

    The more I think about this the more I find there may be even more ways to do this. Any advice is appreciated.

    Friday, March 13, 2015 3:04 PM

Answers

  • https://msdn.microsoft.com/en-us/library/bb387090.aspx?f=255&MSPPError=-2147217396

    You should learn how to use MS SQL Server Service Broker and the Broker Queue. You push the XML into a SB queue.

    https://msdn.microsoft.com/en-us/library/bb522893.aspx

    http://www.codeproject.com/Articles/20843/Simulating-Monolog-Conversations-with-SQL-ServerT

    Then you can use a Console or Windows Service that's looking at the queue to get the XML, using inline parameterized T-SQL or a stored procedure and SQL Command objects to work with SQL Server with no datasets or datatables involved -- period.

    As a matter of fact, MS SQL Server  SB can use the .NET CLI, which means that you can write a C# or VB.NET program that can be hosted on SQL Server that's  looking at the queue and processing the XML into T-SQL statements and executed.

    You should get yourself a good book on MS SQL Server Service Broker.

    Saturday, March 14, 2015 3:19 PM