转载：SQL through PowerShell 使用Powershell访问SQL并对数据进行读写增加删除操作

来源：互联网发布：数据库审计部署编辑：程序博客网时间：2024/05/01 07:42

作者很强大，防止链接丢失，转载学习下~以下为原文地址：

http://www.powershell.nu/2009/01/26/sql-through-powershell/

Written by Niklas Goude on January 26, 2009 – 9:29 pm

Workingwith Databases through PowerShell can be a little tricky sincePowerShell doesn’t contain any SQL CmdLets. Let’s start connecting toour SQL database using the SQLConnection object:

PS > $Connection = New-Object System.Data.SQLClient.SQLConnection

PS > $Connection

StatisticsEnabled : False

ConnectionString :

ConnectionTimeout : 15

Database :

DataSource :

PacketSize : 8000

ServerVersion :

WorkstationId : Server

FireInfoMessageEventOnUserErrors : False

State : Closed

Site :

Container :

Next, we have to set the ConnectionString, this defines which DataBase that we want to connect to.
Note that the authentication method that is used in this example isintegrated authentication. If you use non-integrated authentiacation,you can add “userid=username;password=password;” to the connectionstring.

Note that the database we will use in these examples is Northwind whichis a sample database from Microsoft that you can download for free at Microsoft.com

PS > $Connection.ConnectionString =

"server=Server;database=Northwind;trusted_connection=true;"

PS > $Connection.ConnectionString

server=Server;database=Northwind;trusted_connection=true;

And when all is set, we open the Connection.

PS > $Connection.Open()

When you are finished using your database, you can close it with the close() method.

PS > $Connection.Close()

Now that we’ve opened a connection to our database, we can start with a couple of queries. The four basic queries are:

SELECT
INSERT
UPDATE
DELETE

When executing queries, use the SQLCommand Object. Note that if you useINSERT, UPDATE or DELETE, you won’t get a value returned so you shoulduse the ExecuteNonQuery() method. In case of SELECT, you should use theExecuteReader() method. Let’s start looking at the SELECT query:

Northwind contains a table called Products. So if we want to see allColumns in Products we would use “SELECT * from Products” to retrieveall columns and rows:

Note. In order to retrieve the data from our database, we have to use the ExecuteReader() method and
loop through it. Column Names are retrieved through .GetName() and values are retrieved through .GetValue()

PS > $Command = New-Object System.Data.SQLClient.SQLCommand

PS > $Command.Connection = $Connection

PS > $Command.CommandText = "SELECT * FROM Products"

PS > $Reader = $Command.ExecuteReader()

PS > $Counter = $Reader.FieldCount

while ($Reader.Read()) {

for ($i = 0; $i -lt $Counter; $i++) {

@{ $Reader.GetName($i) = $Reader.GetValue($i); }

}

}

Name Value

---- -----

ProductID 1

ProductName Chai

SupplierID 1

CategoryID 1

QuantityPerUnit 10 boxes x 20 bags

UnitPrice 18,0000

UnitsInStock 39

UnitsOnOrder 0

ReorderLevel 10

Discontinued False

ProductID 2

ProductName Chang

SupplierID 1

CategoryID 1

QuantityPerUnit 24 - 12 oz bottles

UnitPrice 19,0000

UnitsInStock 17

UnitsOnOrder 40

ReorderLevel 25

Discontinued False

ProductID 3

ProductName Aniseed Syrup

SupplierID 1

CategoryID 2

QuantityPerUnit 12 - 550 ml bottles

UnitPrice 10,0000

If we want a specific Row in the table, we can specify it in our SELECT query.

PS > $Command = New-Object System.Data.SQLClient.SQLCommand

PS > $Command.Connection = $Connection

PS > $Command.CommandText = "SELECT * FROM Products where ProductName = 'Röd kaviar'"

PS > $Reader = $Command.ExecuteReader()

PS > $Counter = $Reader.FieldCount

PS > while ($Reader.Read()) {

for ($i = 0; $i -lt $Counter; $i++) {

@{ $Reader.GetName($i) = $Reader.GetValue($i); }

}

}

Name Value

---- -----

ProductID 73

ProductName Röd Kaviar

SupplierID 17

CategoryID 8

QuantityPerUnit 24 - 150 g jars

UnitPrice 15,0000

UnitsInStock 101

UnitsOnOrder 0

ReorderLevel 5

Discontinued False

And if we want to check out another table, all we have to do is change the SELECT query as shown below.

PS > $Command = New-Object System.Data.SQLClient.SQLCommand

PS > $Command.Connection = $Connection

PS > $Command.CommandText = "SELECT * FROM Region"

PS > $Reader = $Command.ExecuteReader()

PS > $Counter = $Reader.FieldCount

PS > while ($Reader.Read()) {

for ($i = 0; $i -lt $Counter; $i++) {

@{ $Reader.GetName($i) = $Reader.GetValue($i); }

}

}

Name Value

---- -----

RegionID 1

RegionDescription Eastern

RegionID 2

RegionDescription Western

RegionID 3

RegionDescription Northern

RegionID 4

RegionDescription Southern

Let’s check out the INSERT query. INSERT is used when you want to addnew data to a table. First specify the Columns that you want to INSERTinto and the specify the Values.

Let’s play a little with the Region table.

PS > $Command = New-Object System.Data.SQLClient.SQLCommand

PS > $Command.Connection = $Connection

PS > $Command.CommandText =

"INSERT INTO Region (RegionID,RegionDescription) VALUES ('5','Even More North')"

PS > $Command.ExecuteNonQuery()

1

The return value is 1 since the row was affected.

Let’s check that the row really was affected by using a SELECT query:

PS > $Command = New-Object System.Data.SQLClient.SQLCommand

PS > $Command.Connection = $Connection

PS > $Command.CommandText = "SELECT * FROM Region where RegionID = '5'"

PS > $Reader = $Command.ExecuteReader()

PS > $Counter = $Reader.FieldCount

PS > while ($Reader.Read()) {

for ($i = 0; $i -lt $Counter; $i++) {

@{ $Reader.GetName($i) = $Reader.GetValue($i); }

}

}

Name Value

---- -----

RegionID 5

RegionDescription Even More North

The UPDATE query modifies existing data. It’s often used with a WHEREclause to limit the range of affect to the specified rows. Let’s UPDATEthe row that we INSERTED above.

PS > $Command = New-Object System.Data.SQLClient.SQLCommand

PS > $Command.Connection = $Connection

PS > $Command.CommandText =

"UPDATE Region set RegionDescription = 'Go West' WHERE RegionID = '5'"

PS > $Command.ExecuteNonQuery()

1

And again, we check that our row was affected.

DELETE will remove data from the database.

PS > $Command = New-Object System.Data.SQLClient.SQLCommand

PS > $Command.Connection = $Connection

PS > $Command.CommandText = "DELETE * FROM Region WHERE RegionID = '5'"

PS > $Command.ExecuteNonQuery()

1

And, finally, we check that the data really was deleted.

Below is the Code used in this post.

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString =

"server=Server;database=Northwind;trusted_connection=true;"

$Connection.Open()

$Query = "SELECT * FROM Products

$Command = New-Object System.Data.SQLClient.SQLCommand

$Command.Connection = $Connection

$Command.CommandText = $Query

$Reader = $Command.ExecuteReader()

$Counter = $Reader.FieldCount

while ($Reader.Read()) {

for ($i = 0; $i -lt $Counter; $i++) {

@{ $Reader.GetName($i) = $Reader.GetValue($i); }

}

}