.NET CoreからDBに繋いでみる

開発環境がひと段落したので、ぼちぼちコードを書いていきます。

前々回、PCLプロジェクトを.NET Standardプロジェクトに変換しました。
ええ、既にPCLになってるヤツは、いいんです。

問題は、旧来の.NET Frameworkのソースです。
いやもう、すんげえ、しんどい。

たとえばDBアクセス。
2.0時代からDataTableが大好きだったんですが、.NET Standardプロジェクトの名前空間には、DataTableもDataRowもDataAdapterも存在しません。
うええ。しぬ...。

まあしかし。
ちょうどXamarinでSQLiteに繋ぎたくて、手持ちのDBライブラリが修正待ちでした。
色々としゃーないので、DB接続を最初から試していきます。

環境

ビルドターゲットは、.NET Standard 1.3です。
ASP.NET Coreはもとより、Xamarin.iOS/Androidでも動くことが目標です。
バージョン1.3の根拠は、SQL Server接続用NuGetパッケージSystem.Data.SqlClientの依存バージョンです。

また、Xamarinではまだ対応していないEntity Frameworkは、今回は対象外にします。

ひとまず接続対象は、SQL Serverで。
とはいえ、なるべくSystem.Data.Commonの抽象クラスを念頭に置いて実装します。
後日に基底クラスとSQL Server用クラスに分離して、MySQL や SQLite のサブクラスを書く予定です。

接続、切断

まずは接続と切断。
特に以前と変わりなく、接続文字列を渡してOpenする感じですね。

var connectionString
    = string.Format("server={0};user id={1}; password={2}; database={3}; pooling=false",
        "サーバのアドレス or ホスト名",
        "DBユーザー名",
        "DBユーザーのパスワード",
        "DBインスタンス名");

var connection = new System.Data.SqlClient.SqlConnection();
connection.ConnectionString = connectionString;
connection.Open();

//何か処理する。

connection.Close();

ここまでの実装ソース

SELECTでないコマンド

INSERT とか UPDATE とかですね。
ここも変わりないです。

var connection = new System.Data.SqlClient.SqlConnection();
connection.ConnectionString = "DB接続文字列";
connection.Open();

var command = new System.Data.SqlClient.SqlCommand();
command.Connection = connection;
command.CommandText = "Non-QueryなSQL";
var result = command.ExecuteNonQuery();
command.Dispose();

connection.Close();

ここまでの実装ソース

DbDataReader

この辺は、懐かしい。
VB6のDAOとか、こんな感じで書いてました。
DataSetにFillするより、処理は早いですよね。

var connection = new System.Data.SqlClient.SqlConnection();
connection.ConnectionString = "DB接続文字列";
connection.Open();

var command = new System.Data.SqlClient.SqlCommand();
command.CommandText = "SELECT文";

var reader = command.ExecuteReader(CommandBehavior.SingleResult);
while (reader.Read())
{
    var value = reader.GetValue(0);
}
command.Dispose();

connection.Close();

ここまでの実装ソース

DataTableに変わるもの

さて、いよいよ課題に差し掛かりました。
DataTableのように、SELECTクエリの結果に応じて動的に列を生成してほしい。
カラムの型情報も保持しといてほしい。

IEnumerable<IDataRecord>をまるっと保持させようかと思ったんですが、なんかメモリをドカ食いしそうな気がします。

かっこ悪いですが、データは単なるobject配列にして。
行オブジェクトからは、カラム名で値を取得できるようにします。

DbDataReaderからはReadOnlyCollection<DbColumn>のカラム情報が取れました。
データとカラム情報だけ貰ったら、DbDataReaderは破棄してしまいましょう。

DataTableの代わり：ResultTableクラス

public class ResultTable : IDisposable
{
    private ReadOnlyCollection<DbColumn> _columns;
    public ReadOnlyCollection<DbColumn> Columns => _columns;

    private ResultRow[] _rows;
    public ResultRow[] Rows => _rows;

    private int _columnCount;
    public int ColumnCount => _columnCount;

    private int _rowCount;
    public int RowCount => _rowCount;

    private Dictionary<string, int> _columnNameIndexes;


    public ResultTable(DbDataReader reader)
    {
        if (reader == null
            || !reader.CanGetColumnSchema())
        {
            throw new ArgumentException("DbDataReader has no column schema");
        }


        //Columns
        this._columns = reader.GetColumnSchema();

        this._columnNameIndexes = new Dictionary<string, int>();
        for (var i = 0; i < this._columns.Count; i++)
            this._columnNameIndexes.Add(this._columns[i].ColumnName, i);

        this._columnCount = this._columns.Count;


        //Rows
        var rows = new List<ResultRow>();
        while (reader.Read())
            rows.Add(new ResultRow(this, reader));

        this._rows = rows.ToArray();
        this._rowCount = this._rows.Length;
    }


