kpitsimpl: ASP.NET Core MVC

Showing posts with label ASP.NET Core MVC. Show all posts

Embedding RS reports, HighCharts, and PBI interactives in ASP.NET Core MVC views

SSRS embed

 @model ReportView  
 <script>  
 </script>  
 <div class="container" style="background-color:#ffffff">  
   <section>  
     <div style="background-color:#ffffff; box-shadow: 5px 10px 8px #888888;">  
       <iframe height="800" width="900" src="@Model.SelectedReport.Uri" class="body-box-shadow"></iframe>  
     </div>  
   </section>  
 </div>

This is the entire view of _Report.cshtml which renders the RS report; see the extRSAuth project for how to enable non-Windows AD SSRS client authentication

The <iframe> is rendered with the URL of the report on your report server which renders a ReportViewer control view of the report

HighCharts embed

 <script src="https://code.highcharts.com/highcharts.js"></script>  
 <script src="https://code.highcharts.com/themes/adaptive.js"></script>  
 <head>  
   <script>  
     $(document).ready(function () {  
       Highcharts.seriesTypes.line.prototype.getPointSpline = Highcharts.seriesTypes.spline.prototype.getPointSpline;  
       Highcharts.chart('potusApproval', {  
       title: {  
         text: 'Trump Job Approval',  
         align: 'left'  
       },  
       subtitle: {  
           text: 'Source: <a href="https://api.votehub.com/polls?poll_type=approval&subject=Trump" target="_blank">VoteHub</a>.',  
         align: 'left'  
       },  
       yAxis: {  
         title: {  
           text: 'Approval'  
         }  
       },  
       legend: {  
         layout: 'vertical',  
         align: 'right',  
         verticalAlign: 'middle'  
       },  
       plotOptions: {  
             area: {  
               pointStart: '1/1/2025',  
               relativeXValue: false,  
               marker: {  
                 enabled: true,  
                 symbol: 'circle',  
                 radius: 2,  
                 states: {  
                   hover: {  
                     enabled: true  
                   }  
                 }  
               }  
             }  
           },  
       series: [{  
         name: 'Approve',  
         data: [ @string.Join(", ", Model.HighChartsModel.Approves) ]  
       }, {  
         name: 'Disapprove',  
         data: [ @string.Join(", ", Model.HighChartsModel.Disapproves) ]  
       }],  
     });  
   });  
   </script>  
 </head>  
 <table style="width: 100%; height:50%">  
   <tr><td><div id="potusApproval"></div></td></tr>  
 </table>

The same data can easily be embedded as a HighCharts visual using some JS and HTML; this example uses the simple HighCharts LineChart

The above HighCharts embed code renders this line chart

Power BI embed

The easiest Power BI embedded method is to use the Developer Playground "demo code" script; select File >> Developer Playground

Next, click the "Set up now" button on the upper right side of the following screen

Here you are presented with the embed <iframe> script that you put into your view...

 <iframe title="PBI_Report" style="width:100%; height:73%" src="https://app.powerbi.com/reportEmbed?reportId=695fe0f1-5c9e-497d-8913-e59f0b939ac4&autoAuth=true&embeddedDemo=true" frameborder="0" allowFullScreen="true"></iframe>

And, voila! You have a Power BI visual embedded inside your web app now

Reference: https://learn.microsoft.com/en-us/power-bi/developer/

Source: https://github.com/sonrai-LLC/extRS/tree/main/ExtRS.Portal | https://extrs.net

Customizing ASP.NET Core Identity Razor Pages via Scaffolding (in an MVC app)

If you are trying to add the built-in ASP.NET Core Identity Authentication scaffolding to an MVC application, you will notice that traversing between MVC controller actions and simple requests to Razor Pages doesn't work as transparently as you might expect.

To scaffold the Identity Razor Pages so that we can customize them, we first do the following:

Right-click the project and select, "Add >> New Scaffolded Item..."

Select the Identity scaffolding template

From the next screen, select the Razor pages that you would like to scaffold for overriding/customizing

Once you have the scaffolded pages you will need to run this from the Developer console to generate the necessary ASP.NET Core Identity database tables: dotnet ef database update

If you want to be able to get to the Login page from an MVC controller, you will; need to an [AllowAnonymous] attribute to the Login.cshtml Razor Page code-behind class, and you will need to call the Login page while referencing the Area path in which it is located within the project file structure.

The folder structure for an MVC project overriding the built-in Identity auth Razor pages via scaffolding

An MVC logout link that you want to redirect to an overridden Identity auth Login Razor page might look like this in your _Layout view:

The view link in the MVC's _Layout.cshtml

The controller redirect (note: new { area = "Identity" }); "/Pages/" is ignored for some reason..

In order to reach the Login Razor page (when not being authenticated) you will need to add the [AllowAnonymous] attribute to the LoginModel class a la:

With [AllowAnonymous] users will be able to reach the overridden Identity Login page from any controller action in your MVC application

An that's it. You can customize all of the front-end and back-end code of the scaffolded Identity Razor pages. This is an example of customization that uses the extRS service to create SSRS user accounts when a user registers a new Identity account for the app:

