2019年2月7日 / 最終更新日時 : 2021年5月7日 kazuhiro ソフトウェアのスキル

VB.NETでデータベースを操作

VB.netでデータベースサーバーへ問い合わせる

以前の記事で、AccessやExcelなど、様々なアプリからMySQLのデータベースサーバーを呼び出してデータを取り出す方法をご紹介しました。

今度はVB.netで試してみましょう。

データの一覧性では、Excelには劣りますが、例えばデータベースの管理ツールなどを作る事ができます。

前提として、サーバー構築の記事でLinux（CentOS)のサーバーが構築されていて、MySQLがインストールされているものとします。

MySQLを使う場合

Visual Studio でMySQL を使用するには、 Connector/NETが必要です。
Googleなどで、「Connector/NET」 で検索しすると、MySQLの、downloadsのページがヒットします。

はじめて Connector/NET をダウンロードする場合は、Oracleへユーザー登録が必要です。

Connector/NET をインストールする

Connector/NET のファイルのダウンロードが終了したら、ダウンロードしたファイルを実行してインストールを開始します。

インストールは、特に変わった事をしない限りは、まさに「典型的な」のtypicalで進行して大丈夫でしょう。

Connector/NET をプロジェクトに追加する

インストールが完了したら、 Visual Studio community を起動し、Visual Basic のフォームアプリケーションを開きます。

プロジェクトのウィンドウが開きます。
ソリューションエクスプローラから参照を選び、参照を追加します。

mysql で検索すると、MySql.Data が見つかりますので、OKを押してプロジェクトに追加します。

なお、この手順を実行したコンピュータは Connector/NET が既にインストールされているので、新しいプロジェクトを起こす時は、このプロジェクトへの追加からで大丈夫ですが、MySQLを使用するプロジェクトを新規に作成する際は、必ずプロジェクトへの参照の追加が必須になります。

Moduleにデータベースへの処理を追加する。

ソリューションの下で右クリックし、追加からモジュールを追加します。
私の場合、データベース関係はここに設定しています。

Imports MySql.Data.MySqlClient

Module Module1

    Public mysqlCon As New MySqlConnection
    Public sqlCommand As New MySqlCommand

    Sub sql_st()
        ''データベースに接続

        Dim Builder = New MySqlConnectionStringBuilder()
        ' データベースに接続するために必要な情報をBuilderに与える
        Builder.Server = "192.168.1.14"
        Builder.SslMode = MySqlSslMode.None
        Builder.Port = 3306
        Builder.UserID = "user01"
        Builder.Password = "upassword"
        Builder.Database = "vegetables"
        Dim ConStr = Builder.ToString()

        mysqlCon.ConnectionString = ConStr
        mysqlCon.Open()

    End Sub

    Sub sql_cl()
        ' データベースの切断
        mysqlCon.Close()
    End Sub

    Function sql_result_return(ByVal query As String) As DataTable
        ''データセットを返すSELECT系のSQLを処理するコード

        Dim dt As New DataTable()

        Try
            ' 4.データ取得のためのアダプタの設定
            Dim Adapter = New MySqlDataAdapter(query, mysqlCon)

            ' 5.データを取得
            Dim Ds As New DataSet
            Adapter.Fill(dt)

            Return dt
        Catch ex As Exception

            Return dt
        End Try

    End Function

    Function sql_result_no(ByVal query As String)
        ''データセットを返さない、DELETE、UPDATE、INSERT系のSQLを処理するコード

        'System.Threading.Thread.Sleep(50)

        Try
            sqlCommand.Connection = mysqlCon
            sqlCommand.CommandText = query
            sqlCommand.ExecuteNonQuery()

            Return "Complete"
        Catch ex As Exception

            Return ex.Message
        End Try

    End Function

End Module

Subが2つ、Functionが2つあります。

sql_st　はデータベースへの接続
sql_cl　はデータベースの切断
sql_result_return　はSELECT文を処理する
sql_result_no　はDELETE、UPDATE、INSERTを処理します。
これを定型文にしておき、プロジェクト作成の時にモジュールに貼り付けしています。

