This lesson is on how to perform SQL Server table inserts, updates, deletes and selects with PowerShell.
The more I do with PowerShell, the more excited I get about it. Ultimately PowerShell can be used to build complete turn-key applications. Of course for that you need a GUI. You can build web form and HTML front ends for your programs. However, I want to keep this example as simple as possible to focus on how to do database maintenance with PowerShell.
The first thing to note is that I am going to use the SQLPS module to communicate to the database. Usually this is installed on your system. You can get more information about SQLPS at:
http://msdn.microsoft.com/en-us/library/hh231286.aspx
Below is the whole set of working code. This was written and tested with version 2.0 of PowerShell and SQL Server 2008 R2.
First, you'll need the table so here's the create table statement...
CREATE TABLE [dbo].[student](
[ID] [int] identity(1,1) primary key not NULL,
[FNAME] [varchar](100) NULL,
[LNAME] [varchar](100) NULL,
[AGE] [int] NULL
)
And to get some data in there to start with...
USE [Development]
GO
INSERT INTO [dbo].[student]
([FNAME]
,[LNAME]
,[AGE])
VALUES
('Benny'
,'Fits'
,25)
GO
INSERT INTO [dbo].[student]
([FNAME]
,[LNAME]
,[AGE])
VALUES
('Gerry'
,'Attrics'
,45)
GO
INSERT INTO [dbo].[student]
([FNAME]
,[LNAME]
,[AGE])
VALUES
('Hal'
,'Lucination'
,25)
GO
I created this in a database named Development so if you use a different database name, you'll need to modify the PowerShell database calls accordingly. I use "localhost" as the database server so again, change this to fit your environment.
<# Author: Bryan Cafferky, BPC Global Solutions, LLC 2014-07-13
Purpose: Demo how to do data maintenance via PowerShell.
#>
function ufn_check_for_module_and_load (
[string]$modulename
)
{
if(-not(Get-Module -name $modulename)) {
Import-Module $modulename
}
}
ufn_check_for_module_and_load "sqlps" # We need to make sure the SQLPS module is loaded.
While (1 -eq 1 )
{
$action = read-host "Please enter an action (a = add, d = delete, c = change, l = list)..."
If ($action -eq "d")
{
$key = read-host "Please enter the key of the row you want to delete..."
$data = Invoke-Sqlcmd -ServerInstance "localhost" -Database "Development" -Query "select * from dbo.student where id = $key"
"Row to be deleted..."
$data
$answer = read-host "Delete record (y/n)?"
if ($answer.toupper() -eq "Y" )
{
Invoke-Sqlcmd -ServerInstance "localhost" -Database "Development" -Query "delete from dbo.student where id = $key"
"Record deleted."
}
else { "Deletion aborted..." }
}
ElseIf ($action -eq "c")
{
$key = read-host "Please enter the key of the row you want to change..."
$data = Invoke-Sqlcmd -ServerInstance "localhost" -Database "Development" -Query "select * from dbo.student where id = $key"
"The record you are about to change is displayed below..."
$data
$fn = read-host "Enter new First Name"
$ln = read-host "Enter new Last Name"
$age = read-host "Enter new Age"
$sqlupdate = "update dbo.student set fname = '$fn', lname = '$ln', age = $age where id = $key"
Invoke-Sqlcmd -ServerInstance "localhost" -Database "Development" -Query $sqlupdate
write-host "Updated Record..."
Invoke-Sqlcmd -ServerInstance "localhost" -Database "Development" -Query "select * from dbo.student where id = $key"
}
ELSEIF ($action -eq "a")
{
$fn = read-host "Enter new First Name"
$ln = read-host "Enter new Last Name"
$age = read-host "Enter new Age"
$sqlinsert = "insert into dbo.student (fname, lname, age) Values ('$fn', '$ln', $age)"
Invoke-Sqlcmd -ServerInstance "localhost" -Database "Development" -Query $sqlinsert
write-host "Inserted Record..."
Invoke-Sqlcmd -ServerInstance "localhost" -Database "Development" -Query "select * from dbo.student where id = IDENT_CURRENT( 'dbo.student' )"
}
ELSEIF ($action -eq "l")
{
Invoke-Sqlcmd -ServerInstance "localhost" -Database "Development" -Query "select * from dbo.student"
}
ELSE
{ write-host "bad action" }
}
This simple application starts by calling a function defined at the top of the code to check for the SQLPS module and if not found, load it. Note: This is a useful function to have.
The code is written to maintain data in a table named dbo.student, i.e. columns are first name, last name and age. You are prompted with the options to add a row (a), change a row (c), delete a row(d) or list all rows (l). The actual prompt is shown below.
If you enter l...
If you enter an l for the action, all table rows will be listed to the console.
If you enter a...
If you enter an a, you will be prompted to enter the first name, last name and age values. The row will then be inserted. This code is interesting because after the row is inserted, I display it back to the console but to do that I need to get the value of the primary key just inserted, which is an identity column. The function IDENT_CURRENT( 'dbo.student' ) returns the last identity column value for the table and not just for this database connection but for all database connections so it will work in concurrency with other database access.
If you enter a c...
If you enter a c, you will be prompted for the ID of the student. After you enter it, the student will be displayed to the console and you will be prompted for new values for the columns. This is crude but shows you how to do an update. After the change, the updated record is displayed to the console.
If you enter a d...
If you enter a d, you will be prompted for the ID of the student you want to delete. Then the student data will be displayed to the console. You will be prompted to confirm you want to delete the student and if you enter y, the student is deleted and a message to this effect is displayed to the console.
Wrapping Up...
At the heart of all this is the Invoke-SQLCmd which is available in the SQLPS module. You can do a lot with this module but see how easy it is to manipulate data with it. You can go the ADO.Net route as well but I like this approach because it is easier and requires a lot less code.
The point of this code is to show how easy it is to read and update database tables with PowerShell. I plan to do other blogs about writing Win Forms for PowerShell scripts and using HTML Applications. This is just the bare bones to show the CRUD (create, read, update, delete) operations.
I hope you find this useful!
Bryan
No comments:
Post a Comment