Josh-CO Dev

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

Powershell – Extract Data from a Database

Leave a comment


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()
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s