Create a Dashboard - C#, SQL Server, WinForms & OOP / By Date Range - Explained

hello it's been a long time since i last uploaded a video i've been a bit busy well i'm going to start uploading some existing video tutorials from the spanish channel on this english channel as some of you have been asking me for some time now however i'll make a new tutorial with updated and improved code well this time i will show you how to make a dashboard with a connection to a database and an attractive appearance in this example the data source points to a store so we will show the performance of order revenue and products in a range of dates either by the current date the last seven days the current month or custom date range in theory a data dashboard is an essential component in a system and in the user experience since it allows monitoring the general state of a business or process basically it is an information management tool that is used to track analyze and show key performance indicators metrics and data points dashboards should not be confused with reports as they have different purposes and perspectives in summary mode the tutorial will consist of five parts create the database establish the connection create the model which will take care of data access and business logic then create the user interface and finally set the model data to the form okay let's start with the tutorial firstly in sql server we will create this database of five related tables the products orders order items customers and suppliers table i already have it created i will leave you a download link of the complete script so that you can simply execute it and thus create the database the tables the relationships and the data insertion this is optional you can use the data source you want however it must contain data with current dates this database has dates inserted from the year 2020 to april of this year 2022. okay once we have the database we go to visual studio create a windows form project in c-sharp then we add a folder for the database connection then we add an abstract class it is very important to have the components and functions of a software project organized for this we can use some design pattern or architecture or we can just organize in a general and intuitive way like i'm doing now we declare a read-only field of type string for the database connection string in the constructor we initialize the connection string the database server in my case local since i have a default instance installation on my computer we indicate the database and we connect using windows credentials or through a username and password if you have one finally we create a protected method of type sql connection to establish and return the connection to the database for those just getting started with object oriented programming and abstract classes are used primarily for inheritance and polymorphism purposes commonly used to define some common attribute or behavior for a group of subclasses but also to ensure that the class cannot be created since in some cases it does not make sense to create an instance of the base class it is also advisable to create instances only when necessary and opportune on the other hand the access modifier is allowed the encapsulation pillar to be applied this private field can only be accessed by the same class in addition to being read-only and can only be initialized by the constructor this protected method can only be accessed by subclasses that inherit this base class by thinking in the above ways the pillar of abstraction is being applied abstraction encapsulation inheritance and polymorphism are the four basic pillars of object-oriented programming all right now we add another folder for the models in it we add a class for the dashboard we convert to a public class and inherit the database connection class for this we must add the reference to the db namespace we start by declaring the fields and properties to generate a dashboard this requires a start date and end date and the number of days in the date range okay now we declare the necessary properties to store the result of the queries to the database for example the total number of customers we set the set accessor to private since for common sense and security only this class should be able to modify the value however it can be obtained from anywhere it is instantiated again it enters an action the pillar of abstraction and encapsulation we continue to declare properties for the total number of suppliers and products well now we declare a property for best selling products in this case it must be of type list since the result has several rows and columns to generate the list we can reuse some existing class creative new class or structure with two properties or we can use the key value pair structure included in dotnet we do the same for low stock products in the same way for the list of gross revenue however this result can have hundreds or thousands of rows so it is not affordable to show it in a chart control so we will modify and group the result according to the number of days in the date range whether by hours days weeks months or years using link operations but the key value pairs structure doesn't work very well in that sense so we'll create a custom structure to group and format the gross revenue list all right so we finally declare a property for the total number of order total revenue and total profit in the constructor we can initialize the fields and properties however the data will change constantly so it is better to initialize or assign them in the corresponding methods we then define the following private methods a method to get the total number of customers suppliers and products using the using statement we define and assign the connection remember that the get connection method is in the base class db connection continuing we open the connection to the database and create sql command object to execute transactions or stored procedures we reference the data sql client assembly to use the sql data provider we establish the command connection first we will obtain the total number of customers for this we simply execute a query to count the number of rows to make writing easier and faster and avoid compilation problems in the future it is recommended to write the query in the same database manager so using the count function we select the number of rows from the customers table as a result there are 91 customers if it is correct we simply copied the query we assign the text command execute the command and assign the result to the corresponding property it is worth mentioning that there are many different methods to execute an sql command each one has different purposes in this case we will use the execute scalar method since it executes a query and returns the first column in row this is very convenient for our case since we only have one result also this method is much lighter than running a reader that returns many rows for the result of this method it is necessary to convert to the corresponding data type we do similarly for the total number of suppliers products and orders if you want you can create a single stored procedure and return the result in output parameters or in a table it's up to you whether to use transact sql or stored procedures in case of the total number of orders the table has a date field so the query will have a condition to count rows in a given date range to make it easier to write my queries here i have defined a parameter for the start date and another for the end date well in this date range there are 25 orders keep in mind that this query only counts how many orders have been made regardless of how many and what type of products have been sold [Music] right now we need to declare and set the from date and to date parameters it is very important to use parameters instead of concatenation in transact sql queries by using parameters we avoid sql injection it avoids problems in the data format mainly in the date time type and finally it makes it easier to create the queries since we do not have to worry about concatenating and putting the single quotes carefully it is also recommended to indicate the data type of the parameter as this prevents the server from working to match and convert the data type according to the data type of the column well that's about it for getting the total number of items now we define another private method to get the order performance we copied the previous code and we are left only with the definition of the connection and the command before continuing with the query we are going to initialize the corresponding properties for this case just fade away well let's continue we establish the text command here we will obtain the gross revenue group by date and a date range then we select from the order table where the order date is from date and to date we group by date and select the date and sum the total amount of the don't really know what comes next i'm just doing my best even though i'm so stressed out everything just feels like a test that i feel so depressed when i can't seem to get out but something deep inside won't let me quit i swear this tell me [Music] [Music] all right similarly to the previous method we declare and set the from date and to date parameters [Music] we declare an implicit variable and in it we execute the command in reader mode this method returns an sql data reader which allows reading a stream of rows from a database and while the reader is open no other operations can be performed until the reader is closed since sql connection is busy servicing the reader okay now we define a list of key and value pairs of the type date time and decimal to store the result of the query using the while statement we execute the read method of the reader and while it continues to read we add the rows from the reader to the result list we get the value of the first column and convert to the corresponding data type in the same way for the second column it is worth mentioning that the order value of the column starts at zero you can also get the value through the column name however it is more cumbersome to access the column through the name since it has to perform a search to find it that is why it is it is advisable to access the column by the position number and that applies to any type of collection well we are also going to take advantage of the cycle to add the total of the revenue and profits [Music] in the case of profits in the database i do not have the purchase price of the products so i am going to assume that the earnings are 20 [Music] finally we close the reader as i said at the beginning this list of order revenue can have hundreds or thousands of results and it is feasible for us to show it in the data graph therefore we will group the result in the list of gross revenue either by hours days weeks months or years depending on the number of days in the date range for this we will use link operations however i don't have the record of the hours and the date as a result the demonstration will not be possible so i am going to skip grouping by hours and i will simply pass the result to the revenue list and i will give the corresponding format to the date but i'll leave the full code for grouping by hours and days in the video description i'm inspired by thirst i'm inspired by worth i desire your worst so you can just hide while i work i ain't tired you first i write a second third verse about the lies you go disperse you never do i know it hurts [Music] the tostring method of the date time class allows you to convert the date value to a string in addition to being able to give a custom format you can read the microsoft documentation for more information on date and time format strings all right now let's start grouping the result using link first we will group the result by weeks as long as the date range is less than or equal to 92 days or three months then the list of gross revenue will be equal to the result table grouped by weeks for this we use the cultural information class also called regional configuration this class provides information about the culture such as the language the calendar the order of classification of the strings and the formatting of dates and numbers which in this case allows us to obtain the week number of the year well having grouped the list of orders by weeks we store the result in an identifier then project it into a new object of the same type as the gross revenue list optionally we can concatenate the word week to the week number and sum the total amount of the group [Music] finally we convert the list we do similarly to group by months if the number of days is less than or equal to two years we group and format the result by months optionally we can make another format to show only the name of the month if the number of days is equal to a year so we define a field of type boolean to determine if the number of days equals a year now and date if it is a year we only extract the month for this we use the substring method as parameters we indicate that it extracts from position 0 until it finds a blank space if you placed another date separator for example a slash you must also indicate the slash here if the condition is not met we simply set the value as set in the format very good now finally if none of the above conditions are met that is the date range exceeds two years we grouped the result by years well that's all on the web page i will leave the complete code to also group by hours and days using link now we create another private method to get the performance of the products we initialize the list property of the best-selling products and low stack products in the same way as the previous one using the using block we create the connection and the sql command it is very important to use the using statement since we ensure that any object that implements the disposable interface is correctly disposed as soon as it fulfills its objective and does not require explicit code to ensure this happens for example in the case of the sql connection once the transaction completes the connection object will be disposed and closed without the need to explicitly call the close method and this will happen even if an exception is thrown well first we will get the five best selling products in the specified date range then in the database manager we perform the following query select from the order item table join the product table by product id and also we join the order table using the order id where the order date is between the start date and end date and group on the product name finally we select only the first five rows we show the product name column and we sum the number of products in the order item this query will not return the products with the most order yet since it is not ordered by the sum of the product quantity so we must order it by the quantity column by default it is order descending so we change the descending to show from highest to lowest and thus obtain the best-selling products you can select the number of products you want in my case getting the top five is enough we declare the parameters for the date range in this method we will do two queries so we declare a field for the sql data reader to reuse later well we execute the query in the data reader we declare the while loop to read the rows of the query and add it to the list of top products [Music] we close the sql data reader to release it and be able to do another query on the same connection as i mentioned before while the reader is open no other operations can be performed until the reader is closed since sql connection is busy servicing the reader all right now we get the low stock products in this case my alert level will be when the products are below six stocks you can choose the quantity you want so we select the product name and stock column from the products table where the stock is less than or equal to six and the product is not discontinued [Music] well there were only four low stock products however as i said you can choose the minimum stock alert you want for example 10. there are now 41 products with stock below 10 stock but i think six is a considerable stock for a small neighborhood store the minimum stock alert will depend on the size of the business [Music] all right so we run the query on the data reader through a while loop we read the rows of the query and add it to the list of under stuck products and finally we close the data [Music] but even reader couldn't learn how to contain it her high heels make a damn good statement there is no replacement a body biceps okay that's all for the database queries now we'll define a public method to load the data using these private methods [Music] as parameters the start date and end date we assign the date parameters to the date fields and get the number of days in the date range [Music] we call the private methods to get the data from the database [Music] very well with this we would be finishing creating the data dashboard model however on many occasions a user unconsciously due to boredom or stress tends to press a button constantly without stopping for a long time and i include myself so that will make this method run constantly therefore the queries to the database will also be done every second and that will slow down the database server a bit as long as it has multiple connections but if it's a local database i don't think it's going to be a problem even so we will limit the operation so as not to constantly execute it in the same time interval so here we add a condition so that the code is executed only when the date range parameters are different than the date range fields however these dates are a date and time structure therefore there will always be a difference of seconds as a result operations can still be executed every second then we will modify the end date to set a fixed second and thus the date and time have a difference by minutes and allow the queries to be executed once a minute since it would practically be the same query [Music] here we establish the fixed second of time i recommend establishing 59 seconds to obtain all the related data in the date and time range since when registering in order it is done with the exact hour minute and second of course this only influences when you use a date time field well doing this type of the validations and restrictions we can also make the method return a boolean value to determine if the data has been refreshed and thus also refresh the form or not optionally we print a message in the console to know if the code is running correctly for example data not refreshed because it is the same query or the time interval difference is very short i recommend using these types of messages as they help you determine if your code is running as planned as well as help you identify and fix a problem faster all right that's it now we go to the form to show the model data on the screen first we will add all the necessary components to visualize the model data now the gist for the queries to work we need to add two date time pickers to assign the dashboard date range we set the name and optionally the date format then we add buttons to get analytics for last 30 days this month today or custom date [Music] now we add a panel with labels to show the total number of orders total revenue and total profits [Music] tell me [Music] [Music] then we add a chart control to show the list of gross revenue and another to show the list of five top products customizing the appearance through the properties as we like [Music] [Applause] [Music] we added another panel with labels to show the total number of customers suppliers and products i'm inspired by thirst i'm inspired by worth i desire your worst so you can just hide while i work i'm tired you first i write a second third verse about the lies you go disperse you never do i know [Music] finally we add a panel and a tag review to show the list of low stock products [Applause] [Music] well once the form has been designed we go to the form code we declare a field for the dashboard model in the constructor we set the default date range for the data dashboard for example the last seven days so the value of the start date picker control will be equal to today's date subtracted by seven days and the value of the end date picker control will be equal to the date and time of now we also select the button for the last seven days so that it has a type of highlight then we instantiate the model and finally we call a method to load the data into the form this method doesn't exist so we generate it well in the load data method we call the load data method of the model this method returns a boolean value which determines if the data has been refreshed or not and as i commented in this one then we will also make the form viewload the data in the controls only when it is necessary or the model has changes since it would not make sense to refresh the form if the data is the same so we declare variable and init we call the method load data from the model as parameters we send the value of the start date and end date control well we define a condition if the refresh data field is true we load or refresh the form data it is optional to explicitly compare a true value we can use the short way just like for the false value using one of the other depends on the tastes of each one however there are still people who do not know the short version or are used to the explicit version okay now we set the model data to the form components let's start with the text of the labels for example the text of the number orders label will be equal to the number of orders property of the model and so we assign each data to the corresponding controls now we set the data source for the chart of gross revenue by period on the chart of five best-selling the liquor that products chase got some to the face baby i don't need no space coming closer for a taste and i'll show you how i make everything just fade away in the case of charts it is necessary to specify the column of the series to link the data of the values of the x and y axes in this case the data source of the chart is a list of the revenue by date structure has a date member that will be the data source for the x-axis of the series and a total amount member which will be the data source for the y-axis of the series the same goes for the list of key value pairs there is the key member and the value member okay let's continue if you want you can set the number of the member column instead of the name of the member column finally we call the data bind method to force the chart view to refresh with the data source we do the same way for the chart of the best selling products it is worth mentioning that the data bind method does not need to be invoked when setting a single data source that is you only need to set and show the data once without changing the data finally we establish the data source of the datagridview of the products low in stock optionally we print messages to determine that it is working correctly all right let's test the app in the data dashboard it should show us the performance analysis and metrics for the last seven days the default button is highlighted correctly and the data source is set correctly in the form components to corroborate that these are the impressions of the message we wrote all right now we'll refresh the dashboard based on these buttons either this month the last 30 days or a custom date range i'll also change the text of the columns in the data grid view since it doesn't make sense for them to be named that [Music] well we go to the forum and subscribe to the click event of each button let's start with the button today here we simply set the start date value to today and the end date value to now [Music] finally we call the load data method however for aesthetic reasons we will make the date pickers and the ok button of the custom date disabled this to give the behavior of the custom date range and show the selected date [Music] it she range get you high all the dopamine inside triggers fast once she's naked her body's at creation they say the devil made her for his entertainment but even he couldn't learn how to contain it her high all right we continue by subscribing to the click event of the buttons and setting the corresponding date range make a damn good statement there is no replacement and we tearing down this place got some amigos to the face baby i don't need no space coming closer for a taste and i'll show you how i make everything just fade away because she's like eggs drugs cocaine vice versa on a tight frame all game no shame baby came here for the case of the custom date button we simply re-enable the custom date state pickers and ok button and on the ok button we call the load data method [Music] [Music] great everything works fine as well as the limitations we set that is does not allow to refresh the data in the same minute or constantly however that is optional you can remove the conditions if you want to refresh the data in real time or every second all right that's all for now the video got pretty long so i'll show you how to customize the appearance of the dashboard in an upcoming video so it looks like this well until the next video [Music]

Channel: RJ Code Advance EN

Views: 114,639

Keywords: Windows Form, C#, VB, .NET, Software, HTML, Modern Forms, software, computer, technology, design of modern user interfaces, visual basic, php, java, design flat login, software development, web programming, information systems development, JavaScript, Visual Basic, Software Patterns, Architecture Patterns, Design Patterns, Language Patterns, Software Engineering, Systems, Web pages, mvc, layers, objects, POO, object-oriented programming, mysql, mariadb, .Net Framework, Custom Controls, SQL

Id: qHyrJDd4sd8

Length: 40min 35sec (2435 seconds)

Published: Sun Mar 06 2022