How to create dynamic report depend on user input by ASP.NET Core?

Arwa Sami 20

I want to build a dynamic report by Asp.net core -EF-, I searched for many ways and ideas, but it doesn't support my idea, I am so disappointed, the core idea of my project is to get tables and columns names from the database and displays them for the user to choose which will it display in the report, and at last generate a dynamic report. how can I build this report in Asp.net core, Can anyone give me guidelines to start building my project? Thank you for your attention. I am waiting for your guide

1 answer

Zhi Lv - MSFT 32,016 Reputation points Microsoft Vendor

2023-02-27T06:27:01.24+00:00
Hi @Arwa Sami

From your description, it seems that you want to show/hide columns dynamically, right? If that is the case, you can try to use the following methods:

Method 1: Use EF core to execute the dynamically generated SQL commands.

a. Use the following code to get the table columns: For example: get the Movie's columns:

var entityType = _context.Model.FindEntityType(typeof(Movie)); var properties = entityType.GetProperties(); var schema = entityType.GetSchema(); var tableName = entityType.GetTableName(); var storeObjectIdentifier = StoreObjectIdentifier.Table(tableName, schema); List<string> moviescolumns = properties.Select(x => x.GetColumnName(storeObjectIdentifier)) .ToList();

b. Use ViewBag/ViewData to transfer the columns to the view page, and then use checkbox to display the column name.

c. After checked the checkbox, click the submit button to submit the selected value to the Controller action method.

d. In the Action method, based on the selected columns to build the SQL query statement. And then use Sql Query or Database.ExecuteSqlCommand method to execute the sql command.

e. Transfer the query result to view page and display the result.

Method 2: Use JQuery Data table plugin.

Some JQuery Data table plugin provides the function to show/hide columns dynamically. Such as JQuery DataTables.

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Best regards,

Dillion
Please sign in to rate this answer.
Arwa Sami 20 Reputation points

2023-03-01T07:44:34.0666667+00:00

Hello @Zhi Lv - MSFT

Thank you very much for your response,

but I forgot to explain the critical point, can I use method 1 with multiple tables, I mean when I extract a report from db, create SQL query from multiple tables (inner join)?

And in Method 1 step d, what is the type of parameter in action method? with consideration, the names of the columns taken from multiple tables?

I hope you understood me,,

Thank you for your support,

Arwa Sami 20 Reputation points

2023-03-01T08:05:16.6966667+00:00

hi @Zhi Lv - MSFT ,,

I want to be sure when using SQL query or SQL command, is it a safe way from SQL injection?

Thank you for your huge support

Zhi Lv - MSFT 32,016 Reputation points Microsoft Vendor

2023-03-03T07:52:25.77+00:00

And in Method 1 step d, what is the type of parameter in action method? with consideration, the names of the columns taken from multiple tables?

You can use a string array to receive the selected columns.

I want to be sure when using SQL query or SQL command, is it a safe way from SQL injection?

You can use the safe method to execute the sql query, or before generating the sql query, you can check whether the parameter contains the special char which will cause SQL injection.

The FromSql and FromSqlInterpolated methods are safe against SQL injection, and always integrate parameter data as a separate SQL parameter. However, the FromSqlRaw method can be vulnerable to SQL injection attacks, if improperly used. See below for more details. Refer this article: SQL Queries.

Arwa Sami 20 Reputation points

2023-03-05T07:02:22.53+00:00

Thank you very much for your reply,

But there is a critical point that still needs to answer, can I use the first method with multiple tables, I mean when I extract a report from db, or generate SQL query from multiple tables (inner join)?

if this is possible that means I can't use a string array to receive the selected columns as the parameter of the action method because the selected columns maybe from multiple tables so, the parameter of the action method should be to receive selected tables with its columns (ex: [table1{column1, ...}, table2{columns2,....},....])

I hope everything is clear for you,,

Thank you for being so supportive,

Zhi Lv - MSFT 32,016 Reputation points Microsoft Vendor

2023-03-07T05:15:35.3+00:00

if this is possible that means I can't use a string array to receive the selected columns as the parameter of the action method because the selected columns maybe from multiple tables so, the parameter of the action method should be to receive selected tables with its columns (ex: [table1{column1, ...}, table2{columns2,....},....])

Yes, you can use multiple parameters to receive columns from different tables. Besides, you can also try to use a Dictionary or a string (build a string with the delimiter).

Arwa Sami 20 Reputation points

2023-03-07T06:30:36.3166667+00:00

Ok, thank you for the help

I create the class vmDynamicReport and then use a list of vmDynamicReport as a type of parameter in the action method thus, I related each table with its columns, this is correct and does not return any error, but the Hard thing is how to write inner join SQL query after receiving the selected table and columns, can help me how to write inner join?

I hope everything is clear for you,,

Thank you for being so supportive,

// -----------------> this vmDynamic_Report class public class vmDynamic_Report { public string Table_Name { get; set; } public List<string> Table_Columns { get; set; } } // -----------------> this action method in controller public async Task<IActionResult> ManageDynamicReport(List<vmDynamic_Report> dynamic_Reports) { try { } catch (Exception ex) { } return View(); }

Arwa Sami 20 Reputation points

2023-03-12T11:05:39.1433333+00:00

Hi @Zhi Lv - MSFT ,, Thank you for your support, I am still waiting your response

Ok, thank you for the help I create the class vmDynamicReport and then use a list of vmDynamicReport as a type of parameter in the action method thus, I related each table with its columns, this is correct and does not return any error, but the Hard thing is how to write inner join SQL query after receiving the selected table and columns, can help me how to write inner join? I hope everything is clear for you,, Thank you for being so supportive,

> // -----------------> this vmDynamic_Report class public class vmDynamic_Report { public string Table_Name { get; set; } public List<string> Table_Columns { get; set; } } > // -----------------> this action method in controller public async Task<IActionResult> ManageDynamicReport(List<vmDynamic_Report> dynamic_Reports) { try { } catch (Exception ex) { } return View(); }
Sign in to comment