Powershell Query SQL database data

Beans are not familiar to SQL Server's Sqlps modules. Yesterday, I found a SQL module provided by Don Jones, which theoretically supports any ODBC driver database (MySQL, MSSQL, Oracle, etc.), and the principle is called. NET Framework for some of the underlying functions. It's easy to use and you don't need to learn new skills.

Https://technet.microsoft.com/zh-cn/magazine/hh855069.aspx

After downloading, found that this module actually only includes two functions, one for query, and the other to modify the deletion. All two functions have only 3 parameters, the ConnectionString, the SQL statement that needs to be executed, and whether it is SQL Server. The first format varies according to the database, so you can go to http://www.connectionstrings.com/to see the corresponding example. The second is a really executed SQL statement, the last one is actually a Boolean value, if the input of this parameter is true, no input is false.

Function get-databasedata {    [cmdletbinding ()]    param   (        [string] $connectionString,         [string] $query,         [switch] $isSQLServer     )     if  ($isSQLServer)  {         Write-Verbose  ' In sql server mode '           $connection  = New-Object -TypeName  '              system.data.sqlclient.sqlconnection    } else {         Write-Verbose  ' In oledb mode '           $connection  = New-Object -TypeName  '              system.data.oledb.oledbconnection    }    $ Connection. connectionstring =  $connectionString      $command  =  $connection. CreateCommand ()      $command. commandtext =  $query     if  ($isSQLServer)  {          $adapter  = New-Object -TypeName  '          System.Data.SqlClient.SqlDataAdapter  $command     } else {          $adapter  = New-Object -TypeName  '          System.Data.OleDb.OleDbDataAdapter  $command      }     $dataset  = New-Object -TypeName System.Data.DataSet      $adapter. Fill ($dataset)      $dataset. tables[0]     $connection. Close ()}function invoke-databasequery {    [ Cmdletbinding (supportsshouldprocess= $True,                    confirmimpact= ' low ')]    param  (         [string] $connectionString,         [string] $query,         [switch] $isSQLServer      )     if  ($isSQLServer)  {         Write-Verbose  ' In sql server mode '         $ connection = new-object -typename  '              System.Data.SqlClient.SqlConnection    } else {        &nBsp write-verbose  ' In oledb mode '          $connection  =  New-Object -TypeName  '              system.data.oledb.oledbconnection    }     $connection. connectionstring =  $connectionString      $command  =  $connection. CreateCommand ()      $command. commandtext =  $query     if  ($pscmdlet. shouldprocess ($query))  {          $connection. Open ()          $command. ExecuteNonQuery ()          $connection. Close ()     }

After you download the script, rename it and copy it to the corresponding path on the $env:psmodulepath.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/80/22/wKiom1c5Grzg-JcNAAAYwKRdmXY002.png "style=" float: none; "title=" 0.PNG "alt=" Wkiom1c5grzg-jcnaaaywkrdmxy002.png "/>

Now look at how to use it.

For example, the bean wants to query the backup record of a database. First login MSSQL2012 Express management interface, using T-SQL statement query to see what should be the effect.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/80/1F/wKioL1c5G6qTtdFkAAYUrR34kLY255.png "style=" float: none; "title=" 1.PNG "alt=" Wkiol1c5g6qttdfkaayurr34kly255.png "/>

Copy the T-SQL statement as an incoming string parameter, as shown below, calling the function of the first query

import-module db$connectionstring =  "Server=sydav01\sophos;database=sophos521;trusted_ Connection=true; " $query = "Select top 100s.database_name,cast (CAST (S.backup_size / 1000000 as int)  as varchar)  +  '   '  +  ' MB '  as bksize,cast (DATEDIFF (second,  s.backup_start_date,s.backup_finish_date)  as varchar (4))  +  '   '  +  ' Seconds '  timetaken,s.backup_start_date,cast (S.first_lsn as varchar ())  AS FIRST_LSN, CAST (S.last_lsn as varchar)  AS last_lsn,CASE s.[type] WHEN  ' D '   then  ' full ' when  ' I '  THEN  ' differential ' when  ' L '  THEN  ' Transaction log ' end as backuptype,s.server_name,s.recovery_modelfrom msdb.dbo.backupset swhere  S.database_name= ' SOPHOS521 ' "get-databasedata  -connectionstring  $ConnectionString  - Issqlserver -query&nbSP; $query  | ft 

The results are as follows

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/80/22/wKiom1c5GsaRBd7jAADs8a4sS50425.png "style=" float: none; "title=" 2.PNG "alt=" Wkiom1c5gsarbd7jaads8a4ss50425.png "/>

Successful acquisition, can be seen and seen in the graphical interface results are the same.

This article is from the "Mapo Tofu" blog, please be sure to keep this source http://beanxyz.blog.51cto.com/5570417/1773777

Powershell Query SQL database data