Created
November 4, 2013 02:38
-
-
Save kurukurupapa/7297327 to your computer and use it in GitHub Desktop.
PowerShellでSQL文の実行結果件数を取得してみました ref: http://qiita.com/kurukurupapa@github/items/7f455b879e7c47372e3d
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Set-StrictMode -Version Latest | |
$ErrorActionPreference = "Stop" | |
$WarningPreference = "Continue" | |
$VerbosePreference = "Continue" | |
$DebugPreference = "Continue" | |
# ライブラリ読み込み | |
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data") | |
###################################################################### | |
### 関数定義 | |
###################################################################### | |
# Updateの件数を取得するテスト | |
function U-Test-UpdateCount() { | |
# コマンド実行(UPDATE) | |
$odbcCmd.CommandText = "UPDATE TEST SET NAME='りんご類' WHERE name like 'りんご%'" | |
$count = $odbcCmd.ExecuteNonQuery() | |
Write-Debug "Update文の更新件数:$count" | |
} | |
# Deleteの件数を取得するテスト | |
function U-Test-DeleteCount() { | |
# コマンド実行(DELETE) | |
$odbcCmd.CommandText = "DELETE TEST WHERE name like 'りんご%'" | |
$count = $odbcCmd.ExecuteNonQuery() | |
Write-Debug "Delete文の削除件数:$count" | |
} | |
###################################################################### | |
### 処理実行 | |
###################################################################### | |
# DB接続 | |
$connectionString = "DSN=H2TestDsn;uid=sa;pwd=sa;" | |
$odbcCon = New-Object System.Data.Odbc.OdbcConnection($connectionString) | |
$odbcCon.Open() | |
# コマンドオブジェクト作成 | |
$odbcCmd = New-Object System.Data.Odbc.OdbcCommand | |
$odbcCmd.Connection = $odbcCon | |
# コマンド実行(テーブル作成) | |
$odbcCmd.CommandText = "CREATE TABLE TEST (ID INT PRIMARY KEY, NAME VARCHAR(255))" | |
$odbcCmd.ExecuteNonQuery() | Out-Null | |
# コマンド実行(INSERT) | |
$odbcCmd.CommandText = "INSERT INTO TEST (ID, NAME) VALUES (100, 'りんご')" | |
$odbcCmd.ExecuteNonQuery() | Out-Null | |
$odbcCmd.CommandText = "INSERT INTO TEST (ID, NAME) VALUES (101, 'りんご2')" | |
$odbcCmd.ExecuteNonQuery() | Out-Null | |
$odbcCmd.CommandText = "INSERT INTO TEST (ID, NAME) VALUES (102, 'りんご3')" | |
$odbcCmd.ExecuteNonQuery() | Out-Null | |
# テスト | |
U-Test-UpdateCount | |
U-Test-DeleteCount | |
# コマンド実行(SELECT) | |
$odbcCmd.CommandText = "SELECT * FROM TEST ORDER BY ID" | |
$odbcReader = $odbcCmd.ExecuteReader() | |
while ($odbcReader.Read()) { | |
$odbcReader["ID"].ToString() + " " + $odbcReader["NAME"].ToString() | |
} | |
$odbcReader.Dispose() | |
# コマンド実行(テーブル削除) | |
$odbcCmd.CommandText = "DROP TABLE TEST" | |
$odbcCmd.ExecuteNonQuery() | Out-Null | |
# コマンドオブジェクト破棄 | |
$odbcCmd.Dispose() | |
# DB切断 | |
$odbcCon.Close() | |
$odbcCon.Dispose() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment