Q:

How to modify my code that works with an ACCESS database to work with an SQL database

I have a code running to import CSV files into a Access database. However now I have access to a SQL server and want to use the same code if possible, but no idea what to change to adapt the code
I have a set of running code which uses a connection string I add parameters and it works.
$connectionString="Provider=Microsoft.Ace.OLEDB.12.0; Data Source=$inputDatabase" 
 $conn = New-Object System.Data.OleDb.OleDbConnection($connectionString)
 $conn.Open()
 $cmd = $Conn.CreateCommand()

 $cmd.CommandText = "INSERT INTO [Tabl1]([date],[zone],[location],[Plugin ID],[CVE],[CVSS],[Risk],[Host],[Protocol],[Port],[Name])
         VALUES(@date, @zone, @location, @PluginID, @CVE, @CVSS, @Risk,@Host,@Protocol,@Port,@Name)"```

        [void]$cmd.Parameters.Add('@date',      [System.Data.OleDb.OleDbType]::DateTime)
        [void]$cmd.Parameters.Add('@zone',      [System.Data.OleDb.OleDbType]::VarChar)

both code snippets above show how I set up my connection string, and a quick example of how 2 values are set with their corresponding variable type, it is not the whole code . but the snippets I know I need to change to make it work with an SQL server, the main issue being that  i dont know which $connectionstring to use with an SQL server, or for example how do I make it so the data source points to the SQL server network address and uses windows authentication.
And on the second code snippet, which type of format do I use, I knwo the variable types but I assume that if I change from ace.oledb.12 , i should change the oldebtype segment.  The SQL server is on SQL 13.0.5426

A:

You'll need to update your connection string. Assuming you're using Windows authentication:
$ConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=SSPI' -f $ServerInstanceName, $DatabaseName

And you should use the built-in SQL Server client class, System.Net.SqlClient, instead of OLEDB classes.
$conn = New-Object System.Data.SqlClient.SqlConnection $connectionString

And you'll want to update your parameter types:
[void]$cmd.Parameters.Add('@date',      [System.Data.SqlDbType]::DateTime)
[void]$cmd.Parameters.Add('@zone',      [System.Data.SqlDbType]::VarChar)

Everything else should be more or less identical.

Here's a working PowerShell script that connects to an SQL Server database, fetches data, and fills a data table:
$SqlQuery = 'SELECT * FROM Student WHERE Student_Id = @student_id'
$ConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=True' -f $SqlServer, $Database

$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString
$SqlCommand = New-Object -TypeName System.Data.SqlClient.SqlCommand -ArgumentList $SqlQuery, $SqlConnection
$SqlCommand.Parameters.Add('@student_id', [System.Data.SqlDbType]::VarChar, 10).Value = $Student_Id

$DataTable = New-Object -TypeName System.Data.DataTable -ArgumentList 'Student'

try {
    $SqlConnection.Open()
    $SqlDataReader = $SqlCommand.ExecuteReader()
    $DataTable.Load($SqlDataReader)
}
finally {
    $SqlConnection.Close()
}
$DataTable

