Selecting rows from Datatable by applying Length filter

I wasn’t able to find a code snippet on web which select rows from Datatable by applying both Length and Trim function on a particular string column in datatable.

My requirement was to filter rows from datatable which are invalid and has blank columns.

This works for me.

public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            var datatable = CreateDatatable();
            datatable = FilterDatatable(datatable);
            MessageBox.Show(datatable.Rows.Count.ToString());
        }

        private DataTable FilterDatatable(DataTable sourceDatatable)
        {
            // This is the line which does both the thing Trimming and length check
            string expression = “LEN(TRIM(FirstName)) > 0”;
            var rows = sourceDatatable.Select(expression);
            //Only works on .net 3.5 and above
            DataTable datatable = rows.CopyToDataTable();
            return datatable;
        }

        private DataTable CreateDatatable()
        {
            DataTable table = new DataTable();
            table.Columns.Add(“FirstName”, typeof(string));
            table.Columns.Add(“LastName”, typeof(string));

            table.Rows.Add(“Sumit”, “Gupta”);
            table.Rows.Add(”  “, “Singh”);
            table.Rows.Add(” Nagaraja”, “Singh”);
            table.Rows.Add(”            “, “Singh”);
           
            return table;
        }
    }

One caveat in approach above: This is going to change the order of rows which you might not want.

Ideally you should filter like this:

private DataTable FilterDatatable(DataTable sourceDatatable)
      {
          // This is the line which does both the thing
          string expression = “LEN(TRIM([First Name])) = 0”;
          var rows = sourceDatatable.Select(expression);
          foreach (var row in rows)
              row.Delete();
          return sourceDatatable;
      }

Please note that I have reversed my expression here. 

In case you have space in column name than please follow this blog to resolve that:

http://cpramod.wordpress.com/2009/01/06/datatable-select-problem-with-column-name-having-space/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s