Blogged thoughts, is our web blog. Expect views, opinion, rants and tirades about everything and anything 

I’d rather not store the XML files Im using in my current project onto the server as they are very verbose so I decided to use the SQLBulkCopy class in .net to allow me to write the data (and only the data - no schema related stuff) within the XML files to a SQL Server database table which gives me the power of SQL (for data filtering) which I’m more familiar with than XPath. The process for doing this is extremely easy, check out the code snippet below:

DataSet reportData = new DataSet();
reportData.ReadXml(Server.MapPath(”report.xml”));

SqlConnection connection = new SqlConnection(”CONNECTION STRING”);
SqlBulkCopy sbc = new SqlBulkCopy(connection);
sbc.DestinationTableName = “report_table”;
       
//if your DB col names don’t match your XML element names 100%
//then relate the source XML elements (1st param) with the destination DB cols
sbc.ColumnMappings.Add(”campaign”, “campaign_id”);
sbc.ColumnMappings.Add(”cost”, “cost_USD”);

connection.Open();

//table 4 is the main table in this dataset
sbc.WriteToServer(reportData.Tables[4]);

connection.Close();

//remove the xml file
…

Basically your creating a DataSet, populating it with XML data from a *.xml file somewhere on your system and passing the DataTable of interest from your DataSet into the WriteToServer method of the SQLBulkCopy class. The table specified in DestinationTableName must exist, additionally if your DB column names do not match your XML element names 100% you will need to use the ColumnMappings property to let SQLBulkCopy know which XML elements match to which DB cols. In the code above I’m telling SQLBulkCopy to pair the ‘campaign_id’ and ‘cost_USD’ columns in the DB with the ‘campaign’ and ‘cost’ elements from the XML file. XML elements which are not explicitly specified will not be written to the DB table.

The SQLBulkCopy class is available in the System.Data.SqlClient namespace, so you will need to add a using statement at the top of your code if you want to instantiate a SQLBulkCopy instance. Although my project will only really involve XML data with a max of about 2,000 elements I’ve done a bit of reading about some of the performance benefits some .net developers are getting by using SQLBulkCopy, a couple of people said they were writing 40-50K XML elements (how big were the elements though?) to their DB in only a few seconds, now that’s quick… it sure beats looping over ‘insert into’ statements.

Resources:
http://dotnetslackers.com/Articles/ADO_NET/SqlBulkCopy_in_ADO_NET_2_0.aspx
http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server

Recently while working in my Data Access Layer (DAL) I pulled data from two different databases and combined them into a .Net DataTable before returning that DataTable to my business logic layer (BAL) for.. well business logic stuff. Since I could not find an AddAt() method for adding columns at a specific index I had to try and find a way to reorder columns after adding them. I found the solution on forums.asp.net and it’s actually very easy to do. Suppose you had a DataTable named ‘dt’ and column named ‘Staff Number’ which you wanted to be in at column/index 0 in your datatable. The following line of code is all it takes.

dt.Columns["Staff Number"].SetOrdinal(0);The columns that follow ‘Staff Number’ will all shift across by 1. Incidentally DataTable.Columns[] has two overloads, one which takes a zero-based index of the column to return and another that takes the column name of the column to return. While accessing columns via index numbers is quicker it’s not recommended as it’s too difficult to maintain and read. Imagine for instance I had code which accessed various columns from the ‘dt’ DataTable via an index number and then I realised that I wanted my ‘Staff Number’ column to be first and thus set its index or Ordinal to 0, I would then have to change my index based statements for all columns that were shifted by 1. Since this has the potential to be a royal pain and since my development team would rather read dt.Columns["COLUMNNAME"]; than dt.Columns[INDEXNUMBER]; I always recommend using Column names for accessing data from things like DataTables, DataSets and SQL statements.

The columns that follow ‘Staff Number’ will all shift across by 1. Incidentally DataTable.Columns[] has two overloads, one which takes a zero-based index of the column to return and another that takes the column name of the column to return. While accessing columns via index numbers is quicker it’s not recommended as it’s too difficult to maintain and read. Imagine for instance I had code which accessed various columns from the ‘dt’ DataTable via an index number and then I realised that I wanted my ‘Staff Number’ column to be first and thus set its index or Ordinal to 0, I would then have to change my index based statements for all columns that were shifted by 1. Since this has the potential to be a royal pain and since my development team would rather read  than  I always recommend using Column names for accessing data from things like DataTables, DataSets and SQL statements.

I’m using .NET Generics a lot lately in my code so I thought I’d give a quick overview of what they are and what the advantages of using them while developing are. I like About.com’s definition of them and thus I’ll offer it here:

“Generics is a form of abstraction in developing code. Instead of writing a function or a class for a particular type, it can be written generally to use any type. When an instance of the generic class is instantiated, the type is specified.”

Generics have been around in other programming environments for a while however they are new to the 2.0 version of the .NET framework. In the framework they are used mostly by various collection classes, which are used to store items in memory for later retrieval. In C# creation of a generic collection such as a List requires the use of angle brackets (< >) within which you specify the specific type or types you want to use. The line below instantiates a List set up to store ints only:

intList can now be used just like any regular non generic collection. Any attempt by the developer to add anything other than an int to the intList collection will result in a compile time error, thus generics are said to be type safe. Generic type safety is discussed next.

Generics offer a number of advantages including type safety, more elegant code and alleged performance improvement.

Type Safety
Generics provide type safety as once a generic class, interface etc. is instantiated with a specific type or types the framework will not allow your code to compile if you then try to inadvertently use a wrong type(s) with your class, interface etc. Compare this with say using a non generic object such as an ArrayList. Since an ArrayList stores instances of the object class you can store any .Net object as everything derives from object.

Imagine intending to use an ArrayList to store strings but you mistakenly also store a int. The compiler will not complain about this, however when you go to retrieve objects from the ArrayList and attempt to cast each of the objects to strings the runtime will throw an InvalidCastException error as you will have attempted to cast an int to an string. Here’s some C# code which should illustrate this a little better:

As ArrayList is not a type safe class the above code error will not be caught by the compiler. It will however throw a runtime error as you are attempting to cast an int object to a string object. Now see equivalent code using List<>, the generic version of the ArrayList class.

Again your attempting to add an int when your purpose is to add only strings. This time however because you have specified a type (string) at creation time, the compiler knows that stringList.Add(3); is invalid and comes back with The best overloaded method match for ‘System.Collections.Generic.List.Add(string)’ has some invalid arguments error message meaning this section of your code is type safe and will never throw an invalid cast type of exception at runtime.

More elegant code & Performance Improvement.
Microsoft lists performance improvement as a benefit of using generics due to the fact that since you specify your correct type or types at time of creation for an object there is no need to use casting as you can be guaranteed your object contains/uses items of a certain type. Casting requires boxing and unboxing. Boxing relates to converting a value type to a reference type while unboxing relates to converting a reference type to a value type. Both steal processor time and slow performance. Many developers however conclude that the performance benefit from using generics is negligible.

I said I’d share this information about how to resolve this error since it took me almost a whole day to fix it. The ‘Cannot have multiple items selected in a DropDownList‘ exception will be thrown when the developer attempts to select a ListItem in a DropDownList when an item in that same DropDownList has already been selected earlier in previous code.

When searching on Google and the forums.asp.net site most of the suggested fixes to this problem suggested explicitly deselecting all selected items in a dropdown before selecting another. This can be done via something like ddl.clearselection() or ddl.SelectedIndex = -1. It seemed like a fairly logical approach, however it did not work for me, no matter what variation of deselection I tried.

On further investigation, it seemed the problem stemmed from the fact that I was adding the same ListItem to multiple dropdowns like so:

ListItem areaItem = new ListItem(Areas.Rows[i][0].ToString(),Areas.Rows[i][0].ToString());
ddlArea.Items.Insert(i, areaItem);
ddlArea2.Items.Insert(i, areaItem);
ddlArea3.Items.Insert(i, areaItem);
ddlArea4.Items.Insert(i, areaItem);
ddlArea5.Items.Insert(i, areaItem);

but because a ListItem object is a reference object when I changed the selectedItem (and by doing so set a particular ListItems selected property to true) property of say ddlArea4 I also changed it in all the other dropdowns which contained the ListItem which was now selected. Remember reference objects do not keep a copy of an object they simply store a reference to the original object, thus a change in one dropdown list was leading to a change in all so code like the following which aims to explicitly deselect items before selecting another would not work:

ddlArea4.ClearSelection();
ddlArea4.SelectedIndex = -1;
ddlArea4.Items.FindByValue(itdp.Rows[2][1].ToString()).Selected = true; //the ListItem that this will select is present in multiple dropdowns, if any of them have anything selected an error will be thrown

The solution to the problem is to create new instances of ListItems for each option you need to add to dropdown lists, so instead of the first bit of code above, I tried something like:

ddlArea.Items.Add(new ListItem(Areas.Rows[i][0].ToString(),Areas.Rows[i][0].ToString()));
ddlArea2.Items.Add(new ListItem(Areas.Rows[i][0].ToString(),Areas.Rows[i][0].ToString()));
ddlArea3.Items.Add(new ListItem(Areas.Rows[i][0].ToString(),Areas.Rows[i][0].ToString()));
ddlArea4.Items.Add(new ListItem(Areas.Rows[i][0].ToString(),Areas.Rows[i][0].ToString()));
ddlArea5.Items.Add(new ListItem(Areas.Rows[i][0].ToString(),Areas.Rows[i][0].ToString()));

It meant when I selected a particular ListItem in a particular dropdown only the one ListItem and dropdown was affected. It worked well and it even allowed me to remove the extra deselection logic which in this case it turned out I did not need.

This was quite an uncommon scenario that led to this problem and tracking down the solution was difficult. I think in over 90% of cases the problem with the “Cannot have multiple items selected in a DropDownList” error would come from the developer trying to select more than one item in a DropDownList in a more obvious way such as simply having two or more .Selected = true statements perhaps in different parts of his or her code without realising. Therefore always give something like ClearSelection() or SelectedIndex = -1 a go first before thinking too hard about this problem, it will most likely resolve it.

The ASP.Net repeater control has over the last while become one of my favourite controls due to the fact it can be highly customised because it’s a templated control. Two shortcomings with the current implementation of the repeater control however are its lack of paging and sorting capabiltites. Of these I believe paging is perhaps the more desirable feature and thus I will now provide an outline of how to implement paging with a repeater using an SqlDataSource. I’ve chosen to work with an SqlDataSource as this I imagine is the most common underlying data source used with repeaters. 

PagedDataSource class
The most popular way ASP.Net developers enable repeaters to page through large amounts of results is with the help of the PagedDataSource class. This is a class which

Encapsulates the paging-related properties of a data-bound control (such as DataGrid, GridView, DetailsView, and FormView) that allow it to perform paging and is used by control developers when providing paging support to a custom data-bound control.

Since this class can be used to provide paging support to custom data-bound controls it can of course be used by built in data-bound controls such Repeaters and DataLists to provide the same support. If you look on the MSDN page which I have linked to above you’ll notice however that the class implements the ICollection interface. This means that any underlying source you want to feed into the PagedDataSource class (which in turns feeds into the repeater itself) must also implement ICollection. The SqlDataSource class does not implement this interface and thus we must put the data into some class that does implement ICollection, in this case we are using a DataView. OK lets look the c# code behind.

All the important paging related code is encapsulated in the Page_with_Repeater() function, it’s all commented so I won’t repeat myself here. As far as what the code does, well it displays 5 rows of data at a time from the underlying datasource (in this case SqlDataSource1) in a repeater. LinkButtons are dynamically created and then added to the repeater to display page numbers as links to allow the user to select a specific result page. Often interfaces allow the user to select a specific page (as in this example) and to use previous and next buttons for working his or her way through data, on that note a good example of using buttons for repeater paging is given on the 4guysfromrolla.com website. 

The corresponding .aspx markup for the c# code is very simple and contains a repeater (named ‘repeater1′) with an embedded panel (named ‘placeLinks’) and an SqlDataSource (named ‘SqlDataSource1′) which specifies an event hander for the ’selected’ event in order for us to create the correct amount of paging links.

Incidentally you may notice in the C# code above that I have used a custom function called FindControlRecursive to enable me to add the dynamically created placeholder (which contains all the page number linkbuttons) to the statically created panel within the repeater. This is a handy function I came across recently and which I often use in conjunction with repeater controls (and many other controls too). It accepts a root control and an id of the target control to look for. It works in a similar way to the standard FindControl method except it searches all controls (including child controls) in a control tree hierachy whereas FindControl will only search the specific control you pass it without examining any child controls.

That’s it - paging with a repeater using an SqlDataSource is implemented. As you can see it is not too difficult. If you have any questions please feel free to ask.  

Booked myself in for one of the two exams required for the MCTS - .NET Framework 2.0 Web Applications certification yesterday. The exam I’m going for is the Microsoft .NET Framework 2.0 - Web-Based Client Development exam (code 70-528). I’m booked in for mid September to do it with New Horizons Ireland at a cost of €140. New Horizons seem to be the only Dublin based exam center available.

The exam focuses on using ASP.Net 2.0 to build highly dynamic, secure and scalable web applications and questions a developers knowledge of such things as standard & custom controls, data access via ADO.Net, web parts, membership, state management, mobile web applications, management & deployment of web apps and much much more. To help me prepare I got the training kit book by Glenn Johnson and Tony Northrup covering this specific exam and reviewed two very highly rated Wrox ASP.Net books which I bought last year. Additionally the fact I’ve been doing most of this stuff in my daily 9 to 5 for a good while now means I’m feeling pretty confident. In fact I anticipate much more difficulty with the other exam required for this certification - the Microsoft .NET Framework 2.0 - Application Development Foundation exam (code 70-536).

Incidentally I’m also considering going for another certification. It’s the ISEB Foundation Certificate in Software Testing. I think everyone would agree that software testing and software development are very complementary skills. Not sure where this can be done though and if classroom training is required, ideally I would just like to get stuck into a good book for a few weeks.