kenger.SQLData.txt
|
Import-Module kenger.SQLData.psm1
#specify SQL credentials (non windows account) $user = "sa" $pass = "secret4sa" | ConvertTo-SecureString -asPlainText -Force $cred = New-Object System.Management.Automation.PSCredential($user,$pass) # create a new connection object, to be reused $conn = New-SQLConn -server 192.168.1.96 -database TestDB -sqlCredentials $cred # read SQL data Read-SQLData -query "SELECT * FROM Table1" -conn $conn id Name Age Data1 -- ---- --- ----- 1 aaa 33 ccc 2 xxxx 33 abcd 3 kege 33 xxx # Write to db, simple insert statement (manual) Write-SQLData -query "INSERT INTO Table1 (Name,Age,Data1) VALUES ('admin',66,'yyyy')" -conn $conn # read SQL data again, new rows shows up. Read-SQLData -query "SELECT * FROM Table1" -conn $conn id Name Age Data1 -- ---- --- -------- 1 aaa 33 ccc 2 xxxx 26 new data 3 kege 33 xxx 4 admin 66 yyyy # create a new object, with properties named as table columns $obj = "" | select Name, Age, Data1 $obj.Name = 'testa' $obj.Age = 99 $obj.Data1 = "xxx" # write the object to the sql db, SQL is generated by module Write-SQLObject -object $obj -table Table1 -conn $conn # read SQL data again, now record created with data from the object Read-SQLData -query "SELECT * FROM Table1" -conn $conn id Name Age Data1 -- ---- --- ----- 1 aaa 33 ccc 2 xxxx 33 abcd 3 kege 33 xxx 4 admin 66 yyyy 5 testa 99 xxx # read SQL data to object (datatable) $data = Read-SQLData -query "SELECT * FROM Table1" -conn $conn # assign row[1] as new object, and change some of its properties $record = $data[1] $record.Age = 26 $record.Data1 = "new data" # send the changed object back to the module -mode Update, tells the module to generate a update statement. Write-SQLObject -object $obj1 -table Table1 -conn $conn -mode Update -objectKeys name -IgnoreColumns id # row have been updated Read-SQLData -query "SELECT * FROM Table1" -conn $conn id Name Age Data1 -- ---- --- ----- 1 aaa 33 ccc 2 xxxx 26 new data 3 kege 33 xxx 4 admin 66 yyyy 5 testa 99 xxx # create a new object, data1 set to Get-Date. $obj1 = "" | select Name, Age, Data1 $obj1.Name = "hugo" $obj1.Age = 12 $obj1.Data1 = (get-date).ToString() # use mode InsertOrUpdate to insert the object into the database, the module will check a row with the name 'hugo' exists and update it, or if not, insert a new record. # the module wraps the insert and update in a TSQL IF statement, so its up to the SQL server to do the check. Write-SQLObject -object $obj1 -conn $conn -table Table1 -mode InsertOrUpdate -objectKeys Name # new record inserted Read-SQLData -query "SELECT * FROM Table1" -conn $conn id Name Age Data1 -- ---- --- ----- 1 aaa 33 ccc 2 xxxx 26 new data 3 kege 33 xxx 4 admin 66 yyyy 5 testa 99 xxx 6 hugo 12 20-10-2016 13:00:44 # update the Data1 with a new Get-Date, updated time stamp. $obj1.Data1 = (get-date).ToString() # use same command as before Write-SQLObject -object $obj1 -conn $conn -table Table1 -mode InsertOrUpdate -objectKeys Name # now a Name = 'hugo' was found, and updated with the new data in the data1 property Read-SQLData -query "SELECT * FROM Table1" -conn $conn id Name Age Data1 -- ---- --- ----- 1 aaa 33 ccc 2 xxxx 26 new data 3 kege 33 xxx 4 admin 66 yyyy 5 testa 99 xxx 6 hugo 12 20-10-2016 13:03:43 # Get records again $data = Read-SQLData -query "SELECT * FROM Table1" -conn $conn # select the records we want to delete $toDelete = $data | where Data1 -eq 'xxx' # delete the records by their id Write-SQLObject -object $del -conn $conn -table Table1 -mode Delete -objectKeys id # records are deleted Read-SQLData -query "SELECT * FROM Table1" -conn $conn id Name Age Data1 -- ---- --- ----- 1 aaa 33 ccc 2 xxxx 26 new data 4 admin 66 yyyy 6 hugo 12 20-10-2016 13:03:43 # SQL in Powershell, made easy :) |