Accessing SQL Data using Powershell

Hi Guys,

In this post I am explaining how we can access sql db and read the data from a table using PowerShell cmdlets.  The syntax and programming for the sql  operations is same like how we use in C#.

Here am having a sql db called "EmployeeInfo"  with a table "EMP".  Basically this script connects to sql , reads table  data and prints to a text file. The logging and tracing info will  be written to another separate text file.

In this script I am referring these three files

• DBDetails.xml - Contains DB details
• Log File PCM_Powershell_SQL_Log_timestamp - file to which the script writes the Log/Execution information
• Result.txt - A text file to which the result would be written

The dbdetails xml file  looks like below

 

 

here is the PowerShell script (refer the comments for each block)

 Write-Host "Started Execution..."-ForegroundColor Green

    #variables
    $LogFilePath = "C:\Logs\"  # LogPath 
    $LogFilePath += "PCM_Powershell_SQL_Log_"+(Get-Date).ToString("MM_dd_yyyy_hh_mm_ss")+".txt" # Log name with current time stamp
    $DBCredPath= "E:\DBDetails\DbDetails.xml"#DB Details xml File
    $outputfilepath="E:\Result.txt";

    #DB variables
    $Connection=$null;
    $DBServerName=$null;
    $DatabaseName=$null;
    $SQLUserName=$null;
    $SQLPassword=$null;

 #Reading DB details from xml
   
Try
{
 
    "Getting DB details from xml at " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append    
 
    [xml]$dbcred = Get-Content $DBCredPath

    $DBServerName=$dbcred.DBCredentials.sqlservername
    $DatabaseName=$dbcred.DBCredentials.databasename
    $SQLUserName=$dbcred.DBCredentials.username
    $SQLPassword=$dbcred.DBCredentials.password

}

Catch
{   
    "Issue in accessing the DbDetails XML File . Failed with error : " + $_.Exception.Message + " at " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append  
    Exit
}

#Getting Data from SQL and Printing to Text file

Try
{
    #connection String With Windows Authentication
    $SQLConnectionString = "server="+$DBServerName+";database="+$DatabaseName+";Integrated Security=True;" 

    #Connection String with username and password
    #$SQLConnectionString = "server="+$DBServerName+";database="+$DatabaseName+";User ID="+$SQLUserName+";Password="+$SQLPassword+";"
    
    #Query
    $sqlQuery="Select * from Emp";

    #Program
    $sqlCon = New-Object Data.SqlClient.SqlConnection
    $sqlCon.ConnectionString = $SQLConnectionString
    $sqlCon.open()
    $sqlCmd = New-Object Data.SqlClient.SqlCommand
    $sqlCmd.Connection = $sqlCon
    $sqlCmd.CommandText = $sqlQuery
    $empResult= $sqlCmd.ExecuteReader()

    #Loading Result to Data Table
    $empDataTable=New-Object System.Data.DataTable
    $empDataTable.Load($empResult);

    #Printing data table values to text file 
    $format = @{Expression={$_.EmpId};Label="Employee ID";width=20},@{Expression={$_.EmpName};Label="EmployeeName"; width=30}  
    $empDataTable | format-table $format | Out-File $outputfilepath

}

Catch
{   
   "Issue in db function and writing to File . Failed with error : " + $_.Exception.Message + " at " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append  
    Exit
}

# Closing and Disposing Connection Object
try
{

    if($sqlCon -ne $null) 
    {
       if($sqlCon.State -eq 'Closed')
       {
          $sqlCon.Dispose();
          "SQL DB Connection Object Disposed " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append          
       }
       else
       {          
           $sqlCon.Close()         
           "SQL DB Connection Closed " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append
             $sqlCon.Dispose();
           "SQL DB Connection Object Disposed " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append
       }
    }

    "Completed execution at " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append
     Write-Host "Completed Execution, Please check the Log at  $LogFilePath"-ForegroundColor Green

 }
Catch
{   
   "Issue in db function and writing to File . Failed with error : " + $_.Exception.Message + " at " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append  
    Exit
}

Here is the output Result.txt  file

In the above  I am reading data in command text(query) mode. But we can read

Using stored procedure

$sqlCon.ConnectionString = $SQLConnectionString
$sqlCon.open()
$sqlCmd = new-Object System.Data.SqlClient.SqlCommand("Your Procedure Name", $sqlCon)
$sqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$sqlCmd.ExecuteNonQuery() 
$sqlCon.close()
$sqlCon.dispose()

 

 

 

using stored procedure with a input parameter

 

$sqlCon = New-Object Data.SqlClient.SqlConnection
$sqlCon.ConnectionString = $SQLConnectionString
$sqlCon.open()
$sqlCmd = new-Object System.Data.SqlClient.SqlCommand("Your proc name", $sqlCon)
$sqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$sqlCmd.Parameters.AddWithValue("@paramname","paramvalue")
$sqlCmd.ExecuteNonQuery() 
$sqlCon.close()
$sqlCon.dispose()