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

Merging multiple PDFs using iTextSharp in asp.net c#

In this article i will show you how to merge multiple pdfs into one using ITextSharp below is the two approach one is to pass your input files path, output file path (will be created if not exist) and another is pass direct input stream, output stream and it will write the merge files into output stream.

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 -

Uploading large file in chunks in Asp.net Mvc c# from Javascript ajax

Often we have a requirement to upload files in Asp.net, Mvc c# application but when it comes to uploading larger file, we always think how to do it as uploading large file in one go have many challenges like UI responsiveness, If network fluctuate for a moment in between then uploading task get breaks and user have to upload it again etc.

How to handle click event of linkbutton inside gridview

Recently I have posted how to sort only current page of gridview, Scrollble gridview with fixed header through javascript, File upload control inside gridview during postback and now i am going to explain how to handle click event of linkbutton or any button type control inside gridview.

We can handle click event of any button type control inside gridview by two way first is through event bubbling and second one is directly (in this type of event handling we need to access current girdviewrow container)

Scrollable Gridview with fixed headers in asp.net

horizontal and vertical scrollable Gridview with static header row.fixed header in gridview in asp.net.static header in gridview through java script.I was looking for a solution for this for a long time and found most of the answers are not working or not suitable for my situation i also find most of the java script code for that they worked but only with the vertical scroll not with the horizontal scroll and also combination of header and rows doesn't match.