2019年2月7日 / 最終更新日時 : 2022年5月12日 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同様、「新宿ベジタブル」を追加すると正常完了のダイアログが表示され、再度開くと、先ほどの表示プログラムが作動し、追加された新宿ベジタブルが見えています。

Access を使う場合

Accessは、上記の他データベースと異なり、自身にクエリを視覚的に組む機能があり、更にプログラムを可能にするVBAを搭載しているため、VB.NETでプログラムを作る事はあまりないと思いますが、もちろん、制御は可能です。

Access のaccdbファイルを用意し、テーブルを作成。データを投入します。

今回は以前のAccessデータベースでデータ管理 からデータを流用しました。

AccessRun timeを用意する

Accessに外部から接続するため、Run timeを用意します。

注意点として、VBからAccesssに「アクセス」するためには、Accessが入っていると出来ないという、一見理解不能の現象が起こりますので、詳しくはこちらのコラムでご紹介します。

吉川万能ＩＴ研究所
VisualStudioとAccessの関係
https://yoshisyou.com/?p=3263

前提条件として、この記事のなかの、Microsoft365が入っておらず、32bit版のAccessRun timeのインストールが完了しているものとします。

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

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

Imports System.Data.OleDb

Module Module1
    Dim olecon As OleDbConnection
    Dim oleCommand As OleDbCommand

    Sub sql_st()
        ''データベースに接続
        Dim oledb As String = "Microsoft.ACE.OledB.16.0;"
        Dim acfile As String = "C:\Users\kazuhiro\Documents\vegetables.accdb"

        olecon = New OleDbConnection("Provider=" & oledb & "Data Source=" & acfile)
        olecon.Open()

    End Sub

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

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

        Dim dt As New DataTable()

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

            ' 5.データを取得
            Dim Ds As New DataSet
            oledaad.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を処理するコード

        Try
            oleCommand = New OleDbCommand
            oleCommand.Connection = olecon
            oleCommand.CommandText = query
            oleCommand.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 = "SELECT [卸先].[Serial],[卸先].[仕入先] FROM [卸先] 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

SQLServerとあまり変わりません。

ただ、呼び出すSQLが少し独自です。

Accessはテーブル名やカラム名の区切りに[]を使用するようになっているため、ちょっと特殊な記述に見えます。

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

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

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

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

        Dim sql1 As String = "INSERT INTO [卸先] ([仕入先]) 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に与えて結果を文字列に取り込んでいます。

プログラムの内容は、Oracle用に作成したものと同じですが、前述の通り、テーブルやカラム名の区別は[]で囲むため、対応した記述になっています。また、他のデータベースでは文字列データを'(シングルクォーテーション)で囲ってSQLにするものが多いですが、Accessの場合は、”(ダブルクォーテーション)で囲むルールになっていますので、そちらも変えてあります。

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

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

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

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

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

“VB.NETでデータベースを操作” に対して3件のコメントがあります。

  1. VB初心者 より:
    2023年3月28日 14:56

    初めまして、VB＋mariaDBの勉強を始めた者です
    AccessVBAを今まで利用していましたがVBを利用して開発するように変更をするために勉強をしています。
    その中でmariaDBとVB.netで上記の様にDB接続をしようと思っていますが
    以下のエラーが出て進みません。
    「MySql.Data.MySqlClient.MySqlException: ‘Authentication method ‘auth_gssapi_client’ not supported by any of the available plugins.’」

    AシステムでサーバにmariaDBを利用して開発をしているデータベースが存在しています
    （ベンダーが開発して稼働中）
    このDBをエクスポートして受け取り自分のPCにmariaDB等をインストールしてデータインポートしてVB.netでSQL参照をしようと思っています。
    (USERSIDやPASSword、databaseなどは同じものをセットしています。)

    madiaDB ver10.11
    VB.net(visual studio2022)
    mysql Connentort NET 8.0.32

    すみませんがご教授よろしくお願いいたします。

    返信
    1. kazuhiro より:
      2023年4月2日 23:22

      吉川万能ＩＴ研究所です。発見が遅れてすみません。
      まず、この事例ですが、当方では再現しませんでした。
      ただエラーメッセージからすると、私が作成したConnectorの6から、そちらでお使いの8までの間に、認証方法が変ったようです。
      お使いのmariaDBは、SSL暗号はされていますでしょうか？
      こちらの記事を作成したときは、SSLは考慮していませんが、昨今ではDBもSSL接続が当然のようで、その辺りで改変されたようです。
      こちらでも新しい環境を用意して試験してみるので、少しお時間ください。

      返信
    2. kazuhiro より:
      2023年4月3日 21:58

      こちらで、VisualStudio2022で試したところ、問題なく繋がりました。
      ご確認いただきたい点としまして、
      1．元々のデータベースは、外部から接続を許可していますか？
      →セキュリティなどの問題で、localhost接続しか許可していないという事はないでしょうか？
      　mariadbでしたら設定ファイルで、接続可能なホストを指定できます。
      そこが127.0.0.1　などで制限されていないか確認ください。
      2．使用しているユーザーは、外部から接続できますか？
      　プログラムでサンプルにしているユーザー、’user01’は　Hostの指定が％になっていて、何処からでも接続できるユーザーです。
      試しに、ユーザー権限をlocalhostに変更したところ、今回ご質問のエラーが発生しました。
      使用しているユーザーがmariadbに存在しているか、存在している場合、hostは％になっているか、確認ください。
      SELECT user, host FROM mysql.userをmariadbで実行すると、確認できるかと思います。

      返信

コメントを残す

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

ソフトウェアのスキル

前の記事

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

次の記事

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