C Sharpとデータベース - 結果の取得

概要

C#でSELECT文の実行結果を取得する方法を説明する。

結果の取得には、主に以下の2つのアプローチが存在する。

• DataTableを使用して結果を一括で読み込む方法 (DataAdapter.Fill)

  SELECT文の全結果をメモリ上のDataTableに展開する。

  データのランダムアクセスが可能であり、シンプルな実装で小～中規模のデータ処理に適する。

  
  

• DataReaderを使用して結果を1行ずつ読み込む方法 (ExecuteReader)

  前方向のみの読み取り専用カーソルで1行ずつ処理する。

  メモリ効率が良く、大量データの処理に適する。

  O/Rマッピングも容易に実装できる。

また、COUNT等の集計値を取得する場合は、ExecuteScalar メソッドを使用する方法もある。

各DBMS (SQL Server, Oracle Database, MySQL) では、対応するクラスが異なる。

• SQL Server

  SqlDataAdapter / SqlDataReader

• Oracle Database

  OracleDataAdapter / OracleDataReader

• MySQL

  MySqlDataAdapter / MySqlDataReader

SQL Server

一括読み込み (DataTable)

DataTableへSELECT文の結果を一括で読み込むことができる。

DataSetを使用する方法もあるが、DataTableを取り出すためにワンクッション必要となるため、DataTableへ直接代入する方がよい。

一括でDataTableへ読み込むには、SqlDataAdapterを使用する。

 using System;
 using System.Configuration;
 using System.Data;
 using System.Data.SqlClient;
　
 public DataTable GetData()
 {
    var table = new DataTable();
　
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
　
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          // データベースの接続開始
          connection.Open();
　
          // クエリの作成
          command.CommandText = @"SELECT ID, PASSWORD, ROLE_NAME FROM T_USER";
　
          // クエリの実行
          var adapter = new SqlDataAdapter(command);
          adapter.Fill(table);
       }
       catch (Exception exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          // データベースの接続終了
          connection.Close();
       }
    }
　
    return table;
 }

1行ずつ読み込む (SqlDataReader)

SELECT文の結果を1行ずつ読み込む場合、SqlDataReaderを使用する。
Commandの ExecuteReader() メソッドを実行することで、SqlDataReaderを取得し、1行ずつ読み込んで処理を行う。

実装はDataTable方式より手間がかかるが、O/Rマッピングのような処理が容易に実装できる。

以下の例では、UserクラスはT_USERテーブルに対応する独自のモデルクラスである。

 using System;
 using System.Collections.Generic;
 using System.Configuration;
 using System.Data.SqlClient;
 
 // T_USERテーブルに対応するモデルクラス
 public class User
 {
    public string Id       { get; set; }
    public string Password { get; set; }
    public string RoleName { get; set; }
 }
 
 public List<User> GetData()
 {
    var list = new List<User>();
 
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          // データベースの接続開始
          connection.Open();
 
          // クエリの作成
          command.CommandText = @"SELECT ID, PASSWORD, ROLE_NAME FROM T_USER";
 
          // クエリの実行
          using (var reader = command.ExecuteReader())
          {
             while (reader.Read())
             {
                list.Add(new User()
                {
                   Id = reader["ID"] as string,
                   Password = reader["PASSWORD"] as string,
                   RoleName = reader["ROLE_NAME"] as string
                });
             }
          }
       }
       catch (Exception exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          // データベースの接続終了
          connection.Close();
       }
    }
 
    return list;
 }

単一値の取得 (ExecuteScalar)

COUNT等の集計値のように単一の値を取得する場合は、ExecuteScalar メソッドを使用する。