Moduleの処理をフォームから呼び出す。

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ''データベースと接続
        Call sql_st()

        Dim sql1 As String = "SELECT"
        sql1 &= " `serial`"
        sql1 &= ",`仕入先`"
        sql1 &= " FROM `wholesale`"
        sql1 &= ";"

        Dim dTb1 As DataTable = sql_result_return(sql1)

        If dTb1.Rows.Count = 0 Then

        Else
            For Each DRow As DataRow In dTb1.Rows

                Me.ListBox1.Items.Add(DRow.Item(1))

            Next
        End If


        ''データベースを切断
        Call sql_cl()
    End Sub

フォームには、リストボックスを一つ設置し、Form1_Loadにコードを記述します。
loadは、フォームが表示されたときに実行されるコードです。
つまり、フォームが呼び出される度、データベースに接続する手続きをして、sql1にSELECT文を入れ、 sql_result_return に送り、データベースがSQLを実行した結果が、dTb1のオブジェクトに入ります。

あとは、dTb1が空になるまでループで繰り返し取り出し、それをリストボックスにaddしています。
最後に、 データベースと切断する手続きを行っています

実行すると、フォームが表示され、リストの中に先ほどのExcel VBAの時と同じ表示がされました。

VB.netからMySQLに書き込みする。

データベースへ問い合わせが出来るという事は、新規登録や修正も可能です。

フォームにテキストボックスを一つと、ボタンを一つ加えます。
そのボタンに対し、プログラムコードを与えます。

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ''データベースと接続
        Call sql_st()

        Dim sql1 As String = "INSERT INTO `wholesale` (`仕入先`) VALUE ('"
        sql1 &= Me.TextBox1.Text
        sql1 &= "');"

        Dim rs As String = sql_result_no(sql1)

        If rs = "Complete" Then
            MsgBox("登録完了", MsgBoxStyle.Information Or MsgBoxStyle.OkOnly, "正常終了")
        Else
            MsgBox("登録に失敗しました" & vbCrLf & "システム担当を呼んでください", MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "異常終了")
        End If

        ''データベースを切断
        Call sql_cl()
    End Sub

先ほどと同じく、データベースに繋ぎます。
テキストボックスの値をＩＮＳＥＲＴ文に取り込み、sql_result_noに与えて結果を文字列に取り込んでいます。
ＳＱＬが成功すると、Completeが返ってくるはずですから、それを拾えば成功のメッセージボックスを表示します。

一度、フォームを閉じて、再度開くと、先ほどの表示プログラムが作動し、追加されたホクレン青果が見えています。

このように、VB.NETでデータベースをコントロールすれば、例えば、テーブルを作成する　CREATE TABLEを発行したり、テーブルを削除するDROP TABLEなども発行できます。
データベースの管理で、定例的に行う操作があったら、プログラムしておけば効率的にメンテナンスを施す事が可能になるでしょう。

PostgreSQLを使う場合

PostgreSQLを使用する場合も、上記コードをほぼ書き換えずに使用できます。

PostgreSQLサーバーは、こちらの記事の手順で用意しました。

npgsqlのインストール

VB.NETでPostgreSQLを扱うには、npgsql　というPostgreSQL データベースサーバに対する .Net データプロバイダが必要です。

このプログラムはVB.NETのNuget パッケージ管理画面からインストールできます。

Moduleにデータベースへの処理を追加する。

ソリューションの下で右クリックし、追加からモジュールを追加します。
私の場合、データベース関係はここに設定しています。

Imports Npgsql