    public DbColumn Column(int index)
    {
        return this._columns[index];
    }


    public DbColumn Column(string columnName)
    {
        return this._columns[this.GetColumnIndex(columnName)];
    }


    public int GetColumnIndex(string columnName)
    {
        return this._columnNameIndexes[columnName];
    }


    public void Dispose()
    {
        foreach (var row in this._rows)
            row.Dispose();

        this._rows = null;

        this._columns = null;
        this._columnNameIndexes = null;
    }
}

DataRowの代わり：ResultRowクラス

public class ResultRow : IDisposable
{
    private object[] _items;
    private ResultTable _table;

    public ResultRow(ResultTable table, IDataRecord dataRecord)
    {
        this._table = table;
            
        this._items = new object[this._table.ColumnCount];
        dataRecord.GetValues(this._items);
    }


    public object Item(int index)
    {
        return this._items[index];
    }


    public object Item(string columnName)
    {
        return this._items[this._table.GetColumnIndex(columnName)];
    }


    public void Dispose()
    {
        if (this._items != null)
        {
            for (var i = 0; i < this._items.Length; i++)
                this._items[i] = null;
        }

        this._items = null;
        this._table = null;
    }
}

DbDataReaderからResultTableを取得すると、こんな感じです。

var connection = new System.Data.SqlClient.SqlConnection();
connection.ConnectionString = "DB接続文字列";
connection.Open();

var command = new System.Data.SqlClient.SqlCommand();
command.CommandText = "SELECT文";

var reader = command.ExecuteReader(CommandBehavior.SingleResult);
command.Dispose();

var resultTable = new ResultTable(reader);
reader.Dispose();

connection.Close();

ここまでの実装ソース

任意クラスの配列を返すジェネリックメソッド

自由にSELECTクエリを書けるようになりました。
しかし、C#は静的型付け言語。戻り値の型を指定出来ると便利です。

Entity FrameworkにはDataReader.AutoMap<T>というのがあるようですが...。
そういう便利メソッドは、見当たりません。

自力でマッピングするサンプルがありましたので、要所をまるっと頂いて実装しました。

public T[] Query<T>(string sql)
{
    try
    {
        var result = new List<T>();
        var props = typeof(T).GetRuntimeProperties().ToArray();
        var reader = this.GetReader(sql);  //<- SQL文字列からDbDataReaderを取得するメソッド

        var done = false;
        var matchProps = new List<PropertyInfo>();

        while (reader.Read())
        {
            if (!done)
            {
                var columnNames = new List<string>();
                for (var i = 0; i < reader.FieldCount; i++)
                    columnNames.Add(reader.GetName(i));

                matchProps.AddRange(props.Where(prop => columnNames.Contains(prop.Name)));
                done = true;
            }

            var row = Activator.CreateInstance<T>();
            foreach (var property in matchProps)
                property.SetValue(row, reader[property.Name]);

            result.Add(row);
        }

        reader.Dispose();
        return result.ToArray();
    }
    catch (Exception)
    {
        throw;
    }
}

ここまでの実装ソース

参考：
DataReader からクラスにマッピングのサンプル - Qiita

パラメータを差し込んでくれるように

いまのところ、SQL文は全て手書き前提の実装になってます。

このままだとサーバサイドをやるとき、インジェクション対策がめんどいですね。
そこで、DbParameterを使って渡し値をセットできるようにします。

SELECTクエリもNon-Queryメソッドも、実行するたびにDbCommandを生成します。
渡し値パラメータのセットは、そのロジックを切り出して共用するようにします。

private DbCommand GetCommand(DbParameter[] parameters = null)
{
    var result = new System.Data.SqlClient.SqlCommand();
    result.Connection = (System.Data.SqlClient.SqlConnection) this._connection;

    if (parameters != null
        && parameters.Length > 0)
    {
        result.Parameters.AddRange(parameters);
    }

    return result;
}

public int Execute(string sql, DbParameter[] parameters = null)
{
    var command = this.GetCommand(parameters);
    --- 以下略 ---
}

public DbDataReader GetReader(string sql, DbParameter[] parameters = null)
{
    var command = this.GetCommand(parameters);
    --- 以下略 ---
}

public ResultTable Query(string sql, DbParameter[] parameters = null)
{
    var command = this.GetCommand(parameters);
    --- 以下略 ---
}

public T[] Query<T>(string sql, DbParameter[] parameters = null)
{
    try
    {
        var result = new List<T>();
        var props = typeof(T).GetRuntimeProperties().ToArray();
        var reader = this.GetReader(sql, parameters);
        --- 以下略 ---
    }
}

ここまでの実装ソース

ひとまず、最低限のDB入出力が確認できました！