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

Often as a developer, it’s best to keep things as simple as possible… and you do know by ‘often’ I mean always so if you have the opportunity to use something simple but which works well then why not?

Earlier today I started on a small web app (only about five days work) which needed a data store of some sort to store I’d say 100 records (absolute max) containing three basic things: name, email and ID. I’ve worked with SQL Server, Oracle, MySQL, XML, XSD, XSLT etc. extensively but I wasn’t going anywhere near them for this project as it was just plain overkill so I just went with old school CSV format for the data store needs of my application.

Getting access to CSV based data in C# ASP.Net is very easy. First I’d suggest using the static ReadAllLines() method of the File class to place all lines in the CSV file into a String based Array. Next you will want to call something like the Split() method to actually get your hands on the individual bits of data. Using something like this is fine but I prefer to use these methods as part of a wider effort to import the CSV data into a DataTable / DataSet as processing is made an awful lot easier.

CSV based DataTables with C#
If you too wanted to convert CSV data into a ASP.Net / C# DataTable here’s a method I wrote earlier which you can use. It’s by no means bullet proof (i.e - it requires a well formed CSV) and maybe there’s a more efficent way of doing it but it’s working well for me.

public static DataTable csvToDataTable(string file, bool isRowOneHeader)
{

DataTable csvDataTable = new DataTable();

//no try/catch - add these in yourselfs or let exception happen
String[] csvData = File.ReadAllLines(HttpContext.Current.Server.MapPath(file));

//if no data in file ‘manually’ throw an exception
if (csvData.Length == 0)
{
    throw new Exception(”CSV File Appears to be Empty”);
}

String[] headings = csvData[0].Split(’,');
int index = 0; //will be zero or one depending on isRowOneHeader

if(isRowOneHeader) //if first record lists headers
{
    index = 1; //so we won’t take headings as data
   
    //for each heading
    for(int i = 0; i < headings.Length; i++)
    {
        //replace spaces with underscores for column names
        headings[i] = headings[i].Replace(” “, “_”);

       //add a column for each heading
        csvDataTable.Columns.Add(headings[i], typeof(string));
   } 
}
else //if no headers just go for col1, col2 etc.
{
    for (int i = 0; i < headings.Length; i++)
    {
       //create arbitary column names
       csvDataTable.Columns.Add(”col”+(i+1).ToString(), typeof(string));
    }
}

//populate the DataTable
for (int i = index; i < csvData.Length; i++)
{
    //create new rows
    DataRow row = csvDataTable.NewRow();

    for (int j = 0; j < headings.Length; j++)
    {
         //fill them
         row[j] = csvData[i].Split(’,')[j];
    }

    //add rows to over DataTable
    csvDataTable.Rows.Add(row);
}

//return the CSV DataTable
return csvDataTable;

} 

You can then a) call the csvToDataTable() method to fill a DataTable and b) bind that DataTable to something like a GridView using a code snippet similar to below:

DataTable csvDataTable = csvToDataTable(”data.csv”,true);
       
//what’s in the DataTable?
GridView gv = new GridView();
form1.Controls.Add(gv);
gv.DataSource = csvDataTable;
gv.DataBind(); 

The first parameter is obviously the location of the CSV file, while the second parameter lets you specify that the first line in the CSV file is (or is not) the name of the column/elements in the CSV which will then be used to create matching column names on the outputted DataTable. A CSV excerpt I used it on was:

name,email,account num,telephone
Dave,x@x.com,1234565,18001234567
James,y@y.com,932323332,232332332332
Tina,t@t.com,3838383838,262626263
Tom,tom@tom.com,729372937,12121212121

Convert CSV to XML with C# ASP.Net
Incidentally if for whatever reason you wanted to convert from CSV to XML with C# it’s as simple as calling the WriteXml() method of the DataTable you’ve created using csvToDataTable():

csvDataTable.WriteXml(”csv.xml”);

Please let me know if you have any comments and/or questions.

We all know that connections to databases from within our applications cost us processor cycles and thus time so it’s a good idea to only ‘talk’ to your database when you really need too. In the .NET framework items like DataSets and DataTables etc. facilitate this to a large degree. In this post let’s take the .Select() method of the DataTable class as evidence of this.

.NET framework 2.0 DataTable.Select() method
The DataTable.Select() method has four overloads and allows you to filter and display your rows in a number of ways in a manner similar to the way SQL does. The method can accept an expression string which although not nearly as powerful as full blown SQL does provide the advantage of not having to go back to the database to do simple things like showing records relevant to only a certain date range or a certain customer, product etc.

Below is some of the valid expressions and filters you can pop into the Select() method to narrow your record set.

Standard Operator based expressions
Things like >, <, >=, <=, =, <> are all supported of course. If your DataTable was called salesStaff and you only wanted to return staff with sales of greater than 100, you could do this with salesStaff.Select(”sales > 100″). An array of DataRows will be returned, so depending on what you want to do with the results of the Select() method you may have to copy them back into another DataTable… more on that below.

More SQL query like expressions
Filtering by Like is also supported by the Select() method which I think is quite sweet. Wildcards as in SQL are * and %. Just like most variations of SQL support aggregate types so too does the Select() expression ‘language’. Items like AVG, MIN, MAX, SUM and COUNT are all supported, so too are functions like LEN and SUBSTRING. If you need to test multple columns/conditions you can join them with the AND or OR operators but be careful if your using .Net 1.1 SP 1 as there was a documented bug in .Select() when it was used with AND.

Sorting with DataTable.Select()
This is probabely the main way I use the Select() method. Its supported not via the expression parameter but via the sort parameter which is available in two overloaded .Select() methods. Its format is columnName asc/desc.

Importing DataTable.Select() method results into another DataTable
Putting the results from a Select() query into another DataTable is often a requirement say for instance if you wanted to bind your results to a control. As mentioned above the Select() method returns an array of DataRows and since DataRows are the main building blocks of DataTables the process is very easy. Steps outlined are:

Issue .Clone() your source DataTable to create a new DataTable with the same schema
Issue .Select() on your source DataTable() to get an array of rows with the data you want
Loop through the DataRows array and issue .ImportRow() on your destination table each interation

and a code snippet to do the above might look something like the following:

//copy the schema of source table
DataTable above24 = dt.Clone();
 
//get only the rows you want
DataRow[] results = dt.Select(”age > 24″);

//populate new destination table
foreach (DataRow dr in results)
above24.ImportRow(dr);

DataTable.Select() shortcomings
The main things I don’t like about the .Select() method is how you have to go through intermediary steps to get your results into another DataTable, why can’t it just return another DataTable (which is directly bindable to a load of .NET data controls) and its lack of support for selecting distinct/unique rows which is often needed. As as note on that last one, it is possible to return distinct rows in a DataTable using LINQ which is a .NET 3.5 component, however that topic might best be served with another post at a later date.

Can’t I just use a DataView instead of calling DataTable.Select()?
You can and DataViews are directly bindable to many controls too, however it is not always the best solution due to the generally accepted believe among many developers that .Select() is much faster than using the DataView equivalent of RowFilter (property). I regularly interchange between the two for a lot of small database projects, however for the projects where I need to be processing a mega amount of data I pretty much stick with .Select() 100% of the time as I reckon it provides real speed (as in seconds, not PC invisible micro time) advantages compared to DataViews.

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.