Module Module1

    Public pgsqlCon As New NpgsqlConnection
    Public sqlCommand As New NpgsqlCommand

    Sub sql_st()
        ''データベースに接続

        Dim Builder = New NpgsqlConnectionStringBuilder()
        ' データベースに接続するために必要な情報をBuilderに与える
        Builder.Host = "centos8_01"
        Builder.Port = 5432
        Builder.Username = "postgres"
        Builder.Password = "postgres"
        Builder.Database = "postgres"

        Dim ConStr = Builder.ToString()

        pgsqlCon.ConnectionString = ConStr
        pgsqlCon.Open()

    End Sub

    Sub sql_cl()
        ' データベースの切断
        pgsqlCon.Close()
    End Sub

    Function sql_result_return(ByVal query As String) As DataTable
        ''データセットを返すSELECT系のSQLを処理するコード

        Dim dt As New DataTable()

        Try
            ' 4.データ取得のためのアダプタの設定
            Dim Adapter = New NpgsqlDataAdapter(query, pgsqlCon)

            ' 5.データを取得
            Dim Ds As New DataSet
            Adapter.Fill(dt)

            Return dt
        Catch ex As Exception

            Return dt
        End Try

    End Function

    Function sql_result_no(ByVal query As String)
        ''データセットを返さない、DELETE、UPDATE、INSERT系のSQLを処理するコード

        'System.Threading.Thread.Sleep(50)

        Try
            sqlCommand.Connection = pgsqlCon
            sqlCommand.CommandText = query
            sqlCommand.ExecuteNonQuery()

            Return "Complete"
        Catch ex As Exception

            Return ex.Message
        End Try

    End Function

End Module

Subが2つ、Functionが2つあります。

sql_st　はデータベースへの接続
sql_cl　はデータベースの切断
sql_result_return　はSELECT文を処理する
sql_result_no　はDELETE、UPDATE、INSERTを処理します。

MySQLの時と、呼び出しているデータプロバイダが書き換わっているだけです。

Moduleの処理をフォームから呼び出す。

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ''データベースと接続
        Call sql_st()

        Dim sql1 As String = "SELECT"
        sql1 &= " serial"
        sql1 &= ",仕入先"
        sql1 &= " FROM wholesale"
        sql1 &= ";"

        Dim dTb1 As DataTable = sql_result_return(sql1)

        If dTb1.Rows.Count = 0 Then

        Else
            For Each DRow As DataRow In dTb1.Rows

                Me.ListBox1.Items.Add(DRow.Item(1))

            Next
        End If


        ''データベースを切断
        Call sql_cl()


    End Sub

これも、MySQL用を少し変形しただけで、フォームが呼び出される度、データベースに接続する手続きをして、sql1にSELECT文を入れ、 sql_result_return に送り、データベースがSQLを実行した結果が、dTb1のオブジェクトに入ります。

変更箇所としては、カラムなどの区切りのクォートが違います。

あとは、dTb1が空になるまでループで繰り返し取り出し、それをリストボックスにaddしています。

VB.netからPostgreSQLに書き込みする。

MySQLの時と同様、上記のボタンには書き込み用のプログラムコードを記述します。

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ''データベースと接続
        Call sql_st()

        Dim sql1 As String = "INSERT INTO wholesale (仕入先) VALUES ('"
        sql1 &= Me.TextBox1.Text
        sql1 &= "');"

        Dim rs As String = sql_result_no(sql1)

        If rs = "Complete" Then
            MsgBox("登録完了", MsgBoxStyle.Information Or MsgBoxStyle.OkOnly, "正常終了")
        Else
            MsgBox("登録に失敗しました" & vbCrLf & "システム担当を呼んでください", MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "異常終了")
        End If

        ''データベースを切断
        Call sql_cl()
    End Sub

テキストボックスの値をＩＮＳＥＲＴ文に取り込み、sql_result_noに与えて結果を文字列に取り込んでいます。

INSERT文もクォートの記述などが違う他は、MySQLの時とほぼ同じです。

ＳＱＬが成功すると、Completeが返ってくるはずですから、それを拾えば成功のメッセージボックスを表示します。

まず、「東京青果」を追加し、つづいて「新宿ベジタブル」を追加すると、こうなります。

一度、フォームを閉じて、再度開くと、先ほどの表示プログラムが作動し、追加された新宿ベジタブルが見えています。

SQL Serverの場合

Microsoft SQL Serverは、VB.NETと同じMicrosoft 社製で親和性が良いです。

SQL Serverを搭載したWindowsServerの構築は、こちらの記事の手順で用意しました。

SQL Serverが構築できたら、test_db　というデータベースを作成し、

