none
VBで、テーブルにI/Oする時用の構造状の定数の書き方 RRS feed

  • 質問

  • Microsoft Visual Basic 2015

    技術的な事というより、コーディング作法寄りの話です。
    少し前に構造状の定数の書き方について教えて頂いたのですが、その延長線上の話でもあります。

    テーブル名や項目名を定数にして、実行時エラーを減らそうと思います。

    定数化よりもクラスを作ったほうが良いとか、併用するとか、そういったお話も歓迎します。

    少し模索してみましたがイマイチです。
    とりあえず、ベタ書きしてみましたが、何か良いサンプルはありませんか?



    ■table(postgres)■
    CREATE TABLE table1
    (
      item1 varcharacter,
      item2 integer,
      item3 date,
      CONSTRAINT table1_pk PRIMARY KEY (item1)
    );
    ■table■



    ■vbのコード■
    Dim NpgsqlConnection1 As NpgsqlConnection
    Dim NpgsqlDataAdapter1 As NpgsqlDataAdapter
    Dim DataTable1 As DataTable
    Dim Stringitem1 As String = Nothing
    Dim StringSql As String = Nothing

    NpgsqlConnection1 = New NpgsqlConnection
    NpgsqlConnection1.ConnectionString = コネクト
    NpgsqlConnection1.Open()

    DataTable1 = New DataTable

    ' ↓この部分の話です。
    StringSql = "SELECT item2, item3 FROM table1 WHERE item1 ='" & Stringitem1 & "'"

    NpgsqlDataAdapter1 = New NpgsqlDataAdapter(StringSql, NpgsqlConnection1)
    NpgsqlDataAdapter1.Fill(DataTable1)

    NpgsqlConnection1.Close()
    NpgsqlConnection1.Dispose()
    NpgsqlDataAdapter1.Dispose()
    ■vbのコード■



    ■模索してみた内容■
    Public Class DB
        Public Const SELECT As String = "SELECT"
        Public Const FROM As String = "FROM"
        Public Const WHERE As String = "WHERE"
        Public Class table1
            Public Const nm As String = "table1"
            Public Const item1 As String = "item1"
            Public Const item2 As String = "item2"
            Public Const item3 As String = "item3"
        End Class
    End Class

    StringSql = DB.SELECT & Space(1) & DB.table1.item2 & vbCrLf
    StringSql &= "," & DB.table1.item3 & vbCrLf
    StringSql &= DB.FROM & Space(1) & DB.table1.nm & vbCrLf
    StringSql &= DB.WHERE & Space(1) & DB.table1.item1 & "='" & Stringitem1 & "';"
    ■模索してみた内容■
    2017年4月14日 8:59

回答

  • 下記のコードを今、イケている書き方で書くとどうなりますか?

    いままでの質疑を読んで思ったのですが、文字列を結合してクエリを組み立てるのは、SQLインジェクションを引き起こしかねない、非常にイケてない・・・いや下手すれば誰かがクビもしくは会社も潰しかねない、賢明なエンジニアなら回避すべき方法です。

    ADO.NET および ADO.NET データプロパイダを利用する TableAdapter・Entity Framework ではパラメタライズドクエリをサポートしています。パラメタライズドクエリはSQLインジェクションを回避する定石で、文字列をプログラムで結合して組み立てることなく、渡された値をサニタイズ(無害化)してクエリにバインドします。

    パラメタライズドクエリに関しては、以下の記事が参考になると思います。

    C# で SQL Server に パラメタライズドクエリ を 実行する

    なお記事では SQL Server に接続してますが、PostgreSQL の ADO.NET プロバイダ Npgsql もインターフェイスはほぼ同じであるため、やることはまったく変わりません。NpgsqlCommand のインスタンスを生成し、パラメタライズドクエリを設定、後は NpgsqlCommand.Parameters  プロパティにパラメータと値のセットを追加するだけです。

    ついでですが、コードを書くとこんな感じになります。NpgsqlCommand の生成と Parameters.AddWithValue メソッドの使い方に着目してください。なお当方はポスグレ環境ないので試せてません、あしからず。

    Public Sub GetItems(item1 As String)
        Using con = New NpgsqlConnection("接続文字列")
            con.Open()
    
            Using command = New NpgsqlCommand("SELECT item2, item3 FROM table1 WHERE item1 = @item1", con)
                command.Parameters.AddWithValue("@item1", item1)
                Using adapter = New NpgsqlDataAdapter(command)
                    Using dt = New DataTable()
                        adapter.Fill(dt)
                        Dim row = dt.Rows(0)
                        MsgBox(row("item2").ToString())
                        MsgBox(row("item3").ToString())
                    End Using
                End Using
            End Using
        End Using
    End Sub


    本フォーラムは、ユーザー(開発者)同士で情報交換を行うためのコミュニティです。初めて利用される方は フォーラムでご質問頂くにあたっての注意点 をご覧ください。





    2017年4月20日 1:37
    モデレータ

