PowerShell 에서 MSSQL 접속하기.

안녕하세요. 엉스데브 입니다.

PowerShell 로 필요한 스크립트를 만들던 중, MSSQL 서버에 저장 되어 있는 데이터를 참조해야 하는 경우가 생겼습니다.

PowerShell 은 .NET Framework  기반의 언어이기 때문에 .NET Framework 가 제공하는 여러 클래스를 사용 할 수 있으며, 이 클래스 들 중에는 MSSQL 접속 및 데이터 조회 / 수정을 위한 클래스 들도 있습니다. 이 클래스 들을 이용해서 직접 사용 할 MSSQL 관련 함수들을 만들어 보았습니다. 

Function Query-Mssql
{
  Param(
    [parameter(Mandatory=$true)][String]$Query,
    [parameter(Mandatory=$true)][String]$Server,
    [parameter(Mandatory=$true)][String]$Id,
    [parameter(Mandatory=$true)][String]$Password,
    [parameter(Mandatory=$true)][String]$DBName,
    [Int32]$Timeout = "30"
  )

  $Conn = New-Object System.Data.SqlClient.SqlConnection
  $Conn.ConnectionString = "Server=" + $Server + ";Database=" + $DBName + ";User ID=" + $Id + ";Password=" + $Password
  $Conn.Open()

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlCmd.CommandText = $Query
  $SqlCmd.Connection = $Conn
  $SqlCmd.CommandTimeout = $Timeout

  Return $SqlCmd.ExecuteNonQuery()
}

Function Get-MssqlData
{
  Param(
    [parameter(Mandatory=$true)][String]$Query,
    [parameter(Mandatory=$true)][String]$Server,
    [parameter(Mandatory=$true)][String]$Id,
    [parameter(Mandatory=$true)][String]$Password,
    [parameter(Mandatory=$true)][String]$DBName,
    [Int32]$Timeout = "30"
  )

  $Conn = New-Object System.Data.SqlClient.SqlConnection
  $Conn.ConnectionString = "Server=" + $Server + ";Database=" + $DBName + ";User ID=" + $Id + ";Password=" + $Password
  $Conn.Open()

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlCmd.CommandText = $Query
  $SqlCmd.Connection = $Conn
  $SqlCmd.CommandTimeout = $Timeout
 
  $SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $SqlDataAdapter.SelectCommand = $SqlCmd

  $DataSet = New-Object System.Data.DataSet
  $SqlDataAdapter.Fill( $DataSet )

  Return $DataSet.Tables
}

Query-Mssql 함수는 Insert, Update, Delete 등 결과 레코드가 반환되는 것이 아닌 단순 실행 쿼리를 날릴 때 사용하며, Get-MssqlData 함수는 Select 등 리턴되는 데이터가 있는 쿼리를 날릴 때 사용합니다.

#아래 더보기를 클릭하시면 다소 복잡한 스크립트 설명을 볼 수 있습니다.#

MSSQL 서버의 접속은 System.Data.SqlClient.SqlConnection 클래스를 이용합니다. New-Object cmdlet 으로 개체를 생성한뒤, ConnectionString 속성에 접속 관련 정보를 설정 해 둡니다. 그 후 Open() 메소드를 호출하면 접속 됩니다.

실행 할 쿼리는 System.Data.SqlClient.SqlCommand 클래스를 사용합니다. New-Object cmdlet 을 통해 개체를 생성하고, CommandText 속성에 실행 할 쿼리를 넣어 준 뒤 Connection 속성에 기존에 열었던 MSSQL 서버 접속 개체를 지정 해 줍니다. CommandTimeout 이란 속성을 통해 해당 쿼리의 실행 시간을 제한 할 수도 있습니다.

쿼리를 실행하는 방법은 해당 쿼리의 종류에 따라 두가지로 나뉩니다. 바로 SELECT 등의 결과셋 반환 쿼리와 INSERT, UPDATE, DELETE 등의 비반환 쿼리이지요.

결과셋 비반환 쿼리의 경우, System.Data.SqlClient.SqlCommand 개체의 ExecuteNonQuery() 메소드를 이용해서 실행 할 수 있습니다. 리턴되는 값은 영향을 받은 행의 개수이지요.

결과셋 반환 쿼리의 경우, System.Data.SqlClient.SqlDataAdapter 개체를 사용합니다. New-Object cmdlet 으로 개체를 만들어 주고, SelectCommand 파라미터에 만들어 둔 System.Data.SqlClient.SqlCommand 개체를 지정 해 줍니다. 그 뒤, Fill() 메소드를 호출해서 쿼리를 실행 할 수 있습니다. Fill() 메소드에는 System.Data.DataSet 타입의 개체를 넣어주어야 하는데, 미리 New-Object cmdlet 으로 System.Data.DataSet 개체를 만든 뒤 지정 해주면 됩니다.

반환 값은 System.Data.DataSet 형식의 개체입니다.

 

이제 PowerShell 에서 MSSQL 데이터도 엑세스 및 수정 할 수 있으니, 좀 더 정교한 스크립트를 만들어 봐야 겠습니다. ^^

<참고 URL>
http://nsjokt.springnote.com/pages/5657751