Hosting ASP.NET Web Data App with Microsoft SQL Server on Alibaba Cloud

By Amit Maity, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud’s incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

In this article, we will learn how to create a Windows-based Elastic Compute Service (ECS) instance to run .NET web apps with SQL Server.

Architecture Overview

Below is the overall architecture of this sample solution. ASP.NET will be deployed into IIS server running in Windows Server 2008 with its own VPC network. A Microsoft SQL Server instance will be created in separate VPC network using ApsaraDB for RDS product. However, MS SQL Server will be exposed to public internet by applying internet address in Alibaba Cloud. This will allow ASP.NET application to access MS SQL server over public internet. ASP.NET web application will be accessed in Windows server 2008 machine by remotely logging into the server.

Image for post
Image for post

Prerequisites

To follow this tutorial, you need to have an Alibaba Cloud account. You should also have basic knowledge on the following products or tools:

  1. Alibaba Cloud ApsaraDB RDS for SQL Server
  2. Alibaba Cloud Elastic Compute Service instance
  3. GitHub
  4. IIS
  5. ASP .Net
  6. HeidiSQL tool

ASP.NET Code Walkthrough

You’ll be using one basic .NET application which was written by me for this tutorial. If you are familiar with .NET, you must be knowing that there can be several ways to connect to a database through C#. In this sample application, simple ADO.NET approach has been used. This is a student registration application that consists of 2 web pages and 2 application control file. 1 page to display registration form for entering the details of the student and next page to display the details of the student submitted in the first page.

StudentRegistration.aspx

Student registration web page is designed as “Web Form with Master Page” Visual Studio component and it is a simple student registration form with 6 text fields with 1 submit button as shown below,

Image for post
Image for post

Each text input field is displayed using the asp “TextBox” tag,

The runat=”server” attribute is used for information web server that it is a server control (rather than static HTML)

StudentRegistration.aspx.cs

Once you click “Submit” in the student registration web form, btnSubmit_Click function receives the form’s contents and send them to SQL server database using INSERT statement. SQL Server connection is established is using below connection string,

SQL Server INSERT command is prepared as follows using the values entered into each of the form’s text fields. Here each of the text fields is referenced using the TextBox ID attribute values

Above command is then executed using ExecuteNonQuery function to insert the values into STUDENT table.

Once record is inserted into table, it’ll redirect to another web page record.aspx to display the registration details of the student after selecting from STUDENT table and clear the student registration form.

record.aspx

This code is to display the student registration details in grid view. The ASP.NET page framework supports an automatic way to associate page events and methods. AutoEventWireup attribute of the Page directive is set to true to call page events specifically Page_Init and Page_Load methods in record.aspx.cs code file. This code also inherits record class which is defined in record.aspx.cs file.

GridView control displays data in tabular form. Data source to the GridView is directly assigned using ID attribute value in record.aspx.cs. Data from datasource is rendered in separate columns, in the order it occurs in the data source. By default, the field names appear in the grid’s column headers, and values are rendered in text labels.

record.aspx.cs

When record.aspx page is accessed in browser, Page_Load function is called. This function connects to database, fetch records from STUDENT table and pass the records to record.aspx to display in grid view. This is a two-step process.

First, define new data set and populates with data from student table.

Second, associate the data set with grid view data source and bind the columns.

MS SQL Server Instance and Database

Create MS SQL Server instance using “ApsaraDB for RDS”. Choose DB Engine option as Microsoft SQL Server. After creating instance, status of the instance will be in “Creating”. Please wait until the status changed to “Running”. This may take a few minutes.

Set Whitelist of the instance to allow any remote machine (For this case, windows server) to access this RDS instance. Use CIDR range 0.0.0.0/0 to grant remote access to any IP.

Image for post
Image for post

In connection option, apply for internet address. This will assign public/external hostname to RDS instance so that any machine can access this instance over internet. Use internet address and port number as connection string in web application.

Image for post
Image for post

Create a new database in MS SQL server instance.

Create superuser account and standard db user account for this database. Standard db user account will be used in the application for connecting to database. If standard db user creation option is not available, then close and reopen the SQL server instance configuration page. Also, authorise standard db user account with “Owner” privilege.

Setup firewall rule in ECS, Security Groups to open 3433 sql server port.

Download and install HeidiSQL tool to connect to MS SQL database using host name, port number, database name, db standard user and password.

For example,

Run below command in HeidiSQL tool to create the sample table in sql databse.

Microsoft Windows Server 2008

Create a Windows Server ECS instance. Preferably select minimum 2 GB memory for better performance of windows server. You can set this up yourself, but I have chosen a marketplace image of Windows with ASP.NET stack for easier setup of ASP.NET environment in new window server.

Image for post
Image for post

Once instance is started, open instance by clicking on instance name and click “Connect” button to open VNC terminal for remotely connecting to Windows server.

After logging into windows, download and install following software,

  1. Web Deploy v3.6 [https://www.microsoft.com/en-us/download/details.aspx?id=43717]
  2. Microsoft .NET Framework 4.5 [https://www.microsoft.com/en-in/download/details.aspx?id=30653]
  3. Download sample application from GitHub [https://github.com/itexpertshire/sampleASP.NETapp]

Unzip the downloaded folder. Open the below 2 files from SimpleASP.NetWebAppsPackage folder to modify the connection strings and overwrite both the files after updating the connection string.

  1. record.aspx.cs
  2. StudentRegistration.aspx.cs

Zip the folder SimpleASP.NetWebAppsPackage. Open IIS Manager and navigate to Default Web Site.

Image for post
Image for post

Click Import Application link in Deploy section and import the zipped file prepared in previous step. Finish the application install in IIS manager.

After successful installation, you’ll be able to see new application SimpleWebApps under Default Web Site

Image for post
Image for post

Now open the application in web browser using url as http://localhost/SimpleWebApps/StudentRegistration.aspx

Image for post
Image for post

Enter the student details and click on “Submit”

Once submitted, application will open next page displaying the details entered in previous web page.

Image for post
Image for post

Tips or Common Issues

Here are some of the tips to avoid common issues you may face in completing this tutorial.

  1. During ECS instance creation make sure to select marketplace windows server image. If you select windows server public image, you’ll have to change few security settings in windows server to be able to download and install required software.
  2. Don’t forget to open 3433 port in firewall security rules for MS SQL Server RDS instance.
  3. If you get .Net Framework version error while accessing the application, please verify below settings in IIS manager and select the .Net framework version 4.7.

Reference:https://www.alibabacloud.com/blog/hosting-asp-net-web-data-app-with-microsoft-sql-server-on-alibaba-cloud_594222?spm=a2c41.12345687.0.0

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store