Jump to content
buymeapc

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

Share this post


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 !


Share this post


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 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.
    • By Nas
      Hello Guys,
       
      I am running the Ado example script, if I run this line, it runs without errors :
      Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';' But when I try the windows auth one , it failed everytime :
      Func _Example_MSSQL_WindowsAuthorization() Local $sDatabase = 'AdventureWorks2016' Local $sServer = 'SQLSRV' _Example_5_MSSQL_WinAuth($sServer, $sDatabase, "Select * from Person.Address") EndFunc here is the error :
       
      ADO.au3 v.2.1.16 BETA (1204) : ==> COM Error intercepted !
      $oADO_Error.description is:     Provider cannot be found. It may not be properly installed.
      $oADO_Error.windescription:     Exception occurred.
      $oADO_Error.number is:     80020009
      $oADO_Error.lastdllerror is:     0
      $oADO_Error.scriptline is:     1204
      $oADO_Error.source is:     ADODB.Connection
      $oADO_Error.helpfile is:     C:\Windows\HELP\ADO270.CHM
      $oADO_Error.helpcontext is:     1240655
      By the way, I have 64x windows server 2016 with SQL Server , I already installed the odbc my sql connector, any thought on this will be greatly appreciate it.
    • By argumentum
      $sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1"; $rs = mysqli_query($conn, $sQueryUpdateTime); $row = mysqli_fetch_assoc($rs); the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool.
      $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows"; // takes 30 ms. on the indexed int $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows"; // takes 300 ms. due to "PatientName" been a text field, even as I did index it So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.
      ..tho, I find that any 2nd argument in the ORDER BY is just slow.
      Is there a way to query this in a way to have a faster result back ?
      Thanks
      PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE;  and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).
    • By nooneclose
      Good morning, I am trying to figure out if it is possible to check if these invoice lines exist or not. 
      Here are the table and column name: INVOICELINE.INVOICELINENUM (a required field if created)
      Here is a picture of what I am talking about. (Do not worry about security. the picture is from a demo test site so all information is fake)
       
      is there any way to check if these fields exist or not? (they do not exist unless the user clicks on "New Row")
      Example: line 11 does not exist right now. How would I go about to see if it did or not? 
      This is what I have so far:
      SELECT DISTINCT iv.invoicenum, iv.description FROM invoice AS iv JOIN invoiceline AS ivl ON iv.invoicenum = ivl.invoicenum AND iv.siteid = ivl.siteid /* = 'nothing' and yet somehow not null? */ WHERE iv.invoicenum NOT IN (SELECT invoicelinenum FROM invoiceline WHERE invoicelinenum IS NOT NULL) I get 0 results where I should get more than 0. 
    • By ptrex
      WHAT : is .NET Common Language Runtime (CLR) Framework
      The Common Language Runtime (CLR) is a an Execution Environment . Common Language Runtime (CLR)'s main tasks are to convert the .NET Managed Code to native code, manage running code like a Virtual Machine, and also controls the interaction with the Operating System.
      As part of Microsoft's .NET Framework, the Common Language Runtime (CLR) is managing the execution of programs written in any of several supported languages. Allowing them to share common object-oriented classes written in any of the languages.
      HOW : To access the CLR environment you need to create an Appdomain Object - _CLR_GetDefaultDomain()
      An AppDomain provides an isolated region in which code runs inside of an existing process.
      Application domains provide an isolation boundary for security, reliability, and versioning, and for unloading assemblies. Application domains are typically created by runtime hosts, which are responsible for bootstrapping the common language runtime before an application is run.
      WHEN : Would you use CLR Runtime Hosts
        1. To access  .NET Class Libraries :
      System System.Collections System.Data System.Drawing System.IO System.Text System.Threading System.Timers System.Web System.Web.Services System.Windows.Forms System.Xml   2. Accessing custom build .Net Assemblies :
        Some Examples (but there are a ton out there)
      AutoItX3 - The .NET Assembly for using AutoItX JSonToXML libr. XMLRPC Libr. .NETPDF libr. .NETOCR Libr WInSCP Libr.  ...   3. To Compile .Net Code into an Assembly
        4. To Run C# or VB.net Code
        5. To Mix AU3 and .Net functionality in your Application
       
      WHERE :  To find documentation about CLR
      First of all you can find a lot on MSDN and here  : Post 4 & Post 6
       
      EXAMPLES : Multiple examples included in Zip !!
      Example : “System.Text.UTF8Encoding”

      Example : “System.IO.FileInfo”

      Example : “System.Windows.Forms”

      Example : AutoItX3 Custom .NET Assembly AutoItX

      Example : Compile Code C# and Code VB

      Example : Compile Code C# at Runtime

       
      WHO : Created the CLR.au3 UDF
      All credits go to : Danyfirex / Larsj / Trancexx  / Junkew
       
      TO DO : The library is still Work in Process …
      (Some of the GUI Controls are not yet working as expected...)
      Anyone is free to participate in contributing to get the bugs resolved and to expand the CLR.au3 functionality ...
      Enjoy !!
      DOWNLOADS :  (Last updated) 
      - added CLR Constants.au3 - Danyfirex
          - Global Constants added (Magic numbers)
      - added .NET CLR CreateObject vs ObjCreate Example.au3 - Junkew
               • 2 approaches give the same result (only valid for COM Visible Assembly)
               • Includes a function that shows you which Assembly Classes are COM Visible
      - added .Net Conventional COM Objects Examples - ptrex
      - added .NET CLR CreateComInstanceFrom Example - Danyfirex
          - You can use it for Regfree COM Assembly Access
          - System.Activator has 4 methods :
              • CreateComInstanceFrom : Used to create instances of COM objects.
              • CreateInstanceFrom : Used to create a reference to an object from a particular assembly and type name.
              • GetObject : Used when marshaling objects.
              • CreateInstance : Used to create local or remote instances of an object.
      - added .NET Access SafeArrays Using AccVarsUtilities Example - LarsJ
      - added SafeArray Utilities functions in Includes - LarsJ 
      - added .NET Access Native MSCorLib Classes - System - ptrex
          Multiple System Class Examples :
               • System.Random
               •  System.DateTime
               •  System.Diagnostics.PerformanceCounter
               •  System.IO.FileInfo
               •  System.Int32
               •  System.Double
               •  System.Speech
               • System.Web
      - added Third Party Assembly Access - ptrex
               • WinSCP : https://winscp.net/eng/download.php
               • IonicZip : http://dotnetzip.codeplex.com/
      - added more Examples using PowerShell GUI Assembly Access - ptrex 
               •  GUI Ribbon .NET Assembly using &nbsp;CLR Library 
               •  GUI Report Designer .NET Assembly using &nbsp;CLR Library
               •  GUI SSRS Reporting .NET Assembly using &nbsp;CLR Library
       
      CLRv3a.zip
      .NET CLR Framework for AutoIT.pdf
×
×
  • Create New...