Read and write SQL table with powershell

$SQLDB = "ServerAutomation"
$sDate = Get-Date
 
function Get-SqlParms {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SQLConnection.ConnectionString = "Server=$SQLSERVER;Database=$SQLDB;Integrated Security=True" 
$SqlCommandText = "select * from FileMoveParms" 
$SQLConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand($SqlCommandText, $SQLConnection)
	$reader = $SqlCmd.ExecuteReader()
	while ($reader.read())
	{ $sServer = $reader["server"].Tostring()
		$sPath = $reader["path"].tostring()
		$sSearchS = $reader["searchstring"].tostring()
		$scopydest = $reader["copydest"].tostring()
						
		$UNCPath = "`"\\$sServer\$sPath`""
						
		$sSearchs = "`"$sSearchs`""
		$sCopydest = "`"$sCopydest`""
				
	}
# for debugging
#Write-Host "$sServer", "$sPath", "$sSearchs", "$scopydest"
#Write-Host $UNCPath
}
 
 
function Log-ToSQL
{
	$SQLCn = New-Object System.Data.SqlClient.SqlConnection
	$SQLCn.ConnectionString = "Server=$SQLServer;Database=$SQDB;Integrated Security=True"
	$SQLCn.Open()
	$SQLCMD = $SQLCn.CreateCommand() 
	$SQLCMD.CommandText = " INSERT Into $SQLLogs (server, path, filename, copydate, copiedto) values ('$sServer', '$_.Path', '$_.item', '$sDate', '$sCopyDest')"
 
	$Results = $SQLCMD.ExecuteNonQuery()
	$SQLCN.Close()
}
 
Get-Sqlparms
 
gci $UNCPath -recurse | Select-String $sSearchs -List | % { Copy-Item -Path $_.Path -Destination $sCopyDest } | % {Log-ToSql}
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:

$SqlServer = "SomeServer"
$SqlDB = "ServerAutomation"
# This returns an Object not a string. Pulling the DateTime string from the object.
$Date = (Get-Date).DateTime 
 
$SqlConnection = New-Object Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDB;Integrated Security=True" 
 
$SqlCommandText = "SELECT * FROM FileMoveParms" 
$SqlConnection.Open()
$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
 
$Reader = $SqlCmd.ExecuteReader()
 
While ($Reader.Read())
{
  $sServer = $Reader["server"].Tostring()
  $sPath = $Reader["path"].tostring()
  $sSearchS = $Reader["searchstring"].tostring()
  $scopydest = $Reader["copydest"].tostring()
                                                
  $UNCPath = "\\$sServer\$sPath"
                                                
  $sSearchs = "$sSearchs"
  $sCopydest = "$sCopydest"
 
  Get-ChildItem $UNCPath -Recurse | Select-String -Pattern $sSearchS -List | %{
    Copy-Item -Path $_.Path -Destination $sCopyDest
 
    $SqlCmd.CommandText = "INSERT INTO $SQLLogs (server, path, filename, copydate, copiedto) VALUES ('$sServer', '$_.Path', '$_.FileName', '$sDate', '$sCopyDest')"
    $Results = $SqlCmd.ExecuteNonQuery()
  }
}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

$SqlServer = "SomeServer"
$SqlDB = "ServerAutomation"
# This returns an Object not a string. Pulling the DateTime string from the object.
$Date = (Get-Date).DateTime 
 
$SqlConnection = New-Object Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDB;Integrated Security=True" 
 
$SqlCommandText = "SELECT * FROM FileMoveParms" 
$SqlConnection.Open()
$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
 
$Reader = $SqlCmd.ExecuteReader()
 
$SqlData = @()
While ($Reader.Read())
{
  $SqlData += $Reader | Select-Object `
    @{n='Server';e={ $_["server"].ToString() }}, `
    @{n='Path';e={ $_["path"].ToString() }}, `
    @{n='SearchString';e={ $_["searchstring"].ToString() }}, `
    @{n='CopyDest';e={ $_["copydest"].ToString() }}
}
 
$Reader.Close()
 
ForEach ($Item in $SqlData) 
{
  Get-ChildItem "\\$($Item.Server)\$($Item.Path)" -Recurse | Select-String -Pattern $Item.SearchString -List | %{
    Copy-Item -Path $_.Path -Destination $Item.CopyDest
 
    $SqlCmd.CommandText = "INSERT INTO $SQLLogs (server, path, filename, copydate, copiedto) VALUES ($Item.Server, '$_.Path', '$_.FileName', $Date, $Item.CopyDest)"
    $Results = $SqlCmd.ExecuteNonQuery()
  }
}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:

$SqlServer = "SomeServer"
$SqlDB = "ServerAutomation"
# This returns an Object not a string. Pulling the DateTime string from the object.
$Date = (Get-Date).DateTime 
 
$SqlConnection = New-Object Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDB;Integrated Security=True" 
 
$SqlCommandText = "SELECT * FROM FileMoveParms" 
$SqlConnection.Open()
$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
 
$Reader = $SqlCmd.ExecuteReader()
 
$SqlData = @()
While ($Reader.Read())
{
  $SqlData += "" | Select-Object `
    @{n='Server';e={ $Reader["server"].ToString() }}, `
    @{n='Path';e={ $Reader["path"].ToString() }}, `
    @{n='SearchString';e={ $Reader["searchstring"].ToString() }}, `
    @{n='CopyDest';e={ $Reader["copydest"].ToString() }}
}
 
$Reader.Close()
 
ForEach ($Item in $SqlData) 
{
  Get-ChildItem "\\$($Item.Server)\$($Item.Path)" -Recurse | Select-String -Pattern $Item.SearchString -List | %{
    Copy-Item -Path $_.Path -Destination $Item.CopyDest
 
    $SqlCmd.CommandText = "INSERT INTO $SQLLogs (server, path, filename, copydate, copiedto) VALUES ($Item.Server, '$_.Path', '$_.FileName', $Date, $Item.CopyDest)"
    $Results = $SqlCmd.ExecuteNonQuery()
  }
}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:

$SqlServer = "SomeServer"
$SqlDB = "ServerAutomation"
# This returns an Object not a string. Pulling the DateTime string from the object.
$Date = (Get-Date).DateTime 
 
$SqlConnection = New-Object Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDB;Integrated Security=True" 
 
$SqlCommandText = "SELECT * FROM FileMoveParms" 
$SqlConnection.Open()
$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
 
$Reader = $SqlCmd.ExecuteReader()
 
$SqlData = @()
While ($Reader.Read())
{
  $SqlData += "" | Select-Object `
    @{n='Server';e={ $Reader["server"].ToString() }}, `
    @{n='Path';e={ $Reader["path"].ToString() }}, `
    @{n='SearchString';e={ $Reader["searchstring"].ToString() }}, `
    @{n='CopyDest';e={ $Reader["copydest"].ToString() }}
}
 
$Reader.Close()
 
ForEach ($Item in $SqlData) 
{
  Get-ChildItem "\\$($Item.Server)\$($Item.Path)" -Recurse | Select-String -Pattern $Item.SearchString -List | %{
    Copy-Item -Path $_.Path -Destination $Item.CopyDest
 
    $SqlCmd.CommandText = "INSERT INTO $SQLLogs (server, path, filename, copydate, copiedto) VALUES ('$($Item.Server)', '$_.Path', '$($_.FileName)', '$Date', '$($Item.CopyDest)')"
 
    # To confirm the query...
    Write-Host $SqlCmd.CommandText
 
    $Results = $SqlCmd.ExecuteNonQuery()
  }
}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:

ForEach ($Item in $SqlData) 
{
  Get-ChildItem "\\$($Item.Server)\$($Item.Path)" -Recurse | Select-String -Pattern $Item.SearchString -List | %{
    Copy-Item -Path $_.Path -Destination $Item.CopyDest
 
    Write-Host "Server:      $($Item.Server)"
    Write-Host "Path:        $($_.Path)"
    Write-Host "FileName:    $($_.FileName)"
    Write-Host "Date:        $Date"
    Write-Host "Destination: $($Item.CopyDest)"
 
    $SqlCmd.CommandText = "INSERT INTO $SQLLogs (server, path, filename, copydate, copiedto) VALUES ('$($Item.Server)', '$_.Path', '$($_.FileName)', '$Date', '$($Item.CopyDest)')"
 
    # To confirm the query...
    # Write-Host $SqlCmd.CommandText
 
    $Results = $SqlCmd.ExecuteNonQuery()
  }
}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

$conn = New-Object system.data.sqlclient.sqlconnection("data source=someserver; initial catalog=ServerAutomation; integrated security=sspi")
$conn.open()
$date = (Get-Date -format g)
$cmd = $conn.createcommand()
$cmd.commandtext = "INSERT FileMoveLogs VALUES ('server', 'path', 'filename', '$date', 'copiedto')"
$cmd.executenonquery()
$conn.close()
                                              

1:
2:
3:
4:
5:
6:
7:

ForEach ($Item in $SqlData) 
{
  Get-ChildItem "\\$($Item.Server)\$($Item.Path)" -Recurse | Select-String -Pattern $Item.SearchString -List | %{
    Copy-Item -Path $_.Path -Destination $Item.CopyDest
 
    $SqlCmd.CommandText = "INSERT INTO $SQLLogs (server, path, filename, copydate, copiedto) VALUES ('$($Item.Server)', '$($_.Path)', '$($_.FileName)', '$Date', '$($Item.CopyDest)')"
 
    # To confirm the query...
    Write-Host $SqlCmd.CommandText
 
    $Results = $SqlCmd.ExecuteNonQuery()
  }
}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

$SqlServer = "SomeServer"
$SqlDB = "ServerAutomation"
$SqlLogs = "FileMoveLogs"
# This returns an Object not a string. Pulling the DateTime string from the object.
$Date = (Get-Date -format g)
 
$SqlConnection = New-Object system.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDB;Integrated Security=True"
$SqlConnection2 = New-Object system.Data.SqlClient.SqlConnection ("Data Source=$SqlServer; Initial Catalog=$SqlDB; Integrated Security=SSPI")
 
$SqlCommandText = "SELECT * FROM FileMoveParms" 
$SqlConnection.Open()
$SqlConnection2.Open()
$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$sqlcmd2 = $SqlConnection2.createcommand()
$Reader = $SqlCmd.ExecuteReader()
 
$SqlData = @( )
while ($Reader.Read())
{
	$SqlData += "" | Select-Object `
	@{ n = 'Server' ; e = { $Reader["server"].ToString() } }, `
	@{ n = 'Path' ; e = { $Reader["path"].ToString() } }, `
	@{ n = 'SearchString' ; e = { $Reader["searchstring"].ToString() } }, `
	@{ n = 'CopyDest' ; e = { $Reader["copydest"].ToString() } }
}
 
$Reader.Close()
foreach ($Item in $SqlData)
{ 
Get-ChildItem "\\$($Item.Server)\$($Item.Path)" -Recurse | Select-String -Pattern $Item.SearchString -List | % {
	Copy-Item -Path $_.Path -Destination $Item.CopyDest
			 
	$SqlCmd2.CommandText = "INSERT INTO $SQLLogs (server, path, filename, copydate, copiedto) VALUES ('$($Item.Server)', '$($_.Path)', '$($_.FileName)', '$Date', '$($Item.CopyDest)')"
			 
	# To confirm the query...
			 
	$Results = $SqlCmd2.ExecuteNonQuery()
	}
}
$SqlConnection.close()
$SqlConnection2.close()
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:

$SqlServer = "SomeServer"
$SqlDB = "ServerAutomation"
$SqlLogs = "FileMoveLogs"
# This returns an Object not a string. Pulling the DateTime string from the object.
$Date = (Get-Date -format g)
 
$SqlConnection = New-Object system.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDB;Integrated Security=True"
$SqlConnection2 = New-Object system.Data.SqlClient.SqlConnection ("Data Source=$SqlServer; Initial Catalog=$SqlDB; Integrated Security=SSPI")
 
$SqlCommandText = "SELECT * FROM FileMoveParms" 
$SqlConnection.Open()
$SqlConnection2.Open()
$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$sqlcmd2 = $SqlConnection2.createcommand()
$Reader = $SqlCmd.ExecuteReader()
 
$SqlData = @( )
while ($Reader.Read())
{
  $SqlData += "" | Select-Object `
    @{ n = 'Server' ; e = { $Reader["server"].ToString() } }, `
    @{ n = 'Path' ; e = { $Reader["path"].ToString() } }, `
    @{ n = 'SearchString' ; e = { $Reader["searchstring"].ToString() } }, `
    @{ n = 'CopyDest' ; e = { $Reader["copydest"].ToString() } }
}
 
$Reader.Close()
foreach ($Item in $SqlData)
{ 
  Get-ChildItem "\\$($Item.Server)\$($Item.Path)" -Recurse | `
    Select-String -Pattern $Item.SearchString -List | % {
 
    If (Test-Path "$($Item.CopyDest)\$($_.FileName)")
    {
      # File Exists
      # Execute SQL Command here
    }
    Else
    {
      Copy-Item -Path $_.Path -Destination $Item.CopyDest
			 
      $SqlCmd2.CommandText = "INSERT INTO $SQLLogs (server, path, filename, copydate, " + `
        "copiedto) VALUES ('$($Item.Server)', '$($_.Path)', '$($_.FileName)', " + `
        "'$Date', '$($Item.CopyDest)')"
	
      $Results = $SqlCmd2.ExecuteNonQuery()
    }
  }
}
$SqlConnection.close()
$SqlConnection2.close()

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:

$SqlServer = "someserver"
$SqlDB = "ServerAutomation"
$SqlLogs = "FileMoveLogs"
# This returns an Object not a string. Pulling the DateTime string from the object.
$Date = (Get-Date -format g)
 
$SqlConnection = New-Object system.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDB;Integrated Security=True"
$SqlConnection2 = New-Object system.Data.SqlClient.SqlConnection("Data Source=$SqlServer; Initial Catalog=$SqlDB; Integrated Security=SSPI")
 
$SqlCommandText = "SELECT * FROM FileMoveParms" 
$SqlConnection.Open()
$SqlConnection2.Open()
$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$sqlcmd2 = $SqlConnection2.createcommand()
$Reader = $SqlCmd.ExecuteReader()
 
$SqlData = @( )
while ($Reader.Read())
{
	$SqlData += "" | Select-Object `
	@{ n = 'Server' ; e = { $Reader["server"].ToString() } }, `
	@{ n = 'Path' ; e = { $Reader["path"].ToString() } }, `
	@{ n = 'SearchString' ; e = { $Reader["searchstring"].ToString() } }, `
	@{ n = 'CopyDest' ; e = { $Reader["copydest"].ToString() } }
}
 
$Reader.Close()
foreach ($Item in $SqlData)
{
	Get-ChildItem "\\$($Item.Server)\$($Item.Path)" -Recurse | `
	Select-String -Pattern $Item.SearchString -List -exclude *.lnk* | % {
		 
		if (Test-Path "$($Item.CopyDest)\$($_.FileName)")
		{
			$i = 1 ; $Destination = "$($Item.CopyDest)\$($_.FileName)$i"
			do
			{
				$i++ ; $Destination = "$($Item.CopyDest)\$($_.FileName)$i"
			} until ( (Test-Path $Destination) -eq $False )
			Copy-Item -Path $_.Path -Destination $Destination
			$Date = (Get-Date -format g)
Write-Host $_.path
$SqlCmd2.CommandText = "INSERT INTO $SQLLogs (server, path, filename, copydate, " + `
"copiedto) VALUES ('$($Item.Server)', '$($_.Path)', '$($_.FileName)$i', " + `
"'$Date', '$($Item.CopyDest)')"
				
$Results = $SqlCmd2.ExecuteNonQuery()
		}
		else
		{
			Copy-Item -Path $_.Path -Destination $Item.CopyDest
			$Date = (Get-Date -format g)
			Write-Host $_.filename
			$SqlCmd2.CommandText = "INSERT INTO $SQLLogs (server, path, filename, copydate, " + `
			"copiedto) VALUES ('$($Item.Server)', '$($_.Path)', '$($_.FileName)', " + `
			"'$Date', '$($Item.CopyDest)')"
				
			$Results = $SqlCmd2.ExecuteNonQuery()
		}
	}
}
$SqlConnection.close()
$SqlConnection2.close()

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:

$SqlServer = "someserver"
$SqlDB = "ServerAutomation"
$SqlLogs = "FileMoveLogs"
# This returns an Object not a string. Pulling the DateTime string from the object.
$Date = (Get-Date -format g)
 
$SqlConnection = New-Object system.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDB;Integrated Security=True"
$SqlConnection2 = New-Object system.Data.SqlClient.SqlConnection("Data Source=$SqlServer; Initial Catalog=$SqlDB; Integrated Security=SSPI")
 
$SqlCommandText = "SELECT * FROM FileMoveParms" 
$SqlConnection.Open()
$SqlConnection2.Open()
$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$sqlcmd2 = $SqlConnection2.createcommand()
$Reader = $SqlCmd.ExecuteReader()
 
$SqlData = @( )
while ($Reader.Read())
{
	$SqlData += "" | Select-Object `
	@{ n = 'Server' ; e = { $Reader["server"].ToString() } }, `
	@{ n = 'Path' ; e = { $Reader["path"].ToString() } }, `
	@{ n = 'SearchString' ; e = { $Reader["searchstring"].ToString() } }, `
	@{ n = 'CopyDest' ; e = { $Reader["copydest"].ToString() } }
}
 
$Reader.Close()
foreach ($Item in $SqlData)
{
	Get-ChildItem "\\$($Item.Server)\$($Item.Path)" -Recurse | `
        ?{ $_.PsIsContainer -eq $False } | `
	Select-String -Pattern $Item.SearchString -List -exclude *.lnk* | % {
		 
		if (Test-Path "$($Item.CopyDest)\$($_.FileName)")
		{
			$i = 1 ; $Destination = "$($Item.CopyDest)\$($_.FileName)$i"
			do
			{
				$i++ ; $Destination = "$($Item.CopyDest)\$($_.FileName)$i"
			} until ( (Test-Path $Destination) -eq $False )
			Copy-Item -Path $_.Path -Destination $Destination
			$Date = (Get-Date -format g)
Write-Host $_.path
$SqlCmd2.CommandText = "INSERT INTO $SQLLogs (server, path, filename, copydate, " + `
"copiedto) VALUES ('$($Item.Server)', '$($_.Path)', '$($_.FileName)$i', " + `
"'$Date', '$($Item.CopyDest)')"
				
$Results = $SqlCmd2.ExecuteNonQuery()
		}
		else
		{
			Copy-Item -Path $_.Path -Destination $Item.CopyDest
			$Date = (Get-Date -format g)
			Write-Host $_.filename
			$SqlCmd2.CommandText = "INSERT INTO $SQLLogs (server, path, filename, copydate, " + `
			"copiedto) VALUES ('$($Item.Server)', '$($_.Path)', '$($_.FileName)', " + `
			"'$Date', '$($Item.CopyDest)')"
				
			$Results = $SqlCmd2.ExecuteNonQuery()
		}
	}
}
$SqlConnection.close()
$SqlConnection2.close()
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65: