How To Create a DataTable In Powershell

      No Comments on How To Create a DataTable In Powershell

The DataTable is an extremely useful Object when working with different sources and types of data in SQL and using Powershell. The DataTable Class is packed with Methods to work with data coming, going relationships and more. Creating a DataTable is a simple as:

$Datatable = New-Object System.Data.DataTable

In this example we are focusing on running a SQL statement and loading the results into a new DataTable. We will create the table, columns and data-types for this DataTable and then the the results can be piped to a Format-Table or just display the results using the Out-Gridview command.

Create DataTable In Powershell

This is a simple T-SQL query to grab the existing user databases on a SQL instance. For this we use the Invoke-SQLCmd command.

$query = "SELECT 
    [name], 
    [create_date], 
    [is_read_only], 
    [state_desc],
    [recovery_model]
FROM master.sys.databases WHERE database_id > 4"

$databases = invoke-Sqlcmd -Query $query -ServerInstance 'ServerName\sql1'

The $databases variable is now loaded with our SQL result set and we need to create the DataTable, iterate through the results and load them into the Table in Powershell. Next we create the DataTable object:

## build a "data" table
$Datatable = New-Object System.Data.DataTable

Now that the objects is created you can type the variable name ($Datatable) and use the DOT notation to display the methods available.

You will see Rows, Namespace, PrimaryKey and more choices to explore. In this example we need to create some fields or columns and define them based on our SQL results.

$Datatable.Columns

DataTable Column Names

Let’s define the columns by matching them up with our SQL results. We are changing the column names slightly but you can use the same SQL field names for the column names if you prefer.

 $Datatable.Columns.Add("DBName") 
 $Datatable.Columns.Add("Created")
 $Datatable.Columns.Add("Read_Only")
 $Datatable.Columns.Add("State")
 $Datatable.Columns.Add("Recovery")

Notice that the fields correlate directly to one of the results from the SQL query. Since this is a virtual Powershell table we do not need to match the naming convention or the column names.

Let’s add a timestamp column to include today’s date and time: 

 $Datatable.Columns.Add("timestamp")

Loop Through The DataTable

With our DataTable defined we can now loop through the SQL results and add them. Use a foreach loop to do this.

As we loop through the results we need to use the “NewRow” method to assign the values. The first statement in the foreach is to create this.

foreach($db in $databases) {    
    $row = $Datatable.NewRow()

Now we assign the value for each column based on the SQL results record. Notice that we are using the SQL result column name or alias for the assignment.

foreach($db in $databases) {
    $row = $Datatable.NewRow()

    $row.DBName = $db.name
    $row.Created = $db.create_date
    $row.Read_Only = $db.is_read_only
    $row.State = $db.state_desc
    $row.Recovery = $db.recovery_model
    $row.timestamp = Get-Date -format 'yyyy-MM-dd HH:mm:ss'

and with the values assigned we finally “Add” a row to the DataTable.

foreach($db in $databases) {
    $row = $Datatable.NewRow()

    $row.DBName = $db.name
    $row.Created = $db.create_date
    $row.Read_Only = $db.is_read_only
    $row.State = $db.state_desc
    $row.Recovery = $db.recovery_model
    $row.timestamp = Get-Date -format 'yyyy-MM-dd HH:mm:ss'

    $Datatable.Rows.Add($Row)
}

DataTable & DataTypes

In the above code we are ignoring the datatypes and letting powershell handle it. When moving data to and from different databases you can run into issues with datatypes. If your dealing with Unix, Linux and Windows you might need to cast the results as something different on occasion.

The “Columns.Add” method does allow you to assign a type to the field when you create it. This is the same DataTable columns but with types defined:

 $Datatable.Columns.Add("DBName", "System.String") 
 $Datatable.Columns.Add("Created", "System.Datetime")
 $Datatable.Columns.Add("Read_Only", "System.Decimal")
 $Datatable.Columns.Add("State", "System.String")
 $Datatable.Columns.Add("Recovery", "System.Int32") 
 $Datatable.Columns.Add("timestamp", "System.Datetime")

Note: Using this method I ran into a CAST issue for the timestamp field.

Powershell Get-Date Format | Cast as Datetime

When preparing this example I ran into an issue with our “timestamp” field where it would not accept assigning the “Get-Date” value. This is supposed to be Datetime but Powershell kept giving me an error.

The “System.Datetime” datatype was not accepting the date and time from Get-Date but I found you can do the following to (db tales com) force converting the results to a Datetime that the DataTable will accept.

[Datetime](Get-Date -format 'yyyy-MM-dd HH:mm:ss')

We are casting the complete Get-Date statement (in parenthesis) as a Datetime and this seemed to work well. For applications using the default should be fine but be careful when dealing with complex or very large datasets.

Execute

At this point thte $DataTabe variable is loaded and you can pipe it to something like Format-Table or Out-Gridview.

$Datatable | Format-Table

Conclusion

We used only single SQL statement for this example but you can just as easily create a DataTable and load it using many SQL results from different instances or clusters. You can also query this data as you would any other object in Powershell:

$Datatable | SELECT DBName, State, Timestamp | Format-Table

$Datatable | SELECT DBName, State, Timestamp | Where-Object {$_.DBName -like '*db_name_here*'}

Full Example Code

## Just the User databases
$query = "SELECT 
    [name], 
    create_date, 
    is_read_only, 
    state_desc,
    recovery_model
FROM master.sys.databases WHERE database_id > 4"

$databases = invoke-Sqlcmd -Query $query -ServerInstance 'ServerName\sql1' 

## build a "data" table
 $Datatable = New-Object System.Data.DataTable

 $Datatable.Columns.Add("DBName", "System.String") 
 $Datatable.Columns.Add("Created", "System.Datetime")
 $Datatable.Columns.Add("Read_Only", "System.Decimal")
 $Datatable.Columns.Add("State", "System.String")
 $Datatable.Columns.Add("Recovery", "System.Int32") 
 $Datatable.Columns.Add("timestamp", "System.Datetime")

foreach($db in $databases) {
    
    $row = $Datatable.NewRow()

    $row.DBName = $db.name
    $row.Created = $db.create_date
    $row.Read_Only = $db.is_read_only
    $row.State = $db.state_desc
    $row.Recovery = $db.recovery_model
    $row.timestamp = [Datetime](Get-Date -format 'yyyy-MM-dd HH:mm:ss')

    $Datatable.Rows.Add($Row)

}
CLS
$Datatable.Columns | Format-Table
# $Datatable.Columns | Out-Gridview