Powershell query SQL database data

Douzi is not familiar with the sqlps module that comes with SQL Server. It was found yesterday that a SQL module provided by Don Jones theoretically supports any ODBC Driver-compatible database (MySQL, MSSQL, Oracle, etc.), based on calling some of the underlying functions of the .net framework. It is easy to use, and there is no need to learn new skills.

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

After downloading, it is found that this module only includes two functions, one for query and the other for modification and deletion. Both functions take only three parameters, namely connectionString, the SQL statement to be executed, and whether it is SQLServer or not. The format of the first one varies according to the database, you can go to http://www.connectionstrings.com/ to see the corresponding example. The second is the actual execution of the SQL statement, and the last one is actually a Boolean. If you enter this parameter, it is true, and if you do not enter it, it is false.

Function Get-DatabaseData {[CmdletBinding ()] param ([string] $connectionString, [string] $query [switch] $isSQLServer) if ($isSQLServer) {Write-Verbose'in SQLServer 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 SQLServer 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 ($pscmdlet.shouldprocess ($query)) {$connection.Open () $command.ExecuteNonQuery () $connection.close ()}}

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

Now let's see how to use it.

For example, Douzi wants to query the backup records of a certain database. First of all, log in to the management interface of MSSQL2012 Express and use the T-SQL statement to query to see what effect it should be.

Copy the T-SQL statement as the passed-in string parameter, as shown below, and call the function of the first query

Import-Module db$ConnectionString = "server=sydav01\ Sophos;database=sophos521;trusted_connection=True "$query=" SELECT TOP 100s.databasekeeper name cast (CAST (s.backup_size / 1000000 AS INT) AS VARCHAR (14)) +'+ 'MB' AS bkSize,CAST (DATEDIFF (second, s.backupstarting date date) AS VARCHAR (4) +' + 'Seconds' TimeTaken,s.backup_start_date,CAST (s.first_lsn AS VARCHAR (50)) AS first_lsn,CAST (s.last_lsn AS VARCHAR (50)) 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.databaseroomnameplates SOPHOS521' "Get-DatabaseData-connectionString $ConnectionString-isSQLServer-query $query | ft

The results are as follows

If you get it successfully, you can see the same result as you can see in the graphical interface.

Attachment: http://down.51cto.com/data/2367697