Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Create a new DataTable object based on an existing Microsoft SQL database table
function New-DataTableFromSqlTable{
<#
.SYNOPSIS
Create a new (empty) DataTable object based on a SQL Table.
.DESCRIPTION
Create a new (empty) DataTable object based on a SQL Table.
.PARAMETER DatabaseServer
The Hostname of the database server.
.PARAMETER DatabaseName
The name of the database
.PARAMETER TableName
The name of the table you wish to create as an empty DataTable object.
.EXAMPLE
$dataTable = New-DataTableFromSqlTable -DatabaseServer DBServer01 -DatabaseName DB01 -TableName Table01
Description
-----------
Will connect to DBServer01/DB01 and create a new DataTable object based on Table01 and save to $dataTable.
.NOTES
Name: New-DataTableFromSqlTable
Author: Øyvind Kallstad
Date: 18.02.2014
Version: 1.0
#>
[CmdletBinding()]
param(
[Parameter(Mandatory = $true)]
[Alias('Server')]
[string]$DatabaseServer,
[Parameter(Mandatory = $true)]
[string]$DatabaseName,
[Parameter(Mandatory = $true)]
[string]$TableName
)
try{
# connect to database server
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$dbSrv = New-Object Microsoft.SqlServer.Management.SMO.Server $DatabaseServer
# get the database
$db = New-Object Microsoft.SqlServer.Management.SMO.Database
$db = $dbSrv.Databases[$DatabaseName]
# get the table
$table = New-Object Microsoft.SqlServer.Management.SMO.Table
$table = $db.Tables[$TableName]
# get table columns
$columns = New-Object Microsoft.SqlServer.Management.SMO.Column
$columns = $table.Columns
# create a datatable - this will be our returned object
$dt = New-Object System.Data.DataTable($TableName)
# an array to hold primary keys
$keys = @()
# iterate through each column
foreach ($column in $columns){
# convert SQL datatypes to .NET datatypes
switch ($column.DataType.Name){
'tinyint' {$dataType = 'Byte'}
'binary' {$dataType = 'Byte[]'}
'varbinary' {$dataType = 'Byte[]'}
'image' {$dataType = 'Byte[]'}
'timestamp' {$dataType = 'Byte[]'}
'bit' {$dataType = 'Boolean'}
'smallint' {$dataType = 'Int16'}
'int' {$dataType = 'Int32'}
'bigint' {$dataType = 'Int64'}
'real' {$dataType = 'Single'}
'float' {$dataType = 'Double'}
'decimal' {$dataType = 'Decimal'}
'numeric' {$dataType = 'Decimal'}
'money' {$dataType = 'Decimal'}
'smallmoney' {$dataType = 'Decimal'}
'time' {$dataType = 'TimeSpan'}
'date' {$dataType = 'DateTime'}
'datetime' {$dataType = 'DateTime'}
'datetime2' {$dataType = 'DateTime'}
'smalldatetime' {$dataType = 'DateTime'}
'xml' {$dataType = 'Xml'}
'nchar' {$dataType = 'String'}
'char' {$dataType = 'String'}
'nvarchar' {$dataType = 'String'}
'varchar' {$dataType = 'String'}
'ntext' {$dataType = 'String'}
'text' {$dataType = 'String'}
DEFAULT {$dataType = 'string'}
}
# create a datacolumn object
$dtColumn = New-Object System.Data.DataColumn ($column.Name), ($dataType)
$dtColumn.AllowDBNull = $column.Nullable
if ($column.Identity){
$dtColumn.AutoIncrement = $true
$dtColumn.AutoIncrementSeed = $column.IdentitySeed
$dtColumn.AutoIncrementStep = $column.IdentityIncrement
}
# collect keys
if ($column.InPrimaryKey){
$keys += $dtColumn
}
# add the column to the datatable
$dt.Columns.Add($dtColumn)
}
# add primary keys
$dt.PrimaryKey = $keys
}
# catching exceptions
catch{
if ($_.Exception.InnerException){
Write-Warning $_.Exception.InnerException.Message
}
else{
Write-Warning $_.Exception.Message
}
exit
}
# the finished datatable object
Write-Output @(,($dt))
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment