Leanpub: Publish Early, Publish Often

Writing to SQL Server

Saving data to SQL Server - versus Excel or some other contraption - is easy.

Assume that you have SQL Server Express installed locally. You’ve created in it a database called MYDB, and in that a table called MYTABLE. The table has ColumnA and ColumnB, which are both strings (VARCHAR) fields. And the database file is in c:\myfiles\mydb.mdf. This is all easy to set up in a GUI if you download SQL Server Express “with tools” edition. And it’s free!

 1 $cola = "Data to go into ColumnA"
 2 $colb = "Data to go into ColumnB"
 3 
 4 $connection_string = "Server=.\SQLExpress;AttachDbFilename=C:\Myfiles\mydb.mdf;Datab\
 5 ase=mydb;Trusted_Connection=Yes;"
 6 $connection = New-Object System.Data.SqlClient.SqlConnection
 7 $connection.ConnectionString = $connection_string
 8 $connection.Open()
 9 $command = New-Object System.Data.SqlClient.SqlCommand
10 $command.Connection = $connection
11 
12 $sql = "INSERT INTO MYTABLE (ColumnA,ColumnB) VALUES('$cola','$colb')"
13 $command.CommandText = $sql
14 $command.ExecuteNonQuery()
15 
16 $connection.close()

You can insert lots of values by just looping through the three lines that define the SQL statement and execute it:

 1 $cola = @('Value1','Value2','Value3')
 2 $colb = @('Stuff1','Stuff2','Stuff3')
 3 
 4 $connection_string = "Server=.\SQLExpress;AttachDbFilename=C:\Myfiles\mydb.mdf;Datab\
 5 ase=mydb;Trusted_Connection=Yes;"
 6 $connection = New-Object System.Data.SqlClient.SqlConnection
 7 $connection.ConnectionString = $connection_string
 8 $connection.Open()
 9 $command = New-Object System.Data.SqlClient.SqlCommand
10 $command.Connection = $connection
11 
12 for ($i=0; $i -lt 3; $i++) {
13   $sql = "INSERT INTO MYTABLE (ColumnA,ColumnB) VALUES('$($cola[$i])','$($colb[$i])'\
14 )"
15   $command.CommandText = $sql
16   $command.ExecuteNonQuery()
17 }
18 
19 $connection.close()

It’s just as easy to run UPDATE or DELETE queries in exactly the same way. SELECT queries use ExecuteReader() instead of ExecuteNonQuery(), and return a SqlDataReader object that you can use to read column data or advance to the next row.