none
Filter DataGridView wiht DataSet wihout source change RRS feed

  • Question

  • Hello,

    I have following problem and I can't find soulution, can you help me, please?

    If DataGridView has a DataSource = dataTable (connected directly on runtime): I can filter it like this:

    dataTable.DefaultView.RowFilter = "country LIKE 'S%'";
    

    If DataGridView has a DataSource = bindingSource: I can filter it like this:

    bindingSource.Filter = "country LIKE 'S%'";

    However, how to filter if DataGridView is connected te DataTable from DataSet object during design time (e.g. when you use designer to design DataSet and DataTable, and then connect it to DataGridView directly)? I mean: if dataGridView.DataSource = dataSet; and dataGridView.DisplayMember = "TableName";

    I've thought this should work:

    dataSet.Tables["TableName"].DefaultView.Filter = "country LIKE 'S%'";

     but it does't refresh DataGridView.

    Please note, that I know that it can be solved using DataView like this:

    DataView dv = dataSet.Tables[0].DefaultView;
    dv.RowFilter = "country LIKE 'S%';
    dataGridView1.DataSource = dv;

     or in similar way using BindingSource, however, both of this solutions change DataSource (it's not DataSet anymore, but a DataView). I want to avoid that, as I'm making UserControl to work with DataView, and changing source during runtime may appear in exceptions eg. throwing during casting. What I want is before and after filtering casting like this should bo possible:

    (DataSet)(dataGridView1.DataSource);
    

    How can I get to element (DataView or BindingSource - or mayby it's something else?) - that is binded to DataGridView - and make a filter on it?

    The piece of code that ilustrates my problem is below: after filtering DataSource should remain the same type:

    DataSet ds = new DataSet();
    DataTable dt = new DataTable();
    
    private void Form1_Load(object sender, EventArgs e)
    {
      dt.Columns.Add("id", typeof(int));
      dt.Columns.Add("country", typeof(string));
    
      dt.Rows.Add(new object[] { 1, "Belgium" });
      dt.Rows.Add(new object[] { 2, "France" });
      dt.Rows.Add(new object[] { 3, "Germany" });
      dt.Rows.Add(new object[] { 4, "Spain" });
      dt.Rows.Add(new object[] { 5, "Swiss" });
      dt.Rows.Add(new object[] { 6, "United Kingdom" });
    
      ds.Tables.Add(dt);
      dataGridView1.DataSource = ds;
      dataGridView1.DataMember = dt.TableName;
    }
    
    private void textBox1_TextChanged(object sender, EventArgs e)
    {
      MessageBox.Show("DataSource type BEFORE = " + dataGridView1.DataSource.GetType().ToString());
    
      //it is not working
      ds.Tables[0].DefaultView.RowFilter = "country LIKE 'S%'";
    
      MessageBox.Show("DataSource type AFTER = " + dataGridView1.DataSource.GetType().ToString());
    }
    
    

     

    I appricieate any help :)

    Best regards,

    Marcin

    Sunday, May 1, 2011 4:58 PM

Answers

  • I just tried, what you said and made a simple form, check code below

    <pre lang="x-c#">public class TestForm
    {
    
    	//define this dataset globally
    	DataSet dsData;
    
    	private void TestForm_Load(System.Object sender, System.EventArgs e)
    	{
    		string strsql = null;
    
    		strsql = "EXEC TestProc";
    
    		dsData = dataLayerObject.GetDataSet(strsql);
    		// my method to get data into dataset
    		DataGridView1.DataSource = dsData.Tables[1];
    		// Bind DataGridView1 
    
    	}
    
    	private void Button1_Click(System.Object sender, System.EventArgs e)
    	{
    		string strsql = null;
    
    		strsql = TextBox1.Text.Trim();
    
    		dsData.Tables[1].DefaultView.RowFilter = "PayCategory Like '" + strsql + "%'";
    		// Doing row filter with the data entered in textbox, you can hard code the string here
    
    		DataGridView2.DataSource = dsData.Tables[1];
    		//just to check, i binded the same filtered dataset to another DGV DataGridView2 but the same was reflected in DataGridView1 i.e. 1st DGV refreshed after i filter the dataset. 
            //But if you didn't defined the dataset globally, rebind the DGV datasource
    
    	}	
    }
    


    Amit Govil(amit.govil@hotmail.com)
    • Marked as answer by Helen Zhou Tuesday, May 17, 2011 3:05 AM
    Thursday, May 5, 2011 5:49 AM

All replies

  • syntax for filtering a datatable to a dataview is

     

    DataView dvFilterData = new DataView(dtMasterData, "Filter On =" + FilterValue, DataViewRowState.CurrentRows);
    

     

    and later bind this filtered dataview to DGV datasource

     

     


    Amit Govil(amit.govil@hotmail.com)
    Sunday, May 1, 2011 5:28 PM
  • Hi Marcin,


    If the dataGridView is binded to a DataTable that is one of the Tables in DataSet, and filter on the bindingSource of the DataGridView should be like this:

    private void PopulateDataViewAndFilter()
    {
        DataSet set1 = new DataSet();

        // Some xml data to populate the DataSet with.
        string musicXml =
            "<?xml version='1.0' encoding='UTF-8'?>" +
            "<music>" +
            "<recording><artist>Coldplay</artist><cd>X&amp;Y</cd></recording>" +
            "<recording><artist>Dave Matthews</artist><cd>Under the Table and Dreaming</cd></recording>" +
            "<recording><artist>Dave Matthews</artist><cd>Live at Red Rocks</cd></recording>" +
            "<recording><artist>Natalie Merchant</artist><cd>Tigerlily</cd></recording>" +
            "<recording><artist>U2</artist><cd>How to Dismantle an Atomic Bomb</cd></recording>" +
            "</music>";

        // Read the xml.
        StringReader reader = new StringReader(musicXml);
        set1.ReadXml(reader); 

        // Get a DataView of the table contained in the dataset.
        DataTableCollection tables = set1.Tables;
        DataView view1 = new DataView(tables[0]);

        // Create a DataGridView control and add it to the form.
        DataGridView datagridview1 = new DataGridView();
        datagridview1.AutoGenerateColumns = true;
        this.Controls.Add(datagridview1);

        // Create a BindingSource and set its DataSource property to
        // the DataView.
        BindingSource source1 = new BindingSource();
        source1.DataSource = view1;

        // Set the data source for the DataGridView.
        datagridview1.DataSource = source1;

        //The Filter string can include Boolean expressions.
        source1.Filter = "artist = 'Dave Matthews' OR cd = 'Tigerlily'";
    }

    For more information about BindingSource.Filter Property .

    If you ahve questions, please feel free to let me know.


    Helen Zhou [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 4, 2011 3:25 AM
  • Hello again and thank you both for answers so far. Unfortunately, they do not provide behavior I need...

    Problem is, that I do not create or define datasource for DataGridView. The code I provided is just an example to re-create the behaviour which cause the problem.

    Solution Govil_MCTS with DataView as datasource works, solution of Helen Zhou with BindingSource also works (they're in my post as well). However, they both CHANGE THE DATASOURCE.
    They're good if you are the only developer of applicaion.

    I want to make a kind of "universal" user control working with unknown DataGridView. It is possible, that its developer used visual desginer to create DataSet and DataTable WIHOUT using DataBinding (I do it sometimes, too ;)) and connected DataSet as DataSource and table name as DataMember.

    In that case, changing DataSource to DataView (first solution) or to BindingSource (second solution), may appear later in exception while casting.

    Like I said, solution:

    dataSet.Tables["TableName"].DefaultView.Filter = "country LIKE 'S%'";

    is closest to the one I need: it filters dataTable and do not change type of dataGridView.DataSource (it's still DataSet), it do not refresh dataGridView, however...

    Maybe the question should be other: I know, that when you set dataGridView.DataSource = DataTable, in fact you bind to DataTable.DefaultView property. Why the same do not happen (or: what happens?), when you select DataSet as dgv.DataSource AND a dataset's table name as DataMember? What object is really binded to DataGridView?

    Or maybe the answer is simple: it cannot be done the way I want to??

    Best regards,
    Marcin

    ----

    Edit:

    Once again thank you for all the replies. I believe they show me all the ways it can be done. I've finally limited my class to work with BindingSource, DataTable and DataView objects.

    Best regards



    • Edited by marcin82 Tuesday, May 17, 2011 7:33 PM
    Wednesday, May 4, 2011 5:44 PM
  • I just tried, what you said and made a simple form, check code below

    <pre lang="x-c#">public class TestForm
    {
    
    	//define this dataset globally
    	DataSet dsData;
    
    	private void TestForm_Load(System.Object sender, System.EventArgs e)
    	{
    		string strsql = null;
    
    		strsql = "EXEC TestProc";
    
    		dsData = dataLayerObject.GetDataSet(strsql);
    		// my method to get data into dataset
    		DataGridView1.DataSource = dsData.Tables[1];
    		// Bind DataGridView1 
    
    	}
    
    	private void Button1_Click(System.Object sender, System.EventArgs e)
    	{
    		string strsql = null;
    
    		strsql = TextBox1.Text.Trim();
    
    		dsData.Tables[1].DefaultView.RowFilter = "PayCategory Like '" + strsql + "%'";
    		// Doing row filter with the data entered in textbox, you can hard code the string here
    
    		DataGridView2.DataSource = dsData.Tables[1];
    		//just to check, i binded the same filtered dataset to another DGV DataGridView2 but the same was reflected in DataGridView1 i.e. 1st DGV refreshed after i filter the dataset. 
            //But if you didn't defined the dataset globally, rebind the DGV datasource
    
    	}	
    }
    


    Amit Govil(amit.govil@hotmail.com)
    • Marked as answer by Helen Zhou Tuesday, May 17, 2011 3:05 AM
    Thursday, May 5, 2011 5:49 AM
  • Hi marcin82,

    If you feel the solution I gave doesn’t real answer your question, please feel free to unmark it and update the thread.


    Helen Zhou [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 17, 2011 3:05 AM