CREATE TABLE wholesale (
     [serial] [int] IDENTITY(1,1) NOT NULL
    ,[仕入先] varchar(25)
);

INSERT INTO wholesale (仕入先) VALUES ('鈴木屋');
INSERT INTO wholesale (仕入先) VALUES ('佐藤青果');

のSQLで。テーブルを作成し、データを投入しました。

SQL Severの外部接続を許可する。

こちらの記事は、WISAサーバーを構築するのが目的でした。

WISAサーバーの場合は、実行プログラムがサーバー内にあるため、この処理が必要ないのですが、VB.NETでプログラムを作成して実行するとなると、サーバーに外部から接続するのを許可する必要があります。

Windows Serverのファイアウォールから、SQL Serverが使用する1433ポートを許可します。

受信の規則をクリックします。

新しい規則をクリックし、開いた画面でポートを選択します。

特定のポートで、1433を許可します。

接続を許可する　に設定します。

プロファイルはデフォルトのままです

適当な名前をつけます。

これで、LAN上のパソコンから接続できるようになりました。

A5M2でテスト接続

SQL Server　のコンピュータのIPアドレス、ユーザー名をsa　設定したパスワードと、データベース名を指定すると、接続できます。

Moduleにデータベースへの処理を追加する。

ソリューションの下で右クリックし、追加からモジュールを追加します。

MySQLやPostgreSQLと異なり、Importsで接続用のNetデーターベースプロバイダを読み込む必要がありません。

Module Module1

    Public sqlsvCon As New System.Data.SqlClient.SqlConnection
    Public sqlCommand As New System.Data.SqlClient.SqlCommand

    Sub sql_st()
        ''データベースに接続

        Dim Builder = New System.Data.SqlClient.SqlConnectionStringBuilder()
        ' データベースに接続するために必要な情報をBuilderに与える
        Builder.DataSource = "192.168.1.23"
        Builder.UserID = "sa"
        Builder.Password = "passwd"
        Builder.InitialCatalog = "test_db"

        Dim ConStr = Builder.ToString()

        sqlsvCon.ConnectionString = ConStr
        sqlsvCon.Open()

    End Sub

    Sub sql_cl()
        ' データベースの切断
        sqlsvCon.Close()
    End Sub

    Function sql_result_return(ByVal query As String) As DataTable
        ''データセットを返すSELECT系のSQLを処理するコード

        Dim dt As New DataTable()

        Try
            ' 4.データ取得のためのアダプタの設定
            Dim Adapter = New System.Data.SqlClient.SqlDataAdapter(query, sqlsvCon)

            ' 5.データを取得
            Dim Ds As New DataSet
            Adapter.Fill(dt)

            Return dt
        Catch ex As Exception

            Return dt
        End Try

    End Function

    Function sql_result_no(ByVal query As String)
        ''データセットを返さない、DELETE、UPDATE、INSERT系のSQLを処理するコード

        'System.Threading.Thread.Sleep(50)

        Try
            sqlCommand.Connection = sqlsvCon
            sqlCommand.CommandText = query
            sqlCommand.ExecuteNonQuery()

            Return "Complete"
        Catch ex As Exception

            Return ex.Message
        End Try

    End Function

End Module

Subが2つ、Functionが2つあります。

sql_st　はデータベースへの接続
sql_cl　はデータベースの切断
sql_result_return　はSELECT文を処理する
sql_result_no　はDELETE、UPDATE、INSERTを処理します。

MySQLの時と、呼び出しているデータプロバイダの記述内容が少し違っている他は、ほぼ同一内容です。

Moduleの処理をフォームから呼び出す。

   Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ''データベースと接続
        Call sql_st()

        Dim sql1 As String = "SELECT"
        sql1 &= " serial"
        sql1 &= ",仕入先"
        sql1 &= " FROM wholesale"
        sql1 &= ";"

        Dim dTb1 As DataTable = sql_result_return(sql1)

        If dTb1.Rows.Count = 0 Then

        Else
            For Each DRow As DataRow In dTb1.Rows

                Me.ListBox1.Items.Add(DRow.Item(1))

            Next
        End If


        ''データベースを切断
        Call sql_cl()


    End Sub

