I'm running some PowerShell code to connect to MySQL using the MySQL Connector .NET 9.3, and everything works perfectly when I run the commands directly in the console. However, when I try to execute the same commands in a script, I encounter the error: "Cannot find type [MySql.Data.MySqlCommand]: verify that the assembly containing this type is loaded." I've already tried using Unblock-File on the DLL and even ran it in unrestricted mode, but I'm still stuck. Here's the core part of the script:
```powershell
[void][System.Reflection.Assembly]::LoadFrom("C:Program Files (x86)MySQLMySQL Connector NET 9.3MySql.Data.dll")
$connString = "server=" + $MySQLHost + ";port=3306;user id=" + $MySQLUser + ";password=" + $MySQLPass + ";SslMode=Disabled;Database=" + $MySQLdb + ";pooling=False;"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection
$conn.ConnectionString = $connString
$conn.Open()
$query = "insert into siteGmus ..."
$cmd = New-Object MySql.Data.MySqlCommand
$cmd.Connection = $conn
$cmd.CommandText = $query
$cmd.ExecuteNonQuery()
```
Does anyone have any suggestions for resolving this issue?
2 Answers
One thing you might want to try is using the format operator in PowerShell to build your connection string and SQL query. It can make your code cleaner and easier to read. Also, ensure your script isn't running in a constrained language mode, as that can prevent assemblies from loading properly. A possible format for the connection string could be:
```powershell
$connString = 'server={0};port=3306;user id={1};password={2};SslMode=Disabled;Database={3};pooling=False;' -f $MySQLHost, $MySQLUser, $MySQLPass, $MySQLdb
```
And for the query:
```powershell
$queryTemplate = @'
insert into siteGmus
(sas, serial, version, option, online, siteCode, ip, timeStamp)
values
("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}")
'@
$query = $queryTemplate -f $gmu.Sas, $gmu.Serial, $gmu.Version.Trim(), $option.Substring(0, 8), $online, $siteCode, $gmu.IP, $meterLastUpdate
```
Try that and see if it helps!
Another suggestion is to save the DLL path to a variable instead of hardcoding it directly into the LoadFrom method. Sometimes using escape characters incorrectly can cause issues. Just make sure the path is correct and formatted properly. Here's a quick example:
```powershell
$dllPath = "C:Program Files (x86)MySQLMySQL Connector NET 9.3MySql.Data.dll"
[void][System.Reflection.Assembly]::LoadFrom($dllPath)
```
Give that a try and let me know if it makes any difference!
Unfortunately, changing that didn't resolve the issue for me. I'm still getting the same type loading error.
If that's the case, I'm definitely going to feel a little silly, haha!