Pass a query and access SQL Server Database in Powershell

Question

I have a database named 'SQLDatabase'. It has many folders like 'System _Database', 'R_Database','ReportServer' etc.

I need to access 'R_Database'.

Now 'R_Database' has many folders like 'Storage', 'Security', 'Usage', 'Tables'.

I need to access 'Tables'.

Again, 'Tables' have many tables in it. I need to access a particular table named 'DB_Batch', and update it.

How should I?

This is the code I did for connecting to the SQL Server, and then the Table. But it fails.

$dataSource = ".\MSSQL"
$user = "userID"
$pwd = "password01"
$database = "SQLDatabase"
$connectionString = "Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
#$connection.ConnectionString = "Server=$dataSource;Database=$database;Integrated Security=True;"
$connection.Open()
query = <query>..????

Now I suppose I need to pass a query. How should I? I need to access the table first and then update it.

I even wrote the following code to end the above code, but not able to test as I am stuck.

$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()
$result

I request, please help me with this. I have been trying this for days. I am using Powershell v2.0

score 0 · Answer 1 · answered Sep 07 '15 at 11:57

In order to insert/update a table, use a SqlCommand and its ExecuteNonQuery().

$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $connection
$cmd.commandtext = "INSERT INTO myTable (myColumn) values ({0})" -f $myValue
$cmd.ExecuteNonQuery()

Pass a query and access SQL Server Database in Powershell

1 Answers1