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

Question

asp.net 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

this.myinfo.DataBind();

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?

thanks

Answer 1

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.

Answer 2

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 ....select ...;  
  
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.