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

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:

Microsoft Visual Studio is an integrated development environment (IDE) from Microsoft. It is used to develop computer programs, as well as websites, web apps, web services and mobile apps. Visual Studio uses Microsoft software development platforms such as Windows API, Windows Forms, Windows Presentation Foundation, Windows Store and Microsoft Silverlight. It can produce both native code and managed code.
Visual Studio includes a code editor supporting IntelliSense (the code completion component) as well as code refactoring. The integrated debugger works both as a source-level debugger and a machine-level debugger. Other built-in tools include a code profiler, forms designer for building GUI applications, web designer, class designer, and database schema designer. It accepts plug-ins that enhance the functionality at almost every level—including adding support for source control systems (like Subversion and Git) and adding new toolsets like editors and visual designers for domain-specific languages or toolsets for other aspects of the software development lifecycle (like the Team Foundation Server client: Team Explorer).
Visual Studio supports 36 different programming languages and allows the code editor and debugger to support (to varying degrees) nearly any programming language, provided a language-specific service exists. Built-in languages include C,[8] C++, C++/CLI, Visual Basic .NET, C#, F#,[9] JavaScript, TypeScript, XML, XSLT, HTML, and CSS. Support for other languages such as Python,[10] Ruby, Node.js, and M among others is available via plug-ins. Java (and J#) were supported in the past.

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 (strongly recommended), 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(“thisisyourtesttext@test.com“);
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: 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 t.nissen@loginconsultants.nl