Working With DataTable

DataTable

This object as its name points is the logical representation of a table, it supports column's types, default values, etc.
Usually when you run a query you get the results in a DataTable or into a DataSet which is no other thing that a set of DataTables.

Datatables can be modified in runtime for your code advantage, good cases for using datatable object processing instead of queries? when you need to recalculate over the same large data sets in order to provide different outputs, as in billing, costing and rating.
Let's suppose you have a set of items, and that you have different clients and providers for them, and you want to cost all the items plus run all the pricing formulas to every single one, your item's list is just as it comes from the database, for you the good thing would be to add new columns, one for each specific pricing plan and run all the calculations row by row registering that numbers, and then at the end of the process just deliver the proper pricing lists by client type using Views of the data you have just calculated.
Now imagine you have a hundred thousand items and thirty different client lists, do the math, faster, easier, cleaner to do it in memory at once than doing all the query-process-deliver for every client type and pricing you have.
If you command Datatables you will command large data processing.

DataTable MSDN Reference page

DataColumn

Is just what you think it is, the column object inside the datatable, you need to command this in order of adding new columns, calculating and filtering.

Working the Datatable

Here some code to show you the inner workings on how to create a table, remember you can have it created by the results of a query.

  // Creating a DataTable
  public void createDataTable( ref DataTable dt )
  {

   dt = new DataTable("MyTable");
   addColumn( ref dt, "ID","System.Int32",0 );
   addColumn(ref dt, "Person", "System.String", "Roger Federer");
  
   // adding some records does not hurt
   addRandomRecords(ref dt, 10);

   // we give it the spot
   dataGridView1.DataSource = dt;
   dataGridView1.Refresh();
  }

  //
  // This method will help you to add columns to your datatable,
  // you only need to give the parameters properly as in :
  //
  // - a reference to the actual datatable
  // - The name of the column, which is important cause it will be referenced later
  // - a string with the data type, string being C# type as in : "System.String", "System.Int32", etc
  // - a default value which will be set any time a row is created
  //
  // This will turn up useful once you understand the basics on
  // working on tables program                atically

  public void addColumn(ref DataTable table, string columnName, string tipo, object defaultValue)
  {
   DataColumn auxColumn = new DataColumn(columnName, System.Type.GetType(tipo));
   auxColumn.DefaultValue = defaultValue;
   table.Columns.Add(auxColumn);
  }
 
  // a method to add some random records to the new table
  public void addRandomRecords( ref DataTable dt, int records )
  {
   String[] names = new String[] { "Roger", "Richard", "Ellito", "Lisa","Andy","Barbara","Philip", "Victoria" };
   String[] surNames = new String[] { "Griffin", "Simpsom", "Brown", "Black", "White", "Federer", "Smith" };

   Random rnd = new Random(DateTime.Now.Millisecond);

   for (int i = 0; i < records; i++)
   {

    // This is how ou create a row that duplicates a datatable's row
    // so when you want to create a new record you create one of the
    // datatable's own kind, then fill it your way
    // and finally add it to the datatable
    DataRow dtr = dt.NewRow();

    dtr[0] = rnd.Next(1,records*5);
    dtr[1] = names[rnd.Next(0, names.Length)] + " " + surNames[rnd.Next(0, surNames.Length)];

    dt.Rows.Add(dtr);
   }
  }

Insert Data in DataTable:

             DataTable dt = new DataTable("Emp");

            //Adding columns to table Emp
            DataColumn colEmpid = new DataColumn("Empid",typeof(System.Int32));
            DataColumn colName = new DataColumn("Name", typeof(System.String));
            DataColumn colDept = new DataColumn("Department",typeof(System.String));

            //Adding columns to datatable
            dt.Columns.AddRange(new DataColumn[] { colEmpid, colName, colDept });

         //Adding data
            dt.Rows.Add( 1000, "John Smith Brown", "Finance" );
            dt.Rows.Add( 1001, "Carry Brown", "Engineering" );
            dt.Rows.Add(1002, "Candle Pencil", "Marketing");
            dt.Rows.Add(1003, "Graham Bell", "Engineering");
            dt.Rows.Add(1004, "Peter Kevin", "Finance & Engineering");      
            
DataView

This is what you use for filtering and sorting purposes and you can do it without compromising the source DataTable and with the ease of SQL like sintax.
It works just like a Database's View, you give it an original dataset and then configure which data will you work with and how will it look like.

DataView MSDN Reference page


RowFilter

This is the mechanism the DataView provides in order to make the views filtereable and it uses a friendly TRANSACT-SQL like set of filtering op

RowFilter MSDN Reference page


Sorting and Filtering with the DataView

  // DataView is what you use to get a different view on the datatable
  // you can apply both, sorting and filtering there
  //
  public void sortUsingDataView(ref DataTable dt, string fields)
  {
   DataView dv = new DataView(dt);

   // in order to sort you only need to provide the columns to sort by
   // in a sql's order by fashion like this : ID, Person
   // which means sort by ID and then by person, you can use desc or
   // column numbers instead of names, just like in sql
   //
   dv.Sort = fields;  
   //

   // and of course you change the datagrid source to the view
   // cause the datatable keeps being the sa
   dataGridView1.DataSource = dv;
   dataGridView1.Refresh();
  }

  // Dataview filter,
  private void btFilterColumns_Click(object sender, EventArgs e)
  {

   DataView dv = new DataView( MyTable );

   // this does the trick, we use the filter entered in the text box
   // and apply it to the View
   dv.RowFilter = tbFilter.Text.Trim();

   // always refresh grid's datasource
   dataGridView1.DataSource = dv;
  }

Here we see how our created table looks like






And then we apply the filter






As you can see inside the red marking the RowFilter just takes the same syntax Transact-SQL from MS Sql Server.
So if you know how to work a WHERE clause in SQL you will probably have not problem in your row filtering.

As you can see the code is pretty simple, few lines, but don't let yourself to be deceived if you would have to think and research this it will have took time, getting to right and straight information is not easy, i know, i had to pull the pieces together one by one.

I hope you find this useful and have it in mind when you need to do that good old data process and reprocess kind of stuff, like when you have to calculate cube-like statiscs, such a the day by day, week by week, month by moth accumulators.

You can find the solution with the source code in the attachment.

Make us know if you liked this.