Test SQL 2016 Connection with CLR

[buymeapc]

Hello,

I was looking for a way to use the CLR functions to check a SQL 2016 database state, but I've been unable to find an example that I can get to work. Any help would be appreciated. Here's what I have, which always errors when I try to set the connection string and I'm not sure why. Thanks!

#include ".\Includes\CLR.au3"
#include ".\Includes\CLR Constants.au3"

JustATest()

Func JustATest()
    Local $oAssembly = _CLR_LoadLibrary("System.Data")
    ConsoleWrite("$oAssembly: " & IsObj($oAssembly) & @CRLF)

    Local $oSQLConn = _CLR_CreateObject($oAssembly, "System.Data.SqlClient.SqlConnection")
    ConsoleWrite("$oSQLConn: " & IsObj($oSQLConn) & @CRLF)

    $oSQLConn.ConnectionString = "Server=serverip;Database=dbname;UID=user;PWD=aaaa;"

    $oSQLConn.Open
EndFunc

Edit: I am using ptrex's CLR UDF from here:

Edited January 14, 2020 by buymeapc
Added CLR UDF

[ptrex]

The challenge with .NET CLR and AutoIT is that the data type conversion between the 2 is not working out well in some cases....

Better approach is to use the PS AUTOMATION Object that works in 100% of the cases. Because the type conversion is done outside of the CLR host.

Example : (Dont forget to change the database connection details)

expandcollapse popup

#AutoIt3Wrapper_UseX64=y

#include "CLR.Au3"

Local $PS_Script = "CLS" & @LF
$PS_Script &= "" & @LF
$PS_Script &= "$QueryStr = 'Select * From TableX'" & @LF
$PS_Script &= "" & @LF
$PS_Script &= "$SqlConnection = New-Object System.Data.SqlClient.SqlConnection" & @LF
$PS_Script &= "" & @LF
$PS_Script &= "$SqlConnection.ConnectionString = 'Server=serverip;Database=dbname;UID=user;PWD=aaaa;'" & @LF
$PS_Script &= "" & @LF
$PS_Script &= "$SqlCmd = New-Object System.Data.SqlClient.SqlCommand" & @LF
$PS_Script &= "$SqlCmd.CommandText = $QueryStr" & @LF
$PS_Script &= "" & @LF
$PS_Script &= "$SqlCmd.Connection = $SqlConnection" & @LF
$PS_Script &= "" & @LF
$PS_Script &= "$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter" & @LF
$PS_Script &= "$SqlAdapter.SelectCommand = $SqlCmd" & @LF
$PS_Script &= "" & @LF
$PS_Script &= "$DataSet = New-Object System.Data.DataSet" & @LF
$PS_Script &= "$SqlAdapter.Fill($DataSet) " & @LF
$PS_Script &= "" & @LF
$PS_Script &= "'Rows # :  ' + $DataSet.tables.rows.count" & @LF
$PS_Script &= "$DataSet.tables.rows | Out-GridView -Title AutoIT # Export-CSV " & @LF
$PS_Script &= "" & @LF
$PS_Script &= "$SqlConnection.Close() " & @LF
$PS_Script &= "" & @LF

_Run_PSHost_Script($PS_Script)

Func _Run_PSHost_Script($PSScript)
    Local $oAssembly = _CLR_LoadLibrary("System.Management.Automation")
    ConsoleWrite("!$oAssembly: " & IsObj($oAssembly) & @CRLF)

    ; Create Object
    Local $pAssemblyType = 0
    $oAssembly.GetType_2("System.Management.Automation.PowerShell", $pAssemblyType)
    ConsoleWrite("$pAssemblyType = " & Ptr($pAssemblyType) & @CRLF)

    Local $oActivatorType = ObjCreateInterface($pAssemblyType, $sIID_IType, $sTag_IType)
    ConsoleWrite("IsObj( $oAssemblyType ) = " & IsObj($oActivatorType) & @TAB & @CRLF)

    ; Create Object
    Local $pObjectPS = 0
    $oActivatorType.InvokeMember_3("Create", 0x158, 0, 0, 0, $pObjectPS)
    ConsoleWrite("IsObject: " & IsObj($pObjectPS) & @TAB & "$pObject: " & ObjName($pObjectPS) & @CRLF)

; <<<<<<<<<<<<<<<<<<< PS COMMAND HERE >>>>>>>>>>>>>>>>>>>>
    $pObjectPS.AddScript($PSScript) ; Add Script here
;~  ConsoleWrite($PSScript & @CRLF)

    $objAsync = $pObjectPS.BeginInvoke ; (2); ($oActivatorType,$oActivatorType)

    While $objAsync.IsCompleted = False
;~          ConsoleWrite($objAsync.IsCompleted & @CRLF)
        ContinueLoop
    WEnd
        ConsoleWrite("Completed : " & $objAsync.IsCompleted & @CRLF)

    $objPsCollection = $pObjectPS.EndInvoke($objAsync)
;~  ConsoleWrite("$objPsCollection: " & IsObj($objPsCollection) & @TAB & "$objPsCollection: " & ObjName($objPsCollection) & " - " & ObjName($objPsCollection,6) & " - " & ObjName($objPsCollection,3) & @CRLF)

    $Whnd = WinGetHandle("AutoIT")
    ConsoleWrite($Whnd & @CRLF)

    WinWaitClose($Whnd)
    ConsoleWrite("closed !" & @CRLF)

EndFunc

 

Enjoy !

[buymeapc]

Thank you very much for the help with this, ptrex! This helps a lot to get me started.