logo

parallax-small

ASP.NET, C#, HTML, CSS, IIS Server, SQL Server, Server 2012 Scary??? No it’s not! Let’s automate!

zondag, 07 juli 2019

Door Tim Nissen

For a consumer at my current employer we are struggling how we can handle requests from the business. These are, at the time of writing this article being handled by using an Excel sheet. When I started working for this company, I thought as an IT professional: “This can be done differently”.
In this article I try to describe the steps I have taken to set up a MVP (Minimum Viable Product) from scratch using ASP.NET, IIS8 and Microsoft SQL Server 2012 (SP4-GDR). If you wonder, why we are all using Microsoft Products? Let’s say I am working in a Microsoft based section of the company.

My goal to write this blog, is not to show you how you can code or how you should develop your weblication :). The goal is to guide you through the steps I’ve taken to reach my goal and review the process towards it.
The result of the application development can be seen in the image below. As this project is still under development it will grow as time and code-passes by.

blog Tim 1

IIS Server

While you can make an IIS server installation as complex as you want (failover clustering/docker containers), I have followed the steps below to install the required components, of course, you can install this in a configuration you like. Please note that in many environments you want to install Web Deploy by using the Web Platform Installer (Web PI) since it can be used to update web deploy, but since our servers lack the ability to communicate to the web it cannot be done. I’ve installed WebDeploy using WebDeploy_amd64_en-US and since we’re running server 2012 which doesn’t support DotNet 4.7.2 natively I have installed it using: NDP472-KB4054530-x86-x64-AllOS-ENU.

Installation

Install the following components on your IIS sever:
DotNet 4.7.2 - NDP472-KB4054530-x86-x64-AllOS-ENU
WebDeploy - WebDeploy_amd64_en-US
Install Features (using powershell):
Install-WindowsFeature Web-Server
Install-WindowsFeature Web-WebServer
Install-WindowsFeature Web-Common-Http
Install-WindowsFeature Web-Default-Doc
Install-WindowsFeature Web-Dir-Browsing
Install-WindowsFeature Web-Http-Errors
Install-WindowsFeature Web-Static-Content
Install-WindowsFeature Web-Http-Redirect
Install-WindowsFeature Web-Health
Install-WindowsFeature Web-Http-Logging
Install-WindowsFeature Web-Performance
Install-WindowsFeature Web-Stat-Compression
Install-WindowsFeature Web-Security
Install-WindowsFeature Web-Filtering
Install-WindowsFeature Web-Windows-Auth
Install-WindowsFeature Web-App-Dev
Install-WindowsFeature Web-Net-Ext45
Install-WindowsFeature Web-Asp-Net45
Install-WindowsFeature Web-ISAPI-Ext
Install-WindowsFeature Web-ISAPI-Filter
Install-WindowsFeature Web-Mgmt-Tools
Install-WindowsFeature Web-Mgmt-Console
Install-WindowsFeature Web-Mgmt-Service
Install-WindowsFeature NET-Framework-Features
Install-WindowsFeature NET-Framework-Core
Install-WindowsFeature NET-Framework-45-Features
Install-WindowsFeature NET-Framework-45-Core
Install-WindowsFeature NET-Framework-45-ASPNET

After you installed the these components, I would suggest a reboot.

Configuration

Configure IIS for web deployment using official Microsoft documentation: https://docs.microsoft.com/en-us/iis/install/installing-publishing-technologies/installing-and-configuring-web-deploy-on-iis-80-or-later#configuring-a-site-for-delegated-non-administrator-deployment
In the section Publish to IIS of this document I describe the steps to be taken to publish the application to the external IIS server.

SQL Server

Architecture

Design

While I do not claim to be a Databases Engineer (in fact this is my first database on MSSQL) the database might be the most important part of your application since it is handling the dataflow and logins in the application. Therefore, it is important to think which data you want to store and which relations you need to create. It’s easy to change the design of your database while creating your front-end but it’s more difficult to migrate the existing data when the server is in production.
First, start by creating an ERD (Entity-relationshipmodel), how to accomplish this is widely documented on the web and will not be covered in this Blog.

design blog Tim 2

design blog Tim3

When you’re satisfied with your ERD it’s a great idea to create your tables, in my case I first created a Microsoft Access database for test purposes.

When your Microsoft Access database is working correctly it’s time to build your database in Microsoft SQL Server, if your database is not working correctly it’s easier to make adjustments in Access than later on in SQL. In my case I created additional tables to store more information. I noticed while developing and reviewing the portal I sometimes missed some fields after which I need to delete and re-create the table in question. This might break functions in your application or stored procedures and/or result in data loss if you’re not properly migrating existing data.

blog Tim 4

Development

Tables

You can create tables in SQL Server Management Studio the same way as in Microsoft Access using the Designer:

blog Tim 5

This will create the tables where you can later find the option to export the tables to a SQL script which is the more advanced way to create tables. I have first created the tables and relations using the designer and modified my database later on using the SQL scripts since the designer often doesn’t allow modifications.

Stored Procedures

After implementing the tables in SQL I found it helpful to create Stored Procedures to handle Read and Write requests to and from the SQL Server. This will be the only way data is being inserted to the server since this method is less vulnerable for SQL Injection and it’s much easier to manage permissions on the procedures which results in a safer application. After creating and testing the stored procedure using SQL Server Management Studio don’t forget to assign execute permissions to the service/machine account you’re using to run IIS on.

blog Tim 6

Code (Front and Back-End)

Visual Studio

If you’re reading this blog you most likely have not very much experience with Visual Studio, so I will explain a little bit about it. First off all, it’s free for non-commercial purposes and can be downloaded here: https://visualstudio.microsoft.com/vs/.
If, however you’re planning/using this to develop for your company a license is required.

So what is Visual Studio? According to Wikipedia:

Wiki Visual Studio

While that might raise more questions than it answers I have highlighted the important parts:
For a consumer at my current employer we are working with Microsoft Products, Visual Studio has great integration to publish to IIS and code/version management to GIT or TFS (Team Federation Server). It also allowed developers to choose the language of the application. While Windows applications are widely written in C# (pronounced “see sharp”) web applications (back-end) are using a quite similar approach, of course styling (front-end) is a little different with CSS and HTML/ASP but when you are able to develop windows code like C# or Powershell or even Linux (python), it should not be very difficult to write ASP.NET web application code as well. Whenever I’m stuck there are tons of forums where to look for an answer.

Writing code

While you’re probably reading this article to learn about coding this isn’t the place to learn it at all. the real place to learn coding is behind your desk, starting Visual Studio and start a new project. Take a look around and view all pages like Default, Contact and About. In fact, the only tutorial I watched to get started with ASP code was Step-by-step ASP.NET MVC Tutorial for Beginners | Mosh. Just make sure you know the basic understandings like variables, loop, if/else statements etc.
Remember that the start off something new is difficult and consumes time, things will not work the first time or the second, but when you’re dedicated to get it work it will eventually. This is a process called learning.
I will describe a few important functions in the application below with comments for readability in green:

Connection to SQL Server

// Connection string
SqlConnection con =
new SqlConnection("Server=SERVERNAME; database=DATABASENAME; Integrated Security=SSPI");
// Create new command with name cmd
SqlCommand cmd =
new SqlCommand();
// Add properties to command
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText =
"Server_Check";
cmd.Parameters.Add(
"@SQLParameter", SqlDbType.VarChar).Value = "Value";
// “Attach” command to connection
cmd.Connection = con;

try

{

         // Open connection

         con.Open();

// Execute stored procedure on MS SQL Server, depending on requirements use ExecuteReader();

         Count = (int) cmd.ExecuteScalar();

}

// Execute when try block fails

catch (Exception ex)

{

         throw ex;

}

// Execute after try and/or catch

finally

{

         con.Close();

}

Explanation:

When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication. Recognized values are true, false, yes, no and sspi (stronly recommened), which is equivalent to true.

Connection to Active Directory


In order to read information from Active Directory I use LDAP Query’s in my code:

        // Name of function, it returns a bool which means true or false and when calling the function you need to specify a string (text) variale with it like:

        // CheckEmail("Dit e-mailadres wordt beveiligd tegen spambots. JavaScript dient ingeschakeld te zijn om het te bekijken.");

        public bool CheckEmail(string Mail)

        {

            // Create variable searcher

            var searcher = new DirectorySearcher("LDAP://" + Mail.Split('\\').First().ToLower())

            {

                // Define LDAP query here

                Filter = "(&(objectCategory=user)(objectClass=user)(mail="+Mail+"))"

            };

            // Try to search for 1 result

            try

            {

                var result = searcher.FindOne();

                // if result is not found

                if (result == null)

                {

                    // No mailbox found so return false

                    return false;

                }

                // if result is found

                else

                {

                    try

                    {

                        // mailbox found so return true

                        return true;

                    }

                    catch (Exception)

                    {

                        // Something went wrong, normally you trigger trow exeption but in this case we return mailbox not found by false

                        return false;

                    }

                }

            }

            catch (Exception)

            {

                // Something went wrong, normally you trigger trow exeption but in this case we return mailbox not found by false

                return false;

            }

        }

Web.config

In this file global information about the application will be stored like which components are used and how to handle authentication when launching the application in IIS. Because these reasons you want to make sure that the line: <authentication mode="Windows"/> is present. This will allow the application to pass your currently logged in Windows credentials to be used when authenticating the application. Not sure how it behaves on MacOS since I have none.


Publish to IIS


If you have every followed every step in my blog it should now be possible to publish your application from within Visual Studio using Web Deploy which will save you a lot of time compared to FTP (unsafe) or Web Deploy Package. You can find detailed information on how to set-up Web Deploy client-side in the URL below:
https://docs.microsoft.com/en-us/visualstudio/deployment/quickstart-deploy-to-a-web-site?view=vs-2019

More information?

If you would like to know more about this subject, feel free to contact me via Dit e-mailadres wordt beveiligd tegen spambots. JavaScript dient ingeschakeld te zijn om het te bekijken.

Referenties

  • KPN logo bw   HVA Amsterdam university of applied sciences bw   logo rug
  • mammoet logo bw   kennemer   moeller group logo bw
Cookies make it easier for us to provide you with our services. With the usage of our services you permit us to use cookies.
More information Ok