Archive

Archive for April, 2012

[Powershell] Executing an sql query and returning a disconnected data using Powershell

April 17, 2012 Leave a comment

Below is a script that will execute an sql query and returns a disconnected data (DataTable) using Powershell.

<#
.SYNOPSIS
	Queries a data source and returns a disconnected data (DataTable)

.DESCRIPTION
	Queries a data source and returns a disconnected data (DataTable)

.NOTES
	File Name: Get-DbDataTable.ps1
	Author: Randy Aldrich Paulo

.EXAMPLE
	Get-DbDataTable "Data Source=.;Integrated Security=SSPI;Initial Catalog=DatabaseName" "SELECT TOP 10 * From Table1"
#>

function Get-DbDataTable
(
	[string]$connectionString,
	[string]$query,
	[System.Data.CommandType]$commandType=[System.Data.CommandType]::Text,
	[string]$providerName = "System.Data.SqlClient"	
)
{
	#Initialize
	$databaseFactory = [System.Data.Common.DbProviderFactories]::GetFactory($providerName)
	$dbConnection = $databaseFactory.CreateConnection()
	$dbConnection.ConnectionString = $connectionString

	#Create Command
	$dbCommand = $databaseFactory.CreateCommand()
	$dbCommand.Connection = $dbConnection
	$dbCommand.CommandText =$query
	$dbCommand.CommandType = $commandType

	#Create Data Adapter
	$dbAdapter = $databaseFactory.CreateDataAdapter();
	$dbAdapter.SelectCommand = $dbCommand

	$dataTable = New-Object System.Data.DataTable

	try
	{
		#Fill with Results
		$dbAdapter.Fill($dataTable)
	}
	catch { 
		throw 
		}
	finally { 
			if($dbConnection.ConnectionState -ne [System.Data.ConnectionState]::Closed) 
			{ 
				$dbConnection.Close() 
			}
		}
	return $dataTable
}
Advertisements
%d bloggers like this: