AKA Marketing.com Logo            VISIT THE BLOG            

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

« Home / Forums »        


Subscribe to our SEO / IT related blog by entering your email address below

Blogged thoughts

| by the www.akamarketing.com team

Archive for the 'ASP.NET' Category

Passing ListBox parameters into ObjectDataSource controls in ASP.NET workaround

Saturday, May 2nd, 2009

I’m building a booking system at the moment and have tried to use ObjectDataSource controls as much as possible to keep things nice and clean with respect to architecture, as controls goes however the ObjectDataSource is not without its fair share of ‘gotchas’.

One such gotcha I discovered was in relation to passing ListBoxes in as parameters to an ObjectDataSources Select method (I assume it’s the same for Insert/Update/Delete methods). There seems to be no direct way to do this. I tried a lot of variations for a) defining the ListBox based parameters for the ODS and b) defining the corresponding method signature for the select method.

<asp:ControlParameter ControlID=”lstArea” Name=”areaSelection” PropertyName=”SelectedValue” />
<asp:ControlParameter ControlID=”lstArea” Name=”areaSelection” PropertyName=”Items” />
<asp:ControlParameter ControlID=”lstArea” Name=”areaSelection” PropertyName=”Items” Type=”Object” />

The first parameter above requires a select method which has a parameter called areaSelection and is of type string[] or string, which will compile fine but will always only contain the first selection in the listbox which for a multi select listbox is useless.

When I selected ‘Items’ as the PropertyName property and had the type empty or as Object I made a little bit of progress. In this case the ODS passed a ListItemCollection to the select method. I was actually able to read all the items in the collection from within my business object select method however I kept on getting a run time error:

Type ‘System.Web.UI.WebControls.ListItemCollection’ in Assembly ‘System.Web, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ is not marked as serializable.

when the page tried to load (after the ODS had done it’s work). Not sure why this is happening but it might be to do with the fact that your not really supposed to be using anything from the UI.WebControls namespace in classes.

Passing ListBox selected items into your business objects via ObjectDataSource Selecting event 

I gave up declaratively trying to add a ListBox associated parameter into my ODS (and thus indirectly into my business object method) and decided to hook into the Selecting event of the ObjectDataSource class instead and see what could be done programmatically. The Selecting event fires just before an ObjectDataSource calls your BLL select method. In it you can access an InputParameters dictionary object which allows you to add/edit and remove parameters before the actual call to your BLL is made.

Your declarative parameters do not have to match your method signature (this will compile in Visual Studio) but you’ve got to ‘fix’ your params in the Selecting method otherwise you’ll get an exception. The method signature of your select method must correspond to your ODS final parameters after you’ve made changes in the Selecting event. Here’s what my Selecting method looks like

DateTime temp = DateTime.Now;
Debug.WriteLine(temp.ToLocalTime()); //System.Diagnostics required

StringCollection areaSelections = new StringCollection();
for (int i = 0; i < lstArea.Items.Count; i++)
    Debug.WriteLine(”selected? : ” + lstArea.Items[i].Selected.ToString());
    if(lstArea.Items[i].Selected) areaSelections.Add(lstArea.Items[i].Value);

e.InputParameters.Add(”areaSelections”, areaSelections);
Debug.WriteLine(”params = ” + e.InputParameters.Count.ToString()); //will output 2

I wrote the date (including seconds) so I can easily tell one button press output from another. Instead of trying to have my business object work with a ListItemCollection (which the purists will tell you is wrong and is perhaps why it didn’t work) I simply looped through the ListBox control directly and added any selected values to a StringCollection (System.Collections.Specialized) object called areaSelections. I then simply added the areaSelections StringCollection to the ODS input parameters using the key ‘areaSelections’.

My BLL select method must now have as input a parameter named ‘areaSelections’. Its type can be Object (as everything inherits from Object) in which case you’ll have to cast it to use it or it can be passed in as a StringCollection directly. My select method signature is:

