Return all columns and also select distinct by only two columns

Question

Hi I have a datatable which contains "Name","Number","Type","Guid".

I need to get all the columns and distinct values.

I tried by using

datatable.DefaultView.ToTable(true,"Name","Number","Type");

the data table is returning name,number,type with distinct values only, but not GUID.

If i try to include Guid in the above ,distinct values are not coming as Guid will not be the same for all the rows.

could you please suugest me how to get all the rows which are distinct with all the columns.

Answer 1

Sure not, you didnt specify it in the parameter list (in ToTable() method).

Try to do it in this way:

DataView view = new DataView(table); //your table
DataTable distinctValues = view.ToTable(true, "Name", "Number", "Type", "Guid"); //all columns must be specified in the parameter list if you wanna get it!

---

Mitja

Answer 2

Hi Mitja,

If i use your approach ie including the GUID then the distinct records will not be coming as Guid will be uniuqe for every row for example this the table it will return if we include GUId

Name      Number  GUID

Test        1231      Adfyy7896866

Test        1231      Adhuiyoip8963

User        7899      sdddsd798999

If i remove guid then

Name      Number 

Test        1231      

User        7899

then this is  datatable it will come. I hope you understood, But for me i need distinct records and also with all the columns it should returnd so i mean to say my result should be as below

Name      Number  GUID

Test        1231      Adhuiyoip8963

User        7899      sdddsd798999

Could You please suggest me how to do this. Awaiting for your reply.

Answer 3

Hi Raju_Hai,

I'm afraid you cannot directly select only two columns as distinct.

I think you need to loop each datarow to see whether another row with the same Name and Number has been added into the datatable.

You can look at bahushekh's code snippet in this thread:

http://social.msdn.microsoft.com/forums/en-US/adodotnetdataset/thread/ed9c6a6a-a93e-4bf5-a892-d8471b84aa3b/

Best regards,

---

Chester Hong
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

Raju, let me ask you something. What good is the GUID in the scenario you describe? You'll get distinct Name, Number and Type but even if you could get the GUID included (which you can't using the .ToTable(), as you have seen), it would be meaningless because it would come randomly from any one of the rows which contained that particular Name/Number/Type combination.

It would help if you could explain exactly what you'd like to accomplish ...

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 5

Hi Boonie,

Actually when we are fiiling the grid based on the table ,if we want to persist the selecetd row in the grid after save or delete we need to know which row is selected s we are adding GUID for that. And i have fixed that thing by first filtering all the rows without GUID.

datatable.DefaultView.ToTable(true,"Name","Number");

and then looping through each row and adding GUID to each row. My problem got fixed but dont know whether it is a good fix or not.

Answer 6

So, the GUID is only being used to know which row is selected? How are you binding your DataTable to your grid? Are you using a BindingSource? If so, then all you need is the value of the BindingSource.Position property, which will give you the index of the selected row. If not, describe what you're doing (better yet, posting a little code would help) ...

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 7

Hi Bonnie,

i am using datagridview.DataSource = datatable. Its not possible  to keep my entire code. How can i get the position?

Answer 8

OK, even if you don't use a BindingSource, you can get the position via the BindingContext. It would be used like this:

int index = this.BindingContext[datatable].Position;

Or, if datatable isn't a field/property of your Form, this should work (untested):

int index = this.BindingContext[(DataTable)datagridview.DataSource].Position;

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com