Insert - Update SQL Database using Powershell Script

Insert - Update SQL Database using Powershell Script

Here is example of SQL Server database insert and update transaction using Powershell script function

SQL table

CREATE TABLE [dbo].[BackupLog](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [BackupDetail] [nvarchar](500) NOT NULL,
 [StartTime] [datetime] NULL,
 [EndTime] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



 

 

Insert Record

function InsertRecord($con)
{
 ############  Insert Record  ############
 $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
 $sqlCommand.Connection = $con
 $sqlCommand.CommandText = "SET NOCOUNT ON; " +
  "insert into BackupLog " +
  "VALUES (@BackupDetail,@StartTime,@EndTime); " +
  "SELECT SCOPE_IDENTITY() as [InsertedID]; "

 # Define SQL Parameters 
 $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@BackupDetail",[Data.SQLDBType]::NVarChar, 500))) | Out-Null
 $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@StartTime",[Data.SQLDBType]::DateTime2))) | Out-Null
 $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@EndTime",[Data.SQLDBType]::DateTime2))) | Out-Null
  
 # Set Parameters Value
 $sqlCommand.Parameters[0].Value =  get-date
 $sqlCommand.Parameters[1].Value = get-date
 $sqlCommand.Parameters[2].Value = get-date

 # Run the query and get the scope ID back into $InsertedID
 $InsertedID = $sqlCommand.ExecuteScalar()
 return $InsertedID
}

 

Update Record

function UpdateRecord($con, $recordId)
{
 ############  Update Record  ############
 $sqlCommandUpdate = New-Object System.Data.SqlClient.SqlCommand
 $sqlCommandUpdate.Connection = $con 
 $sqlCommandUpdate.CommandText = "update  BackupLog set  EndTime = @EndTime where id=$recordId " 
  
 # Define SQL Parameters  
 $sqlCommandUpdate.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@EndTime",[Data.SQLDBType]::DateTime2))) | Out-Null
 #$sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@FileLength",[Data.SQLDBType]::BigInt))) | Out-Null
 
 # Set Parameters Value
 $sqlCommandUpdate.Parameters[0].Value = get-date
 
 # Run the query and get the scope ID back into $UpdatedID
 $sqlCommandUpdate.ExecuteScalar() 
}
  

Powershell Script

cls
 
#SQL connection Configuration
$DBServer = "localhost"
$DBName = "DBName"

# Create SQL Connection object
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True;"
$sqlConnection.Open()

# Quit if the SQL connection didn't open properly.
if ($sqlConnection.State -ne [Data.ConnectionState]::Open) 
{
    "There is something wrong in connection string"    
}
else
{

 $newid = InsertRecord -con $sqlConnection 
 # Write to the console.
 "Inserted row ID = $newid"  
 
 #wait for 5 second
 Start-Sleep -s 5
 UpdateRecord -con $sqlConnection -recordId $newid
  
 # Close SQL Connection
 $sqlConnection.Close()
}
  

Hope this will help you and save your time.

Enjoy !!!

:)