Atatable remove duplicate items how to: implement a dataset select distinct helper class in Visual C #. net

Http://support.microsoft.com/default.aspx? SCID = KB; en-US; 326176how to: implement a dataset select distinct helper class in Visual C #. netview products that this Article applies.

Article ID	:	326176
Last Review	:	February 8, 2007
Revision	:	3.1

This article was previusly published under q326176on this page Summary

Requirements

Datasethelper shell class

Selectdistinct Method

Test Application

Enhancement ideas

Troubleshooting

Summary

This step-by-step article extends strates how to implement and how to useDatasethelperClass that includes des sample code to createDatatableObject that contains the unique values of a column of anotherDatatableObject.

To do this, you useSelectdistinctPublic method. You can also use a private helper method that compares fields that may contain in null values (dbnull. value ).

TheDatasethelperClass desDatasetMember variable. Optionally, you can assign an existingDatasetObject toDatasetMember variable. If the member variable points to a validDataset, AnyDatatableObjects thatSelectdistinctMethod creates are added toDataset. In either case, the method call returns a reference toDatatableObject.

For additional information aboutDatasetObjects, click the article number below to view the article in the Microsoft Knowledge Base:

313485 Http://support.microsoft.com/kb/313485/EN-US) Info: Roadmap for ADO. Net dataset, dataview, and dataviewmanager

Back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:

•	Microsoft Windows XP, Windows 2000, or Windows NT 4.0 Service Pack 6a
•	Microsoft Visual Studio. NET

This article assumes that you are familiar with the following topics:

•	Visual C #. Net syntax
•	Ado. Net fundamentals and syntax

Back to the top

Datasethelper shell class

The code in this section declares the shell class to which allDatasethelperArticles add methods and member variables.

1.	Start Visual Studio. NET.
2.	OnFileMenu, pointNew, And then clickProject.
3.	InNew projectDialog box, clickVisual C # ProjectsUnderProject types, And then clickClass LibraryUnderTemplates.
4.	InNameBox, typeDatasethelper.
5.	Replace the class code with the following code: Public class datasethelper {public dataset Ds; Public datasethelper (ref dataset) {DS = dataset;} public datasethelper () {DS = NULL ;}} You can use the two overloads for the constructor to create an instance of the class with or without a reference to a validDataset. For a class that contains a reference to a validDataset,DatatableObjects that the methods return are also added automatically toDataset.

Note: add the following to the top of the code window:

 
Using system. Data;
 

Back to the top

Selectdistinct Method

This section contains the code for the selectdistinct method and the private columnequal helper method.

1.

Add the following private Method to the class definition. this method is the same as the method that is used in other datasethelper articles. it is used to compare field values (including null ). private bool columnequal (Object A, object B) {// compares two values to see if they are equal. also compares dbnull. value. // Note: If your datatable contains object fields, then you must extend this // function to handle them in a meaningful way if you intend to group on them. if (A = dbnull. value & B = dbnull. value) // both are dbnull. value return true; if (a = dbnull. value | B = dbnull. value) // only one is dbnull. value return false; Return (. equals (B); // Value Type Standard Comparison}

2.

Add the following Public Method to the class definition. this method copies unique values of the field that you select into a new datatable . if the field contains null values, a record in the destination table will also contain null values. Public datatable selectdistinct (string tablename, able sourcetable, string fieldname) {datatable dt = New datatable (tablename); DT. columns. add (fieldname, sourcetable. columns [fieldname]. datatype); object lastvalue = NULL; foreach (datarow DR in sourcetable. select ("", fieldname) {If (lastvalue = NULL |! (Columnequal (lastvalue, Dr [fieldname]) {lastvalue = Dr [fieldname]; DT. rows. add (new object [] {lastvalue}) ;}} if (Ds! = NULL) ds. tables. add (DT); Return DT ;}

Back to the top

Test Application

1.	Save and compileDatasethelperClass that you created in the previous sections, and then close the solution.
2.	Follow these steps to create a New Visual C # windows form application in Visual Studio. NET. A. Start Visual Studio. NET. B. OnFileMenu, pointNew, And then clickProject. C. InNew projectDialog box, clickVisual C # ProjectsUnderProject types, And then clickWindows ApplicationUnderTemplates.
3.	In Solution Explorer, right-click the solution and then clickAdd existing project. Add the datasethelper project.
4.	OnProjectMenu, clickAdd reference.
5.	InAdd referenceDialog box, clickProjectsTab, and then add a reference to the datasethelper project to the Windows form application.
6.	In the Form Designer, dragButtonControl andDataGridControl from the toolbox to the form. Name the buttonBtnselectdistinct, And then keep the default name forDataGridControl (datagrid1 ).
7.	In the form code, add the followingUsingStatement to the top of the code window: Using system. Data;
8.	Add the following variable declarations to the form definition: dataset Ds; datasethelper. datasethelper dshelper;
9.	Add the following constructor code (below the initializecomponent () ;): DS = new dataset (); dshelper = new datasethelper. datasethelper (ref DS); // create source tabledatatable dt = new datatable ("orders"); DT. columns. add ("employeeid", type. getType ("system. string "); DT. columns. add ("orderid", type. getType ("system. int32 "); DT. columns. add ("amount", type. getType ("system. decimal "); DT. rows. add (new object [] {"Sam", 5, 25.00}); DT. rows. add (new object [] {"Tom", 7, 50.00}); DT. rows. add (new object [] {"Sue", 9, 11.00}); DT. rows. add (new object [] {"Tom", 12, 7.00}); DT. rows. add (new object [] {"Sam", 14,512.00}); DT. rows. add (new object [] {"Sue", 15, 17.00}); DT. rows. add (new object [] {"Sue", 22, 2.50}); DT. rows. add (new object [] {"Tom", 24, 3.00}); DT. rows. add (new object [] {"Tom", 33, 78.75}); DS. tables. add (DT);
10.	Add the following code toBtnselectdistinct. ClickEvent: Dshelper. selectdistinct ("distinctemployees", DS. Tables ["orders"], "employeeid"); datagrid1.setdatabinding (DS, "distinctemployees ");
11.	Run the application, and then click the button one time. notice thatDataGridIs populated with the tables and the data from the code. Note: You can only clickBtnselectdistinctButton one time. If you click the button more than one time, you receive an error message that you are trying to add the same table two times.

Back to the top

Enhancement ideas

You can only use the function to select a single, distinct field. However, you can extend the functionality to include multiple fields. Alternatively, you can callCreategroupbytable,Insertgroupbyinto, AndSelectgroupbyintoMethods, which use group by-type functionality, to get the same kind of results.

Back to the top

Troubleshooting

•	The fieldname and alias parts of the Field List must complyDatacolumnNaming Conventions. The parser also restricts the names, in that name must not contain a period (.), a comma (,), or a space ().
•	If you click the button more than one time, the same table is added two times toDataset, Which results in an exception. To workaround this problem, you can add code to the test application to check whetherDatatableOf the same name already exists. Alternatively, you can createDatasethelperClass without a reference toDataset, And then bindDataGrid. datasourceProperty directly toDTVariable instead of by usingSetdatabindingMethod call.
•	If the source table uses custom data types (that is, a class), you must add code toSelectdistinctMethod to perform a deep copy of the data. Otherwise, only a reference is copied.