How to connect to and populate SQL database using Powershell

Question

I want to write an application that talks to a database. The databases are created through phpmyadmin interface. I can talk to these fine through php. What I would like is to populate these databases using a powershell script.

How do I connect to the database ? How do I populate a database ? I can't seem to find any good starting points.

@JanezKuhar i take it that means 'DatabaseManagementSystem' ? I've never worked server side before so all this is new to me. All I know is I have wampserver installed on my machine, and I created a database through phpmyadmin — thatOneGuy
– thatOneGuy, Commented Mar 5, 2018 at 20:35
That's what I meant, yeah. I don't know wampserver but it says on their web page that they're using MySQL. You can look into this question. This is in a way a duplicate question. — Janez Kuhar
– Janez Kuhar, Commented Mar 5, 2018 at 20:40

Thom Schumacher · Accepted Answer · 2018-03-05 20:30:18Z

2

Here is a great place to start: https://dbatools.io/ https://dbareports.io/

or you can look here as well: https://www.powershellgallery.com/

answered Mar 5, 2018 at 20:30

Thom Schumacher

1,59313 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TheMadTechnician Over a year ago

The best SQL people I know recommend dbatools.io for managing SQL databases. I upvoted this even if it is an answer with just a link to other sites.

Thom Schumacher Over a year ago

If the poster had included some code I'd have been happy to add to it.

cet51 · Accepted Answer · 2018-03-05 22:55:15Z

Here's a function to handle this type of task

function Invoke-SQL
{
param (
    [string]$server,
    [string]$database,
    [string]$Query
)

$connectionString = "Data Source=$server; " +
"Integrated Security=SSPI; " +
"Initial Catalog=$database"

$connection = new-object 
system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($Query, $connection)
$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

# Use these to populate info #
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null

$connection.Close()

# displays info #
$dataSet.Tables
}

Here's an example of updating the SQL Database

Invoke-SQL -server 'server' -database 'database' -Query "UPDATE [database].[dbo].[Local] SET Field1 = '$InfoForField1', Field2 = '$InfoForField2'"

You can do whatever you need using this method, as long as you know your SQL queries and how to populate the varaibles with the correct information that you need.

Collectives™ on Stack Overflow

How to connect to and populate SQL database using Powershell

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related