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=''" $command.ExecuteScalar()

This code with write a record to the database,

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

This code will read records from the database,

$command.CommandText = "SELECT * FROM Table1 WHERE Email=''" $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,


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s