How to use a Database project in .NET Visual Studio?

Introduction

The Database plays a vital role in any web or windows application development. The Database project can be used to maintain the repository of the schema changes.

Why do we need a Database project?

While developing any application, the developer has to work on many database tasks such as creating tables, triggers, stored procedures, functions, etc. It is difficult to manage the scripts at the time of deployment. Also, it is tedious to review the changes made by the developer during development. The database project in Visual studio addresses these issues.

How to create a database project?

  1. Create a new project in visual studio. Select the SQL Server Database Project, as shown below.

New project

2. Right-click on the project and select ImportđŸĄȘDatabase

Importing database

3. After this, you get one popup as follows. Click on ‘Select Connection
’ option to import the database.

Import database

4. You will receive another popup to select the connection properties of your database. After entering the credentials (and authenticating successfully), you will get the dropdown filled with a list of databases on  the SQL server. Select the database for which you are creating the project.

Connection properties

5. Once you click on ‘Connect’, you will notice all the tables, procedures, functions, etc. which will get imported.

Import database summary

6. Check solution explorer for your database related artefacts.

Solution explorer

7. Now compare your project schema with the database schema after performing modifications to the database. Right-click on the solution and select Schema compare, as shown below.

Database demo

As you can see in the above image, there are two sections Source and Target.

Source: If you have made any changes in the database and you want to compare those on the target, then select the database name. If however, you have made changes in the database project that you want to compare with the database, then select your project path.

Target: It will always be with the source which will be compared. If you want, you can swap Source and Target. Once selected, click on ‘Compare’ to compare Source and Target schemas.

8. For example, I have added the Enquiry table in the database; Clicking on Compare allows you to see a list of modifications.

Database demo 8

The Action column in the above image is displayed if the changes are new or if there are any updates or deletions in the schema.

When you check the box and click on ‘Update’, all the changes will be applied to Target.

9. Now if you have made any changes in the database project and you want to execute those on the Production UAT database, then execute it in the same way as shown above.

10. In order to create the scripts for the changes, then compare the Database project with the Target Database. Then click on ‘Generate Scripts’.

Merits and Demerits of Database Project:

Merits:

  1. DDL (Data Definition Language) is readily available for all the objects.
  2. Deployment scripts can be easily generated.
  3. A database project also includes designers for working with these files.
  4. Version control can be done for the database through a series of check-in and check-outs for various operations performed on scripts and schema changes.
  5. Reviewing database changes is easier and allows  Dot Net developers to be more effective in terms of making changes and reviewing previous modifications done by team members.

Demerits:

  1. Schema designing is not available.
  2. References to external database sources cannot be created in the database project.
  3. The Visual studio database project only supports the SQL Server.

Device and Browser Testing Strategies

Testing without proper planning can cause major problems for an app release, as it can result in compromised software quality and an increase in total cost. Defining and following a suitable and thorough testing procedure is a very important part of the development process that should be considered from the very beginning. Time should be specifically allocated to the manual testing on devices and browsers, as this is a low cost strategy to significantly improve the quality of the app release.  In this article, I will share some of the strategies we follow at MetaSys for real device and browser testing.

There are four points that we consider when defining our testing strategy.

  1. The first point is determining which devices and browsers will be used for testing. This is entirely dependent on the project requirements, and the development team analyses the application use cases to make the selection based on the following principles:
  • For web applications, we usually test on the three most commonly used browsers (Chrome, Firefox and Safari). If time allows for more extensive testing, we will also test on other browsers like Internet Explorer and Microsoft Edge.
  • For Device testing of web applications, we choose the devices based on the functional requirements and priorities of the applications. In other words, if a web application is supposed to run especially well on any particular device we focus the testing on the corresponding commonly used browsers with the appropriate resolution. For instance, for the Android platform we focus on Chrome and Firefox, whereas for the iOS platform we focus on Safari and Chrome.
  • For Native applications we directly test the application on the devices themselves, rather than using an emulator. This provides the most accurate feedback in terms of functionality and application performance.
  1. There are instances where the project timeline and/or budget limits the amount of testing that we can do. It is very important to identify these situations, and to develop strategies in order to still deliver high quality software to the client. At MetaSys we handle these cases by focusing on high level general testing, which covers most of the UI and the functional part of the applications.
  2. For functional testing of web applications, we utilise automation as much as possible. For repetitive testing of browsers, we usually design automated test cases. Using automation not only helps save the time of the testers, it is also very useful for retesting resolved issues. We use the Selenium WebDriver tool for automation testing and the Microsoft Team Foundation Server 2019 and the Microsoft Test Management tools for bug reporting and test case management.
  3. For web applications, we put a strong emphasis on performance, in addition to the ‘look and feel’., The speed of the app is one of the most important factors that determines the user experience. For performance testing we use the Apache JMeter and New Relic tools which give very accurate results regarding the application performance. The New Relic tool also provides an analysis of database query level problems, and gives many more reports and real time graphs. This helps significantly with troubleshooting, and improving performance.

