Using Powershell to read and write MSSQL

These examples are using a test database called EmailList with a table called Table1 which has three fields ‘username’,’email’ and ‘display name’.

This sample code will log in to MSSQL with your current logged in credentials,

$dataSource = "TESTSQLSERVER" $database = "EmailList" $connectionString = "Server=$dataSource;Database=$database;Integrated Security=True;" $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString $connection.Open() $command = $connection.CreateCommand()


This code will return the number of records with the specified email address,

$command.CommandText = "Select Count(*) FROM Table1 Where Email='testemail@test.com'" $command.ExecuteScalar()



This code with write a record to the database,

$command.CommandText = "INSERT INTO Table1 VALUES ('username','testemail@test.com','Users Name')" $command.ExecuteNonQuery()


This code will read records from the database,

$command.CommandText = "SELECT * FROM Table1 WHERE Email='testemail@test.com'" $reader = $command.ExecuteReader() While ($reader.Read()) {$reader[0];$reader[1];$reader[2]} $reader.Close()


Don’t forget to close your connection once you have finished,

$connection.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