Home > Microsoft SQL Server, Powershell > [Powershell] Executing an sql query and returning a disconnected data using Powershell

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


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
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: