Skip to main content
Indiana Wesleyan University Support Knowledge Base

SQL Querying Methods

  1. Last updated
  2. Save as PDF

Overview

Lists available options and methods for querying SQL Server using ExportManager and links to additional resources.

Details

ExportManager has simple methods for retrieving and manipulating one or more SQL query result sets.

Setup

DbConnection.DataSource

String host name of the SQL database server.

DbConnection.IntegratedSecurity

Boolean value indicating whether to use Windows authentication (the same user that runs the program).  Using this option is generally preferable to including a username and password in your script.

DbConnection.UserID

Username with which to connect to the SQL server.

DbConnection.Password

Password with which to connect to the SQL server.

DbConnection other

Many other options can be specified using this object.  It is a .NET SqlConnectionStringBuilder object that will be used to attempt a connection for query execution.  See Microsoft documentation for more options.

SqlScript

String value containing the SQL script to be executed.  It can include one or more SELECT statements. Each result set will be captured in the ExportData.Tables array in sequence.

SqlSelectTimeout

Integer indicating the number of seconds to wait after which the script will time out and log an error.  Default is 600.  This option can also be set by command line argument using sqltimeout=n

AddOutputFileName()

(Overloaded) String value containing a file name to use when writing SQL results to disk using WriteToDisk().  Values are added to the list WorkingSet.DataFiles which is a List of FileInfo objects.  These file names are used in order of addition for each result set returned from the SQL script.  If the number of result sets does not match the number of file names added, you will receive a warning at runtime.

/// <summary>
/// Add an output file name. The number of output files should match the
/// number of result sets received from the database.
/// </summary>
/// <param name="subDir">Subdirectory in which the file should appear</param>
/// <param name="fileName">File name to add to the output list</param>
public void AddOutputFileName( string subDir, string fileName )
public void AddOutputFileName( string fileName ) => AddOutputFileName( "", fileName );

Execution

WaitForWarehouse()

This method will cause the export process to sleep (wait) up to the specified number of minutes for the Data Warehouse update to complete.  The method checks to see if the warehouse is up-to-date once per minute during this time.  If the Warehouse update completed before the specified number of minutes, the method returns true, otherwise false.

/// <summary>
/// Wait for the Data Warehouse update to complete. Checks every 60 seconds and will return true if the Warehouse update
/// completes before the timeout is reached.
/// </summary>
/// <param name="timeout">Number of minutes to wait for the Data Warehouse update to complete, checking every 60 seconds, before giving up</param>
/// <returns>true if today's Data Warehouse update completed before timeout expired, otherwise false</returns>
public bool WaitForWarehouse( int timeout )

LoadFromDB()

This method uses the setup options to connect to the SQL server, execute the script, and save the results in memory to the ExportData object which is a DataSet.

Transform()

This optional method is a virtual method provided that can be overridden by individual exports that need to perform transformation operations on SQL data using the power of C# and .NET prior to being written to disk.

WriteToDisk()

(Overloaded) This method takes the ExportData.Tables datasets and writes them all to disk provided there is a corresponding output file that was added using AddOutputFilename().  You may also specify an integer argument to tell the method to only write a single dataset to disk with the given index.  If you tell it to writeHash, it will also compute a sha256 hash for your file and automatically compare it to the remote after SFTP upload (if the server supports it).

/// <summary>
/// Writes the SQL data in all result sets to disk in delimited file format
/// </summary>
/// <param name="writeHash">In addition to the file, also write the SHA256 hash</param>
/// <param name="fileName">Add this file name to the output list and write the SQL data from its result set to disk in delimited file format.</param>
/// <param name="tableIndex">Write out only the specified index of the result sets and matching DataFiles</param>
public virtual void WriteToDisk( bool writeHash = false )
public virtual void WriteToDisk( string fileName, bool writeHash = false  )
public virtual void WriteToDisk( int tableIndex, bool writeHash = false  )

Example

Script

This example will wait for the Data Warehouse update to complete before executing the SQL script and writing the results to files.  It then copies the files to a remote server.

public SqlSample( string[] args ) : base( args )
{
    // Try to keep setup & options in the constructor

    // Database options
    DbConnection.DataSource         = "WarehouseDB";
    DbConnection.InitialCatalog     = "reports";
    DbConnection.IntegratedSecurity = true;

    // Script can contain multiple SELECT statements. 
    // You only need to add an output file with AddOutputFileName() for each SELECT
    #region SqlScript = ...
    SqlScript = @"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT 1 AS COLUMN_A, 2 AS COLUMN_B
    UNION ALL
    SELECT 2, 4
    UNION ALL
    SELECT 4, 8
    UNION ALL
    SELECT 8, 16";
    #endregion
}

public override void Run()
{
    // Try to keep this method limited to other method calls rather than setup & options

    // If the Data Warehouse is up-to-date within the next 10 minutes, get data from SQL
    if ( WaitForWarehouse( 10 ) )
    {
        LoadFromDB();

        // Transform SQL data results
        Transform();

        // Write SQL results to file in the working set using the 
        // default delimited file settings
        AddOutputFileName( "SampleExport.csv" );
        WriteToDisk();

        // Copy the written files out to a network drive
        ExportFilesTo( @"N:\myfiles" );
    }
}

