At work we, rather I, am testing a new network/system monitoring tool. This one from ActiveXperts called Network Monitor 6.0 (how original, eh?) allows the use of VBScripts. Well, lo and behold, I couldn't get ADO 2.8 to work in a VBScript. So what did I do? I looked up how to get VB.NET to work through a COM call. The example I had to go by used C#, but not to worry, translating to VB.NET was a piece of cake.
So how do you do it? Make that call to a .NET object via a COM call? Simple, you use RegAsm.exe as explained in the example or you can use CLR ComReg . This tool is provided as part of Visual Studio .NET SDK as source code which adds some features left out by RegAsm. These are noted in the Read Me.
First you'll want to create your VB.NET solution as a class library. Simple enough.
Here's the entire class I created for my project here at HP (it checks the Oracle database TEMP tablespace for percent used):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | Imports Oracle.DataAccess.Client Namespace OracleChecks Public Class OraDBChecks Private m_connectString As String Private m_dbuser As String Private m_dbpass As String Private m_dbname As String Public Sub New() ' Required for access from COM ' Don't do anything here... End Sub Public Sub Init(ByVal dbname As String, ByVal dbuser As String, ByVal dbpass As String) m_dbname = dbname m_dbuser = dbuser m_dbpass = dbpass m_connectString = "User ID=" & m_dbuser & ";Password=" & m_dbpass & ";Data Source=" & m_dbname End Sub Public Function CheckTempTablespace(ByVal threshold As Decimal) As Boolean Dim sSqlCommand As String = "SELECT decode(sum(s.blocks),NULL, 0, (sum(s.blocks)/sum(t.blocks))*100) temp_percent_used " sSqlCommand = sSqlCommand & "FROM SYS.v_$sort_usage s RIGHT OUTER JOIN SYS.dba_temp_files t " sSqlCommand = sSqlCommand & "ON s.TABLESPACE = t.tablespace_name" Dim drTestValue As OracleDataReader Dim conTestTable As OracleConnection = New OracleConnection(m_connectString) Dim cmdTestTable As New OracleCommand(sSqlCommand, conTestTable) conTestTable.Open() drTestValue = cmdTestTable.ExecuteReader(CommandBehavior.SingleRow) Do While drTestValue.Read() If drTestValue.GetDecimal(0) >= threshold Then conTestTable.Close() Return True Else conTestTable.Close() Return False End If Loop End Function End Class End Namespace |
COM requires a no argument constructor so that's the reason for the Public Sub New statement. You don't have to make it empty, it just can't be passed any values.
Now compile the project and register using RegAsm or ComReg. If you use ComReg and the /gac parameter to add the assembly to the GAC, you need to sign it with a strong name. This is fairly simple enough using some tools provided with the Framework SDK.
First you create a key pair using the sn.exe tool. Simply use this syntax:
sn -k <keyfilename>
For my assembly I used:
sn -k dbchecks.snk
To create the public key file, use this syntax:
sn -p <private_keyfilename> <public_keyfilename>
For my assembly I used:
sn -p dbchecks.snk dbchecks_public.snk
In Visual Studio, all you need to do now is add the following to the AssemblyInfo.vb file, replacing the path and the key file name appropriately:
<Assembly: AssemblyKeyFileAttribute("C:\Documents and Settings\JOBA\My Documents\VISUAL STUDIO PROJECTS\DBChecks\dbchecks.snk")>
If you are not using Visual Studio, you will need to sign your assembly using the Assembly Linker (al.exe). This one is a little more complex so I am not going to go into that here.
Now, all that is left is calling your assembly from your COM application.
Since my COM app was a VBScript, here's how I used it:
1 2 3 4 5 6 7 | Function CheckTempTablespace(servername, dbuser, dbpass, warnThreshold) Dim c Set c = CreateObject("HP_PSGIT.OracleChecks.OraDBChecks") c.Init servername, dbuser, dbpass CheckTempTablespace = c.CheckTempTablespace(warnThreshold) End Function |
And that's all there is. Enjoy!