public static List<member> getMembers(string name, StringCollection areaSelections)
   //contact DAL to actually get members
   //use areaSelections… do what you want!

In this case the parameter named ‘name’ has been declaratively added during design time using Visual Studio.

The problem with this approach is that the Selecting method only gets called when the ODS deems it necessary to actually go back to the DB to get a fresh load of the data. If the ODS is using caching and the relevant data is in the cache it will not get called but perhaps more relevant in this case is the definition of ‘relevant data’. ODS only seems to look at data associated with your declaratively defined parameters and not the data associated with controls not tied to parameters.

In my case I’m seeking to filter members by their name and/or location. I have declaratively assocated a parameter (named ‘name’) with a TextBox control which the ODS will examine on postback and if it changes it will go back to the DB (thus calling Selecting in the process). The ListBox however is not associated with a parameter so it is not on the ‘radar’ of the ODS. This means that if I was to change the items in the ListBox and then cause a postback by pressing a search button (for instance) the ODS will not see any change to the existing view (assuming I didn’t change the name textbox) and thus will not go back to the DB and refresh the data. What I did to circumvent this was to explicitly call the Select() method of the ODS in its Load event. This means data will be retrieved on every postback regardless of caching settings and changes to controls. Obviously this is not great but so far I’ve been unable to determine a better way, even storing the selected values of the ListBox as a string in a hidden field and associating that control with a declaratively defined control didn’t help much.

I’d love if anyone could share a better way of dealing with ListBoxes in the context of the ObjectDataSource control.

Introducing Google Analytics API with asp.net / C#

Saturday, April 25th, 2009

Recently Google released it’s Google Analytics API into public beta, which means any old joe soap developer can give it a go without having to apply. Despite the fact I didn’t apply for the private beta I’ve been looking forward to this API for a long time… I know, I know get a life. Seriously though this opens the door to some pretty tight integration between web / mobile / desktop apps and analytical data, the possibilities are endless.

Google Analytics Data Export API
The API is easy to use and works with standard HTTP requests which return XML feeds so you can use it from any programming language. I’m going to explore the API more over the next while but I’ve used it below to pull the top 20 content items from an Analytics profile along with pageView counts for each.

Before you dive in please have a look over the developer guide but the Protocol page in particular.


Using the Analytics API mostly centers around 3 key tasks which include Authentication, Account Authorization Query and finally profile Query. Account Authorization Query is not required if you know the numeric ID of the account/profile combo you need to access. All these steps include (in a nutshell) loading a webpage and examining the response for what you need. I’ll go through these steps with code later on but here’s a plain english overview of what’s involved first.


You need to tell Google which user your app is representing/requesting data for. Google offers three ways to authenticate your app for access to a certain Google Analytics profile. These are AuthSub, OAuth and ClientLogin.

AuthSub means Google manages the entering of the username/password of the account you want to work with. This will be reassuring to a lot of people as they login via Google.com and your app will get access to only the services they explicitly approve (in this case Google Analytics). Drawback is you lose a bit of control and Google displays nasty warning messages of differing severity depending on a number of things.

OAuth is kind of an open standard version of AuthSub which can be used for authorising the use of data in many apps (not just Google ones). A security certificate corresponding to your app must be uploaded to use it.

ClientLogin on the other hand is more traditional and requires your app to request username/password from the user or manually define (perhaps in the web.config file) it if it is static. If it is static and your working with the same account (your or your clients account) all the time this is not a problem but if your application works with arbitrary accounts, users of those accounts may be uneasy about giving you their Google login details as you might use it in an unethical way or store it stupidly and later be hacked. Additionally when they give your app their user/pass combo they are giving you access to their entire range of Google services which they use (not just Google Analytics).

Therefore depending the type of app your building one authentication mode may be more appropriate than the others. I’ve written C# code which utilizes both AuthSub and ClientLogin authentication which I’ll step through later but if you want to read more about OAuth please visit OAuth Authentication for Web Applications

Account Authorization Query

