CODEWITHSUNDEEP
sqlpowershell
Victor J Mytre
Victor J Mytre

Reputation: 354

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

Upvotes: 0

Views: 98

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32155

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

Upvotes: 1

Related Questions