ExecuteScalarは、結果セットの最初の行の最初の列を返す。

 using System;
 using System.Configuration;
 using System.Data.SqlClient;
 
 public int GetUserCount()
 {
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
          command.CommandText = @"SELECT COUNT(*) FROM T_USER";
          var count = (int)command.ExecuteScalar();
          return count;
       }
       catch (Exception exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

Oracle Database

Oracle Databaseに対してSELECT文を実行し、結果を取得する。

Oracle Databaseへのアクセスには、Oracle.ManagedDataAccess.Client 名前空間のクラスを使用する。

一括読み込み (DataTable)

OracleDataAdapterを使用して、SELECT文の結果をDataTableに一括で読み込む。

 using System;
 using System.Configuration;
 using System.Data;
 using Oracle.ManagedDataAccess.Client;
 
 public DataTable GetData()
 {
    var table = new DataTable();
    var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
 
    using (var connection = new OracleConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
          command.CommandText = @"SELECT ID, PASSWORD, ROLE_NAME FROM T_USER";
          var adapter = new OracleDataAdapter(command);
          adapter.Fill(table);
       }
       catch (OracleException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 
    return table;
 }

1行ずつ読み込む (OracleDataReader)

OracleDataReaderを使用して、SELECT文の結果を1行ずつ読み込む。

 using System;
 using System.Collections.Generic;
 using System.Configuration;
 using Oracle.ManagedDataAccess.Client;
 
 public List<User> GetData()
 {
    var list = new List<User>();
    var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
 
    using (var connection = new OracleConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
          command.CommandText = @"SELECT ID, PASSWORD, ROLE_NAME FROM T_USER";
 
          using (var reader = command.ExecuteReader())
          {
             while (reader.Read())
             {
                list.Add(new User()
                {
                   Id = reader["ID"].ToString(),
                   Password = reader["PASSWORD"].ToString(),
                   RoleName = reader["ROLE_NAME"].ToString()
                });
             }
          }
       }
       catch (OracleException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 
    return list;
 }

単一値の取得 (ExecuteScalar)

OracleCommandのExecuteScalarメソッドを使用して、COUNT等の集計値を取得する。

OracleのNUMBER型は、decimalにキャストする点に注意が必要である。

 using System;
 using System.Configuration;
 using Oracle.ManagedDataAccess.Client;
 
 public int GetUserCount()
 {
    var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
 
    using (var connection = new OracleConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
          command.CommandText = @"SELECT COUNT(*) FROM T_USER";
          var count = (int)(decimal)command.ExecuteScalar();
          return count;
       }
       catch (OracleException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

MySQL

MySQLに対してSELECT文を実行し、結果を取得する。

MySQLへのアクセスには、MySqlConnectorパッケージの MySqlConnector 名前空間のクラスを使用する。

一括読み込み (DataTable)

MySqlDataAdapterを使用して、SELECT文の結果をDataTableに一括で読み込む。

 using System;
 using System.Configuration;
 using System.Data;
 using MySqlConnector;
 
 public DataTable GetData()
 {
    var table = new DataTable();
    var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
 
    using (var connection = new MySqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
          command.CommandText = @"SELECT ID, PASSWORD, ROLE_NAME FROM T_USER";
          var adapter = new MySqlDataAdapter(command);
          adapter.Fill(table);
       }
       catch (MySqlException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 
    return table;
 }

1行ずつ読み込む (MySqlDataReader)

MySqlDataReaderを使用して、SELECT文の結果を1行ずつ読み込む。

 using System;
 using System.Collections.Generic;
 using System.Configuration;
 using MySqlConnector;
 
 public List<User> GetData()
 {
    var list = new List<User>();
    var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
 
    using (var connection = new MySqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
          command.CommandText = @"SELECT ID, PASSWORD, ROLE_NAME FROM T_USER";
 
          using (var reader = command.ExecuteReader())
          {
             while (reader.Read())
             {
                list.Add(new User()
                {
                   Id = reader["ID"].ToString(),
                   Password = reader["PASSWORD"].ToString(),
                   RoleName = reader["ROLE_NAME"].ToString()
                });
             }
          }
       }
       catch (MySqlException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 
    return list;
 }

単一値の取得 (ExecuteScalar)

MySqlCommandの ExecuteScalar メソッドを使用して、COUNT等の集計値を取得する。

MySQLのCOUNT(*)はlong型で返るため、longにキャストする点に注意が必要である。

 using System;
 using System.Configuration;
 using MySqlConnector;
 
 public long GetUserCount()
 {
    var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
 
    using (var connection = new MySqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
          command.CommandText = @"SELECT COUNT(*) FROM T_USER";
          var count = (long)command.ExecuteScalar();
          return count;
       }
       catch (MySqlException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

DataTableとDataReaderの比較

DataTable (DataAdapter) 方式 と DataReader方式の特徴を下表に示す。

DataTableとDataReaderの比較

項目	DataTable (DataAdapter)	DataReader
読み込み方式	一括読み込み	1行ずつ読み込み
メモリ使用量	全データをメモリに展開	現在行のみメモリに保持
アクセス方向	ランダムアクセス可能	前方向のみ
接続	Fill後に接続クローズ可能	読み込み中は接続維持が必要
適したデータ量	小〜中規模	大規模
O/Rマッピング	不向き	容易