Skip to main content

Making compute column in ado.net with conditions

A question came up in the My Mind as to how to handle Data Column Expressions within a DataTable in Ado.Net. The expression syntax available to create calculated columns is probably much richer than you might think if you don't play around a lot with the Expression Property of the DataColumn.
The question was how to create a Calculated ColumnC that had the following logic:
  • If ColumnA - ColumnB > 100, ColumnC = “Yes“, else ColumnC = “No“
We can create a DataTable to do this in only a few lines of code:
      
   
   // Create the DataTable
    DataTable dt = new DataTable("Expressions");

    // Create PriceA and PriceB
    DataColumn PriceA = new DataColumn("ValueA", typeof(int));
    DataColumn PriceB = new DataColumn("ValueB", typeof(int));
    dt.Columns.AddRange(new DataColumn[] { PriceA, PriceB });

    // Add a Couple of Rows Supplying PriceA and PriceB Data...
    dt.Rows.Add(new object[] { 200, 50 });
    dt.Rows.Add(new object[] { 100, 20 });
    dt.Rows.Add(new object[] { 30, 80 });
    dt.Rows.Add(new object[] { 50, 170 });

   
    // Create Compute column
    DataColumn dtAvg =  new DataColumn("Average", typeof(float),
            "(PriceA + PriceB) /2");
    dt.Columns.Add(dtAvg);

    // Create Compute column with Condition
    DataColumn dtIsGAvg = new DataColumn("IsGoodAvg", typeof(string),"IIF(Average > 100,'Yes','No')");
    dt.Columns.Add(dtIsGAvg);



    grd.DataSource = dt;
    grd.DataBind();


Or when we connect from the back end sql server and filling data table from some of the table like givien bellow tblOrder then how to make compute column for getting average of column and and for calulate the average is greater than 100 or not. 



string sqlConStr = "Data Source=.; Initial Catalog=MyDatabase; Integrated   security=true;";
    string sqlSelect = "SELECT PriceA,PriceB FROM tblOrder";

    SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConStr);
    DataTable dt = new DataTable();
    da.Fill(dt);

     
    // Create Compute column
    DataColumn dtAvg = new DataColumn("Average", typeof(float),
            "(PriceA + PriceB) /2");
    dt.Columns.Add(dtAvg);

    // Create Compute column with Condition
    DataColumn dtIsGAvg = new DataColumn("IsGoodAvg", typeof(string),"IIF(Average > 100,'Yes','No')");
    dt.Columns.Add(dtIsGAvg);

    grd.DataSource = dt;
    grd.DataBind();


As you can see above, there is an IIF Function available to us that populates the column based on whether an expression is true or false.

   IIF(expr, truepart, falsepart) 
  • expr -- The expression to evaluate.
  • truepart -- The value to return if the expression is true.
  • falsepart -- The value to return if the expression is false.
I wrote the expression as follows:
IIF(Average > 100,'Yes','No')

Popular posts from this blog

regex - check if a string contains only alphabets c#

How to validate that input string contains only alphabets, validating that textbox contains only alphabets (letter), so here is some of the ways for doing such task. char have a property named isLetter which is for checking if character is a letter or not, or you can check by the regular expression  or you can validate your textbox through regular expression validator in asp.net. Following code demonstrating the various ways of implementation.

how $document.ready() is different from window.onload()

we often use window.onload() in javascript and $document.ready() in jquery and assume that both are same just jquery library wrap up javascript window.onload() and introduce $document.ready() but no there is much difference between them below is the explanation -

How to validate dropdownlist in JavaScript

In this article you will see how to put validation in dropdownlist by javascript, suppose first item value of dropdownlist is 0 and text is "-Select-" just like given below and we have to validate that at least one item is selected excluding default i.e "-Select-".

Regular expression for alphanumeric with space in asp.net c#

How to validate that string contains only alphanumeric value with some spacial character and with whitespace and how to validate that user can only input alphanumeric with given special character or space in a textbox (like name fields or remarks fields). In remarks fields we don't want that user can enter anything, user can only able to enter alphanumeric with white space and some spacial character like -,. etc if you allow. Some of regular expression given below for validating alphanumeric value only, alphanumeric with whitspace only and alphanumeric with whitespace and some special characters.