【c#,vb】データベースを操作する方法

C#
2020.03.14

C#.NET 及び VB.NETでデータベースを操作する方法を紹介します。
APIはOLE DBを使っています。
ここで紹介する方法はSQLServer、Accessに対して操作できることを確認しています。その他のデータベースに対して操作できることは確認しておりません。
ここで紹介する方法はあくまでも一例であり、他にも方法はあるかと思います。

Accessのデータベースファイルを操作するための準備

Accessのデータベースファイル（.accdb）を操作するためには、あらかじめMicrosoft Access データベース エンジン 2010 再頒布可能コンポーネントをインストールしておく必要があります。
インストールされていない場合は下記の記事を参照してインストールしてください。

「’Microsoft.ACE.OLEDB.12.0’プロバイダーはローカルのコンピューターに登録されていません。」の対処法

接続文字列

SQLServer、Accessそれぞれのデータベースを操作するための接続文字を紹介します。

Accessのデータベースファイルを操作するための接続文字

@"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={ここへデータベースファイルの絶対パス（拡張子は除く）を入力する}.accdb;" +
"Jet OLEDB:Database Password=｛パスワード｝;";//←パスワードを付けている場合には必要となります。パスワードがない場合には不要です。
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source={ここへデータベースファイルの絶対パス（拡張子は除く）を入力する}.accdb;" & _
"Jet OLEDB:Database Password=｛パスワード｝;"'←パスワードを付けている場合には必要となります。パスワードがない場合には不要です。

※exeファイルと同じ階層にある.accdbファイルを参照する仕様にする場合は、exeファイルがどのディレクトリにあるかを取得できるSystem.IO.Directory.GetCurrentDirectory()メソッドを使うと便利です。

SQLServerのデータベースファイルを操作するための接続文字

"Provider=SQLOLEDB;Data Source=｛インスタンス名を入力する｝; Initial Catalog=｛データべース名を入力する（拡張子は不要）｝;Integrated Security=｛認証の種類を入力する。Windows認証の場合は"SSPI"でよい。｝;"

※VBの場合も上記と同じです。

SELECTの実行結果を取得する。

SQLのSELECT構文の結果を取得するには、System.Data.OleDb.OleDbCommand.ExecuteReader()メソッドを使えば良いです。

以下のサンプルでは、SELECT文の結果をSystem.Data.DataTableへ格納しています。

System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection("｛ここへ接続文字列を入力する｝");
System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand("｛ここへSQLを入力する｝", cn);
System.Data.DataTable tb = new System.Data.DataTable();
cn.Open();

try
{
    tb.Load(Com.ExecuteReader());

    //取得結果のN行目１列目を参照する
    for (int i1 = 0; i1 < tb.Rows.Count; i1++)
    {
        tb.Rows[i1][0].ToString();
    }

}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    cn.Close();
}

Dim cn As System.Data.OleDb.OleDbConnection = New OleDb.OleDbConnection("｛ここへ接続文字列を入力する｝")
Dim Com As System.Data.OleDb.OleDbCommand = New OleDb.OleDbCommand("｛ここへSQLを入力する｝", cn)
Dim tb As System.Data.DataTable = New System.Data.DataTable
cn.Open()

Try
    tb.Load(Com.ExecuteReader)

    '取得結果のN行目の"DataName"フィールドの値を参照する。
    Dim i2 As Long
    For i2 = 0 To tb.Rows.Count - 1
      tb.Rows(i2).Item("{ここへ列名を入力する}").ToString
    Next

Catch ex As Exception
    MessageBox.Show(ex.Message)
Finally
    cn.Close()
End Try

UPDATE,INSERT,DELETEでデータベースを操作する。

SQLのUPDATE,INSERT,DELETE構文でテーブルの内容を変更する場合にはSystem.Data.OleDb.OleDbCommand.ExecuteNonQuery()メソッドを使えば良いです。
トランザクションも合わせて実装しておくべきかと思います。
以下のサンプルは、トランザクションを用いてデータベースを操作します。

List<string> sqlCommands = new List<string>();
sqlCommands.Add("｛ここへ１文目のSQLを入力する｝");
sqlCommands.Add("｛ここへ２文目のSQLを入力する｝");
sqlCommands.Add("｛ここへ３文目のSQLを入力する｝");