At MetaSys, We have a team of experienced Dot Net developers who build solutions using Microsoft technologies. We have done web application development using ASP.Net Core, .Net & ASP.Net Framework, Visual Studio, Microsoft SQL Server, MVC, Team Foundation Server, Javascript and JQuery. For more info. https://www.metasyssoftware.com/dot-net

How MetaSys handled performance Issues related to Entity Framework

In building web applications for clients, two important factors we at MetaSys focus on are performance, and speed of development. Good performance is crucial for the success of any web application, as users expect pages and screens to load instantly. Users will quickly stop using slow programs in favour of other web or mobile applications. Development speed is also important, as clients currently expect rapid application development.

We have experienced difficulties in both these areas using the Entity Framework, and in this article, I will be describing the cause of the issues, and the solutions that we at MetaSys came up with. If you are having performance issues with Entity Framework, this article might provide some useful insight and suggestions.

Our issues with the  Entity Framework:

Let me quickly brief you about how we started using Entity Framework. We started application development of a .NET web application roughly 10 years ago. At the time, Entity Framework was a new concept introduced by Microsoft. Its purpose was to allow the developer to more easily write SQL queries including calculations, it also simplified CRUD operations and handled results into objects. We used Entity Framework for our web application, and during initial testing, everything was working very well.

The performance issues arose after the client started using the application, particularly as the amount of data in the database started growing. We used the Ants Profiler tool to identify the root cause of poor performance. It showed us that stored procedures were executed fast without any significant delay, but with the Entity Framework code, it was taking a long time to render data on a page.

Another issue was that the SQL database for the application had more than 300 tables. Updating the Entity model with a change in any of the tables would take a very long time. It was also difficult to merge changes of only one developer, or only one module, as it would update the entire Entity model. This made it a challenge to release the application module-wise.

MetaSys Approach :

To overcome performance issues, we first tried to change some of the settings of EDMX, and secondly updated the Entity Framework to the latest version. Neither made much difference to the performance. In the meanwhile, the applications database size and complexity kept on growing, as the application grew.

Eventually, we replaced the Entity with ADO.NET, and we immediately saw a significant improvement in performance. The difficulty we faced with the conversion was how to handle the ADO.NET result into objects. We resolved this using the open-source Dapper ORM. Dapper is a framework for mapping relational object models. Like Entity Framework, It eases the handling of data in objects and supports multiple data query results. This solution not only improved the page loading time, but as there was no need to update the entity model, the developer’s time also reduced significantly.

So far we have found that using ADO.Net with Dapper ORM solved all the problems we experienced with the Entity Framework.

About Us:

Our team of Dot Net developers have experience of building Dot Net solutions using Microsoft technologies for more than two decades using VB to latest .Net Core applications. For more info. https://www.metasyssoftware.com/dot-net

 

 

InBody Integration for biometric and blood pressure data into a web application

Inbody integration with web appPeople today are more health-conscious than ever before, and digital technology is playing an important role in this development. Thanks to modern technology, there are many tools and devices to measure and record physical characteristics that relate to personal health. Tracking exercise routines and nutrition has become a popular tool for individuals to keep up a healthy lifestyle. Many health-related online platforms, applications and tools are available for individuals, and integration of such tracking devices can improve their service. 

This article details such an integration project. Our Dot Net application team helped a client integrate InBody technology into their application. 

