Generate EDI from data in sql via asp.net c#

Hello Experts,

I am faced with a task where i need to generate EDI message for a shipping booking page in a clients asp.net application all the relevant in formation is captured in ms sql 2008 but I am un sure of the code behind to acheive this... any help with code would be most appreciated..

p.s the schema for the EDI message has not yet been decided as we are waiting to the shipping control company to get back to us with a schema.
LVL 7
XGISAsked:
Who is Participating?
 
Carl TawnSystems and Integration DeveloperCommented:
A sample would be fairly useless since the definition depends entirely on what data you need to capture. But it basically just needs to be a simple class with a set of properties to hold your use input, plus a couple of methods (one to Save to the database, and one to produce an EDI file from the data.

What follows is a very simplistic example, but should hopefully give you a starting point:
public class ShippingInfo
{
   #region Properties

   public string Reference { get; set; }
   public decimal InvoiceValue { get; set; }

   // plus any additional properties you need. bear in mind these samples are automatic-properties
   // which will require .Net 3.0+

   // you may also need to extend these if you need to perform any validation on the input

   #endregion

   #region Public methods

   /// <summary>
   /// Saves the objects internal data to a database
   /// </summary>
   public void Save()
   {
       SqlConnection cn = new SqlConnection("Some connection string");
       SqlCommand cmd = new SqlCommand("prAddShippingInfo", cn);
  
       cmd.CommandType = CommandType.StoredProcedure;
  
       // add a SqlParameter for each piece of data

       cn.Open();
       cmd.ExecuteNonQuery();
       cn.Close();
   }

   /// <summary>
   /// Generates and returns an EDI representation of the objects internal data
   /// </summary>
   public string GenerateEDI()
   {
       StringBuilder edi = new StringBuilder();

       // add EDI formatted data

       return edi.ToString();
   }

   #endregion
}

Open in new window

0
 
Carl TawnSystems and Integration DeveloperCommented:
Well, an EDI document is essentially flat file. Theres not going to be a whole lot you can do without knowing the schema, other than preping the data retrieval mechanisms.

Which bit specifically are you unsure about?
0
 
XGISAuthor Commented:
All of it! understand that the schema is critical but kust imagine that I need to generate an EDI with customerName, Company, Address and country how would I acheive this
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Carl TawnSystems and Integration DeveloperCommented:
It partly depends on what you want to do with the result. Are you creating a file to store on disc, or will you be transmitting the data directly to a recipient, or something else?

Either way the basic process would be:

1) Retrieve the data from the datastore (either as a DataTable, DataReader, Xml, or custom business object collection)
2) Create an output stream (FileStream, MemoryStream; depending on what you need to do with the output)
3) Iterate over your data and write to the output stream.
4) Save the stream to disc/transmit to another process.
0
 
XGISAuthor Commented:
basically the form will be open, the user will enter data in to lets just say a series of text boxes when they click submit the data in the textboxes will write to datatable then to EDI then to sql, or SQL then in the onclick also write EDI from sql or something like this..... but I don't know if writing EDI is the same as xml with different extention or if it is completely different so therefore I have no idea on how to code the generation.
0
 
XGISAuthor Commented:
oh sounds possible it will be transmitted... so your saying when the user adds data to the textboxes I should save the values in the textboxes to a dataTable then write the content of the dataTable to both SQL and EDI then transmit the EDI?

to loop through the dataTable would you recomend that i use a foreach loop?
0
 
Carl TawnSystems and Integration DeveloperCommented:
Personally I would create a class that represents the data you are capturing. When the user "submits" the data you populate an instance of the class from your textboxes.

You can then have the class populate a database and/or produce EDI as necessary. EDI files are essentially flat files that use fixed width fields for their values. Each line in the EDI file is marked with a control code that denotes what type of data the line contains (all of this should be explained in the schema when you get hold of it). It's more akin to a fixed-width CSV file than XML.
0
 
XGISAuthor Commented:
ok I Think I understand.... would you by chance have a sample of such a class???
0
 
XGISAuthor Commented:
Thankyou so much.... I am still waiting for the schema, but if I accept and give you the points now will you still be able to answer any questions when I get the schema????
0
 
XGISAuthor Commented:
p.s these points will push you up to pos 23 sweet.... :-)
0
 
Carl TawnSystems and Integration DeveloperCommented:
I'll be around intermittently....I do have a day job as well :o)

But if i'm not here then i'm sure one of the other experts will be on hand.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.