System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection("｛ここへ接続文字列を入力する｝");
System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand();
Com.Connection = cn;

cn.Open();
Com.Transaction = cn.BeginTransaction();

try
{
    for (int i1 = 0; i1 < sqlCommands.Count; i1++)
    {
        Com.CommandText = sqlCommands[i1];
        Com.ExecuteNonQuery();
    }

    Com.Transaction.Commit();

}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
    Com.Transaction.Rollback();
}
finally
{
    cn.Close();
}
Dim sqlCommands As List(Of String) = New List(Of String)
sqlCommands.Add("｛ここへ１文目のSQLを入力する｝")
sqlCommands.Add("｛ここへ２文目のSQLを入力する｝")
sqlCommands.Add("｛ここへ３文目のSQLを入力する｝")

Dim cn As System.Data.OleDb.OleDbConnection = New OleDb.OleDbConnection("｛ここへ接続文字列を入力する｝")
Dim Com As System.Data.OleDb.OleDbCommand = New OleDb.OleDbCommand()
Com.Connection = cn

cn.Open()
Com.Transaction = cn.BeginTransaction

Try

    Dim i1 As Long
    For i1 = 0 To sqlCommands.Count - 1
        Com.CommandText = sqlCommands(i1)
        Com.ExecuteNonQuery()
    Next

    Com.Transaction.Commit()

Catch ex As Exception
    MessageBox.Show(ex.Message)
    Com.Transaction.Rollback()
Finally
    cn.Close()
End Try

集計関数の実行結果を取得する。

SQLの集計関数の結果を取得したい場合には、System.Data.OleDb.OleDbCommand.ExecuteScalar()を使えば良いかと思います。
SQLの結果の１行１列の値を取得できます。
以下サンプルです。変数”ob”にSQLの結果が格納されます。

System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection("｛ここへ接続文字列を入力する｝");
System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand("｛ここへSQLを入力する｝",cn);
Object ob;
cn.Open();

try
{
    ob = Com.ExecuteScalar();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    cn.Close();
}
Dim cn As System.Data.OleDb.OleDbConnection = New OleDb.OleDbConnection("｛ここへ接続文字列を入力する｝")
Dim Com As System.Data.OleDb.OleDbCommand = New OleDb.OleDbCommand("｛ここへSQLを入力する｝", cn)
Dim ob As Object
cn.Open()

Try
    ob = Com.ExecuteScalar
Catch ex As Exception
    MessageBox.Show(ex.Message)
Finally
    cn.Close()
End Try

データベースを操作するクラスを作りました。

上記で紹介した方法を用いてデータベースを操作するクラスを作ってみましたので紹介します。

おおまかな仕様

コンストラクター

OleDBCommand() インスタンスを初期化します。
OleDBCommand(string) 接続文字を指定してインスタンスを初期化します。

プロパティ

SqlConnect 接続文字を指定します。書き込み専用プロパティです。

メソッド

OleDBDataTable
  • 概要
    SELECTを扱うSQLを実行し、結果をDataTableへ格納します。
  • パラメーター
    sqlCommand：string型です。SELECTを扱うSQLを指定します。
  • 戻り値
    DataTable：実行したSQLの結果をDataTable型で返します。
OleDbExcuteNonQuery
  • 概要
    UPDATE,INSERT,DELETEを扱うSQLを実行し、成功可否を返します。
  • パラメーター
    sqlCommands：Listのstring型です。UPDATE,INSERT,DELETEを扱うSQLを指定します。１要素につき１つのSQLを指定できます。
  • 戻り値
    Boolean：SQLがエラーなく成功した場合はtrue、エラーが発生した場合はfalseを返します。
OleDBExcuteScalar
  • 概要
    集計関数などの実行結果が１行１列となるSQLを実行し、結果を返します。
  • パラメーター
    sqlCommand：string型です。集計関数などの実行結果が１行１列となるSQLを指定します。
  • 戻り値
    object：集計関数などの実行結果が１行１列となるSQLの実行結果を返します。

