14 July, 2008

How to change order of DataTable columns in Dataset Designer of Visual Studio 2005

Why would you like to do something like that?

Well, for example, you changed the underlying source of the DataTable. Let’s say it is a stored procedure on an MS SQL Server doing some select. You inserted new result columns between the other result columns. After that, you refreshed the DataTable structure using the DataSet designer of Visual Studio 2005. And you noticed that the new columns did not get to the same position as in the underlying stored procedure. Instead, they got to the end, after the already existing columns.

So what? Usually, if I want to visualize the DataTable I will use a DataGridView, or something similar where I have to explicitly specify the order of the columns.

Yeah, but I might have a pre-built DataTable-saving method, that saves my DataTable as-is. And I might be a lazy guy, who does not want to change his already written code to explicitly specify column order.
All I want is to simply change the column order using the DataSet Designer. This will be good because I will have a nice looking DataTable in the DataSet Designer reflecting the exact column order of the underlying stored procedure. And the saving of the DataTable will be also corrected.
Bad news: I could not find any possibility of the Visual Studio 2005 DataSet Designer to do this. But I am stubborn, and I will do it.

Playing around with the files created by the DataSet Designer, I found that changing the column order in the DataSetName.xsd file, will reflect the column order change desired by me. This is an XML file, so pay attention to preserve the appropriate structure and tags.
But this is only the visual part, because, when saving the DataTable it still reflects the “wrong” column order.
To have the desired column order when saving, we have to change the (C#) code generated by the DataSet Designer. In the DataSetName.Designer.cs file, in the InitClass method of your DataTable change the part where columns are added. This will look something similar:

this.columnMyColumn1 = new global::System.Data.DataColumn("MyColumn1", typeof(string), null, global::System.Data.MappingType.Element);
this.columnMyColumn2 = new global::System.Data.DataColumn("MyColumn2", typeof(string), null, global::System.Data.MappingType.Element);

To get MyColumn2 before MyColumn1, just Copy-Paste the two lines regarding MyColumn2 before MyColumn1 (actually the order of the base.Columns.Add is important).

And again: Save! Save! Save! Save the project before doing this. And be careful with this when you are working on a huge project.

No comments: