Powershell selecting from SQL Server

I am just getting into PowerShell, and today a work colleague of mine stated he has a table in SQL server that he needed to examine. The table contained names of files on the disk drive. He then needed to examine the names of the files in the table, and if the file existed rename […]

I am just getting into PowerShell, and today a work colleague of mine stated he has a table in SQL server that he needed to examine. The table contained names of files on the disk drive. He then needed to examine the names of the files in the table, and if the file existed rename it to include todays date. He asked if this could be done in PowerShell, at lunch I decided to try it and came up with this:

SQL

Say I have this table

<pre class="brush: sql; gutter: false; title: ; notranslate"> USE [SachaTest] GO /****** Object: Table [dbo].[Files] Script Date: 10/22/2014 13:59:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Files]( [FilePath] [nvarchar](max) NOT NULL ) ON [PRIMARY] GO

Which was populated like this

<pre class="brush: sql; gutter: false; title: ; notranslate"> INSERT INTO [SachaTest].[dbo].[Files] ([FilePath]) VALUES ('C:\Users\barbers\Desktop\PowerShellTest\dummy1.txt') GO INSERT INTO [SachaTest].[dbo].[Files] ([FilePath]) VALUES ('C:\Users\barbers\Desktop\PowerShellTest\dummy2.txt') GO

Where I have the following directory on disk

I then came up with the following PowerShell file to carry out the work as described in the opening paragraph of this article

<pre class="brush: powershell; gutter: false; title: ; notranslate"> <# Establish SQL connection, and grab the stuff from the "Files" table #> $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source=omnidev;Initial Catalog=SachaTest; Integrated Security=True;Timeout=180;MultipleActiveResultSets=true;" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "select * from Files" $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $SqlConnection.Close() <# Processes each DataRow handed to it, where it will grab the FilePath" column value within the DataRow and shall create a new file in the format of currentFilenameDDMMYY #> Function ProcessFile (){ Process { # use the column called "FilePath" to grab the file name from within the DataRow $fileOnDisk = New-Object -TypeName System.IO.FileInfo($_["FilePath"]) write-host "full name is : " + $fileOnDisk.FullName $datePartForFile = (Get-Date -format d).Replace("/","") $justFileName = [System.IO.Path]::GetFileNameWithoutExtension($fileOnDisk.FullName) $newFileName = $fileOnDisk.DirectoryName + '\' + $justFileName + '_' + $datePartForFile + $fileOnDisk.Extension write-host "new file name is : " + $newFileName If (Test-Path $newFileName){ Remove-Item $newFileName } [System.IO.File]::Copy($fileOnDisk.FullName, $newFileName); } } # Skip null objects filter filter Skip-Null { $_|?{ $_ } } <# Loop through the DataSet.Tables[0] (where this will be the Files table, which only has one column called "FilePath" then process each file by calling the "ProcessFile" function #> $DataSet.Tables[0] | Select-Object $_.Rows | Skip-Null | ProcessFile

Which when run gives the following results

 

source collected from :http://www.codeproject.com/Articles/832223/Powershell-selecting-from-SQL-Server