PowerShell connects to the SQLServer database and performs DML, DDL, DCL, and powershelldcl operations.

PowerShell connects to the SQLServer database and performs DML, DDL, DCL, and powershelldcl operations.

Standard Source: http://www.cnblogs.com/wy123/p/6426261.html

 

As ADO. similarly, PowerShell can also connect to the SQL Server database and execute the add, delete, query, modify, and delete functions. PowerShell can also perform DDL and DCL operations, the other is to support DDL + DML + DCL transaction operations, that is to say, when PowerShell executes the T-SQL, the DDL + DML + DCL can be processed as a thing

 

PowerShell performs DML operations

Add operation

Confirm in the database after execution

 

Modify

 

Delete

 

For query operations, insert two records in the database

In PowerShell, the system traverses and reads the records in the table. When reading data, GO is not supported. An error is returned when a GO submitter is added? You are not familiar with PowerShell. If you are familiar with PowerShell, please kindly advise. Thank you.

 

PowerShell executes DDL and DML operations

Unlike ADO. NET, PowerShell can perform DDL and DCL operations. Here we create a table and grant it to the Test user as an example.

Cls $ serverInstance = "localhost \ sqlserver2014A" # SQL Server instance Name $ strSql = "USE TestDB GO CREATE TABLE TestDMLByPS (Id int, Name varchar (100 )) go grant select on TestDMLByPS TO Test GO "[System. reflection. assembly]: LoadWithPartialName ("Microsoft. sqlServer. connectionInfo ") | Out-Null # load the Assembly Microsoft. sqlServer. connectionInfo $ ServerConnection = new-object Microsoft. sqlServer. management. common. serverConnection $ serverInstancetry {$ ServerConnection. connect () $ ServerConnection. executeNonQuery ($ strSql)} Catch {throw}

Created the TestDMLByPS table.

　　

The DCL statement executed also takes effect.

　　

PowerShell transactional operations

Place a DML operation and a DDL operation in one transaction for execution. If the table created by the DDL statement does not exist, the two SQL statements can be successfully executed.

　　

Place a DML operation and a DDL operation in one transaction for execution. If the DDL creation already exists, the DDL execution fails, And the deletion operation performed by DML also fails, this delete operation changes to an existing Id, prompting an exception and transaction rollback.

It can be found that the deletion performed by DML is also rolled back, and no records with Id = 2 are deleted.

　　

Unlike ADO. NET, PowerShell can execute DML, DDL, DCL, and other operations.

 

Attach the operation script

Cls $ serverInstance = "localhost \ sqlserver2014A" # SQL Server instance name $ strSql1 = "USE TestDB GO CREATE TABLE T1 (ID INT, VAL VARCHAR (50 )) GO "$ strSql2 =" USE TestDB go insert into T2 VALUES (1, 'testpower ') GO "$ strSql3 =" USE TestDB go update T2 SET Name = 'test Update by powershell' where id = 1 GO "$ strSql4 =" USE TestDB SELECT * FROM T2 "[System. reflection. assembly]: LoadWithPartialName ("Microsoft. sqlServer. connectionInfo ") | Out-Null # load the Assembly Microsoft. sqlServer. connectionInfo $ ServerConnection = new-object Microsoft. sqlServer. management. common. serverConnection $ serverInstancetry {$ ServerConnection. connect () # Open the transaction $ ServerConnection. beginTransaction () # execute the DDL statement $ ServerConnection. executeNonQuery ($ strSql1) # execute the DML statement $ ServerConnection. executeNonQuery ($ strSql2) # execute the DML statement $ ServerConnection. executeNonQuery ($ strSql3) # execute the DML statement and traverse the query result $ sqlReader = $ ServerConnection. executeReader ($ strSql4) $ Datatable = New-Object System. data. dataTable $ DataTable. load ($ SqlReader) for ($ I = 0; $ I-le $ DataTable. rows. count; $ I ++) {Write-Host 'value is: '+ $ I + ''+ $ DataTable. rows [$ I] [0] Write-Host 'value is: '+ $ I + ''+ $ DataTable. rows [$ I] [1]} # submit a transaction $ ServerConnection. commitTransaction ()} Catch {if ($ ServerConnection. transactionDepth-gt 0) {$ ServerConnection. rollbackTransaction ()} throw}