Josh-CO Dev

Solving the worlds problems one line of code at a time.


Leave a comment

Powershell – Extract Data from a Database

Another handy thing to do with powershell is extract data from a database. Like everything else Powershell, this is incredibly easy to do.

First you need to setup and connect to the database itself.

	#create our database connection
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$SqlConnection.ConnectionString = "Server=ServerName;Database=DatabaseName;Integrated Security=True"
	$SqlConnection.Open()

This should be pretty straight forward. Just replace servername and databasename with your information. This connection string is for sqlserver 2012, you can substitute in the connection string for any sql server database.

Next we need to set up our command and hook it up.

	
	#create and set up our sql command
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

        $SqlCmd.CommandText = "select * from dbo.table"
	$SqlCmd.Connection = $SqlConnection
        $SqlCmd.CommandTimeout = 0

It’s worth noting here that setting the commandtimeout to 0 overrides the default timeout, which I was having trouble with.

Next, just call the execute reader and load the results in a table.

	#execute our command
	$result = $SqlCmd.ExecuteReader()
	
	#load the results of our command into a datatable
	$table = new-object System.Data.DataTable
	$table.Load($result)

If you have ever programmed in .NET before, all of this should look very familiar. Now, you can do whatever you like with the data. In my case, I loop through it and assign the values to a variable so I can use them later.

	foreach($row in $table)
	{
		#set up our variables based on the data row
		$EmailAddress = $row.Item("Email")
                ...
        }

Also, don’t forget to close out your connection after you are done. This will not happen automatically.

        #close out our sql connection to prevent it from staying open. 
	$SqlConnection.Close()