これも、PostgreSQL用と同一です。フォームが呼び出される度、データベースに接続する手続きをして、sql1にSELECT文を入れ、 sql_result_return に送り、データベースがSQLを実行した結果が、dTb1のオブジェクトに入ります。

あとは、dTb1が空になるまでループで繰り返し取り出し、それをリストボックスにaddしています。

VB.netからSQL Serverに書き込みする。

PostgreSQLの時と同様、上記のボタンには書き込み用のプログラムコードを記述します。

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    ''データベースと接続
    Call sql_st()
 
    Dim sql1 As String = "INSERT INTO wholesale (仕入先) VALUES ('"
    sql1 &= Me.TextBox1.Text
    sql1 &= "');"
 
    Dim rs As String = sql_result_no(sql1)
 
    If rs = "Complete" Then
        MsgBox("登録完了", MsgBoxStyle.Information Or MsgBoxStyle.OkOnly, "正常終了")
    Else
        MsgBox("登録に失敗しました" & vbCrLf & "システム担当を呼んでください", MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "異常終了")
    End If
 
    ''データベースを切断
    Call sql_cl()
End Sub

テキストボックスの値をＩＮＳＥＲＴ文に取り込み、sql_result_noに与えて結果を文字列に取り込んでいます。

プログラムの内容は、PostgreSQL用に作成したものと同じです。

ＳＱＬが成功すると、Completeが返ってくるはずですから、それを拾えば成功のメッセージボックスを表示します。

PostgreSQL同様、「東京青果」を追加し、つづいて「新宿ベジタブル」を追加すると正常完了のダイアログが表示され、再度開くと、先ほどの表示プログラムが作動し、追加された東京青果と新宿ベジタブルが見えています。

Oracle データベースの場合

Oracleは高額ですが、シェアはトップのデータベースです。

ORacle databaseサーバーの構築はこちらの記事、そのデータのODBCなどでの使用方法についてはこちらの記事で紹介しました。

サンプルスキーマをインストールしてあるので、そこにテーブルを作成します。

A5M2でOracle Databaseに接続し、テーブルを作成し、データ2件を入れます。

CREATE TABLE wholesale
( 
    serial number GENERATED BY DEFAULT ON NULL AS IDENTITY
    , 仕入先 varchar2 (25)
);

INSERT INTO wholesale (仕入先) VALUES ('鈴木屋');
INSERT INTO wholesale (仕入先) VALUES ('佐藤青果');

ODP.NETのインストール

VB.NETでOracle Databaseを扱うには、ODP.NET　というOracle データベースサーバに対する .Net データプロバイダが必要です。

このプログラムはVB.NETのNuget パッケージ管理画面からインストールできます。

Moduleにデータベースへの処理を追加する。

ソリューションの下で右クリックし、追加からモジュールを追加します。

呼び出しているNet データプロバイダが異なる他は、MySQLやPostgreSQLとほぼ変わりません。

基本としてtnsnames.oraを使用して接続する事になるようなので、ポートやホスト情報が指定されていません。
DataSourceとして、tnsnames.oraの名称部分を与えます。

Imports Oracle.ManagedDataAccess.Client

Module Module1

    Public oracleCon As New OracleConnection
    Public sqlCommand As New OracleCommand

    Sub sql_st()
        ''データベースに接続

        Dim Builder As OracleConnectionStringBuilder = New OracleConnectionStringBuilder()
        ' データベースに接続するために必要な情報をBuilderに与える
        Builder.DataSource = "ORACLE19C"
        Builder.UserID = "HR"
        Builder.Password = "HR"

        oracleCon.ConnectionString = Builder.ConnectionString
        oracleCon.Open()

    End Sub

    Sub sql_cl()
        ' データベースの切断
        oracleCon.Close()
    End Sub

    Function sql_result_return(ByVal query As String) As DataTable
        ''データセットを返すSELECT系のSQLを処理するコード

        Dim dt As New DataTable()

        Try
            Dim Adapter = New OracleDataAdapter(query, oracleCon)

            ' 5.データを取得
            Dim Ds As New DataSet
            Adapter.Fill(dt)

            Return dt
        Catch ex As Exception

            Return dt
        End Try

    End Function

    Function sql_result_no(ByVal query As String)
        ''データセットを返さない、DELETE、UPDATE、INSERT系のSQLを処理するコード

        'System.Threading.Thread.Sleep(50)

        Try
            sqlCommand.Connection = oracleCon
            sqlCommand.CommandText = query
            sqlCommand.ExecuteNonQuery()

            Return "Complete"
        Catch ex As Exception

            Return ex.Message
        End Try

    End Function


