PoshCode Archive  Artifact [6de1cf8556]

Artifact 6de1cf8556b514154651da80b8f4f346dabf2fd4949cd6200e98ee143de28df9:

  • File Get-OLEDBData.ps1 — part of check-in [26045bddce] at 2018-06-10 13:29:52 on branch trunk — Generic function to execute query and return DataTable from any OLEDB data source. Tested against Excel, Informix, Oracle and SQL Server sources. (user: Chad Miller size: 2033)

# encoding: ascii
# api: powershell
# title: Get-OLEDBData
# description: Generic function to execute query and return DataTable from any OLEDB data source. Tested against Excel, Informix, Oracle and SQL Server sources.
# version: 12.0
# type: script
# author: Chad Miller
# license: CC0
# function: Get-OLEDBData
# x-poshcode-id: 3715
# x-archived: 2016-06-20T19:05:42
# x-published: 2013-10-26T11:53:00
#
#
###########################################################################
# Get-OLEDBData
# --------------------------------------------
# Description: This function is used to retrieve data via an OLEDB data
#              connection.
#
# Inputs: $connectstring  - Connection String.
#         $sql            - SQL statement to be executed to retrieve data.
# 
# Usage: Get-OLEDBData <connction string> <SQL statement>
#
#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;"
###########################################################################
function Get-OLEDBData ($connectstring, $sql) {
   $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($connectstring)
   $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
}