I written a script to read a file, use GC to pull the content into an array and am attempting to write the data to SQL Server.  I am using this logic to try and pass each individual line through to a TSQL query that will insert all lines into the database.  Here is my script:

$Log = [IO.FileInfo] 'C:\folder\somefile.log'
$DateCreated = $Log.CreationTime
$DateModified = $Log.LastWriteTime
$LogDate = $Log.CreationTime
$LogLocation = $Log.DirectoryName
$LogName = $Log.Name
$LogType = $Log.Name.Substring(0,8)
$LineNumber = 1
$LogEntry = gc $Log
$conn = New-Object System.Data.SqlClient.SqlConnection("Server=SQLServer;Database=LogDB;Integrated Security=SSPI")
$conn.Open()

for($i = 1; $i -lt ($LogEntry.Count +1); $i++)
{
$cmd = $conn.CreateCommand()
$cmd.CommandText ="INSERT INTO [Management].[dbo].[Logging] ([DateCreated], [DateModified], [LogDate], [LogLocation], [LogName], [LogType], [LineNumber], [LogEntry]) VALUES ('$DateCreated', '$DateModified', '$LogDate', '$LogLocation', '$LogName', '$LogType', '$i', '$LogEntry[$i])'"
$cmd.ExecuteNonQuery()
}
$conn.Close()

When I run this, I get an error like this:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Line 1: Incorrect syntax near 'Audit 00:15:38 Processing Data: Log System 00:15:38 Processing file: C:\folder\somefile.log Info 00:15:38 Defaulting R'." At line:5 char:25 + $cmd.ExecuteNonQuery <<<< () + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException

When I tried to validate the error was the $LogEntry[$1] variable I used this:

for($i = 1; $i -lt ($LogEntry.Count +1); $i++)
{
Write-Host $i
Write-Host $LogEntry[$i]
}

This returns the correct data.  So, using a more SQL like approach I use this test:

for($i = 1; $i -lt ($LogEntry.Count +1); $i++)
{
Write-Host $i
Write-Host '$LogEntry[$i]' # as it would appear when embedded in the query
}

This does not work and just returns the literal string $LogEntry[$i] rather than the evaluated expression for each iteration of the loop. I also tried a backtick to no avail:

for($i = 1; $i -lt ($LogEntry.Count +1); $i++)
{
Write-Host $i
Write-Host `'$LogEntry[$i]`' # as it would appear when embedded in the query PLUS backticks
}
 
How can I pass the line to a TSQL query so it gets written to the log?  If there is a better way to approach this, please feel free to share snippets.