Need Urgent Help with ADO.NET question in C#

[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;