Filter out rows in a DataSet based on unique ID and distinct child parameter

Question

I have a DataSet dsInvoiceData that contains the fields "ACTIVITY", "LOGUID", "VALUE".

Example of problem Dataset:

ACTIVITY-----LOGUID-----VALUE

MOVE  ------- AAAAAA ----- 000000

STORE ------- AAAAAA ----- 111111

STORE ------- AAAAAA ----- 222222

STORE ------- AAAAAA ----- 333333

MOVE  ------- BBBBBB ----- 000000

STORE ------- BBBBBB ----- 222222

How can I create a new DATASET (from the current one)?

which only contains rows:

(WHERE VALUE = 000000)

OR

(IF (VALUE != 000000) AND (each LOGUID can only have DISTINCT ACTIVITY))

Example of Result, Dataset should be:

ACTIVITY-----LOGUID-----VALUE

MOVE  ------- AAAAAA ----- 000000

STORE ------- AAAAAA ----- 111111

MOVE  ------- BBBBBB ----- 000000

STORE ------- BBBBBB ----- 222222

score 1 · Answer 1 · answered Nov 20 '14 at 14:39

1

You should probably use the DefaultView property, and filter the datatable

var t = new DataSet();
t.Tables[0].DefaultView.RowFilter = "VALUE = 000000";
t.Tables[0].DefaultView.ToTable();

The ToTable() method is used to access the results of the filtered table.

You can explore the expressions you can use with RowFilter in this link

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

answered Nov 20 '14 at 14:39

Beronien

33
6

The hardest part for me is figuring out the 2nd part of the OR statement in the correct syntax. – Enrico Nov 20 '14 at 14:54
1

Unfortunately, you can't use DISTINCT keyword with the RowFilter expression. Here you can have some ideas from previous questions: http://stackoverflow.com/questions/602836/select-distinct-in-dataviews-rowfilter – Beronien Nov 20 '14 at 15:26

Enrico · Accepted Answer · 2014-11-24T11:16:20.000

I found a way to get this done: (not sure if there are better ways to do this, if so please do tell)

First create a new DataSet with same columns (.Copy) as the original but remove the rows (.Clear)

DataSet dsInvoiceDataFiltered = new DataSet();
            DataTable dtInvoiceDataFiltered = dsInvoiceData.Tables[0].Copy();
            dtInvoiceDataFiltered.Clear();

Second create a DataTable to hold the LOGUID entries in dtInvoiceDataFiltered where VALUE !=0

DataTable dtDistinctActivityPerLogUid = new DataTable("DistinctActivityPerLogUid");
           dtDistinctActivityPerLogUid.Columns.Add(new DataColumn("Activity", typeof(string)));
           dtDistinctActivityPerLogUid.Columns.Add(new DataColumn("Loguid", typeof(int)));

Then apply filter logic on the original DataSet

        foreach (DataRow dr in dsInvoiceData.Tables[0].Rows)
        {
            int iVALUE = Convert.ToInt32(dr["VALUE"]);
            int iLogUid = Convert.ToInt32(dr["LOGUID"]);
            string sActivity = Convert.ToString(dr["ACTIVITY"]);

            // This is a unique situation (MOVE) 
            // and must be added to the new dataset
            if (VALUE == 0)
            {
                dtInvoiceDataFiltered.ImportRow(dr);
            }
            else
            {
                // This is a STORE/OTHER LOGUID with multiple VALUES 
                // only 1 of them should be added to the new list

                // Check if there is already a VALUE != 0 for that LOGUID
                DataRow[] drRowsThatMatchLogUID = dtDistinctActivityPerLogUid.Select("Loguid = '" + iLogUid + "' AND Activity = '" + sActivity + "' ");
                if (drRowsThatMatchLogUID.Length == 0)
                {
                    // If there isn't one in yet, add 1 to the new list 
                    // and to the Datatable to keep track of LOGUID entries
                    dtInvoiceDataFiltered.ImportRow(dr);

                    DataRow drNewLogUID = dtDistinctActivityPerLogUid.NewRow();
                    drNewLogUID["Activity"] = sActivity;
                    drNewLogUID["Loguid"] = iLogUid;
                    dtDistinctActivityPerLogUid.Rows.Add(drNewLogUID);
                }                   
            }
        }
        // dsInvoiceDataFiltered is now the DataSet that contains the filtered solution
        dsInvoiceDataFiltered.Tables.Add(dtInvoiceDataFiltered);

PS:Not using linq since I have to work in .Net 2.0

Filter out rows in a DataSet based on unique ID and distinct child parameter

2 Answers2