private string sPath = AppDomain.CurrentDomain.BaseDirectory;
private string sConnection;
private SqlConnection oConnection;
private SqlDataAdapter oDaInterchange = new SqlDataAdapter();
private SqlDataAdapter oDaFuncGroup = new SqlDataAdapter();
private SqlDataAdapter oDaHeader = new SqlDataAdapter();
private SqlDataAdapter oDaDetail = new SqlDataAdapter();
private Int32 nInterkey;
private Int32 nGroupkey;
private Int32 nHeaderkey;
private Int32 nDetailkey;
private void btnTranslate_Click(object sender, EventArgs e)
{
ediDocument oEdiDoc = null;
ediDataSegment oSegment = null;
ediSchemas oSchemas = null;
string sSegmentID;
string sLoopSection;
int nArea;
string sQlfr = "";
string sSql = "";
//sPath = AppDomain.CurrentDomain.BaseDirectory;
sConnection = "persist security info=False; integrated security=true; initial catalog=TestDb; server=(local)";
oConnection = new SqlConnection(sConnection);
oConnection.Open();
//CREATES EDIDOC OBJECT
ediDocument.Set(ref oEdiDoc, new ediDocument());
//Sets cursor type to ForwardOnly
oEdiDoc.CursorType = DocumentCursorTypeConstants.Cursor_ForwardOnly;
//Makes certain FREDI uses SEF files only
oSchemas = (ediSchemas)oEdiDoc.GetSchemas();
oSchemas.EnableStandardReference = false;
//Load SEF files
oEdiDoc.LoadSchema(sPath + "850_004010.EVAL0.SEF", 0); //evaluation SEF file
//Load EDI file
oEdiDoc.LoadEdi(txtEdiFile.Text);
oSegment = (ediDataSegment)oEdiDoc.FirstDataSegment;
while (oSegment != null)
{
sSegmentID = oSegment.ID;
sLoopSection = oSegment.LoopSection;
nArea = oSegment.Area;
if (nArea == 0)
{
if (sLoopSection == "")
{
if (sSegmentID == "ISA")
{
//insert a record into interchange table
sSql = @"INSERT INTO [Interchange] ( ISA01_AuthorizationInfoQlfr, ISA02_AuthorizationInfo, ISA03_SecurityInfoQlfr,
ISA04_SecurityInfo, ISA05_SenderIdQlfr, ISA06_SenderId, ISA07_ReceiverIdQlfr, ISA08_ReceiverId, ISA09_Date, ISA10_Time,
ISA11_RepetitionSeparator, ISA12_VersionNumber, ISA13_ControlNumber, ISA14_AckRequested, ISA15_UsageIndicator,
ISA16_ComponentSeparator)
values
(@ISA01_AuthorizationInfoQlfr, @ISA02_AuthorizationInfo, @ISA03_SecurityInfoQlfr,
@ISA04_SecurityInfo, @ISA05_SenderIdQlfr, @ISA06_SenderId, @ISA07_ReceiverIdQlfr, @ISA08_ReceiverId, @ISA09_Date, @ISA10_Time,
@ISA11_RepetitionSeparator, @ISA12_VersionNumber, @ISA13_ControlNumber, @ISA14_AckRequested, @ISA15_UsageIndicator,
@ISA16_ComponentSeparator);
SELECT scope_identity()";
oDaInterchange.InsertCommand = new SqlCommand(sSql, oConnection);
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA01_AuthorizationInfoQlfr", oSegment.get_DataElementValue(1, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA02_AuthorizationInfo", oSegment.get_DataElementValue(2, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA03_SecurityInfoQlfr", oSegment.get_DataElementValue(3, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA04_SecurityInfo", oSegment.get_DataElementValue(4, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA05_SenderIdQlfr", oSegment.get_DataElementValue(5, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA06_SenderId", oSegment.get_DataElementValue(6, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA07_ReceiverIdQlfr", oSegment.get_DataElementValue(7, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA08_ReceiverId", oSegment.get_DataElementValue(8, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA09_Date", oSegment.get_DataElementValue(9, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA10_Time", oSegment.get_DataElementValue(10, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA11_RepetitionSeparator", oSegment.get_DataElementValue(11, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA12_VersionNumber", oSegment.get_DataElementValue(12, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA13_ControlNumber", oSegment.get_DataElementValue(13, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA14_AckRequested", oSegment.get_DataElementValue(14, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA15_UsageIndicator", oSegment.get_DataElementValue(15, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA16_ComponentSeparator", oSegment.get_DataElementValue(16, 0));
nInterkey = (Int32)(decimal)oDaInterchange.InsertCommand.ExecuteScalar();
}
else if (sSegmentID == "GS")
{
//insert a record into FunctionGrop table
sSql = @"INSERT INTO [FunctionalGroup] (InterKey, GS01_FunctionalIdfrCode, GS02_SendersCode, GS03_ReceiversCode, GS04_Date, GS05_Time,
GS06_Control_Number, GS07_Agency_Code, GS08_VersionCode)
values
(@InterKey, @GS01_FunctionalIdfrCode, @GS02_SendersCode, @GS03_ReceiversCode, @GS04_Date, @GS05_Time,
@GS06_Control_Number, @GS07_Agency_Code, @GS08_VersionCode);
SELECT scope_identity()";
oDaFuncGroup.InsertCommand = new SqlCommand(sSql, oConnection);
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@InterKey", nInterkey);
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS01_FunctionalIdfrCode", oSegment.get_DataElementValue(1, 0)); //Functional Identifier Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS02_SendersCode", oSegment.get_DataElementValue(2, 0)); //Application Sender's Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS03_ReceiversCode", oSegment.get_DataElementValue(3, 0)); //Application Receiver's Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS04_Date", oSegment.get_DataElementValue(4, 0)); //Date
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS05_Time", oSegment.get_DataElementValue(5, 0)); //Time
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS06_Control_Number", oSegment.get_DataElementValue(6, 0)); //Group Control Number
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS07_Agency_Code", oSegment.get_DataElementValue(7, 0)); //Responsible Agency Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS08_VersionCode", oSegment.get_DataElementValue(8, 0)); //Version / Release / Industry Identifier Code
nGroupkey = (Int32)(decimal)oDaFuncGroup.InsertCommand.ExecuteScalar();
}
}
} //nArea == 0
else if (nArea == 1)
{
if (sLoopSection == "")
{
if (sSegmentID == "ST")
{
sSql = @"INSERT INTO [Header] ( GroupKey, ST01_TS_IdfrCode, ST02_TS_ControlNumber,
BEG03_PurchaseOrderNumber, BEG05_PODate, REF02_VendorIDNo,
ITD03_TermsDiscountPercent, ITD05_TermsDiscountDaysDue, ITD07_TermsNetDays,
DTM02_DeliveryDate, N102_BillToName, N104_BillToIdCode, N301_BillToAddress, N401_BillToCity,
N402_BillToState, N403_BillToZip, N102_ShipToName, N104_ShipToIdCode,
N301_ShipToAddress, N401_ShipToCity, N402_ShipToState, N403_ShipToZip )
values
(@GroupKey, @ST01_TS_IdfrCode, @ST02_TS_ControlNumber,
@BEG03_PurchaseOrderNumber, @BEG05_PODate, @REF02_VendorIDNo,
@ITD03_TermsDiscountPercent, @ITD05_TermsDiscountDaysDue, @ITD07_TermsNetDays,
@DTM02_DeliveryDate, @N102_BillToName, @N104_BillToIdCode, @N301_BillToAddress, @N401_BillToCity,
@N402_BillToState, @N403_BillToZip, @N102_ShipToName, @N104_ShipToIdCode,
@N301_ShipToAddress, @N401_ShipToCity, @N402_ShipToState, @N403_ShipToZip);
SELECT scope_identity()";
oDaHeader.InsertCommand = new SqlCommand(sSql, oConnection);
oDaHeader.InsertCommand.Parameters.AddWithValue("@GroupKey", nGroupkey);
oDaHeader.InsertCommand.Parameters.AddWithValue("@ST01_TS_IdfrCode", oSegment.get_DataElementValue(1, 0));
oDaHeader.InsertCommand.Parameters.AddWithValue("@ST02_TS_ControlNumber", oSegment.get_DataElementValue(2, 0));
oDaHeader.InsertCommand.Parameters.AddWithValue("@BEG03_PurchaseOrderNumber", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@BEG05_PODate", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@REF02_VendorIDNo", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@ITD03_TermsDiscountPercent", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@ITD05_TermsDiscountDaysDue", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@ITD07_TermsNetDays", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@DTM02_DeliveryDate", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N102_BillToName", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N104_BillToIdCode", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N301_BillToAddress", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N401_BillToCity", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N402_BillToState", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N403_BillToZip", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N102_ShipToName", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N104_ShipToIdCode", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N301_ShipToAddress", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N401_ShipToCity", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N402_ShipToState", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N403_ShipToZip", "");
nHeaderkey = (Int32)(decimal)oDaHeader.InsertCommand.ExecuteScalar();
}
else if (sSegmentID == "BEG")
{
sSql = @"UPDATE [Header] SET BEG03_PurchaseOrderNumber = @BEG03_PurchaseOrderNumber, BEG05_PODate = @BEG05_PODate
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BEG03_PurchaseOrderNumber", oSegment.get_DataElementValue(3, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BEG05_PODate", oSegment.get_DataElementValue(5, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "REF")
{
sSql = @"UPDATE [Header] SET REF02_VendorIDNo = @REF02_VendorIDNo
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_VendorIDNo", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "ITD")
{
sSql = @"UPDATE [Header] SET ITD03_TermsDiscountPercent = @ITD03_TermsDiscountPercent,
ITD05_TermsDiscountDaysDue = @ITD05_TermsDiscountDaysDue, ITD07_TermsNetDays = @ITD07_TermsNetDays
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@ITD03_TermsDiscountPercent", oSegment.get_DataElementValue(3, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@ITD05_TermsDiscountDaysDue", oSegment.get_DataElementValue(5, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@ITD07_TermsNetDays", oSegment.get_DataElementValue(7, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "DTM")
{
sSql = @"UPDATE [Header] SET DTM02_DeliveryDate = @DTM02_DeliveryDate
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@DTM02_DeliveryDate", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
} //sLoopSection == ""
else if (sLoopSection == "N1")
{
//Get Qualifier
if (sSegmentID == "N1")
{
sQlfr = oSegment.get_DataElementValue(1, 0);
}
if (sQlfr == "ST")
{ //Ship To information
if (sSegmentID == "N1")
{
sSql = @"UPDATE [Header] SET N102_ShipToName = @N102_ShipToName, N104_ShipToIdCode = @N104_ShipToIdCode
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N102_ShipToName", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N104_ShipToIdCode", oSegment.get_DataElementValue(4, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "N3")
{
sSql = @"UPDATE [Header] SET N301_ShipToAddress = @N301_ShipToAddress
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N301_ShipToAddress", oSegment.get_DataElementValue(1, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "N4")
{
sSql = @"UPDATE [Header] SET N401_ShipToCity = @N401_ShipToCity,
N402_ShipToState = @N402_ShipToState, N403_ShipToZip = @N403_ShipToZip
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N401_ShipToCity", oSegment.get_DataElementValue(1, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N402_ShipToState", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N403_ShipToZip", oSegment.get_DataElementValue(3, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
} // sQlfr == "ST"
else if (sQlfr == "BT")
{ //Bill To information
if (sSegmentID == "N1")
{
sSql = @"UPDATE [Header] SET N102_BillToName = @N102_BillToName, N104_BillToIdCode = @N104_BillToIdCode
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N102_BillToName", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N104_BillToIdCode", oSegment.get_DataElementValue(4, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "N3")
{
sSql = @"UPDATE [Header] SET N301_BillToAddress = @N301_BillToAddress
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N301_BillToAddress", oSegment.get_DataElementValue(1, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "N4")
{
sSql = @"UPDATE [Header] SET N401_BillToCity = @N401_BillToCity,
N402_BillToState = @N402_BillToState, N403_BillToZip = @N403_BillToZip
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N401_BillToCity", oSegment.get_DataElementValue(1, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N402_BillToState", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N403_BillToZip", oSegment.get_DataElementValue(3, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
} // sQlfr == "BT"
} // sLoopSection == "N1"
} // nArea == 1
else if (nArea == 2)
{
if (sLoopSection == "PO1")
{
if (sSegmentID == "PO1")
{
sSql = @"INSERT INTO [Detail] ( HeaderKey, PO101_AssignedId, PO102_QtyOrdered, PO103_UnitMeasure,
PO104_UnitPrice, PO107_CatalogNo, PO109_EAN, PO401_Pack, PO402_Size, PID05_Description )
values
(@HeaderKey, @PO101_AssignedId, @PO102_QtyOrdered, @PO103_UnitMeasure,
@PO104_UnitPrice, @PO107_CatalogNo, @PO109_EAN, @PO401_Pack, @PO402_Size, @PID05_Description);
SELECT scope_identity()";
oDaDetail.InsertCommand = new SqlCommand(sSql, oConnection);
oDaDetail.InsertCommand.Parameters.AddWithValue("@HeaderKey", nHeaderkey);
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO101_AssignedId", oSegment.get_DataElementValue(1, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO102_QtyOrdered", oSegment.get_DataElementValue(2, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO103_UnitMeasure", oSegment.get_DataElementValue(3, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO104_UnitPrice", oSegment.get_DataElementValue(4, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO107_CatalogNo", oSegment.get_DataElementValue(7, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO109_EAN", oSegment.get_DataElementValue(9, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO401_Pack", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO402_Size", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@PID05_Description", "");
nDetailkey = (Int32)(decimal)oDaDetail.InsertCommand.ExecuteScalar();
}
else if (sSegmentID == "PO4")
{
sSql = @"UPDATE [Detail] SET PO401_Pack = @PO401_Pack, PO402_Size = @PO402_Size
where Detailkey = @Detailkey";
oDaDetail.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PO401_Pack", oSegment.get_DataElementValue(1, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PO402_Size", oSegment.get_DataElementValue(2, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@Detailkey", nDetailkey);
oDaDetail.UpdateCommand.ExecuteNonQuery();
}
}
else if (sLoopSection == "PO1;PID")
{
if (sSegmentID == "PID")
{
sSql = @"UPDATE [Detail] SET PID05_Description = @PID05_Description
where Detailkey = @Detailkey";
oDaDetail.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PID05_Description", oSegment.get_DataElementValue(5, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@Detailkey", nDetailkey);
oDaDetail.UpdateCommand.ExecuteNonQuery();
}
}
} // nArea == 2
else if (nArea == 3)
{
} // nArea == 3
oSegment = (ediDataSegment)oSegment.Next();
} // oSegment != null
MessageBox.Show("Done");
}
