SQL Execution Documentation (NuGet Package)

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:

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