PowerShell, Just Bring It: Looping Through a DataTable – #TSQL2sDay

Thank you to Wayne Sheffield (blog | @DBAWayne) not only for hosting February’s T-SQL Tuesday #39, but for bringing The Rock into it. Wayne is writing a series about PowerShell and asked for others in the community to participate with a post about the most electrifying ways to use PowerShell with SQL Server.

Looping through a DataTable in PowerShell

How do you handle ad-hoc data management when you need to access two or more SQL Server boxes that aren’t set up or able to communicate directly?  Let’s say, for example, you need to read from the Wrestlers database on ServerA and use that data to make changes on ServerB’s Actors database. Writing a .NET console application to do basic data manipulation is overkill. However, if you have a solid .NET background and don’t really want to lay the smackdown on PowerShell, you can read one of my previous posts on how to essentially embed and run .NET code within a PowerShell script.

Now if you are one of the millions, and millions, of PowerShell fans, let me show you how you can tag team these servers and accomplish the same thing ‘the PowerShell way’:

#Create your SQL connection string, and then a connection to Wrestlers
$ServerAConnectionString = "Data Source=ServerA;Initial Catalog=Wrestlers;User Id=TheRock;PWD=PeoplesChamp"
$ServerAConnection = new-object system.data.SqlClient.SqlConnection($ServerAConnectionString);

#Create a Dataset to hold the DataTable from Wrestlers
$dataSet = new-object "System.Data.DataSet" "WrestlersDataset"
$query = "SET NOCOUNT ON;"
$query = $query + "SELECT WrestlerName, BeltsWon "
$query = $query + "FROM   dbo.ProWrestlers "
$query = $query + "WHERE  BeltsWon > 0; "

#Create a DataAdapter which you'll use to populate the DataSet with the results
$dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $ServerAConnection)
$dataAdapter.Fill($dataSet) | Out-Null

#Close the connection as soon as you are done with it
$ServerAConnection.Close()
$dataTable = new-object "System.Data.DataTable" "FormerChamps"
$dataTable = $dataSet.Tables[0]

$ServerBConnectionString = "Data Source=ServerB;Initial Catalog=Actors;User Id=TheRock;PWD=PeoplesChamp"
$ServerBConnection = new-object system.data.SqlClient.SqlConnection($ServerBConnectionString);
$ServerBConnection.Open()

#For every wrestler that has won a belt, update their Actor record to show that they are a FormerChampion
$dataTable | FOREACH-OBJECT {
  $cmd = new-object System.Data.SQLClient.SQLCommand
  $cmd.CommandText = "UPDATE dbo.MovieStars SET FormerChampion = 1 WHERE ActorName = @WrestlerName"
  $cmd.Connection = $ServerBConnection
  $cmd.Parameters.AddWithValue("@WrestlerName", $_.WrestlerName) | Out-Null
  $rowsUpdated = $cmd.ExecuteNonQuery()

  #write-host "WrestlerName: " $_.WrestlerName
}

$ServerBConnection.Close()

For those of you that are still with me, and have never been fans of The Rock, or professional wrestling…you don’t know what you’re missing.