[C#] 46. データベース(MSSQL)に接続する方法

こんにちは。明月です。

この投稿はC#でデータベース(MSSQL)に接続する方法に関する説明です。

我々がプログラムを作成すればデータを格納する場合が多いですが、IOを利用するファイルで格納する方法やSocketサーバを利用して他のPCやプログラムを利用して格納する方法などの様々な方法があります。

でも、データを格納する方法で一番しやすいし、検索やフィルターする方法でデータベースを利用する方法があります。

データベースの種類はすごく多いですが、その中でOracle(オラクル)、MsSQL(Sql-server)、MySqlあるいはMariaDBがよく使うデータベースです。

その中でMsSQLはMS(Microsoft)社で提供するデータベースだし、C#もMS(Microsoft)社で提供するプログラム言語なのでC#では他のデータベースよりMsSQLが扱いしやすいです。扱いしやすいより別途のライブラリがなくても使えるデータベースです。

まず、データベースを接続するためにはデータベースをインストールしなければならないですが、それは別途の投稿で説明します。

link - 予定

データベースがインストールされたら簡単なテーブルを作成してC#プログラムで検索してみましょう。

-- drop table Test;
-- テーブル作成
create table Test(
	idx int identity(1,1),
	data nvarchar(max)
)
-- データインサート
insert into Test values('Hello world - 1');
insert into Test values('Hello world - 2');
insert into Test values('Hello world - 3');
insert into Test values('Hello world - 4');
insert into Test values('Hello world - 5');
insert into Test values('Hello world - 6');
insert into Test values('Hello world - 7');
insert into Test values('Hello world - 8');
insert into Test values('Hello world - 9');
insert into Test values('Hello world - 10');
insert into Test values('Hello world - 11');
insert into Test values('Hello world - 12');
-- 検索
select * from Test;

上の例みたいにデータベースでテーブルを作成して簡単なデータを入力しました。

このデータを利用してC#プログラムでデータを取得しましょう。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace Example
{
  class Program
  {
    // 実行関数
    static void Main(string[] args)
    {
      // SqlCommandクラスのインスタンス生成
      var cmd = new SqlCommand();
      // 接続コネクションを生成する。
      cmd.Connection = new SqlConnection("Data Source=localhost;Database=BlogExample;User Id=sa;Password=");
      // 検索するクエリを入れる。
      cmd.CommandText = "select * from Test";
      // 検索クエリタイプ、一般クエリはTextだし、プロシージャはStoredProcedureだ。
      cmd.CommandType = CommandType.Text;
      // 検索して格納するデータ変数
      List<List<object>> dataList = null;
      // 検索したテーブルのColumn変数
      string[] fieldList = null;
      // Close設定(スタック領域が終わったら、自動にコネクション終了)
      using (cmd.Connection)
      {
        // コネクション Open
        cmd.Connection.Open();
        // 上のクエリを接続してSqlDataReaderを取得
        var dr = cmd.ExecuteReader();
        // データ変数のインスタンス生成
        dataList = new List<List<object>>();
        // Column変数のインスタンス生成
        fieldList = new string[dr.FieldCount];
        // テーブルのフィールドほど
        for (var i = 0; i < fieldList.Length; i++)
        {
          // フィールド名を配列に格納
          fieldList[i] = dr.GetName(i);
        }
        // レコードの個数程にループ
        while (dr.Read())
        {
          // レコードデータを格納する変数リストを生成
          var entity = new List<object>();
          // データ変数リストに格納
          dataList.Add(entity);
          // Columnのサイズ程
          for (var i = 0; i < fieldList.Length; i++)
          {
            // カラムのタイプを取得
            var type = dr.GetFieldType(i);
            // intタイプなら
            if (type == typeof(int))
            {
              // intタイプで取得
              entity.Add(dr.GetInt32(i));
            }
            // stringタイプなら
            else if (type == typeof(string))
            {
              // stringタイプで取得
              entity.Add(dr.GetString(i));
            }
            else
            {
              // objectタイプで取得
              entity.Add(dr.GetValue(i));
            }
          }
        }
      }
      // コンソールにヘッダーにColumn名を出力する。
      foreach (var field in fieldList)
      {
        // コンソール出力
        Console.Write(field);
        // tabの二つをコンソールに出力
        Console.Write("\t\t");
      }
      // 改行
      Console.WriteLine();
      // テーブルColumn名とデータを区分するため、コンソール出力
      for (int i = 0; i < fieldList.Length; i++)
      {
        // コンソール出力
        Console.Write("-----------------");
      }
      // 改行
      Console.WriteLine();
      // データのサイズ程
      foreach (var entity in dataList)
      {
        // Columnのサイズ程
        foreach (var column in entity)
        {
          // コンソール出力
          Console.Write(column);
          // tabの二つをコンソールに出力
          Console.Write("\t\t");
        }
        // 改行
        Console.WriteLine();
      }

      // 任意のキーを押してください
      Console.WriteLine("Press Any key...");
      Console.ReadLine();
    }
  }
}

上の例をみればデータベースにあるTestのテーブルのデータを読み込んでコンソールに出力しました。

SqlCommandクラスのインスタンスを生成してコネクションを作ってExecuteReaderの関数を実行してデータを読み込みました。

そしてExecuteReaderの関数のリターン値はSqlDataReaderのインスタンスだし、SqlDataReaderのRead関数を利用してデータを読み込みました。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace Example
{
  class Program
  {
    // 実行関数
    static void Main(string[] args)
    {
      // SqlCommandクラスのインスタンス生成
      var cmd = new SqlCommand();
      // 接続コネクションを生成する。
      cmd.Connection = new SqlConnection("Data Source=localhost;Database=BlogExample;User Id=sa;Password=");
      // 検索クエリタイプ、一般クエリはTextだし、プロシージャはStoredProcedureだ。
      cmd.CommandType = CommandType.Text;
      // 検索して格納するデータ変数
      List<List<object>> dataList = null;
      // 検索したテーブルのColumn変数
      string[] fieldList = null;
      // Close設定(スタック領域が終わったら、自動にコネクション終了)
      using (cmd.Connection)
      {
        // コネクション Open
        cmd.Connection.Open();
        // データ削除クエリ
        cmd.CommandText = "delete Test";
        // 実行(リターン値が必要ない。)
        cmd.ExecuteNonQuery();
        
        // データ追加クエリ
        cmd.CommandText = "insert into Test values(@data)";
        // @dataパラメータにデータを入力する。
        cmd.Parameters.Add(new SqlParameter("@data", "Hello world - Addition"));
        // 実行(リターン値が必要ない。)
        cmd.ExecuteNonQuery();
        
        // 検索するクエリを入れる。
        cmd.CommandText = "select * from Test";
        // 上のクエリを接続してSqlDataReaderを取得
        var dr = cmd.ExecuteReader();
        // データ変数のインスタンス生成
        dataList = new List<List<object>>();
        // Column変数のインスタンス生成
        fieldList = new string[dr.FieldCount];
        // テーブルのフィールドほど
        for (var i = 0; i < fieldList.Length; i++)
        {
          // フィールド名を配列に格納
          fieldList[i] = dr.GetName(i);
        }
        // レコードの個数程にループ
        while (dr.Read())
        {
          // レコードデータを格納する変数リストを生成
          var entity = new List<object>();
          // データ変数リストに格納
          dataList.Add(entity);
          // Columnのサイズ程
          for (var i = 0; i < fieldList.Length; i++)
          {
            // カラムのタイプを取得
            var type = dr.GetFieldType(i);
            // intタイプなら
            if (type == typeof(int))
            {
              // intタイプで取得
              entity.Add(dr.GetInt32(i));
            }
            // stringタイプなら
            else if (type == typeof(string))
            {
              // stringタイプで取得
              entity.Add(dr.GetString(i));
            }
            else
            {
              // objectタイプで取得
              entity.Add(dr.GetValue(i));
            }
          }
        }
      }
      // コンソールにヘッダーにColumn名を出力する。
      foreach (var field in fieldList)
      {
        // コンソール出力
        Console.Write(field);
        // tabの二つをコンソールに出力
        Console.Write("\t\t");
      }
      // 改行
      Console.WriteLine();
      // テーブルColumn名とデータを区分するため、コンソール出力
      for (int i = 0; i < fieldList.Length; i++)
      {
        // コンソール出力
        Console.Write("-----------------");
      }
      // 改行
      Console.WriteLine();
      // データのサイズ程
      foreach (var entity in dataList)
      {
        // Columnのサイズ程
        foreach (var column in entity)
        {
          // コンソール出力
          Console.Write(column);
          // tabの二つをコンソールに出力
          Console.Write("\t\t");
        }
        // 改行
        Console.WriteLine();
      }

      // 任意のキーを押してください
      Console.WriteLine("Press Any key...");
      Console.ReadLine();
    }
  }
}

上の例ではdeleteとinsertをしてデータを削除と追加しました。

そして関数はExecuteReaderの関数ではなく、ExecuteNonQueryの関数を使って実行しました。

なぜならdeleteとinsertは検索の結果を取得することではなく、実行だけが重要だからです。

上の方法は連結指向タイプのデータベース接続だといいます。つまり、データベースからデータを一括に取得することではなく、Read()関数を通って一つ一つに取得することです。連結指向タイプでシステムに関する負担は少ないと思いますが、データが多いなら、データベースのlockやトランザクションに問題がある可能性があります。

そのため、レコードを一つ一つに読み込む連結指向タイプではなく、データを一括に取得する非連結指向タイプの接続方法もあります。

using System;
using System.Data;
using System.Data.SqlClient;

namespace Example
{
  class Program
  {
    // 実行関数
    static void Main(string[] args)
    {
      // SqlDataAdapterインスタンス生成
      var sda = new SqlDataAdapter();
      // SqlCommandクラスのインスタンス生成
      sda.SelectCommand = new SqlCommand();
      // 接続コネクションのインスタンスを生成する。
      sda.SelectCommand.Connection = new SqlConnection("Data Source=localhost;Database=BlogExample;User Id=sa;Password=");
      // 検索したクエリを格納
      sda.SelectCommand.CommandText = "select * from Test";
      // 検索クエリタイプ、一般クエリはTextだし、プロシージャはStoredProcedureだ。
      sda.SelectCommand.CommandType = CommandType.Text;
      // DataSetインスタンスを生成
      var ds = new DataSet();
      // SqlDataAdapterインスタンスにDataSetデータを入れる。
      sda.Fill(ds);
      // 検索したテーブル個数程
      foreach (DataTable table in ds.Tables)
      {
        // コンソールにヘッダーにColumn名を出力する。
        foreach (DataColumn column in table.Columns)
        {
          // コンソール出力
          Console.Write(column.ColumnName);
          // tabの二つをコンソールに出力
          Console.Write("\t\t");
        }
        // 改行
        Console.WriteLine();
        // テーブルColumn名とデータを区分するため、コンソール出力
        for (int i = 0; i < table.Columns.Count; i++)
        {
          // コンソール出力
          Console.Write("-----------------");
        }
        // 改行
        Console.WriteLine();
        // テーブルのレコード個数程
        foreach (DataRow row in table.Rows)
        {
          // Columnのサイズ程
          foreach (DataColumn column in table.Columns)
          {
            // コンソールに出力
            Console.Write(row[column]);
            // tabの二つをコンソールに出力
            Console.Write("\t\t");
          }
          // 改行
          Console.WriteLine();
        }
      }

      // 任意のキーを押してください
      Console.WriteLine("Press Any key...");
      Console.ReadLine();
    }
  }
}

結果は連結指向タイプでデータを取得することと同じです。データベースからデータを取得した結果は同じですが、データを取得する方法に関する差異があります。

この場合はデータを一括で取得してDataSetに格納するので、データが多くなるとプログラム側に多いインスタンス生成により逆に遅くなるしメモリ負担になります。代わりにデータベースには一括にSelectして結果を送信した後なので、負担がないですね。

仕様に合わせて連結指向タイプで実装するか非連結指向タイプに実装するかを決めたら良いです。

ここまでC#でデータベース(MSSQL)に接続する方法に関する説明でした。

ご不明なところや間違いところがあればコメントしてください。