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

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

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,247 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 111.8K Reputation points
    2021-06-29T03:54:01.027+00:00

    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.

    0 comments No comments

  2. Yijing Sun-MSFT 7,066 Reputation points
    2021-06-29T06:08:12.947+00:00

    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.

    0 comments No comments