SQLExecution 1.4.0

.NET Framework
dotnet add package SQLExecution --version 1.4.0
NuGet\Install-Package SQLExecution -Version 1.4.0
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="SQLExecution" Version="1.4.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add SQLExecution --version 1.4.0
#r "nuget: SQLExecution, 1.4.0"
#r directive can be used in F# Interactive, C# scripting and .NET Interactive. Copy this into the interactive tool or source code of the script to reference the package.
// Install SQLExecution as a Cake Addin
#addin nuget:?package=SQLExecution&version=1.4.0

// Install SQLExecution as a Cake Tool
#tool nuget:?package=SQLExecution&version=1.4.0

SQL Execution Documentation (NuGet Package)

  • 1.3.0: Added non-trusted execution parameters, corrected threaded execution
  • 1.2.0: Made class virtual to allow instantiation
  • 1.1.1: Corrected assembly information
  • 1.1.0: Corrected class hierarchy

Publishing to NuGet is a new experience for me, so use at your own risk, but feel free to contact me if you have issues.

You will need to build this on a computer with the MS Office DLL's and/or PIA's. \

SQL Execution Tests

using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using SQLExecution;
using System.Data;
using System.Data.SqlClient;

namespace SQLExecution.Test
{
    [TestClass]
    public class SQLExecutionText
    {
        [TestMethod]
        public void CreateSqlExecutionClasssTest()
        {
            SQLExecution.SqlExecution sqlClass = new SQLExecution.SqlExecution();
            Assert.IsNotNull(sqlClass);
        }

        [TestMethod]
        public void SQLCommandExecutionTest()
        {
            SQLExecution.SqlCommandParameters param = new SqlCommandParameters()
            {
                CommandTimeOut = 60,
                DatabaseName = "",
                SqlServerInstance = "",
                StoredProcedure = "",
                UseIntegratedSecurity = false,
                UserId = "",
                Password = ""
            };

            SQLExecution.SqlCommandExecution cmd = new SqlCommandExecution(param);

            cmd.Execute();

            System.Data.DataSet set = cmd.Data;

            Assert.IsNotNull(set);
            Assert.IsTrue(set.Tables[0].Rows.Count > 0);
        }

        [TestMethod]
        public void SQLExecutionTest()
        {
            SQLExecution.SqlCommandParameters param = new SqlCommandParameters()
            {
                CommandTimeOut = 60,
                DatabaseName = "",
                SqlServerInstance = "",
                StoredProcedure = "",
                UseIntegratedSecurity = false,
                UserId = "",
                Password = ""
            };

            SQLExecution.SqlExecution exec = new SqlExecution();

            exec.AddToList(param);

            exec.Run();

            System.Data.DataSet set = exec.Commands[0].SqlExecution.Data;

            Assert.IsNotNull(set);
            Assert.IsTrue(set.Tables[0].Rows.Count > 0);
        }

    }
}

VBA Usage

An example using the code to execute SQL asynchronously and write it out to different sheets. In effect, the execution time is nearer to the execution time of the slowest command object, rather than being the sum of execution times.

Some Notes:

  • The data classes used above can be used instead of the ones below if you only want to execute SQL and collect data

  • The classes below both retrieve data and write it out to sheets

  • Some fields in the code below are global parameters as string for server instance, database name and timeout, prefaced by gstr, which can be passed in as variables instead

  • Each item will execute independently, and do not need to be pointing at the same server/database

    Private Sub ThreadedExecution()

    On Error GoTo ErrorTrap

    Dim addin As Office.COMAddIn
    Dim automationObject As Object
    Set addin = Application.COMAddIns("SQLExecutionAddIn")
    Set automationObject = addin.Object
    
    Dim wkb As Workbook
    Set wkb = Application.Workbooks.Add()
    
    Call automationObject.AddSPToCollection("SQL/SP 1", wkb.Sheets("target sheet 1"), "target cell", _ 
    

    True, gstrSQLServerInstance, gstrSQLServerDatabase, gintDefaultCommandTimeOut)

    Call automationObject.AddSPToCollection("SQL/SP 2", wkb.Sheets("target sheet 2"), "target cell", True, _ 
    

    gstrSQLServerInstance, gstrSQLServerDatabase, gintDefaultCommandTimeOut)

    Call automationObject.AddSPToCollection("SQL/SP 3", wkb.Sheets("target sheet 3"), "target cell", True, _ 
    

    gstrSQLServerInstance, gstrSQLServerDatabase, gintDefaultCommandTimeOut)

    Call automationObject.ExecuteSPCollection
    
    Call automationObject.ClearSPCollection
    

    ExitSub:

    Set automationObject = Nothing
    Set addin = Nothing
    
    Set wkb = Nothing
    
    Exit Sub
    

    ErrorTrap:

    Call StandardMessage(Err.Description, vbOKOnly)
    
    GoTo ExitSub
    

    End Sub

Product Versions
.NET Framework net
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.

This package has no dependencies.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.4.0 1,671 5/21/2015

Added non-trusted execution parameters, corrected threaded execution

Support Doc:

http://comparative-advantage.com/code/SQL_ExecutionHelp.php

GitHub Repo:

https://github.com/JamesIgoe/SqlexecutionAddIn