Storing data from a plc program is very crucial for almost every system. You may want to store some sensor readings, events, part data etc... In this tutorial, I will explain how to setup MS SQL Express Database Server and connect to it in TwinCAT 3. 

You can download the MS SQL Express from here. We will also use the SQL Server Management Studio (SSMS) to configure a table and create a user for the database. 

After installing these, start the SQL Server Management Studio program. If you can't see the program icon on your desktop, you can create a shortcut from this file path. "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe"

Connect to the server. 

Right click on the Database folder and select 'New Database'. 

We will be logging events from TwinCAT 3 in this tutorial. Name the database as 'DB_Events' and click OK. 

Expand the DB_Events and right click on the Table folder and select New, then 'Table'. 

Add two columns as EventName (nvarchar(Max)) and EventID (int). 

Click Save (CTRL+S) on Server Management Studio and name the table as plc_events_table, then click OK. 

If you don't see the newly created table, right click on the Tables folder and select 'Refresh'. 

Right click on the dbo.plc_evetns_table and select 'Edit Top 200 Rows'. We will add some events in the table and read those events later in TwinCAT 3. 

Add the example events below and click Save. 

Now we will configure the login information. Expand 'Security' and right click 'Logins' folder and choose 'New Login'. 

On the general section, set the login name and password as 'user'. 

Go to Server Roles tab, check the roles below. 

On the User Mapping section, click on the ... and browse. Select the db_accessadmin object and click OK. Then click OK on the login properties window as well. 

Now we will add a user for the database, which will use the login information we just created. Under DB_Events, expand the Security folder and right click on the Users window and select New User. 

On the General section, select 'SQL user with login' option and set the user name as user and select the login name and default schema from the options, then click OK. 

Now we will change the server authentication mode. Right click on the server and select properties. 

Go to Security tab, and select the 'SQL Server and Windows Authentication mode' then click OK. 

We would need to restart the SQL Server for this change to take effect. Close the server management program and open Services in Windows. Find the SQL Server and right click, select Restart. 

Install the TF6420 TwinCAT 3 Database Server. Then go to this path C:\TwinCAT\Functions\TF6420-Database-Server\Win32\Configurator and run the TF6420 Configurator. 

Activate the trial TF6420 License in TwinCAT 3 before using the TF6420 Configurator.

Start the TF6420 Configurator. Select the target IPC. Right click on the TwinCAT Database Server Project and select New DB Connection. 

Now open the server management studio program again to look up the information we need for the database connection. Right click on the server and go to properties and select the general tab. Copy the server name. Database name is DB_events and user login information is user,user for username and password. 

Select the MS SQL Server from the Database Type dropdown menu and enter the information we just acquired below. Activate the configuration (number 3) , then press CHECK (number 4) to check the database connection. 

You should receive the connection successful message. 

Click on the SQL Query Editor button. 

Refresh the databases. 

Right click on the 'plc_events_table' and click 'Select' . 

Press the 'Create Query' button (number 1), then press FB_SQLCommand button, you will see the data in the plc_events_table on the result window. As we can see, there are currently 6 events in the table. 

Now we will add one more event to the table. Right click on the plc_events_table and select 'Insert'. 

Press the 'Create Query' button (number 1), type the new event information (number 2), then press FB_SQLCommand button (number 3). You will see the execute succeeded message in the messages (number 4). 

Now we will export the plc_events_table as struct and import it in TwinCAT 3. Press the export tablestruct button. 

Name the structure as ST_EventStruct and click OK.

Save the file. 

Create a TwinCAT 3 project and right click on the DUTs folder and select Import PLCopenXML. 

Confirm the import DUT. 

On the database configurator window, right click on the plc_events window and select 'Insert'. Press the 'Copy query PLC in syntax'. 

'INSERT INTO [dbo].[plc_events_table] ([EventName], [EventID]) VALUES ($'$', 0) '

Do the same thing for the SELECT query command. 

'SELECT [EventName], [EventID] FROM [dbo].[plc_events_table] '

These are the commands we will use in TwinCAT 3 to run the insert and select queries.

Add the code below in your MAIN program. 

PROGRAM MAIN
VAR	
	sEventName : T_MAXSTRING;
	nEventID   : DINT;

	fbDBRecordArraySelect  : FB_DBRecordArraySelect;
	fbDBRecordInset	        : FB_DBRecordInsert_EX;
	sCmdSelect 		        : T_MaxString;
	sCmdInsert		        : T_MaxString;
	arrEventData		  : ARRAY[1..10] OF ST_EventStruct;
	bExecuteSelect          : BOOL;
	bExecuteInsert		  : BOOL;
	bInsertBusy		  : BOOL;
	bInsertError		  : BOOL;
	bInsertErrID		  : UDINT;
	bSelectBusy	          : BOOL;
	bSelectError		  : BOOL;
	bSelectErrID		  : UDINT;
	nRecords			  : UDINT;	
END_VAR

sEventName := 'High Pressure Detected';
nEventID   := 8;

sCmdInsert := CONCAT('INSERT INTO [dbo].[plc_events_table] ([EventName], [EventID]) VALUES ($'',sEventName);
sCmdInsert := CONCAT(sCmdInsert,'$', ');
sCmdInsert := CONCAT(sCmdInsert,DINT_TO_STRING(nEventID));
sCmdInsert := CONCAT(sCmdInsert,')');

//sCmdInsert := 'INSERT INTO [dbo].[plc_events_table] ([EventName], [EventID]) VALUES ($'', )';
sCmdSelect := 'SELECT [EventName], [EventID] FROM [dbo].[plc_events_table] ';

fbDBRecordInset(
	sNetID:= '', //String containing the AMS network ID of the target device, at which the ADS command is directed.
	hDBID:= 1, // Indicates the ID of the database to be used.
	pCmdAddr:= ADR(sCmdInsert), //Pointer to the executing INSERT command
	cbCmdSize:= SIZEOF(sCmdInsert), //Indicates the length of the INSERT command.
	bExecute:= bExecuteInsert, 
	tTimeout:= T#5S, 
	bBusy=> bInsertBusy, 
	bError=> bInsertError, 
	nErrID=> bInsertErrID);
	
	
fbDBRecordArraySelect(
	sNetID:= '', 
	hDBID:= 1, // Indicates the ID of the database to be used.
	pCmdAddr:= ADR(sCmdSelect) , //Indicates the pointer address of a string variable with the SQL command to be executed.
	cbCmdSize:= SIZEOF(sCmdSelect), //Indicates the length of a SELECT command to be executed.
	nStartIndex:= 0, //Indicates the index of the first record to be read.
	nRecordCount:= 10, //Indicates the number of records to be read.
	pDestAddr:= ADR(arrEventData), //Indicates the address of the structure array into which the records are to be written.
	cbRecordArraySize:= SIZEOF(arrEventData), // Indicates the size of the structure array in bytes.
	bExecute:= bExecuteSelect, 
	tTimeout:= T#5S, 
	bBusy=> bSelectBusy, 
	bError=> bSelectError, 
	nErrID=> bSelectErrID, 
	nRecords=> nRecords);
	

Rebuild your solution and activate the configuration and log in the plc. Set 'bExecuteInsert' TRUE to insert a new event into the database. You can use the sEventName and nEventID variables to set your event information. 

Set 'bExecuteSelect' TRUE to run the select query. Check the arrEventData array to see the events in the database. We can see that the new event (High Pressure Detected) also logged in the database. 

 You can create different tables in the database and add more columns to your new table and import your new tablestruct in TwinCAT 3 to practice and have a better understanding. Also don't forget to copy your new insert/select query commands for your new table.