Windchill Business Analytics: Programming Journal
Part 03: Automating SQL Queries
Last Updated: August 19, 2016
I have a basic SQL query which gives me some nice results but I need this query to run on a certain schedule. I could make a SQL report to print the results and have that run either through Oracle/SQL directly or via a simple Windows task but the XML formatting I want wouldn't be possible. This is where C# comes in.
Following the Getting Started with Oracle Data Provider for .NET (C# Version) tutorial, I started with a simple program and query just to make sure the database connection was working.
Following the Getting Started with Oracle Data Provider for .NET (C# Version) tutorial, I started with a simple program and query just to make sure the database connection was working.
Code Editor
Executing the above program and clicking the "Query Test" Button gives me:
C# Program SQL Query Test
It's nothing fancy but verifying the answer with SQL Developer shows the program works and the results are correct. One thing that you have to be careful of when executing SQL queries in this fashion is that the results you get will comes in many different forms. The above result was an integer but the label1.Text only accepts a string so the data had to be converted. You can see a full listing of the various data types that Oracle uses online but converting to string will be necessary.
Added: August 19, 2016
Obviously, the above isn't the automation I am looking for as it requires the user to press the "Query Test" button to get a result. I need to setup the program such that the query is automatically ran every so often. This is where timers come in.
Because not all data in the database is changing at the same rate, I need to consider what the best interval is to run each query. It needs to be larger than the time it takes to normally run the query but it also doesn't need to be "real time". For faster testing, I am going to select 5 seconds and run a few simple tests to verify the XML is getting updated.
Because not all data in the database is changing at the same rate, I need to consider what the best interval is to run each query. It needs to be larger than the time it takes to normally run the query but it also doesn't need to be "real time". For faster testing, I am going to select 5 seconds and run a few simple tests to verify the XML is getting updated.
In the above code, I have create a timer called sqlTimer and set it to 5,000 ms (5 seconds). When the button gets pushed, it will either stop or start the timer based on the timer's current status. The timer will call the xmlCreate function which will rerun the entire sql commands from before and create the xml file. You can see that I also changed the label and button text to more appropriately display the program's status.
Tests done with the program:
Tests done with the program:
- Make sure timer doesn't immediately start and create xml when program opens
- Confirm XML is generated 5 seconds after button is pressed to start timer
- Delete generated XML file and make sure timer continues to regenerate every 5 seconds
- Stop timer and make sure XML is no longer being created