In PowerShell ADO.NET is Still Your Friend

A colleague of mine recently asked me the best way to run queries against SQL Server from PowerShell. The scenario is that a software company can’t always install modules on customer systems, and when that software could be run from any number of hundreds of servers in a site, it’s usually best to work with “what’s in the box.” So, when working with SQL Server data, ADO.NET is still our friend. It runs natively in PowerShell without any special add-ins.

ADO.NET

The tool developers use most often to manage data in SQL Server is ADO.NET.  Since PowerShell is built on the .NET Framework, ADO.NET is always available to you, and can be a very powerful tool in meeting some of the “regularly scheduled ad-hoc requests” you’ll receive as a DBA.

ADO.NET is a set of libraries included with the Microsoft .NET Framework that can help you communicate with various data stores from .NET applications. It contains two groups of objects that you can use to manage data, connected objects and disconnected objects.

Connected Objects

The connected objects allow you to work with SQL Server (or another data source) using an active connection to your source.  Here’s a list of the objects you’ll use on a regular basis.

Connection Object – provides a connection to your data source, including all the properties necessary to manage that connection, such as login, password, timeout values, etc.

Command Object – represents a query against your database, a call to a stored procedure, or a direct request to return the contents of a specific table.

DataReader Object – returns your query results as quickly as possible.

Transaction Object – allows you to group a number of changes to your database and treat them as a single unit of work.  The Connection object has a BeginTransaction method that you can use to create Transaction objects

Parameter Object – allows you to specify parameters for stored procedures or parameterized queries.

DataAdapter Object – acts as a bridge between your database and the disconnected objects in the ADO.NET object model.

Disconnected Objects

The disconnected objects in ADO.NET essentially provide an in-memory database, allowing you to work with sets of data without going back to the data source when you have multiple uses for a set of data in your application.  The DataAdapter object is used to populate these objects initially.

DataSet Object – the container for a number of DataTable objects.  This is the object the DataAdapter’s Fill method populates.

DataTable Object – allows you to examine data through collections of rows and columns.  The DataSet contains one or more DataTable objects based on the resultset of the Command executed in the DataAdapter Fill method.

DataRow Object – provides access to the DataTable’s Rows collection.

DataColumn Object – corresponds to a column in your table.

Constraint Object – defines and enforces column constraints.

DataRelation Object – defines the relations between DataTables in the DataSet object.

DataView Object – allows you to examine DataTable data in different ways.

So, you can use the Connection object to connect to SQL Server.  Once connected, you can define your query (or call your stored procedure) using the Command object, using Parameter objects as necessary to provide your stored procedure arguments with the necessary data.  Then you can run your command using either the DataReader object (if results are large) or the DataAdapter object (if results can fit in memory).  If results are in memory, the DataAdapter Fill method fills a DataSet object.  The DataSet’s Tables collection contains DataTable objects, and the DataTable’s Rows collection contains DataRow objects.  While iterating through the DataRow objects, the DataRow’s Columns collection contains the Item objects that you’ll use in your script to do whatever work is necessary.

When managing a set of SQL Server instances I use an XML file containing the instances I choose to manage.  Here’s an example of the type of file I use.

<?xml version=”1.0″ standalone=”yes”?>
<NewDataSet>
<Servers>
<Name>SQLTBWS</Name>
<Server>SQLTBWS</Server>
<Integrated>true</Integrated>
</Servers>
<Servers>
<Name>SQLTBWS\INST01</Name>
<Server>SQLTBWS</Server>
<Instance>INST01</Instance>
<Integrated>true</Integrated>
</Servers>
<Servers>
<Name>SQLTBWS\INST02</Name>
<Server>SQLTBWS</Server>
<Instance>INST02</Instance>
<Integrated>true</Integrated>
</Servers>
</NewDataSet>

The DataSet object has a method called ReadXML which loads an XML file into a DataTable object.  It’s then easy to iterate through the DataTable and connect with each of my servers to perform whatever work is required.  The following code will load the XML listed above into a DataTable.

