c# selecting all record with specific data on datatable

Question

I'm planning to select all rows in my datatable that has 'Dog' in column 2 without using keyword "WHERE" on my query. My guess is to try using DataRow and foreach but I'm not sure where to start. I filled my datatable with records coming from my database and this is what I've done so far.

   using(MySqlConnection con = new MySqlConnection(constring))
   {
   MySqlDataAdapter adp = new MySqlDataAdapter("SELECT * FROM table1",con);
   DataTable dt = new DataTable();
   adp.Fill(dt);
   adp.Dispose();
   }

This is what my datatable looks like:

   Column1 Column2  Column3
   1       Dog      Labrador
   2       Dog      Chowchow
   3       Cat      Persian
   4       Cat      Stubby
   5       Dog      German Shepherd

Why you do not want to use `WHERE`? This is exactly what you need. — Georg Patscheider, Dec 19 '17 at 08:05

Barr J · Answer 1 · 2017-12-19T08:04:11.373

1

Use where to distinguish:

   using(MySqlConnection con = new MySqlConnection(constring))
   {
   MySqlDataAdapter adp = new MySqlDataAdapter("SELECT * FROM table1,con);
   DataTable dt = new DataTable();
   adp.Fill(dt);
   IEnumerable<DataRow> query = adp.Where(x => x.Column2 == 'dog').ToList();

   DataTable filteredRows = query.CopyToDataTable<DataRow>();

   adp.Dispose();
   }

edited Dec 19 '17 at 08:04

answered Dec 19 '17 at 07:53

Barr J

10,636
1
28
46

I know how to do this sir and what I'm trying to do is to select from a datatable. the reason what I'm trying to do this is to feed my curiosity. I'm really sorry. – 19GreenBlankets Dec 19 '17 at 07:54

Niranjan Singh · Accepted Answer · 2017-12-19T08:05:41.507

1

You can filter records when populating table using data adapter by modifying select query:

SELECT * FROM table1 WHERE  Column2='Dog'

and second approach if you need to filter later then use this approach - Creating a DataTable From a Query (LINQ to DataSet):

List<DataRow> result = dt.AsEnumerable().Where(row=> row.Field<string>("Column2") = "Dog").ToList();

You can filter records by using DataView and use the RowFilter Property as below:

// Create a DataView
 DataView dv = new DataView(dt);
 // Filter by an expression. 
 dv.RowFilter = "Column2 = 'Dog'";
DataTable newTable = view.ToTable();

References:
Creating a DataTable from a DataView

edited Dec 19 '17 at 08:05

answered Dec 19 '17 at 07:58

Niranjan Singh

18,017
2
42
75

Yay! I'm going to try this sir and as soon as it works I'm going to mark this as answer. you definitely get what I'm trying to accomplish. Thank you so much. I know how to use Where but I don't know how to do the same thing on datatable. – 19GreenBlankets Dec 19 '17 at 08:00
Thank you so much. It is easy to follow and understand. – 19GreenBlankets Dec 19 '17 at 08:06

score 1 · Answer 3 · answered Dec 19 '17 at 08:02

You are looking for a SQL WHERE clause. Always use SqlParameter or similar to insert variables into a query (prevents potential SQL injection attacks if parameter value is user provided).

string genusInput = "Dog"; // might be provided by user input later on

DataTable result = new DataTable();

using (MySqlConnection con = new MySqlConnection(constring)) {

    const string query = "SELECT * FROM table1 WHERE Column2 = @genus";    
    var adp = new MySqlDataAdapter(query, con);
    adp.SelectCommand.Parameters.AddWithValue("@genus", genusInput);
    adp.Fill(result);
    adp.Dispose();
}

score 1 · Answer 4 · answered Dec 19 '17 at 08:48

    using(MySqlConnection con = new MySqlConnection(constring))
   {
      MySqlDataAdapter adp = new MySqlDataAdapter("SELECT * FROM table1",con);
      DataTable dt = new DataTable();
      adp.Fill(dt);
      adp.Dispose();

      DataRow[] dr=dt.select(“Column2=‘Dog’”);
  }

https://msdn.microsoft.com/en-us/library/det4aw50(v=vs.110).aspx

score 0 · Answer 5 · answered Dec 19 '17 at 07:54

0

Create where statement in your query as below sample

   MySqlDataAdapter adp = new MySqlDataAdapter("SELECT * FROM table1 WHERE Column2='Dog'",con);

answered Dec 19 '17 at 07:54

i3lai3la

980
6
10

c# selecting all record with specific data on datatable

5 Answers5