Using C# and .Net Client Controls.
Simple Data Grid Batch Scheduler Tutorial: Part 1
The SBS (Simple Batch Scheduler) allows the user to connect with a predefined database storing a record of scheduled batches including Batch ID, Batch Name, Equipment Name, Batch Start Time and Batch End Time. Later tutorials in this series will build on the idea and provide a means for accessing other data grids representing other data sets such as IO tags associated with the named batch equipment and other details about the batch and how it performed.
A feature of Archestra System Platform is the ability to import binary compiled Dynamic Link Libraries (DLL’s) into the galaxy. Once imported the DLL is converted into an Archestra Script Function Library or aaSLIB file. These libraries are then immediately available for use in any of the Archestra Graphic script functions.
The cousin to imported Dynamic Link Libraries is the Windows Custom User Control known in the galaxy as a “Client Control”. The client control allows import of graphical user interfaces designed and implemented in other .Net languages, the most widely used is probably the Data Grid View control, but there are many others and each are available for use in the Archestra galaxy.
This Tutorial is a Step-By-Step walk through illustrating the build, import and implementation of a very simple batch scheduler using the Windows Data Grid View connected to an MSSQL database.
This tutorial demonstrates:
This tutorial is not intended as a comprehensive discussion but rather is designed as a simple explanation and illustration of complex concepts in a way that can be understood and followed. The student is expected to take the results of the tutorial and use it as a foundation to build upon. All source code for this tutorial is available to download. Click the download button at the bottom of this tutorial.
- MSSQL Database and Table creation.
- Creation and Implementation of Windows Data Grid View Custom User Control.
- Creation of Browseable Properties in Windows Custom User Control.
- Import and Implementation of Windows Custom User Control in Archestra Graphics.
STEP 1: Create and Configure the MSSQL Data Base and Data Table.
Archestra System Platform is heavily dependent upon MSSQL each galaxy has an associated Database for management for management of all internal object creation and deployments. Alarm history is also managed using an MSSQL Database. Earlier versions of Wonderware Archestra require creation of the “ALMDB” and configuration of an associated Intouch application to “pump” alarms into the database. Later versions integrate the alarms automatically into the “A2ALMDB” while latest versions encourage storing alarms in the Wonderware Historian.
For purposes of the tutorial we will create a new database named “Tutorial” in the local SQL Server where Archestra is installed. Care must be taken when connecting to MSSQL Server that all firewall and network configurations allow connection to the database. The assumption in this tutorial is that the deployed view app has permission and access to connect to the local SQL Server.
Make sure the SQL Management Studio is installed on your development computer, this program is essential for doing MSSQL database creation and configuration. See the image below:
when you locate the SQL Server Management Studio Icon for your version of MSSQL click on the icon to open the studio. You should see the image below:
My SQL Server contains a number of galaxy databases, the galaxy I'm using for this tutorial is the "ArchestraScripting" database. I have already created a database for this tutorial named "Tutorial" it's the last one in the list of databases in the Object Explorer illustrated above.
To create a new database right-click on the Databases folder right under the SQL server, in my case it's the folder underneath the "CHARLES-PC" icon. When you right-click the folder you will see a dialog with several options, choose the "New Database..." option.
Enter the name of the new database and choose an account with rights to the database as owner then click "OK". In my example I'm going to use the wwUser account. I'll show you how to configure the wwUser account after we finish creating the database.
The new database will look like the Tutorial database in the image on the left below, there will not be any tables in the new database. Our next step is to run the SQL Query script as seen in the right hand view to create a new table named "Batches" with the necessary fields to complete this tutorial.
After executing the SQL query script it may be necessary to disconnect and re-connect to the database to see the new table. Once the new table is created we need to add data to the first record to setup the tutorial. Right-Click the table name and choose the "Edit Top 200 Rows" option. The row edit view appears in the table edit view pane. Next plug a "1" in the BatchID field, "Batch1" in the BatchName field, "Equip1" in the EquipName field, "0:0:0" in the StartTime field, and "0:0:0" in the EndTime field.
Now that we have created the Tutorial database and its "Batches" table it's time to set the wwUser account up for this database. First, browse to and expand the "Security" folder. One of the accounts will be wwUser. Right-click the wwUser account and select "Properties". When the property window appears select "UserMapping" and the "Tutorial" database. Make sure the "dbowner" checkbox is checked.
STEP 2: Create Windows User Control Data Grid View.
This step requires us to use Microsoft Visual Studio to create a C# program that will eventually be imported into the galaxy as a custom client control. The professional versions of Microsoft Visual Studio IDE all have the Windows User Control Library available, but I want this tutorial to be accessible to everyone so I am going to use the free version of the Microsoft Visual Studio IDE. Click here to download it.
Unfortunately, the community and express versions of the IDE don't come with the Windows Control Library loaded so we have to do a bit of gymnastics to make that happen. I have a video explaining exactly how to install the Window User Control Library here. I used the 2015 version but the 2017 version works the same way. Go ahead and download Microsoft Visual Studio IDE Community 2017 version and use the video walk through to get the Windows User Control Library loaded then let's get started with the IDE.
Open the Visual Studio Editor and click File/New/Project as illustrated below. The Dialog on the right will appear. Click "Windows Control Library", change name to "TutorialDataGrid" and click "OK".
When the IDE opens it will look empty, we need to do a couple of thongs before diving in. First, select the "Solution Explorer" tab in the bottom right corner of the IDE. This displays the "UserControl1.cs" item in the upper right. Double-Click the item to show the User Control development area. You should now see the image illustrated below. Congratulations! you just created a new Custom Windows User Control! Of course it doesn't do anything but we will change that now.
Now we are going to personalize the user control a bit, right-click "UserControl.cs" and select "rename" option type "TutorialDataGrid" and click the Enter key.
Now that we have the user control named we can start working on the control itself. I increased the size of the control by clicking and dragging one corner down to the right. This gives us plenty of room to work with.
On the left side of the IDE there is a list of Custom Controls, we need to click, drag and drop the "DataGridView" inside the user control on the IDE work space then center it up leaving a bit of space at the bottom for a few buttons we will add later as shown below. Select the properties tab in the lower right then scroll to the upper right and change the name of the data grid view object to "dgvTable".
Now we can add a list box for status messages named "lstStatus". Drag and drop it from the toolbox on the left into a position below the data grid previously placed in the user control.
Next are the buttons we will use for manipulating the data table, we are going to drag and drop a button from the toolbox and place it between the list box and the data grid evenly spaced for a clean visual presentation. The buttons to add are:
- Update - btnUpdate
- Add Row - btnAddRow
- Delete Row - btnDeleteRow
- Commit - btnCommit
These functions form the basis for our simple batch scheduler. The images below show the completed buttons.
After placing the buttons on the user control there is one more small item to add that will \help tremendously as we do the development of this control. We are going to add a way to track which version we are working on. Keeping up with versions will make life a whole lot better removing doubts whether a particular modification was made to a code set. to that we will drag a label to the upper right corner and rename it "lblVersion". I will the text property to "Version" this isn't necessary since the Text property is will eventually display the actual assembly version information but I will do this to keep it clear in the code and static graphic what the label is there to represent at run time.
Adding code to the project
Now its time to start writing some code. Don't worry If you are unfamiliar with the C# language, just follow along and I will explain what each code example does. I will provide a source code download at the end of this tutorial. Each code snippet is shown using github gists which are downloadable. To access the code behind the control from the Visual Studio IDE right click the user control design view and select "View Code" as shown below.
Selecting "View Code" reveals the *.cs file containing the script controlling behavior of the user control. The first thing you will see when you will see in anew project is the base auto generated code, see the code below.
Namespace and .Net Libraries
We will build out the User Control class in sections but first we need to make sure we have all of the necessary .Net libraries or Namespaces that contain the .net methods we will be using in this project. This is also where we will put the description section to provide basic information and revision tracking for this class. The following code block shows the namespace code and class description.
Class Variables and Objects
Class variables and objects establish things at the beginning of the class that will be needed later on in our code. Variables may need a default value and objects will need to be created as new instances in memory using "instantiation".
In our example we have added the following declarations, for clarity an underscore is pre-pended to the variable name to distinguish as a custom property variable :
The Database class is used to access the database directly. Our User Control will use this class to commit changes and create the connection object.
Add Row Button is used to add a new row into the table.
Delete Row Button is used to delete a row into the table. Note - the delete row feature is immediate there is no need to commit the change, the row is deleted from the data table directly.
Commit Button is used to Commit all changes to the database.
I am going to add a private edit mode method that gets used internally to the user control. We need to control visibility of the buttons and this is a good way to have one place for doing that. Add the private edit mode method after the button code.
Complete User Control Class Code
Now we are ready to bring it all together, the following code snippet is the completed User Control Class CS file.
STEP 3: Test Windows User Control Data Grid View.
Great! Congratulations! You have completed build out of the Simple Batch Scheduler User Control and now we need to test it to make sure it works as expected before we import into the Archestra galaxy. But first we need to update the version of the user control dll so we can track any changes we make during testing. From the solutions tab select the TutorialDataGrid class and right-click the choose properties. Click the Assembly Information button and a version control dialog will appear. Set the DLL version at
220.127.116.11 18.104.22.168 as shown in the images below.
To test the User Control we will create a Windows Form project and import the user control into it. Start by creating a new VS17 project select Windows Form App as your template type then name it testTutorialDataGrid. See the image below.
When you click the OK button you will be in the Form App IDE, right-click in the toolbox on the left side and select "choose items". Then a dialog with a list of components appears, select "Browse". See the two images below.
When the browse file window appears browse to the location where the dll for the user control is located and click Open.
We should now see the TutorialSqlGrid as a choice in the Toolbox. Click and Drag the control onto our form. You should be able to verify the version of the control matches the version we set in our DLL at
After dropping the TutorialDataGrid user control we will need to add some components to the Form in order to test our creation. I added the following items to the form:
- Button named btnEdit with text "EDIT".
- Label to display "Batch ID".
- Text Box named txtBatchID, center aligned text.
- Label to display "Batch Name".
- Text Box named txtBatchName, center aligned text.
- Label to display "Equipment Name".
- Text Box named txtEquipName, center aligned text.
- Label to display "Batch Start Time".
- DateTimePicker named dtStart, ShowUpDown = true, Format = HH:mm:ss.
- Label to display "Batch End Time".
- DateTimePicker named dtEnd, ShowUpDown = true, Format = HH:mm:ss.
Complete Test Form Code
Testing the SQL Data Grid User Control
Click the start button in the visual studio IDE header to start the form.
When the form starts click the Edit button to initiate edit mode. The buttons below the grid will appear. Click the add row button to add rows to the data grid then click the Commit button to update the new rows to the database.
Clicking the vertical bar to the left of the BatchID cell in the data grid selects the entire row, as you click each row notice that the form controls are updated with the grid data by the events we added to the control. To update the information in the data grid select a row, modify the data in the form control and click the Edit button then the Update button. The changes have now been sent from the form over to the data grid in the user control via the custom properties we added. However, the changes are not committed to the database until the Commit button is pressed. In the example below I renamed batch and equipment names to match the Batch ID.
Now that basic testing is completed we are ready to import the TutorialSqlGrid user control into Archestra as a Client Control.
STEP 4: Import User Control Data Grid View into Archestra Galaxy.
Start the Archestra IDE choosing Galaxy/Import/Client Control. A file browse dialog will appear. Browse to the location where you saved the TutorialDataGrid project DLL and click "Open".
If you did everything correctly you should see a new client control in the graphic toolbox with the same name as your imported DLL, in this example the new client control name is "TutorialDataGrid".
Because we did all the testing in a Windows Form application the setup and configuration in Archestra is basic and straight forward. I created a new graphic tool set named "Tutorial3" and a new graphic symbol named Tutorial3DataGrid. Click the "Embed Graphic" option at the top of the IDE, select the new client control and drop it onto the symbol page.
All that remains to "connect" the Data Grid to our galaxy is adding a few controls and an OnShow script.
The OnShow script establishes default value for the data grid. Script each of the properties with information needed to connect to the database and table.
The Edit button activate the data grid edit mode making all of the grid buttons visible. Each button on the client control will execute internally and reset the edit mode Boolean property.
Edit Box for Batch ID
The BatchID edit box provides a means to update Archestra. Notice that one line of code in the OnShow script disables the BatchID edit box, this is done because BatchID is intended as a read-only property.
Edit Box for Batch Name
The BatchName edit box provides a means to update Archestra and the data grid with new batch name information. Each time a row in the data grid is selected the edit box is updated, similarly changing data in the Archestra edit box updates the associated cell in the client control when Edit mode is active and the "UPDATE" button is pressed.
Edit Box for Equipment Name
The EquipName edit box provides a means to update Archestra and the data grid with new equipment name information. Each time a row in the data grid is selected the edit box is updated, similarly changing data in the Archestra edit box updates the associated cell in the client control when Edit mode is active and the "UPDATE" button is pressed.
Date Time Picker for Batch Start Time
The BatchStartTime DateTimePicker provides a means to update Archestra and the data grid with new batch start time information. Each time a row in the data grid is selected the edit box is updated, similarly changing data in the Archestra edit box updates the associated cell in the client control when Edit mode is active and the "UPDATE" button is pressed.
Date Time Picker for Batch End Time
The BatchEndTime DateTimePicker provides a means to update Archestra and the data grid with new batch end time information. Each time a row in the data grid is selected the edit box is updated, similarly changing data in the Archestra edit box updates the associated cell in the client control when Edit mode is active and the "UPDATE" button is pressed.
Completed Project Edit Mode False
All that remains to complete this tutorial is to create a screen in the View Application template and embed the Tutorial3DataGrid symbol into it and deploy. When the data grid first appears it will attempt to connect to the database and table configured in the on show script. Initially edit mode is false so none of the client control buttons are visible.
Completed Project Edit Mode True
Pressing the Edit Mode button sets the edit mode Boolean property true activating edit mode making client control buttons visible. From edit mode active it's possible to update rows, add rows, or delete rows. None of the changes are committed to the database until the commit button is pressed.
This concludes the simple data grid tutorial. I hope this has been helpful to you, if you wish to download the entire project you can all of the source files from my github here.
Video tutorials on my youtube channel can be accessed here.
The Free Archestra Programming Mini Course is here.