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.
- 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.
Now we get to the networking part, please select Public Endpoint as a connection method and hit “Yes” to both firewall rules.
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.
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.
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.
Now you get an empty space where you can do almost anything. Click right, click New Table, and then create the following three tables.
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.
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.
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).
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.
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 .
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.
Now change back to Design objects and create a new page in the pages folder.
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.
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.
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.
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.
When you start the oage over you will now see the values linked across the tables… 😉
The tool is very powerful and easy to handle. With a little bit of playing and exploring you can create nice web applications.