Jump to content




Recent Status Updates

  • Photo
      30 Sep
    rhossis

    laptop hard disk seated beneath motherboard but with no access panel. 7 hours to replace :(

    Show comments (3)
  • Photo
      19 Sep
    Chall

    I love it when you go to write a help thread, then while writing, you reach an enlightenment, and figure it out yourself.

    Show comments (3)
View All Updates

Developed by Kemal Taskin
Photo
- - - - -

Working with DataTable, RowFilter, Dataview, etc

DataTable RowFilter DataView C# DataGridView

  • Please log in to reply
1 reply to this topic

#1 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3,447 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 27 August 2012 - 04:39 PM

More than once you have asked yourself one of these questions :

. Can i add additional columns to the result of a query for flagging/calculation purposes ?

. Can i filter the results of a query like i do in SQL ?

. Can i sort those results ?

. Can i add columns ?

. Can i add records ?


The answers for all that questions is the same, YES you can!! and in this tutorial we are gonna show you how to do it.


That is what we are going to talk about in this tutorial, with an small application, and we are going to take advantage of the DataTable, DataRow, DataColumn, DataView and RowFilter classes.



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);
		 }
	 }




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


Posted Image



And then we apply the filter



Posted Image


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.

Attached Files


  • 0

#2 VNFox

VNFox

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 648 posts
  • Programming Language:C#, PHP
  • Learning:Assembly

Posted 28 August 2012 - 10:06 AM

Very nice ... now if you can add export and import section ... then that would be cool.
  • 0

www.pickmike.com
I don't just develop software. I find solutions to your business needs.






Also tagged with one or more of these keywords: DataTable, RowFilter, DataView, C#, DataGridView