/// <summary>
/// Overriding the Transform method is optional as is calling the method itself.
/// The default method simply logs an error that it has not been implemented.
/// </summary>
public override void Transform()
{
    if ( WorkingSet.IsDataLoaded )
    {
        ExportData.Tables[0].Columns[0].ColumnName = "Transformed!";
        log.Info( "Transform successful." );
    }
    else
        log.Error( "Tried to transform empty data set!" );
}

Log output

Expected ExportManager.log output:

2016-09-14 14:01:39.2708 INFO -------------------------------------------------------------------------------
2016-09-14 14:01:39.3238 INFO IWU Export Manager launched
2016-09-14 14:01:39.3238 INFO -------------------------------------------------------------------------------
2016-09-14 14:01:39.3448 TRACE OS=Microsoft Windows NT 6.1.7601 Service Pack 1  Cores=4  64bit=True
    Machine=IWU71563  Domain=IWUNET  User=konrad.willmert
    Command=ExportManager  SqlSample
-------------------------------------------------------------------------------
2016-09-14 14:01:39.3768 DEBUG Export "BasicSample" found
2016-09-14 14:01:39.3938 DEBUG Export "ColleagueSample" found
2016-09-14 14:01:39.4048 DEBUG Export "ColleagueTest" found
2016-09-14 14:01:39.4158 DEBUG Export "FileSample" found
2016-09-14 14:01:39.4158 DEBUG Export "FtpSample" found
2016-09-14 14:01:39.4398 DEBUG Export "SampleExport" found
2016-09-14 14:01:39.4508 DEBUG Export "SqlSample" found
2016-09-14 14:01:39.8368 INFO Executing "SqlSample"
2016-09-14 14:01:40.8528 DEBUG "SqlSample" export process executed in 1,386ms
2016-09-14 14:01:40.8678 DEBUG IWU.ExportManager.Exports.SqlSample with data connection: "Data Source=WarehouseDB;Initial Catalog=reports;Integrated Security=True;Application Name="ExportManager, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null"" FileManager@C:\Users\konrad.willmert\Source\Repos\IWUExportManager\IWUExportManager\bin\Release\data\SqlSample: 1 data files, 0 other files, SQL data loaded, SQL data written to disk ColleagueConnection mocha.task@DEV with database "coll18_dev" and HOLD dir "\\ERPapp\d$\datatel\ftp\coll18_dev\data\_HOLD_"
2016-09-14 14:01:40.8818 DEBUG ExportManager total run time: 1,612ms
2016-09-14 14:01:40.8818 INFO Exiting.

Expected SqlSample.log output:

2016-09-14 13:58:13.9148 INFO -------------------------------------------------------------------------------
2016-09-14 13:58:13.9148 INFO IWU.ExportManager.Exports.SqlSample initializing.
2016-09-14 13:58:13.9148 INFO -------------------------------------------------------------------------------
2016-09-14 13:58:14.9418 WARN TEST MODE is enabled. No actual data files will be exported/uploaded. Instead, write access will be tested. Override with "notest".
2016-09-14 13:58:14.9688 DEBUG Data directory: C:\Users\konrad.willmert\Source\Repos\IWUExportManager\IWUExportManager\bin\Release\data\SqlSample
2016-09-14 13:58:14.9758 DEBUG Archive directory: C:\Users\konrad.willmert\Source\Repos\IWUExportManager\IWUExportManager\bin\Release\archive\SqlSample
2016-09-14 13:58:21.9768 INFO Data Warehouse is up-to-date.
2016-09-14 13:58:21.9918 INFO Executing SQL script.
2016-09-14 13:58:21.9918 TRACE Connecting to database with connection string: Data Source=WarehouseDB;Initial Catalog=reports;Integrated Security=True;Application Name="ExportManager, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null"
2016-09-14 13:58:22.1398 INFO Received 4 total rows in 1 result set(s)
2016-09-14 13:58:22.1518 DEBUG FileManager@C:\Users\konrad.willmert\Source\Repos\IWUExportManager\IWUExportManager\bin\Release\data\SqlSample: 0 data files, 0 other files, SQL data loaded
2016-09-14 13:58:22.1518 DEBUG SQL script executed in 146ms
2016-09-14 13:58:22.1728 INFO Transform successful.
2016-09-14 13:58:22.1838 TRACE Added output file: SampleExport.csv
2016-09-14 13:58:22.2008 INFO Writing SQL results to disk.
2016-09-14 13:58:22.2108 DEBUG Number of output files matches number of SQL result sets received.
2016-09-14 13:58:22.2318 DEBUG Successfully wrote 64 bytes from result set 0 to "SampleExport.csv" in 11ms
2016-09-14 13:58:22.2418 INFO Successfully wrote result set to disk.
2016-09-14 13:58:22.2418 DEBUG FileManager@C:\Users\konrad.willmert\Source\Repos\IWUExportManager\IWUExportManager\bin\Release\data\SqlSample: 1 data files, 0 other files, SQL data loaded, SQL data written to disk
2016-09-14 13:58:22.2718 INFO Exporting working set to: N:\myfiles
2016-09-14 13:58:22.2858 DEBUG Created directory: N:\myfiles
2016-09-14 13:58:22.3118 DEBUG Test file copied successfully.

Considerations

Warning - Temp Tables
There is currently a limitation that you cannot create and use temp tables within your query script due to the script results being loaded by a DataTable behind the scenes
  1. Back to top
  • Was this article helpful?

Recommended articles

  1. Article type
    Reference
    Stage
    Draft
  2. Tags
    This page has no tags.