Working with Data Tables in C#

Most of my work I have done, when it comes to do data look up or creating collection, I always used some sort of ICollection. I was able to solve them using Dictionary or List etc., Recently I came across a situation where none of these were able to solve it directly. I was going to create a custom Collection derived off of IDictionary then I came across DataTable (thanks to Bernardo @ Component One). Once I started using in memory DataTable then there is no going back, you get hooked. It is very powerful. With Linq on top of it, you will write code as if you have local SQL table. The funny thing is, it was there all along except since Silverlight did not support DataTable out of the box, you never use it and forgot about it. By the way, even though Silverlight by itself does not support DataTable, Component One does have their implementation of DataTable for Silverlight. So if you need to use Data Table for Silverlight you can look them up.

There are lot of excellent references out there if you haven’t used DataTable at all. Here are some of the references that I used for get me going

http://www.codeproject.com/KB/grid/practicalguidedatagrids2.aspx

http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx

you got the idea, it is there and use it. I am not going to explain how and what since it has been documented multiple times by talented people. I want to add couple things, I did when I start using it.

1. Please make sure you have primary key defined in the data table otherwise when you get to large data table, your search will slow down drastically. I had one table, where my search took more than a minute without primary key, by adding primary key, my select dropped to seconds.

2. When you do query against the table, it returns collection of DataRow, which by default does not support ForEach in linq, so I added a extension method like the following

public static void ForEach<T>(this IEnumerable<T> sequence, Action<T> action)
{
   foreach (T item in sequence) action(item);
}
 

3. You could avoid the extension by converting the DataRow collection to List and then you can use ForEach in the List type, but I would not recommend that approach since, it is creating additional memory.  Most of the times when you use any of the linq comment, you normally use single statement. In case if you want to use more than one statement, you can do something like this

collection.ForEach( item => { method1(); method2(); });

I update this blog post I learn more about DataTables.

Advertisements

Working with DataTable in Silverlight 4

Silverlight 4 out of the box does not support DataTable as its desktop counter part, WPF. But third party controls are here for the rescue. In this blog we will look at, what it take to create a simple grid and use Data Table that we love. As you might have guessed it, I am using Component One controls. Component One provides a namespace C1.Silverlight.Data, which have DataTable implementation that we have been using. So now that we know, we can use Data table, lets create a simple Grid and populate the data.

Create a Silverlight application and add grid control in your XAML as follow;

<Grid x:Name="LayoutRoot" Background="White">
        <c1:C1FlexGrid ItemsSource="{Binding DataTableWithData, Mode=TwoWay}" IsReadOnly="False" AutoGenerateColumns="True" Name="_flex"/>
    </Grid>

Nothing new here, my Flex Grid control bound a property in my view model. Before we go and code, we require following references in our project to run

c1.silvelright.dll

c1.Silvelright.data

System.Windows.Data

System.Windows.Controls.Data

Now lets look at the view model code;

private DataTable _dataTableWithData;

publicDataView DataTableWithData

{

     get

   
{

        if(_dataTableWithData == null)

            LoadData();

        return_dataTableWithData.DefaultView;

     }

}

_dataTableWithData property is our underlying data source. Which is nothing but DataTable, which is Component One data table type. If you notice we are not really binding data table to the grid control rather, we are binding DataView to the grid control. DataView implements the IEnumerable interface thus make it bindable to grid control. If you were like me trying out and not seeing the data in your grid, probabily your property type is incorrect.

Now lets look at the code which loads data to the data table

private void LoadData()
{
     _dataTableWithData = new DataTable();
     _dataTableWithData.Columns.Add("ID");
     _dataTableWithData.Columns.Add("Name");
     _dataTableWithData.Columns.Add("Age");
     for (int i = 0; i < 10; i++)
     {
          _dataTableWithData.Rows.Add(i.ToString(), "Name" + i.ToString(), i);
     }
}

There is nothing special here, creating and populating the data table as you would do in any other format. Now lets run and see the result;

image

 

That’s about it. To summarize

1. Make sure you use the references I mentioned above in your project.

2. Use Data Table as local variable to hold data but do not bind control to that property.

3. Use DataView type for binding it to the grid.

4. To bind data table to grid, you return ‘DefaultView’ of the data table.