EDI to DB

June 17, 2019 15:42

All EDI Templates are prepared for use by the Entity Framework, so that:

An Id property is included for every class
Complex properties are defined as virtual
Collections of properties are defined as List<>
A separate DB context is provided for each EDI version

HIPAA Database and how to use Entity Framework Migrations

Create EDI Database

Using the Code First functionality of Entity Framework, a new database will be created automatically the first time a transaction is saved.

A DbContext is provided for every X12 and EDIFACT version.

To create a fresh new database for any X12 or EDIFACT version, do the following steps:

Create a new ConsoleApplication project and install Entity Framework and EdiFabric from NuGet.
Add all EDI Templates from 4010 version, together with any shared files and the DbContext.cs file.

Translate a sample X12 or EDIFACT purchase order

Download X12 sample file

Download EDIFACT sample file

Add the following code to Program.cs:

using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using EdiFabric.Core.Model.Edi;
using EdiFabric.Framework.Readers;
using EdiFabric.Templates.X12004010;

Stream edi = File.OpenRead(@"C:\\PurchaseOrder.txt");

List<IEdiItem> ediItems; 
using (var reader = new X12Reader(edi)) 
    ediItems = reader.ReadToEnd().ToList();

var purchaseOrders = ediItems.OfType<TS850>();

using (var db = new X12Context())
{
    db.TS850.AddRange(purchaseOrders);
    db.SaveChanges();
}

Change the path in File.OpenRead to the path of the sample file you'll be using.

Edit the connection string in App.config to point to your desired SQL Server instance

A default connection string would look like:

Edit the DbContext.cs file

Add the following to the DbContext file to use the supplied connection string and to (optionally) remove pluralizing the table names:

public class X12Context : DbContext
{
    public X12Context() : base("name=X12ConnectionString")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }

Also, add DbSets for the control segments if not present:

public DbSet<ST> ST { get; set; }
public DbSet<SE> SE { get; set; }

Step 6: Run the code

The first time this is executed, a new database with the name "X12_4010" (specified in the connection string) will be created.

X12 Example

EDIFACT Example

Pull from the database

Use Entity Framework (or other data access technology if you prefer so) to query the database.

Example code