This Article Was Previously Published Under Q326176
For a Microsoft Visual Basic .NET VERSION OF This Article, See
325684 HOWTO: IMPLEMENT A DATASET SELECT DISTINCT HELPER CLASS IN VISUAL Basic .NET
Note: this article is one of a series of one
Datasethelper articles. The code in the
DataSethelper Class That Is Presented in This Article Can Be Merged with That Given in Other
DataSethelper Articles to make A Single Class with a more comprehensive feature set.
Click Here to View The Complete List of DataSethelper Articles
This Article Refers To The Following Microsoft .NET Framework Class Library Namespace:
• System.Data
In this task
Summary
• Requirements • DataSetHelper Shell Class • Select the Application • Enhancement Ideas • Troubleshooting
Summary
This Step-by-step article illustrates how to import and how to use A
Datasethelper Class That Includes Sample Code to create A
DataTable Object That Contains The Unique Values of a Column of Another
DataTable Object.
To do this, you us
SelectDist Inc. You CAN ALSO USE A Private Helper Method That Compares Fields That May Contain Null Values (DBNULL.VALUE).
THE
DataSethelper Class Includes A
DataSet Member Variable. Optionally, you can assistn an existing
DataSet Object To The
DataSet Member Variable. If The Member Variable Points to a Valid
DataSet, Any
DataTable Objects That the
SelectDistinct Method Creates Are Added to The
DataSet. In Either Case, The Method Call Returns a Reference To The
DataTable Object.
For additional information about
DataSet Objects, Click The Article Number Below To View The Article In The Microsoft Knowledge Base:
313485 Info: RoadMap for ADO.NET DATASET, DATAVIEW, AND DATAVIEWMANAGER
Back to the TopRequirementSthe 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 .Netthis Article Assuments That You Are Familiar with the Following Topics:
• Visual C # .NET SYNTAX • ADO.NET Fundamentals and Syntax
Back to the top
DataSethelper Shell Classthe Code in this section section declares the shell class to which all
DataSethelper Articles Add Methods and Member Variables.
1.Start Visual Studio .NET.2.On the File menu, point to New, and then click Project.3.In the New Project dialog box, click Visual C # Projects under Project Types, and then click Class Library under Templates.4 .In the name box, type datasethelper.5.replace the class code with the folowing code: public class DataSetHelper
{
Public DataSet DS;
Public DataSethelper (Ref Dataset 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 valid DataSet. For a class that contains a reference to a valid DataSet, the DataTable objects that the methods return are also added automatically to THE DATASET. NOTE: Add The Following to the Top of the code window:
Using system.data;
Back to the top
SelectDistINCTMETHODTHIS Section Contains The Code for T
SelectdInstinct 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)
{
................... ..
// NOTE: IF Your DataTable Contains Object Fields, Then You Must Extend this
// Function to Handle Theim in a meaningful way if you intend to group on them.
IF (a == dbnull.Value && b == dbnull.value) // Both area DBNULL.VALUE
Return True;
IF (a == dbnull.value || b == dbnull.Value) // only one is dbnull.value
Return False;
Return (a.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, 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 compile the datasethelper class what you created in the previous section, and the 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. On the File menu, point to New, and then click Project.c. In the New Project dialog box, click Visual C # Projects under Project Types, and then click Windows Application under Templates.3 .In Solution Explorer, right-click the solution and then click Add Existing Project. Add the DataSetHelper project. 4.On the Project menu, click Add Reference. 5.In the Add Reference dialog box, click the Projects tab, and then add a reference to the DataSetHelper project to the Windows form application.6.In the form designer, drag a Button control and a DataGrid control from the toolbox to the form. name the button btnSelectDistinct, and then keep the default name for the DataGrid control ( DataGrid1). 7.in the form code, add of the code window: use system.data; 8.add the folowing variable declarations to the form definition: DataSet DS;
Datasethelper.datasethelper dshelper;
9.Add The Following Construction Code (BELOW The Initialization ";): DS = New Dataset ();
DShelper = New DataSetHelper.DataSethelper (Ref DS);
// Create Source Table
DataTable 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 to the btnse ("DistINCTemployees", DS.Tables ["Orders"], "EMPLOYEEID", DS.TABLES ["Orders"]
DataGrid1.SetDataBinding (DS, "DistINCTemPloyees);
11.Run the application, and then click the button one time Notice that the DataGrid is populated with the tables and the data from the code.NOTE:. You can only click the btnSelectDistinct button one time If you click the button more than one. Time, you receive an error messages you are trying to add the same Table Two Times.
Back to the top
Enhancement Ideasyou CAN Only Use The Function To Select A Single, Distinct Field. However, You Can Extend The Functionality To Include Multiple Fields. Alternative, You Can Call The
CreateGroupByTable, The
INSERTGROUPBYINTO, AND THE
SELECTGROUPBYINTO METHODS, 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 Comply with Datacolumn Naming Conventions. The Parser Also Restricts The Names, In That The Name Must Not Contain A Period (). • OR a Space (). • If you click the button more than one time, the same table is added two times to the DataSet, which results in an exception. to workaround this problem, you can add code to the test application to check whether a DataTable of the same name already exists. Alternatively, you can create the DataSetHelper class without a reference to a DataSet, and then bind the DataGrid.DataSource property directly to the dt variable instead of by using the SetDataBinding method call. • If the source table uses custom data types (that Is, a class
Applies TO
• Microsoft ADO.NET 1.0 • Microsoft ADO.NET 1.0 • Microsoft Visual C # .NET 2003 Standard Edition • Microsoft Visual C # .NET 2002 Standard Edition
Back to the top
Keywords: KbhowTomaster KbsystemData KB326176
Back to the top
