SQL Integration in Your Powershell Scripts

For the windows administrators amongst us, there is a lot that Powershell can offer to help with managing your SQL infrastructure, however, what if you want to use SQL for what it does best? As a database....

I thought I'd just do a quick blog post about the 3 main methods I use for accessing SQL and pushing data in and out.

Warning: Before you issue any commands against your database, make sure you know what effect your T-SQL will have. I don't want any angry DBA's emailing me about lost data! To get to grips with this, I suggest you install a local copy of SQL Express and start having a play around with what a database can offer you.

Before we do this, we need to craft the connection string we're going to use to talk to the database. The format I use is either:

For SQL Auth:

$ConnectionString = "server=$DatabaseServerName;database=$DatabaseName;User Id=$UserID;Password=$Password;trusted_connection=False;"

For Integrated Auth:

$ConnectionString = "server=$DatabaseServerName;database=$DatabaseName;trusted_connection=true;"

To make creating this connection string a bit easier, perhaps you could wrap it in a function like this:

Function Create-SQLConnectionString([string]$ServerName,[string]$DatabaseName,[string]$UserName,[string]$Password,[Switch]$IntegratedAuth)
{
	If($IntegratedAuth)
	{
		$ConnectionString = "server=$ServerName;database=$DatabaseName;trusted_connection=true;"
 
	}
	Else
	{
		$ConnectionString = "server=$ServerName;database=$DatabaseName;User Id=$UserName;Password=$Password;trusted_connection=False;"
	}
 
	Return $ConnectionString
 
}

NOTE: If you're in a firewalled infrastructure, TCP 1433 may be allowed through but the SQL port discovery data may not so you may not be able to connect to your desired server. In which case, on a windows server go to START -> RUN -> CLICONFG (note the spelling on cliconfg, it's not a spelling mistake). Go to the Alias tab and select Add.. Here you can add a TCP/IP alias for your SQL server and deselect the "Dynamically determine port" check box to force connectivity to TCP 1433.

1 - A straight forward query...

In this sample I'm going to use the Data.SqlClient.DataAdapter class with a Data.DataTable. If your query only returns one result set (i.e. one table) then this is the easiest option, however, if your query returns multiple result sets (i.e. more than 1 table) then use a Data.DataSet instead.

Assuming that you're using the connection string function mentioned above, I'm going to run a simple select from "TableA"

$TheConnectionString = Create-SQLConnectionString -ServerName MyServer -DatabaseName MyDatabase -IntegratedAuth
$TheQuery = "Select * from TableA"
$TheDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($TheQuery,$TheConnectionString)
$TheDataTable = New-Object System.Data.DataTable
$TheDataAdapter.Fill($TheDataTable)
$TheDataAdapter.Dispose()

You will now be able to query $TheDataTable as a nice powershell object.

2 - Running a update, insert or delete statement...

Most of the time you'll probably find you're querying your databases, however, you may need to insert records, update records or even delete records. In this case, you're not looking to return data, you're just looking to issue a command, so in this sample i'm going to use a System.Data.SqlCommand object.

Unfortunately, the SQLCommand class doesn't have an overload which accepts a Connection String, it needs an actual connection, so I'll create one of those too.

In this sample, I'm going to set a field called "Deleted" to "1" in table where the "ID" of the Row equals 1 in TableA:

$TheConnectionString = Create-SQLConnectionString -ServerName MyServer -DatabaseName MyDatabase -IntegratedAuth
$TheQuery = "update TableA set Deleted = 1 where ID = 1"
$TheConnectionObj = New-Object System.Data.SqlClient.SqlConnection($TheConnectionString)
$TheCommand = New-Object System.Data.SqlClient.SqlCommand($TheQuery,$TheConnectionObj)
$TheConnectionObj.Open()
$TheCommand.ExecuteNonQuery()
$TheConnectionObj.Close()
$TheCommand.Dispose()
$TheConnectionObj.Dispose()

You can now re-use the select query from example 1 to check your data.

3 - Executing a stored procedure...

Now, you can execute a stored procedure using example 2, but it's not the best method. Firstly, you'll need to take a look at the spec of your stored procedure in SQL Management Studio to ascertain the input parameters and their types. You can then map those onto your script.

In this example, I'm going to call a stored procedure with a name of "MySproc" with two parameters:

@ParamA - This is a varchar (string) with a max length of 1024 characters.
@ParamB - This is an int

$TheConnectionString = Create-SQLConnectionString -ServerName MyServer -DatabaseName MyDatabase -IntegratedAuth
$TheQuery = "MySproc"
 
 
$TheConnection = New-Object system.Data.SqlClient.SqlConnection($TheConnectionString)
$TheCommand = New-Object system.Data.SqlClient.SqlCommand($TheQuery,$TheConnection)
 
$TheCommand.Parameters.Add("@ParamA", [System.Data.SqldbType]::VarChar,1024)
$TheCommand.Parameters.Add("@ParamB", [system.Data.SqldbType]::Int)
$TheCommand.Parameters["@ParamA"].value = "This is my input string"
$TheCommand.Parameters["@ParamB"].value = 100
 
$TheConnection.Open()
$TheCommand.ExecuteNonQuery()
$TheConnection.Close()
$TheCommand.Dispose
$TheConnection.Dispose()

For the different data types, you can find them all here

There are 1000's of ways you can now use these tools to help you with your scripting. I particularly like to use SQL to maintain process state and log errors when I'm writing multi-threaded powershell scripts. That way the controlling script can easily keep an eye on where it has got to, or if any problems have arisen. It's up to you how you use it.