End Module

sql_st　はデータベースへの接続
sql_cl　はデータベースの切断
sql_result_return　はSELECT文を処理する
sql_result_no　はDELETE、UPDATE、INSERTを処理となっています。

Moduleの処理をフォームから呼び出す。

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ''データベースと接続
        Call sql_st()

        Dim sql1 As String = "SELECT"
        sql1 &= " SERIAL"
        sql1 &= ",""仕入先"""
        sql1 &= " FROM HR.WHOLESALE"
        sql1 &= " ORDER BY SERIAL"

        Dim dTb1 As DataTable = sql_result_return(sql1)

        If dTb1.Rows.Count = 0 Then

        Else
            For Each DRow As DataRow In dTb1.Rows

                Me.ListBox1.Items.Add(DRow.Item(1))

            Next
        End If


        ''データベースを切断
        Call sql_cl()

    End Sub

これも、MySQLやPostgreSQL用とほぼ同一です。

フォームが呼び出される度、データベースに接続する手続きをして、sql1にSELECT文を入れ、 sql_result_return に送り、データベースがSQLを実行した結果が、dTb1のオブジェクトに入ります。

他のデータベース処理と異なり；は付けない

上のコードで、SELECT文がString変数に格納される際、SQL文では「ここが最後」を意味する ; が入っていませんが、付与し忘れではありません。

仕様がよく判らないのですが、ODP.NETでSQL文をOracleDataAdapterに送る際、;　が最後についていると

「SQL コマンドが正しく終了していません」

というエラーが吐かれ、データを取得できませんでした。

VB.netからOracleデーターベースに書き込みする。

PostgreSQLの時と同様、上記のボタンには書き込み用のプログラムコードを記述します。

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ''データベースと接続
        Call sql_st()

        Dim sql1 As String = "INSERT INTO wholesale (仕入先) VALUES ('"
        sql1 &= Me.TextBox1.Text
        sql1 &= "')"

        Dim rs As String = sql_result_no(sql1)

        If rs = "Complete" Then
            MsgBox("登録完了", MsgBoxStyle.Information Or MsgBoxStyle.OkOnly, "正常終了")
        Else
            MsgBox("登録に失敗しました" & vbCrLf & "システム担当を呼んでください", MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "異常終了")
        End If

        ''データベースを切断
        Call sql_cl()
    End Sub

テキストボックスの値をＩＮＳＥＲＴ文に取り込み、sql_result_noに与えて結果を文字列に取り込んでいます。

プログラムの内容は、PostgreSQL用に作成したものと同じですが、前述の通り、；　が付いているとエラーになるため、外しています。

ＳＱＬが成功すると、Completeが返ってくるはずですから、それを拾えば成功のメッセージボックスを表示します。

PostgreSQL同様、「新宿ベジタブル」を追加すると正常完了のダイアログが表示され、再度開くと、先ほどの表示プログラムが作動し、追加された新宿ベジタブルが見えています。

このように、細かい違いはあるものの、各種データーベースサーバーに対して接続し、データを取り出したり、登録したりが可能です。

紹介した4つは、データーベースのシェアトップ4にあたるため、ほぼどのデータベースでも処理が可能と言っても過言ではないでしょう。

カテゴリー
ソフトウェアのスキル

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

ソフトウェアのスキル

前の記事

ExcelとSQLデータベースの連携
2019年2月6日
ソフトウェアのスキル

次の記事

ブラウザを自動運転してデータ取得
2019年2月8日