Saturday, 21 March 2015

How to connect SQL database using PowerShell

Importing Machine details:  (In SCCM Database Perspective)

Let's suppose we have an SCCM SQL Server database named CM_VRR (Database of SCCM) and that database has a table named collection, which holds collection resources (System or user). If you are not an SCCM admin, Just imagine like we have an SQL server, database and a table which holds Computer Information.

Normally we will use query to get the data from a table. For that we need to login SQL server and have to type the query, after that we have to find some way for saving data as per you requirement.This will be big task to do manually especially if we have a huge tables and data inside the database. That's where powershell comes into the picture.

Here I would like to show you a scenario based example. I have SCCM database (CM_VRR) and a table which holds device collection details (Computer Information). Below is the over all graphical scenario 

SQL Server 2012 :
Server Name : ADDC-2012
Database : CM_VRR




















SCCM 2012 Console (Front End)

















SQL Query - Used to get the Computer Information (Device Collection Details)



















Now let's start this using powershell !!!

Note the below classes:

Reference : https://msdn.microsoft.com/en-us/library/System.Data.SqlClient


System.Data.SQLClient.SQLConnection : This Represents an open connection to a SQL Server database
System.Data.SqlClient.SqlCommand : Represent a Transact-SQL statement or stored procedure to execute against a SQL Server database
System.Data.SqlClient.SqlDataAdapter : Represents a set of data commands and a database connection that are used to fill the data-set and update a SQL Server database
System.Data.DataSet : Represents an in-memory cache of data.

1 - Opening SQL Connection using System.Data.SQLClient.SQLConnection
        You should have the proper permission to the database to access the data.

001
002
003
004
005
006
007
008
009
010
011
012
$Global:SCCMSQLSERVER = "ADDC-2012"
$Global:DBNAME = "CM_VRR"
Try
{
$SQLConnection = New-Object System.Data.SQLClient.SQLConnection
$SQLConnection.ConnectionString ="server=$SCCMSQLSERVER;database=$DBNAME;Integrated Security=True;"
$SQLConnection.Open()
}
catch
{
    [System.Windows.Forms.MessageBox]::Show("Failed to connect SQL Server:")
}

2 - Crating stored procedure and adding SQL query using System.Data.SqlClient.SqlCommand. In the below SQL Query, I have hard-coded the collection Name. If any portion of your sql query is dynamic, you just create a variable in the initial stage and include in your query.

001
002
003
004
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = "select QueryName,CollectionName from [dbo].[Collections_G] as G inner join [dbo].[Collection_Rules] as R
 on G.CollectionID = R.CollectionID where G.CollectionName = 'softwareinstallation'"

$SQLCommand.Connection = $SQLConnection

3 - Creating SQL Dataadapter and Dataset. Using System.Data.SqlClient.SqlDataAdapter and System.Data.DataSet


001
002
003
004
005
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SQLCommand
            
$SQLDataset = New-Object System.Data.DataSet
$SqlAdapter.fill($SQLDataset| out-null

4 - Now your query result is available in $SQLDataset 

001
$SQLDataset.tables[0]














As per the above result, we got two column with System Name and Collection Name based on our query. You may get many columns based on what query you are going to use. So we have to filter the data based on our required column.

Filtering the table data based on column. Below for-each loop will filter the first column ( $data[0] ) of our dataset.
Increase the array value from [0] to get the next column values $data[1


001
002
003
004
005
006
007
008
$tablevalue = @()

foreach ($data in $SQLDataset.tables[0])
{
$tablevalue = $data[0]
$tablevalue
}
$SQLConnection.close()

Putting all together : You have to modify the server details.


001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
$Global:SCCMSQLSERVER = "ADDC-2012"
$Global:DBNAME = "CM_VRR"
Try
{
$SQLConnection = New-Object System.Data.SQLClient.SQLConnection
$SQLConnection.ConnectionString ="server=$SCCMSQLSERVER;database=$DBNAME;Integrated Security=True;"
$SQLConnection.Open()
}
catch
{
    [System.Windows.Forms.MessageBox]::Show("Failed to connect SQL Server:")
}

$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = "select QueryName,CollectionName from [dbo].[Collections_G] as G inner join [dbo].[Collection_Rules] as R
 on G.CollectionID = R.CollectionID where G.CollectionName = 'softwareinstallation'"

$SQLCommand.Connection = $SQLConnection

$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SQLCommand        
$SQLDataset = New-Object System.Data.DataSet
$SqlAdapter.fill($SQLDataset| out-null

$tablevalue = @()
foreach ($data in $SQLDataset.tables[0])
{
$tablevalue = $data[0]
$tablevalue
}
$SQLConnection.close()


Download Script



No comments:

Post a Comment