Format DataView

[danturn]

Hey guys,

I've got a dataview (shown below)...

UDP Position Label Number
11042266 1 JXXXXX 9XXXXXXXXXXX
11042266 2 GXXXXX 9XXXXXXXXXXX
11042449 1 RXXXXX 11042458
11042458 1 SXXXXX 9XXXXXXXXXXX
11042458 2 IXXXXX 9XXXXXXXXXXX
11042458 4 AXXXXX 9XXXXXXXXXXX
11042458 5 HXXXXX 9XXXXXXXXXXX
11042458 6 MXXXXX 9XXXXXXXXXXX
11042459 1 IXXXXX 9XXXXXXXXXXX
11042459 2 SXXXXX 9XXXXXXXXXXX

you can see in the first column there is a field called UDP which has duplicates ...

what i need to end up with is a collection... probably an arraylist of comma seperated strings in the format...

UDP,Label,Number

but adds the fields Label and Number from the duplicates onto the string in the order of the position field: -

so the first UDP should show like this:

11042266,JXXXXXX,9XXXXXXXXXXX,GXXXXX,9XXXXXXXXXXX

the 2nd should be:
11042449,RXXXXXX,11042458

the 3rd should be:
11042458,SXXXXX,9XXXXXXXXXXX,IXXXXX,9XXXXXXXXXXX,AXXXXXX,9XXXXXXXXXXX,HXXXXXX,9XXXXXXXXXXX,MXXXXXX,9XXXXXXXXXXX

the 4th:
11042159,IXXXXXX,9XXXXXXXXXXX,SXXXXXX,9XXXXXXXXXXX

Hope this makes sense..

i've been trying do do it using loops but am running into difficulty...

Any advice greatly appreciated!

[apegram]

Show us what you've tried! Did the example I provided here give you a good start? And is this homework, work-related, etc.?

[Geekitygeek]

Show us what you have so far and we'll point you in the right direction

doh..pipped by a wide margin there :p

[danturn]

cool, i got it working (in an ugly way)...

dv is the dataview i showed you guys...

im sure there is a simpler way to do this...

its to automate something very painful we do in the office (internal use, not for $ gain!)

ArrayList SpeedDialARR = new ArrayList();
            DataTable FinalSpeedDials = new DataTable();
            string SpeedDialTxt="";
            for (int i = 0; i <= dv.Count - 1; i++)
            {
                if (SpeedDialTxt != "")
                {
                    if (SpeedDialTxt.Substring(0, 8) == dv[i].Row[0].ToString())
                    {
                        SpeedDialTxt += dv[i].Row[2].ToString() + "," + dv[i].Row[3].ToString()+",";
                    }
                    else
                    {
                        SpeedDialARR.Add(SpeedDialTxt.Substring(0,SpeedDialTxt.Length-1));
                        SpeedDialTxt = dv[i].Row[0].ToString() + "," + dv[i].Row[2].ToString() + "," + dv[i].Row[3].ToString() + ",";
                    }
                }
                else
                {

                    SpeedDialTxt = dv[i].Row[0].ToString() + "," + dv[i].Row[2].ToString() + "," + dv[i].Row[3].ToString() + ",";
                
                }
            }
            SpeedDialARR.Add(SpeedDialTxt.Substring(0, SpeedDialTxt.Length - 1));

[Geekitygeek]

That'll work.
You could also try something like:

//use your dataview here
            DataView dv = new DataView();

            //List to store finished strings
            List<string> UDPstrings = new List<string>();

            //log current UDP number
            string CurrentUDP = "";

            //variable to store UDP string as it is built
            string buildUDP = "";

            foreach (DataRowView dr in dv)
            {
                //if UDP number hasnt changed
                if (dr["UDP"].ToString() == CurrentUDP)
                {
                    //append to current string
                    buildUDP += dr["Label"].ToString() + ", " + dr["Number"].ToString();
                }
                else
                {
                    //store current string
                    UDPstrings.Add(buildUDP);

                    //reset string and set current UDP number
                    buildUDP = "";
                    CurrentUDP = dr["UDP"].ToString();
                }
            }

[apegram]

Glad you got it to work.

For giggles, here is the way I extended the LINQ demo I provided yesterday.

using System;
using System.Linq;
using System.Xml.Linq;
using System.Text;

