596

Downloads

164

Downloads of 1.13

2017-02-11

Last published

Write-ObjectToSQL

1.13

Writes an object into a database table. If the table does not exist it will be created based on the properties of the object. For every property of the object a column will be created. The data type for each column will be converted from .Net data types into SQL Server data types.

Not all data types are supported. Unsupported data types will be ignored (but can be listed). If several objects are sent through the pipeline only the first object will be used for creating the template for the table.

Make sure that all objects in the pipeline have the exact same properties (this is usually the case). While creating the table the script will also add two default columns. One called 'id' which is a regular auto counter (integer which increases with 1 for every row) and another column called 'inserted_at' which will have a default value of GetDate() which represents the timestamp for when the row was inserted. If a property is named the same as one of these default columns then a 'x' will be added before the name of those columns to avoid duplication. (if propertyname=id, then propertyname=xid, etc.)

Hashtables are handled slightly different. When using hashtables the script will simply use the keys as columns.

Keep in mind that properties on the objects are used. Some objects, like strings, might only have a length property but what you really want to insert into the table is the value of the string.

The following command would generate a table with one column called Length which would contain the length of the strings (probably not what you want):

'oink','meo' | Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName myTable

The following command is a better way to do it. Instead of piping the strings directly you should create custom objects or, as in this example, hash tables. This will generate a table with a column called 'text' which will contain the values 'oink' and 'meo':

@{'text'='oink'}, @{'text'='meo'} | Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName myTable

Another thing to note is that this script will only take Property and NoteProperty into consideration. So for example ScriptProperty and ParameterizedProperty will be ignored. You can verify your objects with the Get-Member cmdlet and check the MemberType.

Currently the script supports the following data types:

Int32

UInt32

Int16

UInt16

Int64

UInt64

long

int

Decimal

Single

Double

Byte

SByte

String

DateTime

TimeSpan

datetime

string

bool

Boolean

GUID

Inspect

PS> Save-Module -Name Write-ObjectToSQL -Path <path>

Install

PS> Install-Module -Name Write-ObjectToSQL

Deploy

See Documentation for more details.

Release Notes

Added logic to handle System.Nullable data types (thanks to beanska on GitHub for adding this)
Improved the GUID support to use uniqueidentifier when creating the table
Added the optional parameter PrimaryKey (thanks to lw-schick on GitHub for this idea)
When PrimaryKey is used, that column will be set to NOT NULL when creating the table
Added the SchemaName parameter to select which schema the table should have (thanks to lw-schick on GitHub for adding this)
Fixed a bug where zeroes were sometimes treated as null values (thanks to acheung456 in GitHub for reporting this)

Owners

Authors

John Roos

Copyright

(c) 2017 John Roos. All rights reserved.

FileList

Show

Tags

Cmdlets

Functions

DSC Resources

This module has no DSC resources.

Workflows

This module has no workflows.

Role Capabilities

This module has no role capabilities.

Dependencies

This module has no dependencies.

Minimum PowerShell version

4.0

Version History

Version Downloads Last updated
Write-ObjectToSQL 1.10 293 Tuesday, March 15 2016
Write-ObjectToSQL 1.8 139 Monday, August 03 2015