$ds = new-object "System.Data.DataSet" "dsServers"
$ds.ReadXml("C:\Demos\servers.xml")
$dtServer = new-object "System.Data.DataTable" "dtServers"
$dtServer = $ds.Tables[0]
$dtServer

The output of this script looks like this:

Returning XML Content

Query SQL Server with ADO.NET

Normally we’d use the Invoke-SQLCmd cmdlet, but this requires that the snap-ins shipped with SQL Server are installed on the system where the cmdlet is used.  While this is the best way to get data, sometimes it’s not practical.  (I’m often at client sites where I can’t install software on servers easily.)  ADO.NET is built into the .NET Framework, and it’s always available, so I frequently use it to query the servers I work with.

The first thing we need to do is to create a new SqlConnection object, using a standard SQL connection string.  Then we’ll create a DataSet object, and put our query into a string variable.  We pass the SqlConnection object and the query string as parameters to a new DataAdapter object, and invoke the DataAdapter’s Fill method.  Each resultset from the query is loaded into a DataTable within the DataSet, and we can parse each DataTable for the data we’re after.

$cn = new-object system.data.SqlClient.SqlConnection("Data Source=SQLTBWS\INST01;Integrated Security=SSPI;Initial Catalog=AdventureWorks");
$ds = new-object "System.Data.DataSet" "dsPersonData"
$q = @"
SELECT TOP 25  [ContactID],[FirstName],[LastName],[EmailAddress],[Phone]
FROM [AdventureWorks].[Person].[Contact]
"@
$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)
$da.Fill($ds)
$dtPerson = $ds.Tables[0]

The $dtPerson object now contains a DataTable with the results from the query.

Now, let’s say you’ve got stored procedures you want to run, and they don’t return any results, but they do have parameters you need to provide.  Not a problem.  In this case, after you create the SqlConnection object you need to define a SqlCommand object, giving it the name of the stored procedure and the SqlConnection object as arguments.  The SqlCommand object has a set of properties, one of which is the type, which you set to “StoredProcedure”.  For each parameter you need to create a SqlParameter object, set its type and value, and add it to the Parms collection of the SqlCommand object.  Once that’s done, invoke the ExecuteNonQuery method of the SqlCommand object and you’re done.

$ds = new-object System.Data.SqlClient.SqlCommand("[Analysis].[insLogicalDisk]", $cn)
$ds.CommandType = 'StoredProcedure'
$parm = new-object System.Data.SqlClient.SqlParameter ("@Name", [System.Data.SqlDbType]::VarChar, 30)
$parm.Value = $_.Name
$ds.Parameters.Add($parm) | Out-Null
$ds.ExecuteNonQuery()

Now, one of the really great things about SQL Server since its creation is the bulk copy utility (originally using the bcp utility, or by implementing the Bulk API in application programs.)  This utility allows data to be loaded rapidly into SQL Server and is a critical part of many ETL processes.  SQL Server currently provides this ability through the SQLBulkCopy method, but the source must be a DataTable object.

To get a PowerShell object containing data I want to load into SQL Server into a DataTable object can be challenging, but Marc van Orsouw, a PowerShell MVP, created a function called Out-DataTable, that does just that.  Here’s a link to the function: https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

I use this function to create a DataTable with the data to load, ensuring that the object columns exactly match the data structure of the destination table.  Once that’s done I create a SqlBulkCopy object, set the object’s DestinationTableName property to the name of the table to be loaded, and invoke the WriteToServer method.  This is the fastest way to get data into SQL Server.

$logdsk = gwmi -query "select * from Win32_LogicalDisk where DriveType=3" -computername $svr | select Name, FreeSpace, Size
$dtable = $logdsk | Out-DataTable
$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
$bc.DestinationTableName = "dbo.LogicalDisk"
$bc.WriteToServer($dtable)

Now, I mentioned earlier about the Invoke-SQLCMD cmdlet.  This is by far the easiest way to get data in and out of SQL Server in PowerShell.  Set a string variable to the query you want, and use the cmdlet. If you’re getting results back, assign the invocation of the cmdlet to a variable, and those results are returned as a PowerShell object.

$q = @'
SELECT [Name]
      ,[FreeSpace]
      ,[Size]