InBody devices offer detailed measurement of body composition balance, including key health factors such as protein, minerals, BMI and body fat percentage. Our project revolved around integrating the InBody 570 device with a .Net application. The solution we implemented involved the .Net application listening to the serial ports attached to the device for the duration of the test. After receiving a data stream from the InBody device, relevant data is extracted using indices of specific data factors mentioned in the InBody 570 technical documentation. Key settings required by the .Net application include baud rate, parity, data bits, and stop bits. The InBody technical specification includes more details about the values of these settings.  Once the data stream is received, and the data factors are extracted, the data was saved in an SQL server database. The .Net web application reads this data from the SQL server database and displays it to the user. 

Key Challenges

The idea is that the application can be used by the fitness facility subscribing to the application and having an InBody machine. The application runs on a laptop or desktop computer connected to the Inbody machine. However, one challenge was that there is no guarantee that the computer is always connected using the same port. If the computer had more than one port on which the device stream can be received, then we had to lock and keep listening to all available ports. Our solution to these issues was to program the app to lock and keep listening to all available and open ports of the laptop and as soon as we receive either data stream or exception, we unlock all ports locked by the .net application. It is important to note that only that process which has locked the serial port can unlock it, no other process can forcibly remove that lock. In the event that the process which locked the port gets terminated, one is left with only the option of system reboot in order to unlock that port.

Another issue is that the same app was required to integrate with another measurement device manufactured by InBody, the BPBIO 320. This device reads systolic blood pressure, diastolic blood pressure and pulse, and we modified the .Net app to work with both InBody devices. We used a condition-based code, which looks at application settings and accordingly saves the data in either the production or the staging environment. The BPBIO 320 requires a different baud rate to that of InBody 570, therefore we adapted the application so that the user enters the test type, and then the baud rate is automatically set up by the software. We also process information contained within the data stream that indicates status such as “Measurement started”, “Measurement interrupted due to use of the stop button”, and “Measurement interrupted due to error”.  These cases are handled in the application and clear information is passed on to the user.  

We used a PuTTY tool to simulate the device stream in the development environment, as the physical device was not always available. Device testing was performed after development was complete, and we made the necessary changes to the application before the launch.

We will be glad to help anyone interested in doing similar kinds of integrations in their software. For more info https://www.metasyssoftware.com/case-study-dotnet

Using the NReco pdf writing tool

These days financial, marketing and e-commerce websites allow us to download reports and receipts in pdf form. The Pdf file format is a convenient way of sharing information, as there is a high level of confidence that the user can open the document with the intended look and feel. This is even true for documents containing charts, images and text-based on dynamic data. There are many pdf writing tools available online, of which two commonly used ones are wkhtmltopdf and NReco. This blog article details the recent switch we made from wkhtmltopdf to NReco, and the numerous benefits of the switch.

Our experience with wkhtmltopdf

In the past, we generally used wkhtmltopdf to implement pdf functionality in our web applications. It was a practical choice, as it is an open-source tool with which we have extensive development experience already. The converter tool is given a destination file path and a URL of the report web page. Since the download button is contained within the generated report in web page form, the pdf conversion adds an unnecessary report generation step. To avoid this inefficiency, we wanted to explore different pdf converter options.

Our experience with NReco

We came across a library in a NuGet package called .Net Reusable Components (NReco), which contains a collection of reusable components for the .NET platform including a pdf conversion tool. The only input the tool requires is either a URL to the web page or the report contents as an HTML string. NReco is easier to implement, requiring only two to three lines of code. Even reports containing charts and images created using a third-party tool can be rendered to a pdf without additional coding. All CSS, fonts and images in HTML are supported by the NReco conversion tool.

The NReco tool is easy to install, and performs efficiently, taking much less time than wkhtmltopdf to generate a pdf. Although we currently only use NReco for pdf conversion, many other tools are available.

A major advantage of NReco, is that it supports both the .Net framework and .Net Core. Since we are looking to upgrade a number of our applications to .Net Core, it saves us considerable development time if we can use the existing code for pdf conversion.

To conclude, using NReco instead of wkhtmltopdf for pdf conversion has many benefits including easy implementation, performance, and compatibility with .Net Core.

About us

Our team of .Net developers have successfully delivered applications using ASP.Net Core, .Net & ASP.Net framework, Visual Studio, Microsoft SQL Server, Team Foundation Server, Javascript and JQuery. For more info – https://www.metasyssoftware.com/dot-net