Scaffolding the Identity Razor pages allows you focus only on the authentication design and logic that you need to customize

I'm not sure why but much of this is not documented (and what is documented is not easily found).

Lastly, do keep in mind that in order to override certain items like "_LoginPartial", you need to rename the view, as was done with "_LoginPartial2.cshtml" in the extRS project, for example.

References:

https://github.com/sonrai-LLC/extRS/tree/main/ExtRS.Portal

https://extrs.net

Locations - Google Maps API, ASP.NET Core and SQL Server

This app's function/purpose is to use Google Maps API to get geographic data and render locations on maps with editable pins (much like... many apps these days- it is kind of becoming an expectation for any application/service involving a location street address).

In this way you can record or plan the state(s) of an event or location at some particular street address. Or just have a geographic representation of some important locations that you can then print and have a custom map for.

This is a proof-of-concept app illustrating what you can do with a little JavaScript, a web app and the Google Maps API

The code below takes locations records (containing the lat/long of the geographic coordinate) from a database and then initializes the Google Map with some options (I omitted many for brevity). The main interesting thing the code does below, is when it renders the pins (addMarker() function) it adds an event listener to delegate the task of popping up an ASP.NET Core-bound edit modal when a user clicks the pin.

On the Add and Update side as far as mapping Lat/Long from Street, City, State- that is all handled by the incredibly useful GoogleLocationService provided as a Nuget package for .NET Core apps.

Other than that it is just standard JavaScript- Google Maps API does virtually all of the geocoding and map visualization heavy lifting.

The crux of the utilization of the API code (callback and map rendering) is this:

 <script>  
     function initMap() {  
       var map = new google.maps.Map(  
         document.getElementById('map'),  
         {  
           center: new google.maps.LatLng(@Model.CenterLat, @Model.CenterLong),  
           zoom: 8  
         }  
       );  
       var pins = @Html.Raw(Json.Serialize(@Model.Locations));  
       for (var i = 0; i < pins.length; i++) {  
         var myLatLng = {  
           lat: pins[i].lat,  
           lng: pins[i].long  
         };  
         addMarker(myLatLng, map, pins[i]);  
       }  
     }  
     function addMarkerAsync(location, map) {  
       new google.maps.Marker({  
         position: location,  
         title: 'Home Center',  
       });  
       marker.setMap(map);  
     }  
     function addMarker(location, map, pin) {  
       var marker = new google.maps.Marker({  
         position: location,  
         title: '...something dyanmic...',  
       });  
       var infowindow = new google.maps.InfoWindow({  
         content: ''  
       });  
       function AsyncDisplayString() {  
         $.ajax({  
           type: 'GET',  
           url: '/Home/GetLocationModalInfo',  
           dataType: "HTML",  
           contentType: 'application/json',  
           traditional: true,  
           data: pin,  
           success: function (result) {  
             debugger;  
             infowindow.setContent('<div style="background-color:#000000;">' + result + '</div>');  
             infowindow.open(map, marker);  
           },  
           error: function (arg) {  
             alert('Error');  
           }  
         });  
       }  
       google.maps.event.addListener(marker, 'click', function () {  
         AsyncDisplayString(map, marker)  
       });  
       marker.setMap(map);  
     }  
   </script>

And then this Controller Action that uses GoogleLocationService to get coordinates by address:

 [HttpPost]  
     public IActionResult AddLocation(LocationModel location)  
     {  
       string address = location.StreetAddress1.Replace(" ", "+") + "," + location.City.Replace(" ", "+") + "," + location.State.Replace(" ", "+");  
       MapPoint coords = _locationService.GetLatLongFromAddress(address);  
       location.Lat = (decimal)coords.Latitude;  
       location.Long = (decimal)coords.Longitude;  
       using (var db = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))  
       {  
         db.Open();  
         string sql = @"INSERT INTO [Locations].[dbo].[Locations] ([Name], [Contact], [Email], [Website], [Phone], [StreetAddress1], [StreetAddress2], [City]"  
           + ",[State], [Zip], [LocationContact], [PrimaryContact], [Notes], [Type], [Lat], [Long], [Petitions], [Flyers], [Posters], [LastPickUpDateTime], [LastOutOfStockDateTime], LastDropoffDateTime"  
           + ",[AllTimeOutofStock],[Unsupportive],[VolunteerInterest])"  
           + " VALUES ('" + location.Name + "','" + location.Contact + "','" + location.Email + "','" + location.Website + "','" + location.Phone + "','" + location.StreetAddress1 + "','" + location.StreetAddress1 + "','" + location.City + "'"  
           + ",'" + location.State + "','" + location.Zip + "', -1, -1,'" + location.Notes + "', 1, " + location.Lat + "," + location.Long + "," + location.Petitions + "," + location.Flyers + "," + location.Posters + ",'" + location.LastPickUpDateTime + "','" + location.LastOutOfStockDateTime + "','" + location.LastDropoffDateTime + "', 0, 0, 1) " + ";";  
         db.Execute(sql);  
       }  
       var model = GetDefaultMapView();  
       model.KeyString = _configuration["MapsAPIKey"].ToString();  
       return View("Map", model);  
     }