namespace LinqToXmlDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string xmlFile = @"C:\Users\Anthony\Documents\Visual Studio 2008\Projects\LinqToXmlDemo\LinqToXmlDemo\demo.xml";
            XDocument document = XDocument.Load(xmlFile);

            var rows = from row in document.Descendants("row")
                       select new
                       {
                           UDPName = row.Element("UDPName").Value,
                           SpeedDialIndex = int.Parse(row.Element("SpeedDialIndex").Value),
                           Label = row.Element("Label").Value,
                           SpeedDialNumber = row.Element("SpeedDialNumber").Value
                       };

            var udpNames = rows.Select(row => row.UDPName).Distinct();
            var udpGroups = from udpName in udpNames
                            select new
                            {
                                UDPName = udpName,
                                Rows = rows.Where(row => row.UDPName == udpName).OrderBy(row => row.SpeedDialIndex)
                            };

            StringBuilder builder = new StringBuilder();
            foreach (var udpGroup in udpGroups)
            {                
                builder.Append(udpGroup.UDPName);
                foreach (var row in udpGroup.Rows)
                {
                    builder.Append(string.Format(",{0},{1},{2}", row.SpeedDialIndex, row.Label, row.SpeedDialNumber));
                }

                Console.WriteLine(builder.ToString()); // could add to a collection here
                builder.Remove(0, builder.Length);
            }

            Console.Read();
        }
    }
}

[danturn]

Hey Guys,

Thanks for that....

glad my code made some sense...

while writing it I felt like i was drowning!

Ape...
i couldnt get the linq stuff to work... it had a problem with using "Descendants" i think the issue is that i had the XML in an XMLdocument format whereas you're using XDocument ... i couldnt work out how to transform it to the other one...

[apegram]

Ape...
i couldnt get the linq stuff to work... it had a problem with using "Descendants" i think the issue is that i had the XML in an XMLdocument format whereas you're using XDocument ... i couldnt work out how to transform it to the other one...

What version of Visual Studio/C# are you using?

You need to have access to .NET 3.5

-Establish reference to System.Core (for LINQ)
-Establish reference to System.Xml.Linq (for LINQ to XML)
-add the following using directives:

using System.Linq;
using System.Xml.Linq;

[danturn]

im using 2008 pro...

the project is 3.5 and i've added the references...

i think its just cos you loaded in and XML file to an XDocument whereas im starting with the XMl in an XMLDocument variable...

there doesnt seem to be an option in the xdocument.load to import an XMLDocument type so i was a bit stuck without going back and changing my other classes to return something else... i guess i can just return a string then use xdocument.load() to read it in?

Again, thanks for all the help guys!

Dan

What version of Visual Studio/C# are you using?

You need to have access to .NET 3.5

-Establish reference to System.Core (for LINQ)
-Establish reference to System.Xml.Linq (for LINQ to XML)
-add the following using directives:

using System.Linq;
using System.Xml.Linq;

[danturn]

Hey Ape,

Awesome... i worked it out... I even managed to sort out the next step where i needed to cross reference the XML items against a datatable and only add matches to the array... in case you're curious here's the code...

Thanks for all of your help... i think the Linq stuff is going to come in handy as i have a lot more (more complex) processing to do in the next bit!.

I'm thinking i should have just used some sort of relational database to do all this crossreferncing etc. do you know of any free DB products you can embed in a C# program? - i really need this to be a standalone program.

string XMLResp = SoapSend.SoapSender(sAXLRequest);
            XDocument document = XDocument.Parse(XMLResp);
            ArrayList speeddialsarray = new ArrayList();
            var rows = from row in document.Descendants("row")
                       select new
                       {
                           UDPName = row.Element("UDPName").Value,
                           SpeedDialIndex = int.Parse(row.Element("SpeedDialIndex").Value),
                           Label = row.Element("Label").Value,
                           SpeedDialNumber = row.Element("SpeedDialNumber").Value
                       };
            var udpNames = rows.Select(row => row.UDPName).Distinct();
            var udpGroups = from udpName in udpNames
                            select new
                            {
                                UDPName = udpName,
                                Rows = rows.Where(row => row.UDPName == udpName).OrderBy(row => row.SpeedDialIndex)
                            };
            StringBuilder builder = new StringBuilder();
            foreach (var udpGroup in udpGroups)
            {
                foreach (DataRow DRFinal in FinalUsers.Rows)
                {
                    if (udpGroup.UDPName == DRFinal[1].ToString())
                    {
                        builder.Append(DRFinal[0].ToString());
                        foreach (var row in udpGroup.Rows)
                        {
                            builder.Append(string.Format(",{0},{1}", row.Label, row.SpeedDialNumber));
                        }
                        speeddialsarray.Add(builder.ToString());
                        builder.Remove(0, builder.Length);
                    }
                }
            }

[danturn]

Hey Ape,

One last question about the Linq stuff...

if i need to tweak my output slightly -

i.e. instead of showing as:

UDPName,Label1,SpeedDialNumber1,Label2,SpeedDialNumber2,Label3,SpeedDialNumber3...

i need to show it as:

UDPName,Label1,Label2,Label3,SpeedDialNumber1,SpeedDial2,SpeedDial3...

