Erick's Blog
  • Home
  • Blog
  • Apps
    • Mortgage Calculator
  • PowerShell
    • Blog & Examples
    • Excel Reference
  • Windchill
    • Document Exporter
    • Document Worker
    • E.P.L.E.S.
    • Export Released
    • Property Checker
    • Windchill Business Analytics >
      • Part 01 - Program Logic
      • Part 02 - Getting Data with SQL Queries
      • Part 03 - Automating SQL Queries
      • Part 04 - Converting SQL Results to XML
      • Part 05 - Data Processing and Manipulation
      • Part 06 - Displaying XML in HTML
      • Part 07 - Auto Updating Displayed Data
      • Part 08 - Hosting Webpage with an Existing Apache Installation
      • Part 09 - Running Multiple Queries In Sequence
      • Part 10 - Calculating Data Trends
      • Part 11 - Making It Modular
    • Windchill Quick View
  • Reviews
  • Music
  • Contact

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.
Code Editor

    
Executing the above program and clicking the "Query Test" Button gives me:
Picture
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.

    
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:
  • 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

Previous: Getting Data with SQL Queries
W.B.A. â€‹Home
Next: Converting SQL Results to XML
Copyright © 2018 Erick Johnson
  • Home
  • Blog
  • Apps
    • Mortgage Calculator
  • PowerShell
    • Blog & Examples
    • Excel Reference
  • Windchill
    • Document Exporter
    • Document Worker
    • E.P.L.E.S.
    • Export Released
    • Property Checker
    • Windchill Business Analytics >
      • Part 01 - Program Logic
      • Part 02 - Getting Data with SQL Queries
      • Part 03 - Automating SQL Queries
      • Part 04 - Converting SQL Results to XML
      • Part 05 - Data Processing and Manipulation
      • Part 06 - Displaying XML in HTML
      • Part 07 - Auto Updating Displayed Data
      • Part 08 - Hosting Webpage with an Existing Apache Installation
      • Part 09 - Running Multiple Queries In Sequence
      • Part 10 - Calculating Data Trends
      • Part 11 - Making It Modular
    • Windchill Quick View
  • Reviews
  • Music
  • Contact