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}