This is a proof-of-concept app illustrating what you can do with a little JavaScript, a web app and the Google Maps API

As you can see the Google Maps API provides a lot of opportunity for your application- don't underestimate the power of location-based data. With the tools at our disposal today the functionality of applications is being limited less by available algorithms/frameworks/tools- but rather, our imagination.

I strongly suggest you look into the ways you can integrate geographic/mapped data with Google Maps API; very powerful API

GitHub: https://github.com/cfitzg/LocationTracks

Dapper for .NET Data Access

Jeff Atwood described the phrase coined by Ted Neward that "Object-Relational Mapping is the Vietnam of Computer Science".

I agree with everything except Atwood's (huh? 😨- keep in mind this is 2006) conclusion that we should do one or the other: objects or relational data records. Develop apps as a series of SQL data access statements assigning values to arbitrary pieces of monolith application code.. or exclusively object-oriented with everything saved to blob storage... Or something awkward like that.

That, he says (in the 2006 article*), removes the O (object) - R (relational data) mapping problem entirely. It sure does; but how can we develop apps like that?

(fast-forward 6yrs, and.... Dapper to the rescue!)

Dapper is an awesome (IMO) alternative that allows developers to retain SOLID reuse and extensibility in their .NET data access code while still accessing complex relational data- and fast.

Dapper has the best of both worlds in terms of what you look for in a data access framework - speed and clean, easy SQL-to-typed object mapping facilitation

I highly recommend the brief peruse; it is a very interesting article. It essentially describes the pitfalls that ADO.NET, Hibernate and Entity SQL (EF for MSSQL) and so many of the other approaches to modeling relational data as .NET objects that have, if not failed completely- severely been lacking especially in terms of speed and control over the actual SQL that you instruct the SQL engine to execute.

Dapper aims to bridge the eternal gap between application and relational database code in a pretty elegant way for .NET development. So long as your database records (whether from a complex JOIN'd SP or wherever in your db)- can return data with types and field/alias names that match your "query-return-target-type" class' properties' names and data types, you are set for all the kinds of data access you like and are off and running without all the headaches normally associated with ORMs (magic config strings, mappings in separate files out of sync with class or db changes, etc.).

"there is no good solution to the object/relational mapping problem. There are solutions, sure, but they all involve serious, painful tradeoffs. And the worst part is that you can't usually see the consequences of these tradeoffs until much later in the development cycle." -Jeff Atwood on ORMs

I guess you could say that the SQL itself in the queries you tell Dapper to issue to MSSQL are "magic strings" insofar as VS doesn't compile them.. But if you don't use SSMS to parse and execute tests of your queries before using them in application code then you aren't really doing real data development- you are just shooting in the dark.

You should have unit tests for this very purpose. Unit tests of your Dapper calls will catch any db changes in the tests ("hey why did nobody tell me about this schema change in the Archives table?"); regardless- if your SQL field names don't match the class prop names of the object you are trying to "Dapperize"- you will find out at run-time. The exception messages are very "straight to the point of exactly what is off".

Dapper works the same in all versions of .NET; it is currently based on .NET Standard for that very reason, but you will need to bring in more dependency depending on what type of data source you are trying to access (SQL Server, MySQL, Oracle, DB2, Terradata, etc.).

Consider giving Dapper a try - it is very useful and illuminating, and it really shines in the very areas where EF falls short.

Dapper accessing 'UserReport' records from SQL db and returning the dynamic, typed object:

     SqlConnection db = new SqlConnection(WebConfigurationManager.AppSettings["DefaultSQLConnection"]);  
     public List<UserReport> ReadAllSavedUserReports()  
     {  
       using (db)  
       {  
         return db.Query<Report>("SELECT * FROM CLARO.dbo.UserReport").ToList();  
       }  
     }  
     public UserReport FindSavedUserReport(int id)  
     {  
       using (db)  
       {  
         return db.Query<Report>("SELECT * FROM CLARO.dbo.UserReport WHERE Id = @Id", new { id }).SingleOrDefault();  
       }  
     }

Forgive the "SELECT *.... this is just a demonstration..

These methods can then easily be called in controller or other code like so:

     public ViewResult Index()  
     {  
       string nowTime = DateTime.Now.ToShortDateString();  
       ReportDAL dal = new ReportDAL();  
       Demo model = BuildModel(BuildSQLStatement(nowTime, ReportDrafts.BaseballDemo), nowTime);  
       model.Reports = dal.ReadAllSavedUserReports();  
       return View(model);  
     }

Dapper is not a company trying to sell anything- it is just a really useful micro-ORM for those who prefer to work more hands-on with the SQL in data access code (and like to be able to more granularily control optimization for speedier queries).

*Atwood helped contribute (with SO) to the development of Dapper, so... I think he and that team kinda nailed the removal and easing of the very same limitations he bemoaned in the article I reference at the beginning: https://stackoverflow.blog/2012/02/18/stack-exchange-open-source-projects/

References:

https://elanderson.net/2019/02/asp-net-core-with-dapper/

https://dapper-tutorial.net/