CODEWITHSUNDEEP
c#asp.netdatatable
NewbieProgrammer
NewbieProgrammer

Reputation: 864

How to search particular values in a datatable?

Guys I have a datatable in an ASP webforms application in which I am trying to implement a kind-of-sort. Basically I have two fields named Agent_Name & Agent_Code in my datatable.

For example datatable is having values like this : 

+------------+------------+
| Agent_Code | Agent_Name |
+------------+------------+
|          1 | A          |
|          2 | B          |
|          3 | C          |
|          4 | D          |
|          5 | E          |
+------------+------------+

Now I have another array of Agent_Code in which I have 2,3,5 now I want to compare this array with the datatable and only keeps those records in datatable which have Agent_Code = 2,3,5 in them and discard the rest. So the final datatable will look like :

+------------+------------+
| Agent_Code | Agent_Name |
+------------+------------+
|          2 | B          |
|          3 | C          |
|          5 | E          |
+------------+------------+

How should I about doing this ? I will appreciate some pointers.

Note : Please suggest something other than linq, I am using webforms.

Upvotes: 1

Views: 1053

Answers (4)

j.f.
j.f.

Reputation: 3939

You could use a nested for loop:

for (int y = OriginalDataTable.Rows.Count - 1; y >= 0; y--)
{
    int count = 0;
    for (int i = 0; i <= AgentCodeArray.Length - 1; i++)
    {
        if (OriginalDataTable.Rows[y]["Agent_Code"].ToString() != AgentCodeArray[i].ToString())
        {
            count++;
            if (count == AgentCodeArray.Length)
                OriginalDataTable.Rows[y].Delete();
        }
    }
}

What this does is go through each record in the datatable and checks if "Agent_Code" is contained anywhere in the new array. If so, let it be. If not, delete it.

The key here being going backwards through the data table while deleting. If you don't, the indexes will get reordered and you would see an error.

Upvotes: 2

user3794166
user3794166

Reputation:

try this:

//creating some test datatable and agent list
DataTable table = new DataTable();

table.Columns.Add(new DataColumn("Agent_Code"));
table.Columns.Add(new DataColumn("Agent_Name"));

DataRow row1 = table.NewRow();
DataRow row2 = table.NewRow();
DataRow row3 = table.NewRow();
DataRow row4 = table.NewRow();
DataRow row5 = table.NewRow();

row1["Agent_Code"] = 1;
row2["Agent_Code"] = 2;
row3["Agent_Code"] = 3;
row4["Agent_Code"] = 4;
row5["Agent_Code"] = 5;

row1["Agent_Name"] = "A";
row2["Agent_Name"] = "B";
row3["Agent_Name"] = "C";
row4["Agent_Name"] = "D";
row5["Agent_Name"] = "E";

table.Rows.Add(row1);
table.Rows.Add(row2);
table.Rows.Add(row3);
table.Rows.Add(row4);
table.Rows.Add(row5);

string[] Agent_Code = new string[] { "1", "3" };

//finished test data

//actual logic here
StringBuilder filterBuilder = new StringBuilder();

for (int i = 0; i < Agent_Code.Length; i++)
{
     if (i != 0) filterBuilder.Append(" OR ");

     filterBuilder.Append("Agent_Code = '");
     filterBuilder.Append(Agent_Code[i]);
     filterBuilder.Append("'");
}

DataView view = new DataView(table, filterBuilder.ToString(), "Agent_Code", DataViewRowState.CurrentRows);

DataTable newTable = view.ToTable();

Upvotes: 1

user2961405
user2961405

Reputation: 1

In addition to using LINQ, as offered by Pragnesh, you may also consider creating a DataView.

Using the array data you have, loop through the DataView that contains the original set in the DataTable using the RowFilter property to remove the DataTable entries that don't match those in the array.

Sort the filtered results that are in the DataView, and you have your filtered and sorted set.

Upvotes: 0

Pragnesh Khalas
Pragnesh Khalas

Reputation: 2898

Below code will help you

string[] single = new string[2] { "2", "3" };
var arr = dt.Rows.Cast<DataRow>().Where(r => single.Contains(r.Field<string>
                                     ("Agent_Code"))).CopyToDataTable();

In this example dt is your datasource which is get from gridview.datasource and single is array of your Agent_Code.

Upvotes: 0

Related Questions