After your app has ‘logged in’ (authenticated) you need to retrieve the profile ID of the account/profile combo which you want to query for certain data as this is required in the next step. If you intend querying the same profile(s) all the time then you can manually retrieve IDs for these profile(s) via the Google Analytics GUI, simply login and click the ‘Edit’ link listed beside all your website profiles, you will then see the ID listed on the next page. You can hardcode one or more profile IDs into your web.config if you like. If you will not know which website profiles your app will query ahead of time you must run an account query first from which Google will return all website profiles your authenticated user is authorised to work with.

Profile Query

When you are authenticated and have the ID of the profile you want to query you can then do just that, query. This step like the others before involves submitting a HTTP request (asking for a webpage). The response from that request is an XML feed with all the data you asked for. You define the data you want by configuring query params for your aforementioned HTTP request.

Step through - Top 20 page titles by pageview count for March 2009

OK lets actually see how to get our hands on some data. I’m going to grab the top 20 pages (by pageviews) for March 2009 and just output that data in plain text. A rough demo I put together is located at http://www.davecallan.com/analytics/, source code is available at the end of this post.

How to make a HTTP Request (with specified headers) with ASP.NET/C#?
The whole API is HTTP request based so you’ll need to know how to do this. I’m using the below method as a kind of helper when I need to interact with the API at all. The main classes of interest here are HttpWebRequest and HttpWebResponse (if anyone cares these are (roughly speaking) equivalent to using Curl in PHP). Both of these classes are located in the System.Net namespace.

public static string GArequestResponseHelper(string url, string token, mode mode)

HttpWebRequest myRequest = (HttpWebRequest)WebRequest.Create(url);

 //will always be a token of some sort required in the header but the format
 //it is passed in will depend on what type of authorization is being used
 if (mode == mode.ClientLogin)
     myRequest.Headers.Add(”Authorization: GoogleLogin auth=” + token);
 else if (mode == mode.AuthSub)
     myRequest.Headers.Add(”Authorization: AuthSub token=” + token);

 //obviously you need some kind of try/catch here
 //but OK to bubble auth/connection failures up for demo
 HttpWebResponse myResponse = (HttpWebResponse)myRequest.GetResponse();
 Stream responseBody = myResponse.GetResponseStream();

 Encoding encode = System.Text.Encoding.GetEncoding(”utf-8″);
 StreamReader readStream = new StreamReader(responseBody, encode);

 //return string itself (easier to work with)
 return readStream.ReadToEnd();


The above code requests ‘url’ and returns the response to the calling code. You’ll need to import System.Net, System.I0 and System.Text to use it. The Google Analytics API requires (well not always but mostly) authorization tokens to be included in headers which are sent along with requests to the API. An example of how an authorization key is included via HTTP headers when AuthSub or ClientLogin mode is used is also included above. You’ll notice the expected format of the Authorization header changes slightly based on what authentication mode your app is using.

How to authenticate (step 1 of possible 3) using AuthSub. (ClientLogin can be used here instead)
As outlined by Google on the Protocol page regarding AuthSub authentication:

AuthSub proxy authentication is used by web applications that need to authenticate users to Google Accounts. With AuthSub, the website operator and the client code never see the user’s username and password. Instead, the client obtains special AuthSub tokens which it uses to act on a particular user’s behalf.

To use this mode your app must first direct users (via a standard link) to the Google site to login securely. After logging in takes place Google will redirect users back to your app with a query param named ‘token’ embedded in the URL. Your app then in turn needs to upgrade this once off token for a session token, it does this yes you guessed it via a HTTP request.

Click on the ‘AUTHSUB REMOTE LOGIN’ link on http://www.davecallan.com/analytics/ to see what the process is like (notice the address bar when you get redirected back to my site). Notice also the structure of the link which directs users to the login page on Googles site in the first place. In my case it’s



The most important param here is ‘next’. It is used by Google after authentication to determine where to redirect the user to. Please read http://code.google.com/apis/analytics/docs/gdata/1.0/gdataProtocol.html#AuthSub for an explanation of the other params.

