CODEWITHSUNDEEP
c#
Nick LaMarca
Nick LaMarca

Reputation: 8188

Efficent Way To Search For An Item In A Datatable

I have a datatable that has 2 fields an example is shown below

Field A   Field B
0100      0300
0800      1122
5000      6000

These all represent ranges i.e. 0100-0300, I want to select (if any) the row that has an input range within its bounds i.e. if the input range is 0820-0900 then Row 2 would be selected from the table above.

My question is is there any efficent way to do this instead of looking one by one through the datatable until I find a match?

Both parts should be within the defined range.

I have to do this process serval thousand times and the lookup set is small so I put the lookup data in a datatable and want to now query the datable instead of having to call a database several thousands of times. Is this a correct approach or is it better to call the server?

Upvotes: 1

Views: 110

Answers (2)

Steve
Steve

Reputation: 216343

If you have already loaded your datatable from the database then you could use many ways to filter your values using directly the DataTable already in memory. (Makes no sense in this case to go again to the database)

The DataTable.Select approach is really simple

string test = "0820-0900";
string[] parts = test.Split('-');
DataRow[] rows = dt.Select("FieldA <= '" + parts[0] + 
                           "' AND FieldB >= '" + parts[1] + "'");
foreach(DataRow r in rows)
    Console.WriteLine(r.Field<string>("FieldA") + "-" + r.Field<string>("FieldB"));

The LINQ way 

var result = dt.AsEnumerable()
               .Where(x => string.Compare(x.Field<string>("FieldA"),parts[0]) <= 0 && 
                           string.Compare(x.Field<string>("FieldB"),parts[1]) >= 0).ToList();

But all these methods have a problem because the string "1111" is alphabetically lower than the string "3", so, if you have data in your fields with a variable string length you need to add zeros to the beginning of data and to the strings used to check the content of your datatable. This will render the code more complex and at the end of the work very difficult to maintain and test.

The solution is to change your database fields to be numeric and then you could use a simple comparison to extract the values in the range required

string test = "0820-0900";
string[] parts = test.Split('-');
DataRow[] rows = dt.Select("FieldA <= " + Convert.ToInt32(parts[0]) + 
                           " AND FieldB >= Convert.ToInt32(parts[1]));
foreach(DataRow r in rows)
    Console.WriteLine(r.Field<string>("FieldA") + "-" + r.Field<string>("FieldB"));

If you don't have the table in memory you could query directly the database with code like this

string test = "0820-0900";
string[] parts = test.Split('-');
string cmdText = "select * from table where field <= @p1 AND FieldB >= @p2";
using(SqlConnection cn = new SqlConnection(....))
using(SqlCommand cmd = new SqlCommand(cmdText, cn))
{
    cn.Open();
    cmd.Parameters.AddWithValue("@p1", Convert.ToInt32(parts[0]));
    cmd.Parameters.AddWithValue("@p2", Convert.ToInt32(parts[1]));
    using(SqlDataReader r = cmd.ExecuteReader())
    {
        DataTable dt = new DataTable();
        dt.Load(r);
    }
}

Upvotes: 4

Ovais Khatri
Ovais Khatri

Reputation: 3211

Try one of these:

  1.  DataRow[] result = table.Select("FieldA >= 100 AND FieldA<= 300");
  2.  // Create a DataView
      DataView dv = new DataView(dt);
      dv.RowFilter = "FieldA >= 100 AND AND FieldA<= 300";
      // dv will now have filtered rows only and you can use it as source

Upvotes: 1

Related Questions