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 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
    • By TheSaint
      Only early days at this point, but I have been pondering such a program for a while.
      As good as calibre is (thank you Kovid Goyal), which is a great and wonderful ebook suite of tools and a fair database, it does have its limitations. One of which, is how it deals with multiple libraries, another is the views you get. CalibBrowser will seek to address those.
      What CalibBrowser is not going to be, is an editor for existing calibre libraries. That will be left up to calibre, which is very much needed still, and covers many aspects I will never look at. Unlike calibre, which is quite a complex program, CalibBrowser also seeks to be simple. It is mainly a viewer, at this point, but will later be able to create its own libraries. However, it does not and will not export them to calibre, especially as calibre employs a far different method and structure to what CalibBrowser will employ.
      When CalibBrowser starts, it looks for calibre executables and the main Calibre Library. Whatever isn't found, you get prompted for with a browse option.
      A calibre library, is a set of ebook folders (Author\Ebooks) and a database file, always named metadata.db, and which causes an issue when it comes to multiple libraries, but makes life a bit easier when reconstructing any corrupted libraries. However, there are better ways to deal with that, as my program will show.
      The metadata.db file is an SQL database, so I am having a learning curve right now, as I have only ever dealt with an SQL database previously, codewise, when I created my INItoSQL program some time last year, as an exercise to prove a point.
      At the moment, things are pretty basic, and not everything works 100%. Here is a screenshot, to give an idea of it, but keep in mind, I intend to expand the current GUI for other stuff I will be adding.

      Older Screenshots
      Gawd, I just noticed the '3|7' in the Book Input field (original screenshot). I was using that during troubleshooting for the multiple images scrolling and forgot to disable it ... not that it impacts anything. When it comes to maths, I struggle a bit, especially when tired. Right scrolling was easy, with a continuous loop, was easy to implement. Left scrolling was significantly harder for my poor brain ... until I realized I needed to see them as Min and Max.
      As you can see the program is usable, and all the buttons, aside from the Program Information one, work. You can even load different calibre libraries, and even reload after making changes to one with calibre. The calibre program does not need to be running, even to view an ebook in the Calibre Reader. The combo selector for a library and the ADD button are only temporarily placed where they are, until I expand the GUI.
      My intention at this point, is to add another five thumbnail images, directly below existing. Currently they aren't clickable, but I may add that.
      Here is another screenshot, of what you see when you click the larger Cover image.

      If you want to have a play with the program as is, then you will need to also get the 'sqlite3.dll' file from some online source. When CalibBrowser starts successfully with the selected calibre library, it copies its metadata.db file to a sub-folder of the program called 'Backups'. It also creates a sub-folder in that, based on the library name, to house it. That copied file, is the one the program uses, though it does not even edit that, and file modification is checked every time the program starts with a particular library, or when you Reload or select a library. If the original source file has been modified, then the program copy is overwritten. The Reload Database button does nothing, if there is no change detected, and reports such.
      Place the required 'sqlite3.dll' file in the CalibBrowser root folder. Download includes source files (sqlite3.dll excepted).
      Also required of course, is an install of calibre, plus some ebooks in a created library - Calibre Library is the default when you first add ebooks to calibre. The Mobile Read Forums, is a great source for all things ebook, and calibre can be found there in the E-Book Software section.
      CalibBrowser.zip  - Upload 4  (previous downloads: 1 + 12 + 5)
      CalibBrowser (new).zip
      My apologies for the program being created in AutoIt v3.3.0.0. It is the first one I have done in a while, with an older AutoIt version. Basically my Win 7 Netbook, which has a current version of AutoIt, was busy and is always busy doing something, and not suitable for doing big projects for several reasons. My older but more powerful Win XP Laptop, has a better programming environment, better computer chair (most important for my knees etc), better external monitor (wider and newish), full size external keyboard & mouse, and a great suite of setup tools to assist me. I run several older versions of AutoIt already on my Laptop, but haven't yet determined what I need to adjust to add a current version of AutoIt to the mix. This also applies to my hugely beneficial Toolbar For Any program (one of those tools), which I constantly use with SciTE. At some point, when finished, I may update the program to the current AutoIt version ... especially as I believe I am now proficient enough to do so, having become quite familiar with it in the last year or so, making many programs with it.
      Enjoy!
       
×
×
  • Create New...