{"id":2683,"date":"2018-06-03T17:36:10","date_gmt":"2018-06-03T08:36:10","guid":{"rendered":"http:\/\/1bed.allright.life\/?p=2683"},"modified":"2020-04-18T19:50:45","modified_gmt":"2020-04-18T10:50:45","slug":"post-2683","status":"publish","type":"post","link":"https:\/\/1bed.allright.life\/?p=2683","title":{"rendered":"PowerShell \u3067 SQL Server \u306b\u30c7\u30fc\u30bf\u767b\u9332\u306e\u30e1\u30e2"},"content":{"rendered":"<p>PowerShell \u3092\u5229\u7528\u3057\u3066 SQL Server \u306b\u30c7\u30fc\u30bf\u3092\u5165\u308c\u5165\u308c\u3057\u3066\u305f\u30e1\u30e2<\/p>\n<h2>\u74b0\u5883<\/h2>\n<ul>\n<li>Windows 10 Pro<\/li>\n<li>PowerShell 5.1<\/li>\n<li>SQL Server 2017<\/li>\n<\/ul>\n<h2>\u4e8b\u524d\u6e96\u5099<\/h2>\n<p>\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3059\u308b<\/p>\n<pre><code class=\"language-sql\">-- DUMMY_TABLE \u304c\u65e2\u306b\u3042\u3063\u305f\u3089 DROP\nIF object_id(&#039;DUMMY_TABLE&#039;) IS NOT NULL\nDROP TABLE DUMMY_TABLE\n\n-- ID \u5217\u306f\u81ea\u52d5\u63a1\u756a\nCREATE TABLE DUMMY_TABLE(\n    ID             INT   NOT NULL PRIMARY KEY IDENTITY,\n    INT_DATA       INT,\n    NVARCHAR_DATA  NVARCHAR(255),\n    DATETIME_DATA  DATETIME,\n    VARBINARY_DATA VARBINARY(max)\n);\nGO<\/code><\/pre>\n<h2>INT \u578b\u3001NVARCHAR \u578b\u3001DATETIME \u578b\u3001VARBINARY \u578b\u306e Insert \u4f8b<\/h2>\n<pre><code class=\"language-powershell\"># SQL Server \u3078\u306e\u63a5\u7d9a\u6587\u5b57\u5217\n$ConnectionString = New-Object -TypeName System.Data.SqlClient.SqlConnectionStringBuilder\n\n# \u30c7\u30fc\u30bf\u30bd\u30fc\u30b9\n$ConnectionString[&#039;Data Source&#039;]         = &quot;ExampleServer\\ExampleInstance&quot;\n# \u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u540d\n$ConnectionString[&#039;Initial Catalog&#039;]     = &quot;ExampleDB&quot;\n# \u30e6\u30fc\u30b6\u30fc\u540d\n$ConnectionString[&#039;User ID&#039;]             = &quot;ExampleUser&quot;\n# \u30d1\u30b9\u30ef\u30fc\u30c9\n$ConnectionString[&#039;Password&#039;]            = &quot;ExamplePassword&quot;\n# Windows \u8a8d\u8a3c(SqlClient \u306e\u3068\u304d\u306f true)\n$ConnectionString[&#039;Integrated Security&#039;] = $true\n\n# SQL Server \u3078\u306e\u30b3\u30cd\u30af\u30b7\u30e7\u30f3\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u751f\u6210\n$con = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)\n\n$sql = @&quot;\nINSERT INTO DUMMY_TABLE(\n    INT_DATA,\n    NVARCHAR_DATA,\n    DATETIME_DATA,\n    VARBINARY_DATA\n) VALUES (\n    @INT_DATA,\n    @NVARCHAR_DATA,\n    @DATETIME_DATA,\n    @VARBINARY_DATA\n);\n&quot;@\n\ntry {\n    $con.Open()\n    $transaction = $con.BeginTransaction()\n\n    $cmd = $con.CreateCommand()\n    $cmd.Connection = $con\n    $cmd.Transaction = $transaction\n\n    $cmd.CommandText = $sql\n\n    # INT \u578b\n    $sqlparam = New-Object Data.SqlClient.SqlParameter(&quot;@INT_DATA&quot;, [Data.SQLDBType]::INT, -1)\n    $cmd.Parameters.Add($sqlparam).Value = 1\n\n    # NVARCHAR \u578b\n    $sqlparam = New-Object Data.SqlClient.SqlParameter(&quot;@NVARCHAR_DATA&quot;, [Data.SQLDBType]::NVARCHAR, -1)\n    $cmd.Parameters.Add($sqlparam).Value = &quot;\u65e5\u672c\u8a9e\u306e\u5165\u529b&quot;\n\n    # DATETIME \u578b\n    $sqlparam = New-Object Data.SqlClient.SqlParameter(&quot;@DATETIME_DATA&quot;, [Data.SQLDBType]::DATETIME, -1)\n    $cmd.Parameters.Add($sqlparam).Value = [Datetime](&quot;2018\/06\/02 00:00:00&quot;)\n\n    # VARBINARY \u578b\n    $sqlparam = New-Object Data.SqlClient.SqlParameter(&quot;@VARBINARY_DATA&quot;, [Data.SQLDBType]::VARBINARY, -1)\n    $cmd.Parameters.Add($sqlparam).Value = [System.IO.File]::ReadAllBytes(&quot;C:\\hoge.png&quot;)\n\n    # \u30d1\u30e9\u30e1\u30fc\u30bf\u30fc\u30af\u30a8\u30ea\u30fc\n    $cmd.Prepare()\n\n    # \u5b9f\u884c\n    [void]$cmd.ExecuteNonQuery()\n\n    # \u30b3\u30df\u30c3\u30c8\n    $transaction.Commit()\n\n} catch {\n    Write-Error $_.Exception.ToString()\n} finally {\n    $con.Close()\n    $con.Dispose()\n}<\/code><\/pre>\n<h2>\u81ea\u52d5\u63a1\u756a\u3055\u308c\u308b ID \u3092\u53d6\u5f97<\/h2>\n<p>\u30d1\u30e9\u30e1\u30fc\u30bf\u30fc\u30af\u30a8\u30ea\u30fc\u524d\u3042\u305f\u308a\u3092\u4e0b\u8a18\u306b\u66f8\u304d\u63db\u3048\u308b<\/p>\n<pre><code class=\"language-powershell\">    # Insert \u6587\u306e\u5f8c\u306b\u8ffd\u8a18\n    $sql += &#039;SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];&#039;;\n\n    # \u30d1\u30e9\u30e1\u30fc\u30bf\u30fc\u30af\u30a8\u30ea\u30fc\n    $cmd.Prepare()\n\n    # Insert \u5f8c\u306b ID \u53d6\u5f97\n    $Id = $cmd.ExecuteScalar();<\/code><\/pre>\n<h2>\u7a7a\u6587\u5b57\u3084 NULL \u306e\u30bb\u30c3\u30c8<\/h2>\n<pre><code class=\"language-powershell\">    # \u7a7a\u6587\u5b57\u3092\u30bb\u30c3\u30c8\n    $cmd.Parameters.Add($sqlparam).Value \uff1d[string]::Empty\n\n    # NULL \u3092\u30bb\u30c3\u30c8\n    $cmd.Parameters.Add($sqlparam).Value = [System.DBNull]::Value<\/code><\/pre>\n<h2>INT \u578b\u3084 DATETIME \u578b\u306e\u6700\u5927\u5024\u3084\u6700\u5c0f\u5024\u3092\u30bb\u30c3\u30c8<\/h2>\n<pre><code class=\"language-powershell\">    # INT \u578b\u306e\u6700\u5c0f\u5024\u3092\u30bb\u30c3\u30c8\n    $cmd.Parameters.Add($sqlparam).Value = [System.Data.SqlTypes.SqlInt16]::MinValue.Value\n\n    # INT \u578b\u306e\u6700\u5927\u5024\u3092\u30bb\u30c3\u30c8\n    $cmd.Parameters.Add($sqlparam).Value = [System.Data.SqlTypes.SqlInt16]::MaxValue.Value\n\n    # DATETIME \u578b\u306e\u6700\u5c0f\u5024\u3092\u30bb\u30c3\u30c8\n    $cmd.Parameters.Add($sqlparam).Value = [System.Data.SqlTypes.SqlDateTime]::MinValue.Value\n\n    # DATETIME \u578b\u306e\u6700\u5927\u5024\u3092\u30bb\u30c3\u30c8\n    $cmd.Parameters.Add($sqlparam).Value = [System.Data.SqlTypes.SqlDateTime]::MaxValue.Value<\/code><\/pre>\n<h2>SqlParameter \u3092\u3082\u3046\u5c11\u3057\u76f4\u611f\u7684\u306b\u4f7f\u3044\u305f\u3044<\/h2>\n<p>v5 \u4ee5\u964d\u306a\u3089\u30af\u30e9\u30b9\u3092\u4f7f\u3063\u305f\u307b\u3046\u304c\u3088\u3044<\/p>\n<pre><code class=\"language-powershell\"># \u30af\u30e9\u30b9\u306e\u5b9a\u7fa9\nclass SqlParamGenarator\n{\n    [System.Data.Common.DbParameter]\n    Int([string]$Name) { return $this._Genarator($Name, &#039;Int&#039;) }\n\n    [System.Data.Common.DbParameter]\n    NVarchar([string]$Name) { return $this._Genarator($Name, &#039;NVarchar&#039;) }\n\n    [System.Data.Common.DbParameter]\n    DateTime([string]$Name) { return $this._Genarator($Name, &#039;DateTime&#039;) }\n\n    [System.Data.Common.DbParameter]\n    VarBinary([string]$Name) { return $this._Genarator($Name, &#039;VarBinary&#039;) }\n\n    [System.Data.Common.DbParameter]\n    _Genarator($Name, $Type)\n    {\n        return New-Object Data.SqlClient.SqlParameter(&quot;@$Name&quot;,  [Data.SQLDBType]::$Type, -1)\n    }\n}\n\n    ## \u4f7f\u3044\u65b9\n    $GenSqlParam = [SqlParamGenarator]::new()\n    # INT \u578b\n    $cmd.Parameters.Add($GenSqlParam.Int(&#039;INT_DATA&#039;) ).Value = 2\n\n    # NVARCHAR \u578b\n    $cmd.Parameters.Add($GenSqlParam.NVarchar(&#039;NVARCHAR_DATA&#039;)).Value = &quot;\u65e5\u672c\u8a9e\u306e\u5165\u529b&quot;\n\n    # DATETIME \u578b\n    $cmd.Parameters.Add($GenSqlParam.DateTime(&#039;DATETIME_DATA&#039;)).Value = &quot;2018\/06\/03 00:00:00&quot;\n\n    # VARBINARY \u578b\n    $cmd.Parameters.Add($GenSqlParam.VarBinary(&#039;VARBINARY_DATA&#039;)).Value = [System.IO.File]::ReadAllBytes(&quot;C:\\hoge.png&quot;)<\/code><\/pre>\n<p>v4 \u4ee5\u4e0b\u306a\u3089\u3001\u30b9\u30af\u30ea\u30d7\u30c8\u30d6\u30ed\u30c3\u30af\u3067\u9811\u5f35\u308b<\/p>\n<pre><code class=\"language-powershell\">## \u5b9a\u7fa9\n$GenSqlParam = {\n    $Genarator = @{}\n    $Genarator.Set_Item(&#039;Int&#039;,      ({ param($Name)(New-Object Data.SqlClient.SqlParameter(&quot;@$Name&quot;,  [Data.SQLDBType]::Int, -1)) }))\n    $Genarator.Set_Item(&#039;NVarchar&#039;, ({ param($Name)(New-Object Data.SqlClient.SqlParameter(&quot;@$Name&quot;,  [Data.SQLDBType]::NVarchar, -1)) }))\n    $Genarator.Set_Item(&#039;DateTime&#039;, ({ param($Name)(New-Object Data.SqlClient.SqlParameter(&quot;@$Name&quot;,  [Data.SQLDBType]::DateTime, -1)) }))\n    $Genarator.Set_Item(&#039;VarBinary&#039;,({ param($Name)(New-Object Data.SqlClient.SqlParameter(&quot;@$Name&quot;,  [Data.SQLDBType]::VarBinary, -1)) }))\n    # \u52d5\u7684\u306a\u578b\u5909\u63db\u5bfe\u7b56\n    return ,$Genarator\n}.Invoke()\n\n    ## \u4f7f\u3044\u65b9\n    # INT \u578b\n    $cmd.Parameters.Add( $GenSqlParam.Int.Invoke(&#039;INT_DATA&#039;)[0] ).Value = 2\n\n    # NVARCHAR \u578b\n    $cmd.Parameters.Add( $GenSqlParam.NVarchar.Invoke(&#039;NVARCHAR_DATA&#039;)[0] ).Value = &quot;\u65e5\u672c\u8a9e\u306e\u5165\u529b&quot;\n\n    # DATETIME \u578b\n    $cmd.Parameters.Add( $GenSqlParam.DateTime.Invoke(&#039;DATETIME_DATA&#039;)[0] ).Value = &quot;2018\/06\/03 00:00:00&quot;\n\n    # VARBINARY \u578b\n    $cmd.Parameters.Add( $GenSqlParam.VarBinary.Invoke(&#039;VARBINARY_DATA&#039;)[0] ).Value = [System.IO.File]::ReadAllBytes(&quot;C:\\hoge.png&quot;)<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>PowerShell \u3092\u5229\u7528\u3057\u3066 SQL Server \u306b\u30c7\u30fc\u30bf\u3092\u5165\u308c\u5165\u308c\u3057\u3066\u305f\u30e1\u30e2 \u74b0\u5883 Windows 10 Pro PowerShell 5.1 SQL Server 2017 \u4e8b\u524d\u6e96\u5099 \u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3059\u308b &#8212;  [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[62,117],"class_list":["post-2683","post","type-post","status-publish","format-standard","hentry","category-program","tag-powershell","tag-sqlserver"],"_links":{"self":[{"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/posts\/2683","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2683"}],"version-history":[{"count":4,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/posts\/2683\/revisions"}],"predecessor-version":[{"id":3110,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=\/wp\/v2\/posts\/2683\/revisions\/3110"}],"wp:attachment":[{"href":"https:\/\/1bed.allright.life\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2683"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2683"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/1bed.allright.life\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2683"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}