soooo.... still merge all the results for matching UDPs to one line but change the order they're added to the string in so that all of the labels show before all of the numbers.

[apegram]

You can use other strings or stringbuilder objects to create your label and speeddialnumber groups and then append those to your main builder as you go to your collection.

Also, you can modify the var udpNames declaration to pull from your FinalUsers datatable, which would allow you to get rid of one of your inner loops where you're checking if the current udpname matches a value in the table. Consider:

// more code ^^^


            //var udpNames = rows.Select(row => row.UDPName).Distinct();
            var udpNames = (from DataRow dr in FinalUsers.Rows
                            select dr["UDPName"].ToString())
                            .Distinct(); // use Distinct() if datatable does not hold unique UDPName fields
            var udpGroups = from udpName in udpNames
                            select new
                            {
                                UDPName = udpName,
                                Rows = rows.Where(row => row.UDPName == udpName).OrderBy(row => row.SpeedDialIndex)
                            };

            StringBuilder builder = new StringBuilder();
            StringBuilder labels = new StringBuilder();
            StringBuilder speedDials = new StringBuilder();

            List<string> speedDialsList = new List<string>(); // *** using System.Collections.Generic;

            foreach (var udpGroup in udpGroups)
            {                
                builder.Append(udpGroup.UDPName);
                foreach (var row in udpGroup.Rows)
                {
                    labels.Append("," + row.Label);
                    speedDials.Append("," + row.SpeedDialNumber);
                    //builder.Append(string.Format(",{0},{1},{2}", row.SpeedDialIndex, row.Label, row.SpeedDialNumber));
                }

                builder.Append(labels.ToString() + speedDials.ToString());

                speedDialsList.Add(builder.ToString());
                builder.Remove(0, builder.Length);
                labels.Remove(0, labels.Length);
                speedDials.Remove(0, speedDials.Length);
            }
            // more code

Also, you're using an arraylist as your collection, which works. A better object to use is the generic List<T>, where T can be any type you want to throw at it (including value types, reference types, custom types, etc.). ArrayLists store items as objects, which is not as efficient as storing them as their native types. Also, with ArrayLists, there is boxing and unboxing going on, so it's a further drag on efficiency. Just a suggestion, but if other parts of your program require the ArrayList, it is certainly OK to stick with it.

[danturn]

Wowza....

That's amazing!...

Thanks so much fo your help,

There's no real reason for me to use ArrayLists other than i know how to work with them! i've switched it to the Lists now.

It's amazing (and terrifying!) how much there is there is to learn in dotnet. i'm going to go read up about Linq i think... all of the other programs i've written are similar dataprocessing/mashing scripts so it's probably a useful tool for that sort of thing!

Dan

[danturn]

one final thing!!!

if there is another field that is in every record but like UDPName it's a duplicate... how do i add that just once at the beginning after UDPName?

i dont need to check it agains the finalusers table, just append it to the UDPName...i figure i would need to add it here (********):

