Efficient EDI parsing into database in C#

Question

3+ years ago we were asked to develop an EDI solution for a client as a matter of urgency.

They wanted full IP/control etc. of the solution and didn't want to use free open source solutions, pay large sums of money for the likes of BizTalk etc. or pay recurring fees to a VAN.

We did some research at the time and actually didn't find a lot of information regarding EDI formats, parsing etc. so our 2 man development team just jumped straight in and developed a solution in C#/ASP.Net. Due to the low number of EDI message transactions that would be taking place (100 or so a day) we adopted a RegEx process for parsing, validation and inserting into the database. This was done via a seperate C# app that was scheduled to run every few minutes and connect to the clients various providers FTP, AS2, EBMX comms and download data as well as upload any outbound EDI messages.

We then developed a web front-end that allowed the clients staff full access to the data with various revenue reports, ability to control the data as well as allow some of the clients agents to log in and also interact with the data and initiate invoice transactions too.

The client now wants some more EDI work done for another avenue of their business, however, this time the edi message transactions would leap into the 1000's. Our development teams concern is the use of RegEx. I read recently that using RegEx for EDI parsing has huge overheads and should be avoided.

The only reason we adopted it in the first place was an inexperience of not knowing what was the best to use. That said, RegEx has made managing edi message templates a breeze including validation within the templates. The client has added several more providers to their books and we were able to add the new message templates (with custom alterations) in minutes.

After much more research recently we found that most solutions parse EDI files into XML. Is there a reason for this? Is this just to adopt a more common format and/or avoid database access? Is it quicker to just parse XML over the flat file EDI messages?

We want the data elements from the EDI file to be in the database? Would we just parse the XML file instead? Isn't this just another step of processing that could be avoided?

I apologise for the generic nature of my question but I am having a hard time locating the answers.

Many thanks for your time.

NOTE: Our development team only use Microsoft products so please take this into account when giving feedback.

Answer 1

About 3 years ago I also created an x12 parser, that parses x12 edi into xml. It is currently available as open source at http://x12parser.codeplex.com. The reason I did it this way was that I wanted the parsing part to not care about the the target, whether it was a database or perhaps flat files. It turns out that was valuable since some of the users used Oracle instead of Sql Server, and a lot of the users flattened it into flat files to load into their database or send to some downstream process. I think this has made the parser itself very flexible for many environments. The other reason I liked XML is because I was able to add other annotations that were valuable for anyone who didn't have all the EDI codes memorized (basically everyone), and I was able to transform it to HTML (see the site for an example) with those annotations. I also built in the ability to unbundle your objects into individual messages so that your post processing can consume then one object at a time. A lot of users have helped me optimize it so that it would handle huge files, so it's gotten pretty stable. I'm doing some maintenance on it now so that it will support all 4010 transactions. The part about parsing into the database I leave up to the user, because everyone seems to be very particular about how they design data tables (for example I couldn't agree with a co-worker on whether to use ints or GUIDs for table identities, those who lean toward DBA mentality prefer ints, those who use a lot of ORMs prefer GUIDs).

Shortly after I posted this, I did add database support, so you can skip the XML and have it go directly to a SqL Server database. You can decide how many segment types will be parsed out into individual tables so that you don't bloat your database with 300 tables of which you will probably only use 10 or 20. There is a discussion here SQL Server as Staging Environment about pros and cons of using xml or sql server as your intermediary to your final system.

Answer 2

I suspect most developers who chose to write their own solution wrote their own classes for EDI to XML conversion because their end point integration supported XML (or they couldn't write to the db directly, or wanted to use XSLT to show the end user the data nicely). I've written parsers that "translated" into CSV and flat file formats, because that's what we needed to import. I've also written parsers to dump directly into a database. Parsing into XML usually represents a necessary step for some as a "middleware" kind of approach. If you don't need to do the intermediary step, then why should you? If you can write it out to the DB, by all means do so. You also didn't mention what documents you are doing, and I'm assuming you've built out the FA process in your application. RegEx should continue to work for you, and there's a lot of ways to skin the cat.

With that said, my usual disclaimer applies. You are reinventing the wheel here. By miles. I understand your client's wishes, and glad you were able to meet the need. Frankly, I probably would have fired the client :) Since you only use Microsoft products, you've kind of hamstrung yourself. Looking around SO, BizTalk is more discussed than other packages. There's probably a reason for this, and as you found out, it's also very expensive. I'm a big fan of Liaison Delta - runs on Windows, uses Microsoft Foundation Classes at its core and allows you to translate any-to-any at a fraction of BizTalk's cost. Seems to me maintaining drag/drop "maps" is easier than maintaining thousands of lines of code, but hey, policy is policy :) Hope this helps.