In terms of upgrading the once of token in the address bar to a longer term session token, well I’m using the method below for that

//used when you have authenticated on Google (via AuthSub & query params) & have a temp token
public static string getSessionTokenAuthSub(string tempToken)

string response = GArequestResponseHelper(”https://www.google.com/accounts/AuthSubSessionToken“, tempToken, mode.AuthSub);

 //temp (once off) token will have been exchanged for session token, return it
 return response.Split(’=')[1];


Following the API reference on the analytics developer site I know what URL to ask for. I also specify the authentication mode so the HTTP request/response helper method will know what format of header to include.

How to get a list of website profiles which authenticated user is allowed access (step 2 of 3).
This step is not needed if you know the ID of the profile you want to work with. The method I’m using is below

public static NameValueCollection getAccountInfo(string sessionToken, mode mode)

string response = GArequestResponseHelper(”https://www.google.com/analytics/feeds/accounts/default“, sessionToken, mode);

//response will contain an XML formatted string similar to

//we need to convert it to proper XML for parsing
XmlDocument accountinfoXML = new XmlDocument(); accountinfoXML.LoadXml(response);

//each account/profile combo the current user is authorized for will have an ‘entry’ element
XmlNodeList entries = accountinfoXML.GetElementsByTagName(”entry”);

NameValueCollection profiles = new NameValueCollection();
for (int i = 0; i < entries.Count; i++)
    //profile name, profile ID - profile ID is needed for ID what data you want from the API

return profiles;


Pass in the token (now a session token) aquired in the previous step. This method parses the XML response to get website profile name and profile ID which I have bound to a dropdownlist (see the demo) so the user can select the relevant profile to query (in the next step). You need System.XML namespace for the above to work.

How to actually query an account (step 3 of 3).
Authentication this, authorization that… it’s time to actually request and get some real data. Specifying what data you want is all done by configuring query params in the URL which you request from the Analytics API. The Retrieving Report Data section on the protocol page has a wealth of information (but there’s more to be had on other sections of the analytics developer site too) so get it open in another window if you haven’t already done so.

As before it’s all based around a HTTP request (asking for a webpage) so we’ll be using our friend GArequestResponseHelper again. The base request URL is https://www.google.com/analytics/feeds/data however we need to add a load of query string params to that URL to instruct Google what type of information we want. First I’ll show you the URL I have hardcoded (for the purposes of this demo only) which gets me the top 20 page titles by pageviews and then I’ll explain it somewhat. Please refer to the retrieving report data section linked to above for more.


IDs is required and allows you to specify the profileID for the profile you want data for (you got this ID previously). The last four params should be fairly obvious. Metrics are the actual values you want to get hold off. In this case I’ve requested pageview counts. Dimensions relates to metric contexts or breakdowns (or cross sections) . Without dimensions specified the metric value reflects data in an Analytics account as a whole (1 aggregated value only) however if you specify that you want to see a metric (or metrics) broken down by a dimension, you get a breakdown of dimension->value combos. In this instance I don’t just want a count of total pageviews in an Analytics acccout, I want total page views for each (top 20) individual pagetitle in the account.

What’s returned from the API is completely dependent on what you ask for in the request parameters (assuming you have any data in the first place of course). An excerpt from the XML returned by the above request (on the Google Analytics website profile for akamarketing.com) is below:

<title type=’text’>ga:pageTitle=dynamic URL rewriter tool | mod rewrite tool | convert dynamic urls into static urls</title>
<link rel=’alternate’ type=’text/html’ href=’http://www.google.com/analytics’/>
<dxp:dimension name=’ga:pageTitle’ value=’dynamic URL rewriter tool | mod rewrite tool | convert dynamic urls into static urls’/>
<dxp:metric confidenceInterval=’0.0′ name=’ga:pageviews’ type=’integer’ value=’409′/>
<title type=’text’>ga:pageTitle=Google Analytics - exclude your visits even with a dynamic IP</title>
<link rel=’alternate’ type=’text/html’ href=’http://www.google.com/analytics’/>
<dxp:dimension name=’ga:pageTitle’ value=’Google Analytics - exclude your visits even with a dynamic IP’/>
<dxp:metric confidenceInterval=’0.0′ name=’ga:pageviews’ type=’integer’ value=’389′/>

Each dimension (pagetitle) is separated into ‘entry’ elements. In this case each ‘entry’ element has only one dimension and metric however sInce you can request multiple metrics and multiple dimensions in the same request this will not always be the case. You will therefore have to alter your XML parsing code depending on what exact data your requesting. It is important to note that not all metrics can be combined with all dimensions, some data relationships just don’t make sense. If you request a bad combination Google will throw a ‘bad request’ exception.

I’ve used AuthSub method for step 1 above. Step 2 and 3 above are the same regardless of which authentication mode is used. I won’t go through the ClientLogin mode much as it’s simple enough and the code is available & commented. Basically to use ClientLogin you need to POST username/password details to a specific URL (as defined in the API reference), if they are correct you will get back a session token which you will use exactly the same way as if the token had originated from AuthSub authentication.

I’ve put all code helper segments into a class called GoogleAnalytics for you to see the full source. It’s by no means production ready code, so please don’t comment me bringing that to my attention. If you want to see the full code for what’s located on http://www.davecallan.com/analytics then I’ve created an analytics.zip file which might be helpful. The code behind for the default page is quite simple and really just makes use of the static helper methods in the GoogleAnalytics class but if you’ve any questions let me know. The main thing for you, me & everyone else to do at this stage is to just play around with the new API and eventually I’m sure lots of excellent resources, tutorials and walkthroughs will appear to enable it to really take off.

If your not an ASP.Net developer heres some PHP resources about the new Analytics API
Similiar to this blog post only targetted towards PHP programmers

Using PHP & CURL to authenticate against ClientLogin

PHP Class for doing common API thing.

ASP.NET AJAX 3.5 error - “Sys is undefined” workaround

Monday, March 2nd, 2009

I was trying to put together a prototype for a potential client but kept on getting a ‘Sys is undefined’ AJAX error when I tried to run it on my host Blacknight.ie, however when I ran it on another host - discountasp.net everything worked fine. It also worked fine on my local development machine which consists of Visual Web Developer 2008 SP1 and .Net 3.5 SP1.

There’s an absolute tonne of blog posts and forum threads on this topic but I couldn’t find an exact fix. Things like modifying the web.config are most often mentioned but that ‘fix’ usually relates to environments targeting .Net 2.0 (before AJAX was included a part of the framework core).

One blog post that did point me in the right direction was:


It turns out that the ‘Sys is undefined’ error most likely occurs because the actual Javascript functions which power AJAX are inaccessbile to your .aspx pages for some reason. As suggested in that post I looked through my IIS logs to see if ScriptResource.axd was being found as that is the file that is required to ‘inject’ JS into AJAX enabled pages. It was not being found as the IIS log line below shows :

2009-03-02 08:27:54 GET /ScriptResource.axd HTTP/1.1 Mozilla/5.0+(Windows;+U;+Windows+NT+6.0;+en-GB;+rv: http://d1004190.blacknight.com/davetest.com/ajaxtest d1004190.blacknight.com 404 0 1741

The reason for this can apparently be a number of things including bad web.config configuration, bad IIS configuration and incorrect assemblies (mismatch between expected versions & actual versions) in the GAC. I’m pretty sure it’s a blacknight configuration issue though due to the fact it worked on my local machine and on discountasp.net. I’ve sent Blacknight the above link anyhow so watch this space.

What is the Sys is undefined workaround then?
AJAX is as many of you surely know just really javascript and thus ‘under the hood’ Microsofts AJAX implementation is just really Javascript. Knowing that non ASP.Net developers might want to get their hands on a good AJAX framework Microsoft has made their framework (basically a load of .js files) available from their AJAX downloads page.

These can be used by .Net developers in conjuction with the scriptpath property of the scriptmanager control on your page to allow you to remove dependency on your server and actually include the JS code required to run controls like UpdatePanel, Timer etc. with your regular Javascript stuff. This is what I did and it worked fine on the local machine (surprise, surprise) but still on Blacknight the JS source wasn’t being found. I examined the source code of my page on Blacknight and the src tag that was outputted was:

<script src=”MicrosoftAjaxLibrary/System.Web.Extensions/” type=”text/javascript”></script>

However the version of the library available from the downloads page mentioned above is 3.5.30729.1. I renamed the version on the host to 3.5.21022.8 (better to download the right library of course), refreshed and it worked perfectly from then on. Interesting the src tag outputted from my local machine correctly referenced library version 3.5.30729.1.

Why does the framework on blacknight look for version 3.5.21022.8 but discountasp.net and my local environment look for 3.5.30729.1?. I’m not 100% certain but machines with 3.5 sp1 (discountasp.net & local machine) must be configured to ‘look’ for an AJAX library of a particular version, while machines of 2.0 or 3.5 will look for other versions. This means in theory a mismatch between what version of the AJAX library (well what version of the containing assembly - ‘System.Web.Extensions’ to be more correct) the framework expects and what version it finds (or doesn’t find for that matter).

To check if there’s a mismatch you could set the scriptpath property of the scriptmanager to some abitary value, upload, check the src statement which will look similar to the one above to see the expected version of the System.Web.Extensions assembly you need and then you could use reflection to check the actual version you have… of course I’m not certain on any of this mismatch business, casual theory only. 

What I do know however is that ‘Sys is udefined’ basically means that for whatever reason javascript code required to ‘run’ AJAX is missing, so if you cause .net to reference it in the traditional way (ie. .js files in your www folder) by using the scriptpath property it appears to be a fairly solid workaround.

Top 25 dangerous coding errors ‘revealed’

Tuesday, January 13th, 2009

I usually spend a lot of time on BBC.co.uk (so rescuetime tells me anyhow) each day and while on it today I came across an interesting piece in the technology section relating to the publication of a near universially agreed document which lists the top 25 programming errors that us so called professional developers occasionally let creep into our systems & applications.

According to the The SANS Institute (a leading contributor to the document) who issued a press release yesterday (12th Jan 09):

the impact of these errors is far reaching with just two of them leading to more than 1.5 million web site security breaches during 2008.

Contributors to the document who include SANS, MITRE, Microsoft, the US National Security Agency, the Department of Homeland Security and Symantec believe it will have four major impacts:

  1. Software buyers will be able to buy much safer software.
  2. Programmers will have tools that consistently measure the security of the software they are writing.
  3. Colleges will be able to teach secure coding more confidently.
  4. Employers will be able to ensure they have programmers who can write more secure code.

In regards to the 3rd point above I know that with the exception of a passing remark or two about the need to validate input I was not taught a lot about writing secure code during my computer science degree in college. I think in this day and age (Web based/Cloud Computing), teaching secure coding is the way to go so I think this list will definitely help.

Reading through the list, I notice a lot of the usual suspects are listed such as input validation, cross site scripting, SQL injection and that old chestnut hard-coded passwords which I must admit gave me a chuckle just by its very inclusion in such a list, but I guess if developers are still doing this then it must be included.

The fairly detailed press release from SANS is available at http://www.sans.org/top25errors/ while the document itself is located at http://cwe.mitre.org/top25/. There’s a printable PDF version available too which I suggest every software development manager or team leader makes compulsory reading for his or her programmers.

C# CSV DataTable - Convert CSV data to a ASP.Net DataTable

Monday, October 6th, 2008

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

//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();
gv.DataSource = csvDataTable;

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

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():


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

Filtering & sorting DataTables with DataTable.Select() in C#

Tuesday, August 12th, 2008

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 and allow you to update date with ‘too’ much pain. 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)

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.

12 Lorcan Crescent, Santry, Dublin 9, Ireland +353 87 9807629