foreach (var udpGroup in udpGroups)
            {
                builder.Append(udpGroup.UDPName+","+********);

but i can't work out how to reference another value in the Rows...

I suspect i need to add to my query here:..

var udpGroups = from udpName in udpNames
                            select new
                            {
                                UDPName = udpName,
                                Rows = rows.Where(row => row.UDPName == udpName).OrderBy(row => row.LineNumber),
                            };

then i can do this:

builder.Append(udpGroup.UDPName+","+udpGroup.PhoneModel);

but i cant work the syntax out...

in case you are curious the field is the handset type of the UDP which owns the speed dials so i dont need to show it multiple times

[apegram]

Is this handset type coming from the XML file, the datatable, somewhere else? And is the relationship 1 UDPName, 1 handset type, multiple everything else?

[danturn]

Yeah, from the same XML file...

You're right, the relationship is that 1 UDP has 1 Handset Type and Multiple everything else.

i started out trying to get the simple stuff working and figured i could work it out from there, but i have to confess i'm struggling to get my head around it!

Is this handset type coming from the XML file, the datatable, somewhere else? And is the relationship 1 UDPName, 1 handset type, multiple everything else?

[apegram]

Take your time and go through the code. LINQ is powerful and can simplify your code, but it combines a lot of different elements of .NET and particularly .NET 3.5, and understanding each part is critical.

As for the handset type, first start with getting it into the rows structure being established earlier in the code.

var rows = from row in document.Descendants("row")
                       select new
                       {
                           UDPName = row.Element("UDPName").Value,
                           HandsetType = row.Element("HandsetType").Value,
                           SpeedDialIndex = int.Parse(row.Element("SpeedDialIndex").Value),
                           Label = row.Element("Label").Value,
                           SpeedDialNumber = row.Element("SpeedDialNumber").Value
                       };

Obviously, modify the "HandsetType" to match the actual name from the file, if it differs.

Then you leave the rest of the code alone until you get inside the loop handling the creation the of the comma-delimited strings.

foreach (var udpGroup in udpGroups)
            {                
                builder.Append(udpGroup.UDPName);
                bool firstRow = true;
                
                foreach (var row in udpGroup.Rows)
                {
                    if (firstRow)
                    {
                        builder.Append("," + row.HandsetType);
                        firstRow = false;
                    }

                    labels.Append("," + row.Label);
                    speedDials.Append("," + row.SpeedDialNumber);
                }

                builder.Append(labels.ToString() + speedDials.ToString());

                speedDialsList.Add(builder.ToString());
                builder.Remove(0, builder.Length);
                labels.Remove(0, labels.Length);
                speedDials.Remove(0, speedDials.Length);
            }

[danturn]

aha, ok cool, my first idea was to use a flag somewhere and just inject them on the first run...

i was trying to overcomplicate things by doing something clever and linqy!

again, thanks so much!

[apegram]

i was trying to overcomplicate things by doing something clever and linqy!

Oh, there are ways. I'll show you one way, but you want to be sure there are rows in the udpgroup members, so I'll enforce that with a .Where extension method. I could also go back into one of the earlier LINQ queries and modify it to ensure that I'll always have rows.

foreach (var udpGroup in udpGroups.Where(group => group.Rows.Count() > 0))
            {
                builder.Append(udpGroup.UDPName);
                builder.Append("," + udpGroup.Rows.Select(row => row.HandsetType).First()); // append first value

                foreach (var row in udpGroup.Rows)
                {
                    labels.Append("," + row.Label);
                    speedDials.Append("," + row.SpeedDialNumber);
                }

                builder.Append(labels.ToString() + speedDials.ToString());

                speedDialsList.Add(builder.ToString());
                builder.Remove(0, builder.Length);
                labels.Remove(0, labels.Length);
                speedDials.Remove(0, speedDials.Length);
            }

On that note, more about what I've been using. I don't want to say it encompasses everything added in .NET 3.5, but it shows a lot of the new features.

* Lambda expressions
* Extension methods
* Anonymous types
* Query expressions

You'd be well-served to do research on each of those topics individually.

[danturn]

aha, that makes a lot of sense... i've been looking into the Linq stuff today a bit. I think i'm beginning to understand the simple bits but putting it together is a bit of a challenge!

sorry to keep adding to this thread when you've already solved my issues but as it refers to the same code i thought i'd ask you another quicky on here:

This bit of code here:

var udpNames = (from DataRow dr in FinalUsers.Rows
                            select dr[1].ToString())
                            .Distinct();           
            
            var udpGroups = from udpName in udpNames
                            select new
                            {
                                UDPName = udpName,
                                Rows = rows.Where(row => row.UDPName == udpName).OrderBy(row => row.LineNumber),
                            };

is saying Select Column 1 from a row in datatable FinalUsers and make a variable containing that value

then its saying when that variable (from Final Users = the row from the XML matches it return the rest of the values for that row right?

what i've been trying to work out today (i know... i've literally been trying to develop this tiny bit of code for 3 days now!!!!) is how to do the same match... but instead of adding UDPName at this point...

builder.Append(udpGroup.UDPName);

i'm trying to work out how to add the corresponding value in finalusers column [0]

I was hoping you could do something like this:

var udpNames = (from DataRow dr in FinalUsers.Rows
                            select dr[1].ToString(),dr[0].ToString())
                            .Distinct();

then use the combined result to extract [0] later on somehow but i just cant work it out.

[apegram]

One thing you can do, and this only makes sense if the rows in FinalUsers are already unique, is something like this:

var udpGroups = from DataRow dr in FinalUsers.Rows
                            select new
                            {
                                FinalUserRow = dr,
                                XmlRows = rows.Where(row => row.UDPName == dr["UDPName"].ToString()).OrderBy(row => row.SpeedDialIndex)
                            };

That would make the entirety of the datarow accessible, so you could extract anything you wanted from it later on, and still get the groups from the XML file that you need. You could also choose to be more explicit with the fields you extract from the datarows like this:

var udpGroups = from DataRow dr in FinalUsers.Rows
                            select new
                            {
                                Field1 = dr["Field1"].ToString(),
                                Field2 = dr["Field2"].ToString(), // etc.
                                XmlRows = rows.Where(row => row.UDPName == dr["UDPName"].ToString()).OrderBy(row => row.SpeedDialIndex)
                            };

[danturn]

Sorted. That was exactly what i was trying to do.

If there is any way I can remotely buy you a beer please pm me or something.

You've helped enormously and taught me a lot

Dan