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

最新推荐文章于 2020-05-18 18:12:57 发布

yumenma

最新推荐文章于 2020-05-18 18:12:57 发布

阅读量727

点赞数

文章标签： powershell sql command database query insert

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

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

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

Working with Databases through PowerShell can be a little tricky since PowerShell doesn’t contain any SQL CmdLets. Let’s start connecting to our 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 is integrated authentication. If you use non-integrated authentiacation, you can add “userid=username;password=password;” to the connection string.

Note that the database we will use in these examples is Northwind which is 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 use INSERT, UPDATE or DELETE, you won’t get a value returned so you should use the ExecuteNonQuery() method. In case of SELECT, you should use the ExecuteReader() method. Let’s start looking at the SELECT query:

Northwind contains a table called Products. So if we want to see all Columns in Products we would use “SELECT * from Products” to retrieve all 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 add new data to a table. First specify the Columns that you want to INSERT into 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 WHERE clause to limit the range of affect to the specified rows. Let’s UPDATE the 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); }

}

}