Need Help in how to set column sequence in a datatable.

microsoft.public.dotnet.framework.adonet

18 May 2006 4:54 PM

manaleem

Hi:

I am working on routine that takes in a datatable, and add extended
properties to each column, by reading an XMLDocument object. The
routine is currently doing a dt = source.copy, and then has a for each
statement, in which extended properties are added by reading each XML
node. I would like to also include a functionality so I can set
sequence for the columns, so the routine returns a datatable with
columns sequenced from a sequence attribute in my xml. Can someone
please tell me how to set the sequence for each column in a datatable
before returning it. Following is the function, that works right now,
but doesnt do the sequencing althought it does take in a datatable,
adds extended properties by reading xml nodes, and finally returns the
datatable with the extended properties What i really need to is before
returning the final result set, i would like to also change order of
columns, so for eg if i am taking in a table with 3 columns called
Column Name, Column City, Column Zip with a default order of name,
city, zip instead when returning, i would like to change that column
order to zip, name, city. How would i do that? Also I am using an XML
file to grab extended properties, so I do have an option of defining an
attribute for each column node in the xml file, and possibly set a
columnsequence="1" and so forth to distinguish proper sequence when
processing. Thanks and this is something which is due asap, and I need
some urgent help on it. I would appreciate any help possible on this
issue. Thanks.

Here is the current code for the function:

protected DataTable ApplyColumnMappings(DataTable source,
Configuration.Entities entityID)
        {
            // Create a new row and read in the appropriate rows
            // The final data should be the product of the CNSF process
            DataTable dt = new DataTable("EntityID:" +
entityID.ToString());
            string customerId = Context.CompanyNbr;

            //this is where we are returning the XMLDocument with the
following format to look at attributes and other info
            //<entitydefinition>
            //    <columndefinition Hidden="1">
            //        <columnname>ShipmentID</columnname>
            //        <columnalias>ShipmentID</columnalias>
            //    </columndefinition>
            //    <columndefinition Hidden="0">
            //        <columnname>InvoiceNumber</columnname>
            //        <columnalias>InvoiceNumber</columnalias>
            //    </columndefinition>
            //    <columndefinition Hidden="1">
            //        <columnname>CustomerNumber</columnname>
            //        <columnalias>CustomerNumber</columnalias>
            //    </columndefinition>
            //</entitydefinition>

            DataFormatter cnsfprocess = new DataFormatter();
            //Returing the Xmldocument with the above schema to lookup
column alias, whether column is hidden or needs to show etc
            XmlDocument doc =
cnsfprocess.GetCustomerCNSFData((int)entityID, customerId);

             if (source.Rows.Count> 0)
                {
                    if (doc != null)
                    {
                        XmlElement entityDefinition =
doc.DocumentElement;
                        XmlNodeList nodes =
entityDefinition.SelectNodes("//columndefinition");

                        dt = source.Copy();

                        // Loop over each node in the stored XML
defintion and add extended properties for columns

                        foreach (XmlNode node in nodes)
                        {
                            string columnName =
node["columnname"].InnerText;
                            string columnAlias=
node["columnalias"].InnerText;
                            int hiddenNode =
System.Convert.ToInt32(node.Attributes["Hidden"].InnerText);

                            if (hiddenNode == 1)
                            {

dt.Columns[columnName].ExtendedProperties.Add("Visible", false);
                            }
                            else
                            {

dt.Columns[columnName].ExtendedProperties.Add("Visible", true);
                            }

dt.Columns[columnName].ExtendedProperties.Add("ColumnAlias",
columnAlias);

                        }
                        dt.AcceptChanges();

                    }

                }return dt;