FROM [ServerAnalysis].[dbo].[LogicalDisk]
GO
'@
invoke-sqlcmd -ServerInstance 'SQLTBWS\INST01' -Database 'ServerAnalysis' -Query $q

Besides communicating directly with SQL Server, there are other data formats commonly used.

PowerShell supports a number of cmdlets supporting comma-separated values files.  Export-CSV creates a CSV file containing the values in the PowerShell object you send to it.  Import-CSV allows you to re-create objects from the CSV strings.  ConvertTo-CSV and ConvertFrom-CSV cmdlets convert .NET Framework objects to CSV strings.  Use the parameters for these cmdlets to specify delimeters, encoding, headers, etc. to get them to provide your desired results.

Similarly a significant amount of data is stored in XML files, and PowerShell has strong XML support.  ReadXml method reads data only, or both data and schema into a DataSet from an XML document.  Export-Clixml creates an XML-based representation of an object.  Import-Clixml imports a CLIXML File and creates corresponding objects.  ConvertTo-XML creates an XML-based representation of an object, and Select-XML finds text in an XML string or document.

Conclusion

Hopefully we’ve given you enough of an idea on how to work with data in PowerShell that you can start building the scripts you’ll find useful in your business environment.  Once you start putting some of these scripts into an automated process, like SQL Server Agent, you’ll find you have more time to be more effective in your job.

2 thoughts on “In PowerShell ADO.NET is Still Your Friend”

  1. Hi Allen, I’m the author of the DbData module which is on the PowerShell Gallery and is a great and simple way of doing this instead of creating all the different .NET classes over and over.

    It’s very logical to use, has a tonne of features, is very PowerShell-y, and way better than Invoke-Sqlcmd or Invoke-Sqlcmd2.

    It follows a Get-DbConnection | Get-DbCommand | Get-DbData pattern. Build your connection object or connection string (including using secure SqlCredentials), build your command (with parameters), execute the command / get your data the way you want it. All safe and parameterised and pipeline friendly.

    Using your example to get the top 25 contacts:

    $ds = New-DbConnection SQLTBWS\INST01 AdventureWorks | New-DbCommand “SELECT TOP 25 [ContactID],[FirstName],[LastName],[EmailAddress],[Phone] FROM [AdventureWorks].[Person].[Contact]” | Get-DbData -OutputAs DataSet

    Of course if you didn’t care about the DataSet…

    $dt = New-DbConnection SQLTBWS\INST01 AdventureWorks | New-DbCommand “SELECT TOP 25 [ContactID],[FirstName],[LastName],[EmailAddress],[Phone] FROM [AdventureWorks].[Person].[Contact]” | Get-DbData -OutputAs DataTable

    Why do I mention that? Because now you can delete or upsert data into the DataTable type with the Alter() function and a hashtable.

    # Renaming the first contact to have your name. Or upserting
    # (depending on the constraints).
    $dt.Alter(@{
    ContactID = 1,
    FirstName = ‘Allen’,
    LastName = ‘White’,
    })

    How about deleting all of the rows?
    $dt.Rows | ForEach-Object { $_.Delete() }
    $dt.Alter()

    Going back to your examples, this is how you execute your stored procedure… note the same use of safe parameterisation…

    New-DbConnection SQLTBWS\INST01 AdventureWorks | New-DbCommand “[Analysis].[insLogicalDisk]” -Parameter @{ Name = $_.Name } -CommandType “StoredProcedure” | Get-DbData -OutputAs NonQuery

    I don’t have an Out-DataTable but I do have a SqlBulkCopy that will interface with it:

    New-DbConnection SQLTBWS\INST01 AdventureWorks | New-DbBulkCopy -DataSet $dbData

    DbData also does a whole lot more. Transactions wrappers? Database retry blocks? And more… you got it. It’s all available on GitHub (and it even has a new logo in preparation for upping the version and documentation to DbData 2). I hope you check it out.

    1. Hi Cody,
      That’s great work, but remember my first comment. Sometimes you can’t load modules, you have to go with “what’s in the box”, so I wanted to remind people that ADO.NET is always there.
      Allen

Leave a Reply

Your email address will not be published. Required fields are marked *