how to convert sql rows into list of objects

Question

Here is the sample data

Vendor-Return-PRocess.png

I have to create SSIS package that will send emails that will contain 1 order per email. So based on the above sample, this package will send 2 send emails.

In my Control Flow I have Execute SQL Task and Script Task. From Execute SQL task the data is stored in a variable of type OBJECT and accessed in the Script Task.

My approach is to create Data table to store the data from OBJECT variable, and then loop through and store each order in a object. then when a new order is read then add the Order object in a list. I don't know how to detect when the current row has a new order.

Here is my C# code.

Code Begin

public class Order
        {
            public string chwhlo { get; set; }
            public string order_number { get; set; }
            public string claim_number { get; set; }
            public string po_number { get; set; }
            public string qty { get; set; }
            public string purchase_order_price { get; set; }
            public string external_charge { get; set; }
            //public List<Container> container { get; set; }
            public List<string> container { get; set; }
        }

public void Main()
        {
            // TODO: Add your code here

            try
            {

                OleDbDataAdapter oleDA = new OleDbDataAdapter();
                //DataSet ds = new DataSet();
                //ds = (System.Data.DataSet)Dts.Variables["User::RecordsObject"].Value;
                //oleDA.Fill(ds,"Mdata");
                DataTable dt = new DataTable();
                oleDA.Fill(dt, Dts.Variables["User::RecordsObject"].Value);
                //dt = ds.Tables["Mdata"];               


                //List<string> lst_mtcamu = new List<string>();
                StringBuilder subject = new StringBuilder();
                Order Ord = new Order();


                //Ord.chwhlo = "1";



                string previous = "-1";
                double purchase_order_price = 0.00;
                double external_charge = 0.00;
                int recordcount = 0;
                List<string> lstOrderNumber = new List<string>();

                List<Order> Ordlist = new List<Order>();
                string temp = null;


                foreach (DataRow row in dt.Rows)
                {

                    //    //string RN, Location, Container, Order_Number, ClaimNumber, PONumber, Qty, purchase_order_price, external_charge;
                    //    //Order Ord = new Order();
                        object[] array = row.ItemArray;

                    //Ordlist.Add(Ord.chwhlo.ToString());

                    if  (lstOrderNumber.Contains(array[3].ToString()) == false)
                    {
                        lstOrderNumber.Add(array[3].ToString());
                        Ord.order_number = array[3].ToString();
                        Ord.claim_number = array[4].ToString();
                        Ord.po_number = array[5].ToString();
                        temp = array[3].ToString();
                        MessageBox.Show(Ord.order_number.ToString());
                        //MessageBox.Show(array[3].ToString());
                        //Ordlist.Add(Ord);
                        //MessageBox.Show(Ordlist.Count.ToString());

                    }

                    if (temp == array[3].ToString())
                    {
                        Ord.container.Add(array[2].ToString());
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }

Code End

Answer 1

My first thought is that you don't need a script task for this. Once you have the SQL, grouped by claim number or whatever you're using to determine uniqueness, then you have the row(s) you need. Using one or more transform tasks you should be able to build out a table structure that has the information needed to then call the Send Mail Task via a foreach container.

But assuming that you do need a script task then converting from a DataTable to a List<Order> is pretty straightforward. I do question whether it might be overkill though given that you could accomplish the same thing just sticking with the DataTable. But let's work with what you already have. I'm going to assume here that you are running a newer version of SSIS such that you can change the script task to run .NET 3.5 or higher so that LINQ is available. This makes it very easy to do. I'm also testing using the newer compiler so the script task compiler may not accept all this as is.

To use LINQ you need to:

• Change the target framework for the script task to .NET 3.5+
• If not already done ensure the script task has a reference to the System.Core namespace

//Get the rows grouped by the unique column(s)                
//For each group create an order that sets the base order information from the first item and the rest as container
var orders = from r in dt.Rows.OfType<DataRow>()
                     group r by r.Field<string>("Order_Number") into g
                     select new Order() {
                            order_number = g.Key,
                            claim_number = g.FirstOrDefault().Field<string>("ClaimNumber"),
                            po_number = g.FirstOrDefault().Field<string>("po_number"),
                            container = g.Select(x => x.Field<string>("container")).ToList()
                      };