クラスの内容

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace DBConnect
{
    public class OleDBCommand
    {
        string _sqlConnect; //接続文字を格納します。

        /// <summary>
        /// インスタンス化を行います。
        /// </summary>
        public OleDBCommand()
        {

        }

        /// <summary>
        /// 接続文字を指定してインスタンス化を行います。
        /// </summary>
        public OleDBCommand(String sqlConnect)
        {
            _sqlConnect = sqlConnect;
        }

        /// <summary>
        /// 接続文字を格納します。
        /// </summary>
        //（WriteOnly Property)
        public string SqlConnect
        {
            set
            {
                _sqlConnect = value;
            }
        }

        /// <summary>
        /// SQLコマンドを実行し、実行結果を取得します。
        /// </summary>
        /// <param name="sqlCommand">実行するSQL文（SELECT文）</param>
        /// <returns></returns>
        public DataTable OleDBDataTable(string sqlCommand)
        {

            System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(_sqlConnect);
            System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand(sqlCommand, cn);
            DataTable respTable = new DataTable();

            cn.Open();

            try
            {
                respTable.Load(Com.ExecuteReader());
                return respTable;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
            finally
            {
                cn.Close();
            }

        }

        /// <summary>
        /// SQLコマンドを実行します。
        /// </summary>
        /// <param name="sqlCommands">実行するSQL文（UPDATE,INSERT,DELETE文）</param>
        /// <returns>正常終了:=true,異常終了:=false</returns>
        public Boolean OleDbExcuteNonQuery(List<string> sqlCommands)
        {
            System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(_sqlConnect);
            System.Data.OleDb.OleDbTransaction OleTran;
            System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand();
            Com.Connection = cn;

            cn.Open();
            OleTran = cn.BeginTransaction();
            Com.Transaction = OleTran;

            try
            {
                for (int i1 = 0; i1 < sqlCommands.Count; i1++)
                {
                    Com.CommandText = sqlCommands[i1];
                    Com.ExecuteNonQuery();
                }

                OleTran.Commit();
                return true;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                OleTran.Rollback();
                return false;
            }
            finally
            {
                cn.Close();
            }

        }

        /// <summary>
        /// SQLコマンドを実行し、実行結果を１つだけ取得します。
        /// </summary>
        /// <param name="sqlCommand">実行結果が１行１列となるSQL文(集計関数等に有効)</param>
        /// <returns>実行結果（1つだけ返す。</returns>
        public object OleDBExcuteScalar(string sqlCommand)
        {
            System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(_sqlConnect);
            System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand(sqlCommand,cn);
            Object RespObject;

            cn.Open();

            try
            {
                RespObject = Com.ExecuteScalar();
                return RespObject;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
            finally
            {
                cn.Close();
            }
        }
    }
}
Imports System.Windows.Forms

Public Class OleDBCommand

    Private _sqlConnect As String '接続文字を格納します。

    ''' <summary>
    ''' インスタンス化を行います。
    ''' </summary>
    Sub New()

    End Sub

    ''' <summary>
    ''' 接続文字を指定してインスタンス化します。
    ''' </summary>
    ''' <param name="sqlConnect">接続文字</param>
    ''' <remarks></remarks>
    Public Sub New(ByVal sqlConnect As String)
        _sqlConnect = sqlConnect
    End Sub

    ''' <summary>
    ''' 接続文字を格納します。
    ''' </summary>
    ''' <value></value>
    ''' <remarks></remarks>
    Public WriteOnly Property SqlConnect()
        Set(ByVal value)
            _sqlConnect = value
        End Set
    End Property

    ''' <summary>
    ''' SQLコマンドを実行し、実行結果を取得します。
    ''' </summary>
    ''' <param name="sqlCommand">実行するSQL文（SELECT文)</param>
    ''' <returns>実行結果</returns>
    ''' <remarks></remarks>
    Public Function OleDBDataTable(sqlCommand As String) As DataTable

        Dim cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(_sqlConnect)
        Dim Com As OleDb.OleDbCommand = New OleDb.OleDbCommand(sqlCommand, cn)
        Dim respTable As DataTable = New DataTable

        cn.Open()

        Try
            respTable.Load(Com.ExecuteReader)

            Return respTable

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return Nothing
        Finally
            cn.Close()
        End Try

    End Function

    ''' <summary>
    ''' SQLコマンドを実行します。
    ''' </summary>
    ''' <param name="sqlCommands">実行するSQL文(UPDATE又はDELETE文)</param>
    ''' <returns>実行件数</returns>
    ''' <remarks></remarks>
    Public Function OleDbExcuteNonQuery(sqlCommands As List(Of String)) As Boolean

        Dim cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(_sqlConnect)
        Dim OleTran As OleDb.OleDbTransaction
        Dim Com As OleDb.OleDbCommand = New OleDb.OleDbCommand()
        Com.Connection = cn

        cn.Open()
        OleTran = cn.BeginTransaction
        Com.Transaction = OleTran

        Try

            Dim i1 As Long
            For i1 = 0 To sqlCommands.Count - 1
                Com.CommandText = sqlCommands(i1)
                Com.ExecuteNonQuery()
            Next

            OleTran.Commit()
            Return True

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            OleTran.Rollback()
            Return False
        Finally
            cn.Close()
        End Try

    End Function

    ''' <summary>
    ''' SQLコマンドを実行し、実行結果を1つだけ取得します。
    ''' </summary>
    ''' <param name="sqlCommand">実行結果が1行1列となるSQL文(集計関数等に有効)</param>
    ''' <returns>実行結果（1つだけ返す)。</returns>
    ''' <remarks></remarks>
    Public Function OleDBExcuteScalar(sqlCommand As String) As Object

        Dim cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(_sqlConnect)
        Dim Com As OleDb.OleDbCommand = New OleDb.OleDbCommand(sqlCommand, cn)
        Dim RespObject As Object

        cn.Open()

        Try
            RespObject = Com.ExecuteScalar
            Return RespObject

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return Nothing
        Finally
            cn.Close()
        End Try

    End Function

End Class

クラスの操作方法

//インスタンス化する際に接続文字を入力する。
DBConnect.OleDBCommand Ref = new DBConnect.OleDBCommand(
    "｛ここへ接続文字列を入力する｝"
);

//OleDBDataTableメソッドの使用例
System.Data.DataTable ResultTable;
ResultTable = Ref.OleDBDataTable("｛ここへSELECTを文を使うSQLを入力する｝");
for(int i1 = 0; i1 < ResultTable.Rows.Count; i1++)
{
    ResultTable.Rows[i1][0].ToString();
}

//OleDBExcuteScalarメソッドの使用例
object ob;
ob = Ref.OleDBExcuteScalar("ここへ集計関数などの１行１列の結果になるSQLを入力する。");

//OleDbExcuteNonQueryメソッドの使用例
List<string> sql = new List<string>();
sql.Add("｛ここへINSERTやUPDATTE、DELETEを文を使うSQLを入力する｝");
sql.Add("｛ここへINSERTやUPDATTE、DELETEを文を使うSQLを入力する｝");
sql.Add("｛ここへINSERTやUPDATTE、DELETEを文を使うSQLを入力する｝");
Ref.OleDbExcuteNonQuery(sql);
'インスタンス化する際に接続文字を入力する。
Dim Ref As OleDBCommand = New OleDBCommand("｛ここへ接続文字列を入力する｝")

'OleDBDataTableメソッドの使用例
Dim tb As DataTable
tb = Ref.OleDBDataTable("｛ここへSELECTを文を使うSQLを入力する｝")

Dim i1 As Long
For i1 = 0 To tb.Rows.Count - 1
    tb.Rows(i1).Item("{ここへ列名を入力する}").ToString
Next

'OleDBExcuteScalarメソッドの使用例
Dim ob As Object
ob = Ref.OleDBExcuteScalar("ここへ集計関数などの１行１列の結果になるSQLを入力する。")

'OleDbExcuteNonQueryメソッドの使用例
Dim sqlCommands As List(Of String) = New List(Of String)
sqlCommands.Add("｛ここへINSERTやUPDATTE、DELETEを文を使うSQLを入力する｝")
sqlCommands.Add("｛ここへINSERTやUPDATTE、DELETEを文を使うSQLを入力する｝")
sqlCommands.Add("｛ここへINSERTやUPDATTE、DELETEを文を使うSQLを入力する｝")

Ref.OleDbExcuteNonQuery(sqlCommands)

以上となります。
参考になれば幸いです。

タイトルとURLをコピーしました