Access database (.accdb), open/read/write - see MS Access database (.accdb), open/read/write
insert values into SQL Server database
Start by opening a database connection
$server
=
"yourServer\mssql2019"
$database
=
"yourDatabase"
$table
=
" yourTable"
$ConnectionString
=
"Server=$server;Database=$database;Integrated Security=True;MultipleActiveResultSets=True"
try
{
$SqlConnection
=
New-Object
System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString
=
$ConnectionString
$cmd
=
New-Object
System.Data.SqlClient.sqlCommand
$cmd.connection
=
$SqlConnection
$cmd.Connection.Open()
}
catch
[System.Exception]
{
Write-Host
$_.Exception.Message -ForegroundColor Yellow
}
Normally the following might now be done inside a loop (not shown here)
If we don't clear the parameters now using .Parameters.Clear()
immediately below
from when we set them during an earlier iteration of the loop further below,
the system will complain because it thinks we're trying to add the same field again from that earlier iteration
(after the 1st iteration, anyway.) Trying to clear using $null
won't work
$cmd.Parameters.Clear()
Now start to compose the insert command. We're inserting 4 fields.
$cmd.CommandText
=
"insert into
$table
(fileName, location,system,URL) values (@fileName,@location,@system,@URL)"
The "old" way we might have done this was just to put the values we wanted inserted
directly into "values" part of the statement above. But using the "@" above and then filling in those
parameters below helps us avoid problems with embedded single quotes
and are more secure with regard to SQL injection attackes
The | Out-Null
statements at the end of each .Parameters.Add()
commands below prevents a
whole bunch of annoying verbiage from going to your console
$cmd.Parameters.Add("@fileName",$attachment) |
Out-Null
$cmd.Parameters.Add("@location",$location) |
Out-Null
$cmd.Parameters.Add("@system",$system) |
Out-Null
$cmd.Parameters.Add("@URL",$link) |
Out-Null
Now that we've properly cued up our insert statement, try to run it:
try
{
$cmd.ExecuteNonQuery() |
Out-Null
}
catch
[System.Data.SqlClient.SqlException]
{
Write-Host
"Problem: $($_.Exception.Message)"
-ForegroundColor Red
}
catch [System.Exception]
{
# A generic error occurred somewhere in the try area.
Write-Host
"Some other error occurred: $($_.Exception.Message)" -ForegroundColor Red
}
Once we're done with our loop, close the database connection
$cmd.Connection.Close()
log into SQL Server database, can't using non-integrated security - make sure your connection string includes, ";Trusted_Connection = yes"
MS Access database (.accdb), open/read/write
$mytemp
= [environment]::getfolderpath("mydocuments")
$filename
=
"$mytemp\companies.accdb"
$conn = New-Object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.16.0;Data
Source=$filename;Persist Security
Info=False")
$conn.Open()
# read
$cmd=$conn.CreateCommand()
$cmd.CommandText="Select company.* from company;"
"connection is: $($conn.State)"
$rdr
=
$cmd.ExecuteReader()
$dt
=
New-Object
System.Data.Datatable
$dt.Load($rdr)
$dt
# write
$cmd2
=
$conn.CreateCommand()
$cmd2.CommandText
="INSERT into Company (CompanyName)
values('Jumbotron')"
$cmd2.ExecuteNonQuery()
$conn.Close()
"connection is: $($conn.State)"
ODBC entry, create
Add-OdbcDsn -Name "SysAdmin" -DriverName "SQL Server" -DsnType "System" -SetPropertyValue @("Server=DEV1\MSSQL2012", "Trusted_Connection=Yes", "Database=SysAdmin")
or first make sure the driver has been installed
If
($OdbcDriver.Name
-eq
"ODBC Driver
17 for SQL Server")
# Only continue if this specific SQL ODBC driver is installed
{Add-OdbcDsn
-Name
"NetSuiteODBC17d"
-DriverNameriver 17 for SQL Server"
-Platform
64-bit -DsnType System -SetPropertyValue
@("Server=flamboozl.database.windows.net",
"Authentication=ActiveDirectoryPassword",
"Database=flambuuzl",
"Description=flambuuzl using ODBC version 17",
"Encrypt=Yes",
"TrustServerCertificate=No")}
read from Azure DB
$database
=
"SourceOfAll"
$ServerInstance
=
"sourceOfAll.database.windows.net"
$Username
=
"readerGuy"
$password
=
"yuulNeverGess!"
$query
=
"select FirstName,
LastName, DisplayName, UserPrincipalName
from
users
where domain = jupiter.com'"
$results
=
Invoke-Sqlcmd
-ServerInstance
$ServerInstance
-Database
$database
-Username
$Username
-Password
$Password
-Query
$query
foreach
($result
in
$results) {
"$($result.DisplayName)"
}
almost the same for insert, update; just different SQL