Jump to content

Test SQL 2016 Connection with CLR


Recommended Posts

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 by buymeapc
Added CLR UDF
Link to post
Share on other sites

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)

#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 !

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Skeletor
      Hi All,
      So I was searching through the internet and found a plethora of information to learn C#. 
      However, some are way below par and does not explain very well. 

      So, anyone can suggest a proper website or eBook or video course explaining C#?
       
      I've learnt AutoIt (still I'm learning it) by making small programs, reading the forums, going through the F1 guide (huge help, seriously this helps alot) and reading the Wiki.
      Any direction would be appreciated, just not off the edge of a cliff.. 
    • By mLipok
      I have a project in mind that I would like to share.
      I would like to create example windows with standard elements like ComboBox, ListView, some Button, some text, some edit field.

      Nothing special just simple Window.Net form that can be created with this following UDF:
      or this one:
       
      The main idea is to easily provide possible modifications to the "Testing GUI Window", and no need for any other tool than the AutoIt + SciTE kit.

      Such "Testing GUI Window" will be very useful later on topic/threads/projects like:

      As I am not very familiar with NetFramework, I would like to ask for help with this project.
      Thanks in advance for any help.
      @mLipok
       
       
       
    • By mLipok
      I want to present BETA Version of my ADO.au3 UDF.
      This is modifed version of _sql.au3 UDF.
       
      For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH
       
      This is first public release , and still is as BETA
       
       
      DOWNLOAD LINK (in download section): 
       
       
      Have fun,
      mLipok
       
       
      EDIT: 2016-06-03
      Below some interesting topics about databases:
       
       
      EDIT 2016/07/04:
      For more info about ADO look here:
      https://www.autoitscript.com/wiki/ADO
      FOR EXAMPLE DATABASE use AdventureWorksDW2016_EXT.bak from:
      https://github.com/microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW2016_EXT.bak
      I will relay on this database in my examples.
       
      Here is link to post which shows how "ODBC Data Source Administrator" looks like.
       
    • By shino54
      Hello,
      I would like a query to know if an entry exists
      Thank you in advance.
      $sQuery = "SELECT Alger FROM garage where auto='BL1879'" $result1 = $result.Fields("Alger").Value if $result1="" Then MsgBox(0, "ERROR", "BAD not exist") Else MsgBox(0, "Success!", "OK exist") EndIf Exit  
    • By Nas
      Hi everyone,
      I am trying to make a script that runs a query and show it to me to see if everything is right and then decide if I finish it or not so I made a little script as below :
      #include <ADO.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> _ADO_EVENTS_SetUp(True) _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler) Local $sDriver = 'SQL Server' Local $sDatabase = 'DataBase' ; change this string to YourDatabaseName Local $sServer = 'Localhost' ; change this string to YourServerLocation Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & ';PWD=' & ';' ;~ Global $Query = _ ;~ "BEGIN TRAN" & @CRLF & _ ;~ "UPDATE Table" & @CRLF & _ ;~ "SET HOUR = 4" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "SELECT * FROM Table" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "ROLLBACK TRAN" Global $Query = _ "BEGIN TRAN" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" _Query_Display($sConnectionString, $Query) Func _Query_Display($sConnectionString, $sQUERY) ; Create connection object Local $oConnection = _ADO_Connection_Create() ; Open connection with $sConnectionString _ADO_Connection_OpenConString($oConnection, $sConnectionString) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Executing some query directly to Array of Arrays (instead to $oRecordset) Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True) ; Clean Up _ADO_Connection_Close($oConnection) $oConnection = Null ; Display Array Content with column names as headers _ADO_Recordset_Display($aRecordset, 'Query Result') EndFunc ;==> _Query_Display When I ran this script it works great, but when I run the query below :
      Global $Query = _ "BEGIN TRAN" & @CRLF & _ "UPDATE Table" & @CRLF & _ "SET HOUR = 4" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" It doesn't show anything, when I take those begin and rollback it does what it should but still not showing me anything at all, is there a way around it that you know of?
      Thank you.
×
×
  • Create New...