how to use LINQ on data from database to run sql type query on the data to filter?

Bradley Rogers 116 Reputation points
2021-06-28T21:40:54.947+00:00 4.5 c# MVC using stored procedure to fill a custom grid

myDataGrid myinfo; (custom data grid)

this.myinfo.DataSource = ConnectionManager.Default.GetDataSet)CommandType.StoredProcedure, .....

and then


How to use sql type commands on that dataset using LINQ?? to "fix" the poor or wrong stored procedure in the code by saying:

myDataGrid myinfo2;

select item1, item2, item3, item4, item5,
DECODE ( item6, 'Special Price', '<img src=/images/dollar.gif>', null) as item7

So if the value in any row for item6 says "special price" then item7 gets the image html or gets nothing

Changing the original sql isnt an option. have to take the DataSet and convert it into >>? what? and try to run LINQ queries on it to reshape the data

so if column 6 data says 'abc' then column 7 is blank, thats really the goal here.

dataset has 7 columns of data. how to use sort/linq commands on the dataset to build into a new dataset and pass that one onto the grid?


A Microsoft web application framework that implements the model-view-controller (MVC) design pattern.
744 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 89,156 Reputation points

    If ConnectionManager.Default.GetDataSet returns a DataSet, then you can access the corresponding DataTable object and add the new item7 column. For example:

    myDataTable.Columns.Add( "item7", typeof(string), "IIF(item6='Special Price', '<img src=/images/dollar.gif>', '')" );

    Or execute myDataTable.Columns.Add( "item7" ) and fill the column using a loop.

    After these changes, assign it to DataSource and call DataBind().

    Maybe you can also use JavaScript.

  2. Yijing Sun-MSFT 7,026 Reputation points

    Hi @Bradley Rogers ,
    You get the best performance by supplying sorting criteria in the LINQ to DataSet query that the DataView is created from and not modifying the sorting information, later.

    DataTable orders = dataSet.Tables["SalesOrderHeader"];  
    EnumerableRowCollection<DataRow> query = from ...orderby ...;  
    DataView view = query.AsDataView();  
    bindingSource1.DataSource = view;  

    Best regards,
    Yijing Sun

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our  documentation  to enable e-mail notifications if you want to receive the related email notification for this thread.