Some functions to run queries against OLEDB data sources

OLEDBData.psm1

function Get-OLEDBData{
	<# 
    .SYNOPSIS 
        This function is used to retrieve data via an OLEDB data connection.
         
    .DESCRIPTION
        Retrieves data via an OLEDB data connection.
    
	.INPUTS
		System.String,System.String,System.String
         
    .OUTPUTS 
        System.Data.Datatable
         
    .PARAMETER ConnectionString
        The connection string. 
		Connection String for Excel 2007:
			"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
		Connection String for Excel 2003:
			"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=`"$filepath`";Extended Properties=`"Excel 8.0;HDR=Yes;IMEX=1`";"
		Excel query
			'select * from [sheet1$]'
		Informix
			"password=$password;User ID=$userName;Data Source=$dbName@$serverName;Persist Security Info=true;Provider=Ifxoledbc.2"
		Oracle
			"password=$password;User ID=$userName;Data Source=$serverName;Provider=OraOLEDB.Oracle"
		SQL Server
			"Server=$serverName;Trusted_connection=yes;database=$dbname;Provider=SQLNCLI;"

	.PARAMETER SQL
		The SQL statement to be executed to retrieve data.
		 
    .Example 
        PS C:\> $dt = Get-OLEDBData $connectionstring $sql
		PS C:\> $dt | % { Write-Host $_.COLUMN }
		
        ----------- 
        Description 
        Prints the value of COLUMN to the host 
    #>
	
	[CmdletBinding()]
	param(
		[Parameter(Position=0, Mandatory=$true)]
		[ValidateNotNullOrEmpty()]
		[System.String]
		$ConnectionString,
		
		[Parameter(Position=1, Mandatory=$true)]
		[ValidateNotNullOrEmpty()]
		[System.String]
		$SQL
	)
	

   $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
   $OLEDBConn.open()
   $readcmd = New-Object system.Data.OleDb.OleDbCommand($SQL,$OLEDBConn)
   $readcmd.CommandTimeout = '300'
   $da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)
   $dt = New-Object system.Data.datatable
   [void]$da.fill($dt)
   $OLEDBConn.close()
   return $dt
}

function Invoke-OleDbSql {
    <# 
    .SYNOPSIS 
        Execute a SQL statement, ignoring the result set.  Returns the number of rows modified by the statement (or -1 if it was not a DML staement)
         
    .DESCRIPTION
        This function executes a SQL statement, using the parameters provided and returns the number of rows modified by the statement. 
    
	.INPUTS
		System.String,System.String
         
    .OUTPUTS 
        Integer
         
    .PARAMETER ConnectionString
        The connection string. 
		Connection String for Excel 2007:
			"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
		Connection String for Excel 2003:
			"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=`"$filepath`";Extended Properties=`"Excel 8.0;HDR=Yes;IMEX=1`";"
		Excel query
			'select * from [sheet1$]'
		Informix
			"password=$password;User ID=$userName;Data Source=$dbName@$serverName;Persist Security Info=true;Provider=Ifxoledbc.2"
		Oracle
			"password=$password;User ID=$userName;Data Source=$serverName;Provider=OraOLEDB.Oracle"
		SQL Server
			"Server=$serverName;Trusted_connection=yes;database=$dbname;Provider=SQLNCLI;"

	.PARAMETER SQL
		The SQL statement to execute.
		 
    .Example 
        PS C:\> Invoke-OleDbSql $connectionstring $sql
    #>
    
    [CmdletBinding()]
	param(
		[Parameter(Position=0, Mandatory=$true)]
		[ValidateNotNullOrEmpty()]
		[System.String]
		$ConnectionString,
		
		[Parameter(Position=1, Mandatory=$true)]
		[ValidateNotNullOrEmpty()]
		[System.String]
		$SQL
	)
    
    $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
    $OLEDBConn.Open()
    $readcmd = New-Object system.Data.OleDb.OleDbCommand($SQL,$OLEDBConn)
    $readcmd.CommandTimeout = '300'
    $result = $cmd.ExecuteNonQuery()
    $OLEDBConn.Close()
    return $result
}

export-modulemember Get-OLEDBData
export-modulemember Invoke-OleDbSql