Creating an SQL-Database with user GUI the fastest way on AZURE

Working in IT you might know the following sentences, could you please create a “small” Database for me? Oh and please I don’t like those SQL queries so can I have it with a GUI, please? Many bad IT days start with these sentences.

But don’t worry, I have found an easy way to create a database with a simple and stable GUI. I will show you the zen way of database creation and a way to make your user happy.

Prerequisites

  • Azure Account (SQL-Database Plattform)
  • MS SQL Management Studio
  • Internet Information Services (IIS)
  • GAPTEQ (Solution for an Easy GUI)

If you do not have one of the prerequisites, don’t worry, all sources are accessible for free (AZURE 30Days). If you are just interested to a Part of the Tutorial, it is Easily possible to take the Part you need, or just left the GUI.

Table of Content

Create an Azure SQL Database

First of all, we need an Azure account. You can create a free test account here. Most of the time you will get a 30-day trial with a $200 test volume.
Once you have created the account, you can log in to the Azure Dashboard in the upper right corner. There you will find a button called SQL Databases, press it, and then click Create SQL Database. Now we are in the first dialog, where we actually need to do something, you need to create a resource group and select a server.

Azure Dialog for Creating SQl Database

Now we get to the networking part, please select Public Endpoint as a connection method and hit “Yes” to both firewall rules.

Azure Dialog for Editing Network Setting for the SQL Database

In the next steps, you can simply approve by clicking YES and then create the database.
If you go back to the SQL database menu, you should see your SQL database, clicking on it will take you to the dashboard. In the dashboard, you will find the FQND of the server, which will be important for the next steps.

Azure SQL-Database Dashboard

Creating Tables in the Database

Now we have a running SQL database, but we need to add some tables to it. For this installation, the Microsoft SQL Server Management Studio can be downloaded here. After the installation, start it and connect it to the Azure SQL database. Remember to change the authentication to SQL Server authentication. All the information you need, you have configured in the steps before in Azure.

MS SQL Management Studio Connection Menue

Now we will create a simple application creating a reference between an article and an owner. This example is not here because it is perfect, it is here to give you an idea and some content to play around with.

The easiest way to create this structure is to create a New atabase Diagram.

Overview SQL Management Studio

Now you get an empty space where you can do almost anything. Click right, click New Table, and then create the following three tables.

Visualisation of the Created Tabels

Now right-click on the 1st entry in each database and make them primary keys with the Set primary keys button. Now a small key appears. To connect the databases to each other, click on the field Customer_No. in the customer table, keep the mouse button pressed, hover to Customer_Article and release the mouse button. Do the same for Article_No.

Visualisation of The Created Tabels with Primary Key and Links

Now we have a SQL database with the prepared databases, but what about the GUI for filling in the embedded data?


I don’t want to type the numbers of the primary key by hand (Belongings_No…) So head over to SQL Management Studio, find the table, right-click on it and press the design button. Now go to the primary key value and set the identity specification to Yes.

Settings in SQL on the Prmary Key to Auto Increment

Getting GAPTEQ in Place

If you do not already have an ISS Server installed, you can install it in the Windows Feature Dialog (Internet Information Services).

Windows Features Install / Uninstall Dialog

First, you have to go this site and register yourself to get access to the free version of GAPTEQ. When you are registered please download and install all three software parts.

Overview of GAPTEQ Software to Donwload

First you have to create a repository, just follow the wizard. After the creation open it 😉

The first step will be to create a new folder structure, then you need to create a main folder with the function name and the folders DB_Grids and Pages .

GAPTEQ Folder Structure

Now we have to setup our DB connection, click on the view button on top, followed by the connections button. Next click the Add Database Button and connect to your Azure Database.

GAPTEQ Steps to  Create Database Connection

Now change back to Design objects and create a new page in the pages folder.

GAPTEQ Steps to Add A Page

Drag and drop the three headings onto the blank page, each followed by a data grid. When you click on the object, you can change the object’s properties in the pop-up menu.

GAPTEQ View you should get with Headers and Data Grids

You can save the page by pressing Ctrl + S. Place it in the Pages folder below the name Lading_Page. The question whether this should be your start page should be answered with YES.

Now click the New button again, but this time you select Data Layer as your table. Then select the item table from your database. Do this for all three databases.

Go to your landing page again and move the created database object to the Grid, Article to Article , Customer to Customer and Article_Customer to Customer_Belonings. We need all values.

GAPTEQ Setps to add Data to a Data Grid

Enable Inserting, Deleting and Editing in each grid by selecting the option on the right side in the grid properties. Save the landing page and press the green play button in the top center selection on the screen.

Now login to the homepage with the repository credentials. And now you should see your newly created GUI.

You can add values now by clicking the + button. The values will be written directly to the SQL DB.

GAPTEQ Homepage Overwie

Double click on Article row in the Customer_Belonings grid. Hit The Data Layer -> Private Data Layer from table and then chose the Article DB. Now add the key column and display values.

GAPTEQ Settings for Looking up data in a Data Grid

When you start the oage over you will now see the values linked across the tables… 😉

GAPTEQ Homepage Overwie with Looked up values

The tool is very powerful and easy to handle. With a little bit of playing and exploring you can create nice web applications.

One thought on “Creating an SQL-Database with user GUI the fastest way on AZURE

Add yours

Leave a Reply

Powered by WordPress.com.

Up ↑

%d bloggers like this: