Ok, so I’ll try to keep this brief. For one our long-term projects (read: older!), we are using ASP.NET v3.5 WebForms, which is a precursor to the MVC design model of building applications. Currently in our .NET applications we are using MVC v.4 with Entity Framework v.5. So, in our project, each webpage is written and saved as an aspx file. In the aspx you will have all your traditional html elements – JavaScript, jQuery and CSS declarations. With each aspx file you have a tightly coupled aspx.cs file. This is known as the code behind file. The code behind acts, if you will, as the page’s personal controller file. In MVC you will have one controller file that will handle all the interactions for a complete directory, whereas mentioned above the code behind handles all the interactions of the individual page that it is associated with. Through the code behind we can access the service / data layer. This allows us to call stored procedures using table adapters that are data sets which represent the database tables. These table adapters also store information about the stored procedures and defines which variables are passed into the SQL stored procedure and how the tabular data returned from the procedure is to be received. Once the data is passed back to us, we can utilize the C# .NET coding libraries to further manipulate the incoming data, format it and perform calculations as needed.
For our manufacturing plants, we have a plant performance summary report, which is one of eight reports that are run directly from the MS SQL reporting services. The Report Viewer being used is v.9 and is specific to SQL Server 2005. It is in a separate project within the overall project. A set of global (corporate) reports run from https://technet.microsoft.com/en-us/library/ms155062.aspx RDL files. These files are nothing more than a definition of how the output should be shown on the page (or the report itself). The Reporting Viewer takes in, if applicable, input values that are sent to the SQL Stored Procedure that gathers the requested data and returns it to the RDL file for final output. There is no further manipulation done to the data, save for formatting issues like date formatting, two / three decimal points and the like.
So for our plant performance report, we want certain utilization and equipment availability calculations. In all previous occurrences that we perform these calculations we have the luxury to retrieve data from the database and perform loops and other various tricks to get the correct ordering of equipment statuses and durations to perform the calculations needed. Since there is no code behind being utilized we have to create a way to perform those same types of tricks to output the correct data within the Stored Procedure by using temporary tables and SQL custom functions.
In either process (stored procedure or aspx code behind), this is a cumbersome proposition, but handling it in a .NET C# environment is a lot easier than in a stored procedure environment.
