How to create/edit a database table

Create a new SQL script

Navigate to the DatabaseUtil project, which is included in the development pack; go to Core Ø DataLayer Ø DatabaseUtil. The DatabaseUtil contains a number of different update scripts, but we are interested in the scripts located in SQLScripts Ø Update Database Ø LS One <latest version> where the <latest version> should be the folder for the latest release.

Expand the newest folder and locate the highest numbered script. For an example for version 2019 it will be 00895-00.

To trigger a database update through the DatabaseUtil our script will need to have a higher number than the current highest script. In this case we should increment the partner version, which gives use the script number 00895-01.

We are now ready to add our new script. Right-click on the folder and select Add > New item. When the new item dialog appears, select a file type of Code Ø Code File name your file 00659-01.sql and press the Add button.

Now select your newly added file and look at the properties for the file. By default the Build Action property will be set to Content, but in order for the DatabaseUtil to run your script it is very important that you change the Build Action property to Embedded Resource. Make this change now if it is set to Content, the properties should look like this:

You have now successfully added a new script to the DatabaseUtil project. Next we will see a script that adds tables to the database.

SQL script - Create a new table

The following code adds the Person table to your database.

First you need to write this at the top of your SQL script:


Use LSPOSNET
GO

This is a macro that ensures that the name of your database is used when the script is run.

Before running the Create table code we always do a check to see if the table exists in the database, if it does not we can safely create the table. This is done because every script you create should be re-runnable.

Now if you build and run the Site Manager, you will see the update database dialog:

The tables will now have been added and the database version incremented to 00895-01 in the POSISINFO table.

Use LSPOSNET
GO
							
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES where 
TABLE_NAME = 'PERSON')
BEGIN
	Create table PERSON
	(
		ID uniqueidentifier NOT NULL,
		ALIAS nvarchar(250) NOT NULL,
		GENDER int NOT NULL,
		AGE int NULL,
		PHOTO VarBinary NULL,
		DATAAREAID nvarchar(4) NOT NULL
	)

Alter table PERSON Add CONSTRAINT PK_PERSON
Primary Key (ID,DATAAREAID)
END

SQL script - Editing a table

The following script adds a column NewColumn to the table YourTableName.

First you need to write this at the top of your SQL script:

Use LSPOSNET
GO

This is a macro that ensures that the name of your database is used when the script is run.

This script first checks if the column NewColumn exists for the table YourTableName and if it does not, it adds the column to the table. This kind of check is typical for editing a table.

Use LSPOSNET
GO
							
IF NOT EXISTS
(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME 
= 'YOURTABLENAME' AND COLUMN_NAME = 'NEWCOLUMN')
BEGIN
	ALTER TABLE YOURTABLENAME ADD NEWCOLUMN nvarchar(20) NULL
END

And then you have to be able to access the new data: How to access new data