すべての返信

  • O/R Mapper; オブジェクト関係マッピングという概念があります。

    Visual StudioではEntity Frameworkという機能を提供しています。これを使用しますとオブジェクトクエリにあるようにオブジェクト操作を行うと、その操作に対応したSQL文を生成した上でデータベース上で実行、得られた結果をオブジェクトに格納して返してくれます。

    PostgreSQLに対しても操作を行うことはできますが、どのような機能があるかを把握するためにはまずはSQL Serverで試されることをお勧めします。

    もちろん、Entity Framework以外にもライブラリが存在しますので、その辺りを調べられてもいいでしょう。

    2017年4月15日 3:29
  • custardpudding さま よろしく。

    佐祐理 さまも書かれていますが、ご質問を最初に読んだ時には、Entity Framework が浮かびました。

    しかし、もし、型付きデータセット と言う言葉をお聞きになって試されていないなら、以下の URL を辿ってトライされるのをお勧めします。

    https://msdn.microsoft.com/ja-jp/library/esbykkzb(v=vs.110).aspx

    「テーブル名や項目名を定数にして、実行時エラーを減らそうと思います。」 に関して、
    上の URL から引用すれば、
    「認識しやすい名前と厳密に型指定された変数を使用してアクセスできます。」
    「型の不一致が実行時ではなく、コードのコンパイル時にキャッチされます。」
    と言ったメリットが得られます。

    「VB.net 型付きデータセット」 で検索すれば、色々な解説があるとも思います。

    参考までに、Visual Studio (VB も) は、型付きデータセット > Entity Framework へと進化して来た経緯があります。


    2017年4月15日 5:03
  • 有難うございます。

    エンティティ フレームワークですか、ザックリ読んだだけでは理解できませんでした。
    ゆっくり読んでみたいと思います。

    2017年4月17日 0:16
  • 有難うございます。

    検索したところ、
    「型付きDataSetは人格荒廃の元なのでおすすめしない話」というおどろおどろしいタイトルのページがトップに出てきました。

    で読んでいくと、最後に「そもそも古い」
    「型付きデータセットというか、そもそもDataTable/DataSetが古い技術だ。」
    と根本から全否定されていました。

    絶句していると、
    「DataSet/DataTableより新しい次世代の技術がEntity Framework(EF)であり、WPFの高度なバインディング機構です。」
    という事らしいです。

    なるほど、そうやってEntity Frameworkに話が繋がったわけですが、小一時間や半日で理解できそうな気がしない内容です。


    Entity Frameworkの簡単なサンプルとか無いでしょうか。
    2017年4月17日 0:26
  • 簡単なサンプルであれば先の回答の「オブジェクトクエリ」の個所にあります。ですがこれだけ読んでも理解できるものではないでしょう。結局はEntity Framework全体を理解する必要があるのではありませんか?
    2017年4月17日 0:45
  • custardpudding さま 拝見しました。

    進化するには理由があります。  でも、いきなり、最新技術を理解するのは難しいとも思います。
    とても、数日や数か月では無理なのでは?。

    急がば回れで、順を追って、理解して行った方が、他で書かれている内容も呑み込み易いと思います。
    データ処理では、いきなり、型付きデータセットが出て来た訳ではないのですが、
    枯れた手法である為に、色々な解説があって、理解し易いと思います。
    その上で、 Entity Framework や WPF を学ばれては如何ですか?。
      尚、Entity Framework の解説は、その仕組みを理解し易い様に、Console exe としての解説が多かった気がします。
      GUI では、WPF 中心だったとも思います。
      自分の Blog でも、vb.net + Entity Framework について書きましたが、WPF です。
      https://shiroyuki-mot-says.blogspot.jp/search?q=entity&max-results=20&by-date=true

    型付きデータセット ですら、ひとつやふたつの Solution を書いただけで理解出来るとは到底思えません。
    例えば、ご提示のコードには アドホッククエリ の問題もありますし ... 。
      (更に、 変数 Stringitem1 に特定の文字列を入れると全数取得になってしまう。)

    個人的には、ご質問の趣旨を達成するには、型付きデータセットで十分と判断しての、最初の投稿になります。
    2017年4月17日 3:12
  • 個人的には、ご質問の趣旨を達成するには、型付きデータセットで十分と判断しての、最初の投稿になります。

    型付きデータセットを利用すると「"SELECT item2, item3 FROM table1 WHERE item1 ='" & Stringitem1 & "'"」のようなクエリを「実行時エラー」なく記述できるのでしょうか…?

    # データベースから取得する際にフィルタリングすることと、全件取得後にフィルタリングすることは全く別の行為に思いますが…。

    2017年4月17日 4:29
  • 佐祐理 さま 拝見しました。

    「型付きデータセットを利用すると(中略)実行時エラーなく記述できるのでしょうか…?」

    いいえ。  単に、変数名を正確に記述する事が可能になる だけですよね。
    ご質問から、実行時エラーが変数名起因のケースにのみ限定して考えました。


    「# データベースから取得する際にフィルタリングすることと、全件取得後にフィルタリングすることは全く別の行為に思いますが…。」。

    はい。  単に、クエリの記法が問題点含みである点を示したかっただけです。 (パラメタライズへの誘導)
    ご質問の内容とは全く関係ありません。  脱線ですので、ご容赦下さい。


    最終解のひとつが Entiity Framework である点は、同意見です。
    その前に、型付きデータセットの世界を通って下さい との意見です。
    私の書き方が悪くて、申し訳ありません。
    2017年4月17日 4:53
  • 有難うございます。

    > 簡単なサンプルであれば先の回答の「オブジェクトクエリ」の個所にあります。

    はい、それは拝見させて頂きました。


    > ですがこれだけ読んでも理解できるものではないでしょう。

    Using context As New AdventureWorksEntities()
        コーディング
    End Using

    の形式でたくさんありましたが、
    全くなじみがなく、
    この部分だけでなく全体を見ないと、
    これをどこに保存して、どうやって呼び出すのかすら分かりませんでした。


    > 結局はEntity Framework全体を理解する必要があるのではありませんか?

    それはそうなのかもしれません。
    しかし、そうではないかもしれません。

    いくつかのプログラミングをしましたが、細かいことを一切知らなくても、サンプルがあれば作れることもしばしばでした。
    細かい事は追い追い理解できるようになればと思っています。

    PostgresでもMySqlでもSQLServerでも何でもよいのですが、
    SQLをVBに渡してDBMSとI/OするサンプルがSELECT、INSERT、UPDATE、DELETE、FETCH あれば、
    とりあえず、一回動かしてみて、次は項目やWHERE句を弄ってみて、それで動けば一旦はそれでいいという感じです。

    良質のサンプルに出会えるかどうかが最大のカギですが。

    2017年4月18日 0:55
  • 有難うございます。

    実は、細かい事まで100%理解しようとは思っていなかったりします。

    使えればそれでよいという不真面目な考え方です。

    いくつかのプログラミングをしましたが、細かいことを一切知らなくても、サンプルがあれば作れることもしばしばでした。
    細かい事は追い追い理解できるようになればと思っています。

    PostgresでもMySqlでもSQLServerでも何でもよいのですが、
    SQLをVBに渡してDBMSとI/OするサンプルがSELECT、INSERT、UPDATE、DELETE、FETCH あれば、
    とりあえず、一回動かしてみて、次は項目やWHERE句を弄ってみて、それで動けば一旦はそれでいいという感じです。
    2017年4月18日 1:02
  • 良質のサンプルに出会えるかどうかが最大のカギですが。

    この辺りの学習方法は個人の自由ではありますが、そのような方針をとっているからこそ

    1. 2002年 Visual Studio .net 2002 / .NET 1.0のDataSet
    2. 2005年 Visual Studio 2005 / .NET 2.0の型付きDataSet
    3. 2008年 Visual Studio 2008 / .NET 3.5 SP1のEntity Framework

    という登場時期に対して、1.のDataSetを使い続ける羽目に陥っているのではありませんか…?

    なお、Visual StudioはSQL Server向けの支援機能が標準で組み込まれています。他のデータベースでは支援機能に差異があるため操作方法がそれぞれ異なってきます。「何でもよい」という投げやりな態度はこの点を無視し、自滅しているように見受けられます。

    2017年4月18日 1:33
  • custardpudding さま 拝見しました。

    因みに、 私が Entity Framework にトライした 昨年の秋 (2016/08-09) には、
    Internet 上の VB.net WPF ですら あまり サンプルは見付けられませんでした。
    C# で Consol が多いのですが、それでも、とても、参考になりました。( C# は書けないレベルででも、です。)

    単に、コピペして、少し変えて、すんなり動くレベルのサンプルが 良いサンプル とお考えなら、
    その手を見付けるのは これ( Entity Framework )に関しては 難しいのではないでしょうか?。

    やはり、横着せず、基本から歴史から辿るのが、早道だと思っています。

    仮に、その様なサンプルを見い出す事が出来たとしても、
    この世界は複雑なので、壁にぶつかった時、次の扉を開く事が出来ないと思いますよ。
    そして、この様な場で質問するにしても、的が絞り切れない状態になっている事と想像します。

    2017年4月18日 2:20
  • 良質のサンプルに出会えるかどうかが最大のカギですが。

    全くの同意見で、その方向で間違いないと思います。
    ただ、昔と違って新旧いろいろな技術が混在していて、混乱されるのも仕方がないと思います。
    しかし、必ずしも新しいものだけを使えば良く、旧技術を使わなくても良いというわけではありません。新しいものが古いものを完全に置き換えているとは限らないからです。
    例えば、構造化プログラミングよりオブジェクト指向プログラミングは新しいですが、構造化プログラミングの考え方をオブジェクト指向プログラミングの中に取り入れることがあります。結局は、ロジックをコーディングする部分は存在し続けるからです。
    DataTableとEntity Frameworkの話が出てきましたが、要は使いどころなのです。バインドに関してDataTableは不向きです。そもそもバインド用に開発されたものではありません。それなのにバインドに使用して使えないという情報が残念ながら少なからずあります。では、DataTableの使いどころはどこでしょうか? それは、より高次元の業務アプリ等を作成する際に、細部の制御が必要になる場合です。以下のページの13枚目のスライドを参考にしてみて下さい。

    ADO.NET Entity Framework
    https://www.slideshare.net/daisukei/daisukei-ef

    確かにDataTable等、ADO.NETの知識は今は必須ではないのかもしれません。Entity Frameworkを使わなければコーディング量も増え、それを抑える工夫等が必要になることも多いと思います。しかし、だからと言って素通りすれば、それだけ自分の技術の幅が狭まることも事実です。このことを頭の隅にでも置いておいてください。私が言いたかったのはこのことに尽きます。
    ちなみにEntity Frameworkも内部ではADO.NETの基本的な技術を使っています。本当に高い次元を目指すのであれば、ADO.NETを駆使し、ご自分のFrameworkを開発するということだと思います。これを目指しなさいと言っているわけではなく、こういう背景があるということを知った上で、これからの勉強をされるとよいと思います。


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2017年4月18日 3:02
    モデレータ
  • 有難うございます。

    > 1.のDataSetを使い続ける羽目に陥っているのではありませんか…?

    むしろ、下手に2に手を出した為に、それも時代遅れになって、結局3にしてと技法に振り回されて物を作るという目的をおろそかにする羽目にならなかったと考えます。

    私は本職のプログラマではないので、作りたいものを作るために技術を学んでいます。
    学ぶ事が目的ではなく作ることが目的です。
    ビルドした状態で違いが出ないなら、深くは拘りません。
    とはいうものの、どうせ作るなら同じ手間なら品質の良いものを作るに越したことはないし、
    より工数の少ない方法をとれるならそれに越した事はありません。

    Entity FrameworkがDataSetより簡単で品質も良いなら苦労してでも身に着けたいですが、
    少しイレギュラーな事をしようとすると呪縛になるだけのFrameworkなら、あきらめることもアリだと思っています。



    > Visual StudioはSQL Server向けの支援機能が標準で組み込まれています。
    > 他のデータベースでは支援機能に差異があるため操作方法がそれぞれ異なってきます。
    > 「何でもよい」という投げやりな態度はこの点を無視し、自滅しているように見受けられます。

    SQL Serverはしばらく使っていませんが、下記の2点を満たせるなら非常に興味があります。
    ・フリーで利用可能
    ・Linuxにインストール可能

    「何でもよい」という投げやりな事は一切考えていません。
    工数対効果や費用対効果には非常にシビアに考えています。
    2017年4月20日 0:59
  • 有難うございます。

    ( Entity Framework )に関してはコピペして使うといった事は難しいという事ですね。
    それが分かった事を収穫とします。


    > やはり、横着せず、基本から歴史から辿るのが、早道だと思っています。

    なるほど、そうですか。

    しかし、もし数か月トレーニングにかかるのなら、
    同じ数か月使うなら、
    Entity Frameworkを学ぶついでに、VBを捨ててVCでやり直した方が時間の使い方が有意義ですね。


    > この様な場で質問するにしても、的が絞り切れない状態になっている事と想像します。

    的は絞っているつもりです。
    下記のコードを今、イケている書き方で書くとどうなりますか?
    という具体的でシンプルな質問です。

    ■vbのコード■
    Dim NpgsqlConnection1 As NpgsqlConnection
    Dim NpgsqlDataAdapter1 As NpgsqlDataAdapter
    Dim DataTable1 As DataTable
    Dim Stringitem1 As String = Nothing
    Dim StringSql As String = Nothing

    NpgsqlConnection1 = New NpgsqlConnection
    NpgsqlConnection1.ConnectionString = コネクト
    NpgsqlConnection1.Open()

    DataTable1 = New DataTable

    ' ↓この部分の話です。
    StringSql = "SELECT item2, item3 FROM table1 WHERE item1 ='" & Stringitem1 & "'"

    NpgsqlDataAdapter1 = New NpgsqlDataAdapter(StringSql, NpgsqlConnection1)
    NpgsqlDataAdapter1.Fill(DataTable1)

    Dim DataRow1 As DataRow = Me.Drw_viw_terminals_sk = DataTable1(0)

    MsgBox(DataRow1("item2").ToString)
    MsgBox(DataRow1("item3").ToString)

    NpgsqlConnection1.Close()
    NpgsqlConnection1.Dispose()
    NpgsqlDataAdapter1.Dispose()
    ■vbのコード■
    2017年4月20日 1:11
  • 良質のサンプルに出会えるかどうかが最大のカギですが。

    全くの同意見で、その方向で間違いないと思います。
    ただ、昔と違って新旧いろいろな技術が混在していて、混乱されるのも仕方がないと思います。
    しかし、必ずしも新しいものだけを使えば良く、旧技術を使わなくても良いというわけではありません。新しいものが古いものを完全に置き換えているとは限らないからです。
    例えば、構造化プログラミングよりオブジェクト指向プログラミングは新しいですが、構造化プログラミングの考え方をオブジェクト指向プログラミングの中に取り入れることがあります。結局は、ロジックをコーディングする部分は存在し続けるからです。
    DataTableとEntity Frameworkの話が出てきましたが、要は使いどころなのです。バインドに関してDataTableは不向きです。そもそもバインド用に開発されたものではありません。それなのにバインドに使用して使えないという情報が残念ながら少なからずあります。では、DataTableの使いどころはどこでしょうか? それは、より高次元の業務アプリ等を作成する際に、細部の制御が必要になる場合です。以下のページの13枚目のスライドを参考にしてみて下さい。

    ADO.NET Entity Framework
    https://www.slideshare.net/daisukei/daisukei-ef

    確かにDataTable等、ADO.NETの知識は今は必須ではないのかもしれません。Entity Frameworkを使わなければコーディング量も増え、それを抑える工夫等が必要になることも多いと思います。しかし、だからと言って素通りすれば、それだけ自分の技術の幅が狭まることも事実です。このことを頭の隅にでも置いておいてください。私が言いたかったのはこのことに尽きます。
    ちなみにEntity Frameworkも内部ではADO.NETの基本的な技術を使っています。本当に高い次元を目指すのであれば、ADO.NETを駆使し、ご自分のFrameworkを開発するということだと思います。これを目指しなさいと言っているわけではなく、こういう背景があるということを知った上で、これからの勉強をされるとよいと思います。

    有難うございます。

    > ただ、昔と違って新旧いろいろな技術が混在していて、混乱されるのも仕方がないと思います。

    そうですね。
    やりたい事から逆引き的にネット検索すると、複数ヒットしますし、古い内容ほど上位にいたりします。
    VB6の内容も多いです。


    ご意見を参考にさせて頂きます。

    2017年4月20日 1:28
  • 下記のコードを今、イケている書き方で書くとどうなりますか?

    いままでの質疑を読んで思ったのですが、文字列を結合してクエリを組み立てるのは、SQLインジェクションを引き起こしかねない、非常にイケてない・・・いや下手すれば誰かがクビもしくは会社も潰しかねない、賢明なエンジニアなら回避すべき方法です。

    ADO.NET および ADO.NET データプロパイダを利用する TableAdapter・Entity Framework ではパラメタライズドクエリをサポートしています。パラメタライズドクエリはSQLインジェクションを回避する定石で、文字列をプログラムで結合して組み立てることなく、渡された値をサニタイズ(無害化)してクエリにバインドします。

    パラメタライズドクエリに関しては、以下の記事が参考になると思います。

    C# で SQL Server に パラメタライズドクエリ を 実行する

    なお記事では SQL Server に接続してますが、PostgreSQL の ADO.NET プロバイダ Npgsql もインターフェイスはほぼ同じであるため、やることはまったく変わりません。NpgsqlCommand のインスタンスを生成し、パラメタライズドクエリを設定、後は NpgsqlCommand.Parameters  プロパティにパラメータと値のセットを追加するだけです。

    ついでですが、コードを書くとこんな感じになります。NpgsqlCommand の生成と Parameters.AddWithValue メソッドの使い方に着目してください。なお当方はポスグレ環境ないので試せてません、あしからず。

    Public Sub GetItems(item1 As String)
        Using con = New NpgsqlConnection("接続文字列")
            con.Open()
    
            Using command = New NpgsqlCommand("SELECT item2, item3 FROM table1 WHERE item1 = @item1", con)
                command.Parameters.AddWithValue("@item1", item1)
                Using adapter = New NpgsqlDataAdapter(command)
                    Using dt = New DataTable()
                        adapter.Fill(dt)
                        Dim row = dt.Rows(0)
                        MsgBox(row("item2").ToString())
                        MsgBox(row("item3").ToString())
                    End Using
                End Using
            End Using
        End Using
    End Sub


    本フォーラムは、ユーザー(開発者)同士で情報交換を行うためのコミュニティです。初めて利用される方は フォーラムでご質問頂くにあたっての注意点 をご覧ください。





    2017年4月20日 1:37
    モデレータ
  • ' ↓この部分の話です。
    StringSql = "SELECT item2, item3 FROM table1 WHERE item1 ='" & Stringitem1 & "'"

    NpgsqlDataAdapter1 = New NpgsqlDataAdapter(StringSql, NpgsqlConnection1)
    NpgsqlDataAdapter1.Fill(DataTable1)

    SQLインジェクションに関してはひらぽんさんが説明されている通りですが、それとは別にDataAdapterを使うよりTableAdapterを使った方が良いというのがあります。歴史的には、DataAdapterを使いやすくするためにTableAdapterというのが登場しました。TableAdapterは内部でDataAdapterを使用しています。このような関係があるため、DataAdapterを単独で使用しなければならないというケースは稀だと思われます。
    検索しただけですが、PostgreSQLでもTableAdapterが使えるようです。

    PostgreSQLで型付きデータセットとテーブルアダプターを使用する
    http://blog.jhashimoto.net/entry/20110615/1308102561

    さて、SQL Serverのフリー版ですが、Express版とLocalDBの2つがあります。歴史的にはLocalDBが後で、SQL Serverをデータベースとする開発段階での使用を主な目的として登場しましたが、そのままアプリケーションに使用することもできます。
    ただ、いずれも使用においてはライセンスをご確認下さい。
    また、Linux用のSQL Serverですが、現在開発中であり、今年の中旬以降に登場することになっているようです。


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2017年4月20日 2:18
    モデレータ
  • 下記のコードを今、イケている書き方で書くとどうなりますか?

    いままでの質疑を読んで思ったのですが、文字列を結合してクエリを組み立てるのは、SQLインジェクションを引き起こしかねない、非常にイケてない・・・いや下手すれば誰かがクビもしくは会社も潰しかねない、賢明なエンジニアなら回避すべき方法です。

    ADO.NET および ADO.NET データプロパイダを利用する TableAdapter・Entity Framework ではパラメタライズドクエリをサポートしています。パラメタライズドクエリはSQLインジェクションを回避する定石で、文字列をプログラムで結合して組み立てることなく、渡された値をサニタイズ(無害化)してクエリにバインドします。

    パラメタライズドクエリに関しては、以下の記事が参考になると思います。

    C# で SQL Server に パラメタライズドクエリ を 実行する

    なお記事では SQL Server に接続してますが、PostgreSQL の ADO.NET プロバイダ Npgsql もインターフェイスはほぼ同じであるため、やることはまったく変わりません。NpgsqlCommand のインスタンスを生成し、パラメタライズドクエリを設定、後は NpgsqlCommand.Parameters  プロパティにパラメータと値のセットを追加するだけです。

    ついでですが、コードを書くとこんな感じになります。NpgsqlCommand の生成と Parameters.AddWithValue メソッドの使い方に着目してください。なお当方はポスグレ環境ないので試せてません、あしからず。

    Public Sub GetItems(item1 As String)
        Using con = New NpgsqlConnection("接続文字列")
            con.Open()
    
            Using command = New NpgsqlCommand("SELECT item2, item3 FROM table1 WHERE item1 = @item1", con)
                command.Parameters.AddWithValue("@item1", item1)
                Using adapter = New NpgsqlDataAdapter(command)
                    Using dt = New DataTable()
                        adapter.Fill(dt)
                        Dim row = dt.Rows(0)
                        MsgBox(row("item2").ToString())
                        MsgBox(row("item3").ToString())
                    End Using
                End Using
            End Using
        End Using
    End Sub

    有難うございます。
    リアルなサンプルを見ると、一瞬にして多くの事が分かります。
    戦う前に逃げそうになっていましたが、もう少し頑張れそうな気がしてきました。

    あとは自力で調べろよと怒られそうですが、もし許されるなら2点追加で教えてください。

    ◇itemについて
    属性を合わるのですか?それとも常にStringですか?
    Dim item2 as integer それとも string
    Dim item3 as date それとも string

    セットの際に文字列場合、シングルクォートは必要ですか?
    item1 = "あああ"
    それとも
    item1 = "'あああ'"


    ◇検索条件そのものが変動する場合

    画面に item2, item3に対応したTextBoxとDateTimePickerのFrom Toがあり、それぞれにCheckBoxがあり、
    CheckBoxがオンになっている条件だけで絞るという処理があるとします。

    "SELECT * FROM table1"
    までは共通で
    画面操作により
    WHERE句なし
    WHERE item2 = 1
    WHERE item3 BETWEEN '2017-03-20'::date AND '2017-04-20'::date
    WHERE item2 = 1 AND item3 BETWEEN '2017-03-20'::date AND '2017-04-20'::date
    の4パターンの条件が発生するとします。
    ※値部分は具体例を直接埋め込みましたが変数

    こういう場合にはあまり向かないですか?

    結局は
    Dim StringSql As String = "SELECT item2, item3 FROM table1 WHERE item1 = @item1"
    Using command = New NpgsqlCommand(StringSql, con)
    StringSqlの部分をゴリゴリ書きますか、
    それとも、この場合はこの場合でやり方がありますか?

    2017年4月20日 3:05
  • それとは別にDataAdapterを使うよりTableAdapterを使った方が良いというのがあります。
    有難うございます。

    一瞬何のことか分かりませんでしたが、分かりました。

    検索してたまたま出てきたサンプルでは
    DataAdapterを使って、
    DataSetに受け取り、
    それをDataTableに移送して、
    さらに1件1件見る時はDataRowにという感じでやっていました。

    Sqlで複数のテーブルに同時にアクセスして複数のテーブルのフォーマットでデータを受け取るなんて事はないので
    DataSetなんて意味がなく、DataTableに直接受け取りたいと思って検索してみたところ、
    NpgsqlDataAdapter1.Fill(DataTable1)
    でできるという情報にたどり着きました。

    でも、元の部分からテーブル単位でできるモノがあったのですね。

    このご指摘は凄く有り難かったです。

    2017年4月20日 3:19
  • TableAdapter・・・うーん、オープンソースデータベースとは相性悪いので、私は嫌いですねw

    まず型付データセットを作るのに、ポスグレやMySQLだと、いったんODBCドライバーを介さねばならぬという問題がある上、GUI で使うことを前提とした設計のため、ウィザードで生成されたクエリがたいへん読みづらくクエリログが非常に拾いにくいという欠点があります。また単純なクエリの割には吐き出すコードが膨大で、コード内で直にクエリを修正しようとしても、どこにクエリがあるか判らないという弊害もあったりします。

    また SQL標準に準拠し、便利なウィンドウ関数も用意され、ユーザー数も多く、中~大規模案件にも耐えうる PostgreSQL を、わざわざ TableAdapter のためだけに SQL Server に移行する必要も見い出せません。

    Npgsql をお使いなら、今ならむしろ EntityFramework6.Npgsql を使うべきではないでしょうか。

    本フォーラムは、ユーザー(開発者)同士で情報交換を行うためのコミュニティです。初めて利用される方は フォーラムでご質問頂くにあたっての注意点 をご覧ください。

    2017年4月20日 3:31
    モデレータ
  • まず検索条件が変動する場合、事前に数種類のクエリを用意しといて、切り替えて使えばいいのではないでしょうか?

    またパラメタライズドクエリに渡す文字列変数にシングルクォーテーションは不要です。また文字列以外も渡せます。例えば先ほどの SqlCommand.Parameters.AddWithValue メソッドの場合

    command.Parameters.AddWithValue("@item2", 1)
    command.Parameters.AddWithValue("@item3", DateTime.Now)
    command.Parameters.AddWithValue("@item4", False)
    command.Parameters.AddWithValue("@item5", 1.02315544)

    と、様々な型の値を設定することが可能です。

    おっと ヌルを設定するのを忘れていたw

    command.Parameters.AddWithValue("@item6", Nothing)


    本フォーラムは、ユーザー(開発者)同士で情報交換を行うためのコミュニティです。初めて利用される方は フォーラムでご質問頂くにあたっての注意点 をご覧ください。

    2017年4月20日 3:43
    モデレータ
  • さて、SQL Serverのフリー版ですが、Express版とLocalDBの2つがあります。歴史的にはLocalDBが後で、SQL Serverをデータベースとする開発段階での使用を主な目的として登場しましたが、そのままアプリケーションに使用することもできます。

    ただ、いずれも使用においてはライセンスをご確認下さい。
    また、Linux用のSQL Serverですが、現在開発中であり、今年の中旬以降に登場することになっているようです。

    見落とすところでした。

    SQL Serverのフリー版があるのですね。

    オラクルとSQL Serverは高額だと決めつけていました。

    Linux用SQL Serverが出たら是非使ってみたいです。

    来年以降ぐらいに。

    2017年4月20日 3:50
  • Npgsql をお使いなら、今ならむしろ EntityFramework6.Npgsql を使うべきではないでしょうか。


    有難うございます。

    ざっくり読んだところ、

    [PostgreSQL版] Entity Frameworkで世界が変わったでござる

    長々としたSQL文を打つ必要が無くなりました。

    さようならSQL文

    魅力的なキーワードと見やすいページですぐにでも試したくなりました。

    2017年4月20日 3:52
  • Entity Frameworkを学ぶついでに、VBを捨ててVCでやり直した方が時間の使い方が有意義ですね。

    VBに対しても既に15年のハンディキャップがあるわけで、今からVCを勉強するとなると更に長くなると思います。よいサンプルは圧倒的に多いとは思いますが。

    下記のコードを今、イケている書き方で書くとどうなりますか?
    という具体的でシンプルな質問です。

    Entity Frameworkで書いてみました。まず、Table1及びDatabaseのモデルを用意します。

    Imports System.Data.Entity
    
    Public Class Table1
        Public Property Item1 As String
        Public Property Item2 As Integer
        Public Property Item3 As DateTime
    End Class
    
    Public Class Database
        Inherits DbContext
        Public Sub New(nameOrConnectionString As String)
            MyBase.New(nameOrConnectionString)
        End Sub
        Public Table1 As DbSet(Of Table1)
    End Class

    これらを使うことで次のようにデータベースへアクセスできます。

    Dim Stringitem1 As String = Nothing
    
    Using database As New Database("コネクト")
        Dim result = database.Table1.First(Function(row) row.Item1 = Stringitem1)
        MsgBox(result.Item2.ToString)
        MsgBox(result.Item3.ToString)
    End Using

    これで

    SELECT TOP(1) item1, item2, item3 FROM table1 WHERE item1 = <Stringitem1>

    相当のSQL文が自動生成され、データベース上で実行されます。

    Entity Frameworkが最初の質問文にある「テーブル名や項目名を定数にして、実行時エラーを減らそうと思います。」に応える存在だということは理解いただけますでしょうか?

    2017年4月20日 3:54
  • あと余談ですが、コードファーストにしろデータベースファーストにしろ、SQLに対する正しい知識は必須で、そこのところをしっかり弁えてないと、矛盾したデータをDBに登録してしまったり、本番環境で想定外のパフォーマンス劣化を引き起こしたりします。その辺りも踏まえて学習されるとよいかと思います。いまはデータベースに関して非常に素晴らしい書籍も幾つか出ておりますので、こちらも併せてお勧めしておきます。

    理論から学ぶデータベース実践入門

    SQL実践入門

    SQLアンチパターン

    なお上記の書籍、「入門」とは書いておりますが、実際は出版社の編集に無理やり付けられたタイトルだそうで著者が本来意図したタイトルではないと、直接著者の方から伺いました。まあ売るためには仕方ないですねw



    本フォーラムは、ユーザー(開発者)同士で情報交換を行うためのコミュニティです。初めて利用される方は フォーラムでご質問頂くにあたっての注意点 をご覧ください。

    2017年4月20日 3:59
    モデレータ
  • 有難うございます。

    > VBに対しても既に15年のハンディキャップがあるわけで、今からVCを勉強するとなると更に長くなると思います。よいサンプルは圧倒的に多いとは思いますが。

    それは数字の悪用です。

    一から初めても15年かかる訳ではないので、確かに今からVCを勉強するとなると更に長くなると思いますけど、数か月使うならもう大して変わりません。

    サンプルコード有難うございます。

    こうやって見せて頂くとイメージが沸きます。

    2017年4月20日 5:07
  • 有難うございます。

    > まず検索条件が変動する場合、事前に数種類のクエリを用意しといて、切り替えて使えばいいのではないでしょうか?

    それはキツイです。
    2項目のあるなしなら4パターンですが、3項目で8パターンになります。
    10項目だと1024パターン。
    名簿を都道府県で抽出、市区町村で抽出、性別で抽出、読みカナがアで始まるのを抽出、みたいに条件が多様化するヤツには向かないですか?



    SQLに関しては多少書けます。
    具体的には「Oracle Master Bronze SQL基礎」程度なら大丈夫です。
    DBMSごとのクセの領域まで立ち入らない範囲ではある程度問題ありません。

    PostgreSqlは基礎的なSQLの範囲で使っているだけなので、お金さえ掛からなければSQLServerでもいいぐらいです。

    改めてPostgreSqlを見てみると、関数にストアドを仕込めるようですね。
    戻り値はステータスにもできるし、ROWTYPEにすればDataTableにそのまま受けれそうだし、
    下手にVB側でどうこうせずにストアドファンクションで全部やってしまったら良いのかもしれません。

    2017年4月20日 5:34
  • ひらぽんさん、TableAdapterのご指摘、ありがとうございました。私はPostgreSQLの経験がないため、助かります。
    さて、SQLに関してですが、私もSQLを重要視する人です。必須とまでは言いませんが、SQLに強くなると、まずSQLでの実装を考えます。SQLで実装できると、後のVBやC#における実装が本当に楽になります。バッチ的なエラーチェックは全てSQLで行うぐらいです。ただ、可読性が悪いのは欠点ですので、そこはコメントでしっかり補っておくことが重要です。
    custardpuddingさんに勘違してほしくないのは、Entity Frameworkを使う=SQLの勉強をしなくても良いということではなく、Entity Frameworkに加えてSQLにも強くなると、世界が広がるということを伝えたいのです。もっとも最初からSQLを知る必要はなく、Entity Frameworkで十分な場合もあるでしょうから、このことは頭の隅にでも入れておいて下さい。
    ただ、将来的に技術力を向上され、より優れたアプリケーションを開発し続けていかれるのであれば、そのレベルからはSQLは必須だと思います。

    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2017年4月20日 5:39
    モデレータ
  • 条件が多様化するなら、むしろ Entity Framework でクエリを自動生成した方がいいかも知れません。さほどニッチな要件でなければ、プログラム内で安全にクエリを生成できると思います。

    またデータをツリー構造で表現する・・・いわゆるグラフ理論化するなら少し考えものです。RDBとグラフ理論はたいへん相性が悪いという意見もあります。

    データ構造を抜本的に見直すか、もしくはクエリ構文の生成をコーディングに頼らざるを得ない部分が発生しかねないかもしれません。ただしその場合でもコーディングによるクエリ生成は可能な限り最低限にとどめ、パラメタライズドクエリで値を渡すようにすることが必須です。

    あとグラフ理論に関しては、以下の本を参考にするといいかも知れません。安心と信頼のセルコ本です。

    プログラマのためのSQLグラフ原論 リレーショナルデータベースで木と階層構造を扱うために


    本フォーラムは、ユーザー(開発者)同士で情報交換を行うためのコミュニティです。初めて利用される方は フォーラムでご質問頂くにあたっての注意点 をご覧ください。

    2017年4月20日 5:48
    モデレータ
  • 有難うございます。

    SQLを軽視したことは一度もありません。
    むしろ、システムとはDBMSとのI/Oがほぼ全てだと考える派です。
    VBよりはまだSQLの方が書ける方です。

    DBにデータを突っ込む手段と取り出す手段は何だっていいと考えています。
    VBからデータを入れようがWEBから入れようが、オペレーションに大差がないならtableに収まってしまえば同じことです。

    データを取り出す手段もWEBならWEBでjavaで書こうがphpで書こうがブラウザからの見え方がおなじならどっちでもいいですし、Windowsアプリなら、EXEになった状態が同じならVBでもVCでも構いません。

    そういう意味で、WEBサーバだの、APサーバーだのを別途用意する必要もなく、ブラウザもいらない。
    Windows端末さえあれば使えるし、雑に作っても動くVBのWindowsAplicationが使い勝手が良いです。

    ここで怒られるのはむしろVBを軽視している点です。
    2017年4月20日 6:15

  • Public Sub GetItems(item1 As String)
        Using con = New NpgsqlConnection("接続文字列")
            con.Open()
    
            Using command = New NpgsqlCommand("SELECT item2, item3 FROM table1 WHERE item1 = @item1", con)
                command.Parameters.AddWithValue("@item1", item1)
                Using adapter = New NpgsqlDataAdapter(command)
                    Using dt = New DataTable()
                        adapter.Fill(dt)
                        Dim row = dt.Rows(0)
                        MsgBox(row("item2").ToString())
                        MsgBox(row("item3").ToString())
                    End Using
                End Using
            End Using
        End Using
    End Sub

    とりあえず動かしてみました。

    少しアレンジを加えてみました。

    Using adapter = New NpgsqlDataAdapter(command)
    の部分を
    Using adapter = New NpgsqlTableAdapter(command)
    に替えてみたらエラーになりました。

    NpgsqlにはTableAdapterが無いって事ですね?



    ■From1.vb■
    Public Class From1
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim dt1 As DataTable = New DataTable

            Call GetItems(Now.AddYears(-1), Now, dt1)

            Dim row1 As DataRow = dt1.Rows(0)

            MsgBox(row1("item1").ToString())
        End Sub
    End Class
    ■From1.vb■


    ■Module1.vb■
    Imports Npgsql
    Module Module1
        Public PublicStringConnect As Sting = "コネクトの値"

        Public Sub GetItems(item_fr As Date, item_to As Date, ByRef DataTable1 As DataTable)
            Using con = New NpgsqlConnection(PublicStringConnect)
                con.Open()

                DataTable1 = New DataTable
                Dim StringSql As String = "SELECT * FROM tbl1 WHERE item3 between @item_fr and @item_to"
                Using command = New NpgsqlCommand(StringSql, con)
                    command.Parameters.AddWithValue("@item_fr", item_fr)
                    command.Parameters.AddWithValue("@item_to", item_to)
                    Using adapter = New NpgsqlDataAdapter(command)
                        Using dt = New DataTable()
                            adapter.Fill(DataTable1)
                        End Using
                    End Using
                End Using
            End Using
        End Sub
    End Module
    ■Module1.vb■


    他のヤツも追々動かしてみます。

    2017年4月20日 9:16
  • 動かそうとしてみました。
    どうしても上手くいきません。

    最初の囲みの中はModule1.vbに入れておきました。

    2番目のをformのボタンクリックに入れてみました。

    そのまま動かすと
    値を Null にすることはできません。
    パラメーター名:source
    というエラーになります。

    SELECT TOP(1) item1, item2, item3 FROM table1 WHERE item1 = <Stringitem1>
    を忘れていました。

    でも、どこに書いたらいいのか分かりません。


    SELECT TOP(1) item1, item2, item3 FROM table1 WHERE item1 = <Stringitem1>
    と書くと、
    SELECT CASE文だと判断されて

    SELECT CASE TOP(1) item1, item2, item3 FROM table1 WHERE item1 = <Stringitem1>

    に置き換わります。



    ■table1■
    CREATE TABLE table1 (
        item1 character varying NOT NULL,
        item2 integer,
        item3 date
    );

    COPY table1 (item1, item2, item3) FROM stdin;
    A001    1    2017-10-10
    A002    2    2000-11-15
    A003    1    2016-12-10
    A004    1    2017-04-20
    \.

    ALTER TABLE ONLY table1
        ADD CONSTRAINT table1_pk PRIMARY KEY (item1);
    ■table1■

    ■Module1.vb■
    Imports System.Data.Entity
    Module Module1
        ' 値は見せたくないのでリソース内に設定済
        Public PublicConnect As String = String.Format(
            "Server={0};Database={1};UserID={2};Password={3};port={4};" _
          , My.Resources.DB_SVNM _
          , My.Resources.DB_NAME _
          , My.Resources.DB_USER _
          , My.Resources.DB_PSWD _
          , My.Resources.DB_PORT
        )

        Public Class Table1
            Public Property Item1 As String
            Public Property Item2 As Integer
            Public Property Item3 As Date
        End Class

        Public Class Database
            Inherits DbContext
            Public Sub New(nameOrConnectionString As String)
                MyBase.New(nameOrConnectionString)
            End Sub
            Public Table1 As DbSet(Of Table1)
        End Class
    End Module
    ■Module1.vb■

    ■Form1.vb■
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim Stringitem1 As String = "A001"

            Try
                Using database As New Database(PublicConnect)
                    Dim result = database.Table1.First(Function(row) row.Item1 = Stringitem1)
                    MsgBox(result.Item2.ToString)
                    MsgBox(result.Item3.ToString)
                End Using
            Catch Exception1 As Exception
                MsgBox(Exception1.Message.ToString)
            Finally
            End Try
        End Sub
    End Class
    ■Form1.vb■

    2017年4月21日 1:00
  • この質問スレッドは、さまざまなライブラリ機能について言及され話題が発散しています。Entity Frameworkを採用し、Entity Frameworkについて質問するのであれば、新たな質問として仕切り直すことを提案します。
    2017年4月21日 1:13
  • Npgsql をお使いなら、今ならむしろ EntityFramework6.Npgsql を使うべきではないでしょうか。


    ちょっと実際にI/Oするところまでやってみようと思いました。

    EntityFramework6.Npgsql』をインストールして、

    app.configを書き換えて、

    コーディングの部分のところで気付きましたが、これVBじゃないですよね。

    やっぱり、EntityFrameworkを使うなら
    VBからVCに言語も変更した方がよさそうですね。

    2017年4月21日 1:29