Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Powershell selecting from SQL Server

, 22 Oct 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
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

image

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

image


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sacha Barber
Software Developer (Senior)
United Kingdom United Kingdom
I currently hold the following qualifications (amongst others, I also studied Music Technology and Electronics, for my sins)
 
- MSc (Passed with distinctions), in Information Technology for E-Commerce
- BSc Hons (1st class) in Computer Science & Artificial Intelligence
 
Both of these at Sussex University UK.
 
Award(s)

I am lucky enough to have won a few awards for Zany Crazy code articles over the years

  • Microsoft C# MVP 2014
  • Codeproject MVP 2014
  • Microsoft C# MVP 2013
  • Codeproject MVP 2013
  • Microsoft C# MVP 2012
  • Codeproject MVP 2012
  • Microsoft C# MVP 2011
  • Codeproject MVP 2011
  • Microsoft C# MVP 2010
  • Codeproject MVP 2010
  • Microsoft C# MVP 2009
  • Codeproject MVP 2009
  • Microsoft C# MVP 2008
  • Codeproject MVP 2008
  • And numerous codeproject awards which you can see over at my blog

Comments and Discussions

 
SuggestionPlease reformat the code sections PinmemberAlBundyLoves695hrs 56mins ago 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141022.2 | Last Updated 23 Oct 2014
Article Copyright 2014 by Sacha Barber
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid