SQLServerのテーブルに対応するC#のデータ型（O/Rマッパー作成）

客先の旧システムで使用しているライブラリに、SQLServerに接続してDataSetを操作できるユーティリティークラスが作成されており、その資産を活かした簡易なO/Rマッパーが作れないか？との依頼がありました。
とりあえず、Entity Frameworkという便利なフレームワークがあるので、それを使いましょうと提案したんですが、あっさり断られ作ることになってしまいました。（Entity Frameworkは学習コストが高いらしく、旧システムの開発者でも簡単に使えるO/Rマッパーが必要なんだそうです。）
SQLServerのテーブルからC#のクラス定義を生成する処理を実装する時に、今まであまり深く考えていませんでしたが、テーブルの列に対応するC#のデータ型ってどうなるのかな？と思ったのでまとめてみました。

1 使用するSQLServerのバージョン
2 使用するテーブル
- 2.1 T_ALL_COLUMNS
3 データ型を取得する
4 Entity FrameworkのData Model

使用するSQLServerのバージョン

SQLServerのバージョンは2017を使います。

使用するテーブル

データ型の一覧を取得するために、以下のテーブルを作成します。

T_ALL_COLUMNS

SQLServerのテーブルで指定できる全ての種類のデータ型の列を持つテーブルです。

列名	データ型
C_BIGINT	bigint
C_BINARY	binary(50)
C_BIT	bit
C_CHAR	char(10)
C_DATE	date
C_DATETIME	datetime
C_DATETIME2	datetime2(7)
C_DATETIMEOFFSET	datetimeoffset(7)
C_DECIMAL	decimal(18, 0)
C_FLOAT	float
C_GEOGRAPHY	geography
C_GEOMETRY	geometry
C_HIERARCHYID	hierarchyid
C_IMAGE	image
C_INT	int
C_MONEY	money
C_NCHAR	nchar(10)
C_NTEXT	ntext
C_NUMERIC	numeric(18, 0)
C_NVARCHAR	nvarchar(50)
C_REAL	real
C_SMALLDATETIME	smalldatetime
C_SMALLINT	smallint
C_SMALLMONEY	smallmoney
C_SQL_VARIANT	sql_variant
C_TEXT	text
C_TIME	time(7)
C_TIMESTAMP	timestamp
C_TINYINT	tinyint
C_UNIQUEIDENTIFIER	uniqueidentifier
C_VARBINARY	varbinary(50)
C_VARCHAR	varchar(50)
C_XML	xml

データ型を取得する

INFORMATION_SCHEMAで列情報を取得するSQL

select
	TABLE_NAME,
	COLUMN_NAME
from
	INFORMATION_SCHEMA.COLUMNS
where
	TABLE_NAME = 'T_ALL_COLUMNS'

select

TABLE_NAME,

COLUMN_NAME

from

INFORMATION_SCHEMA.COLUMNS

where

TABLE_NAME = 'T_ALL_COLUMNS'

SQLServerのシステムビューからテーブルの列情報を取得して変換しようと思ったんですが、そもそも変換先のデータ型がわからないので、できるわけがありません。
なので、SqlDataAdapterクラスのFillメソッドで、DataTableを取得してデータ型を確認することにします。

DataTableを取得するC#のコード

/// <summary>
/// SQLコネクションオブジェクトを作成するメソッド
/// </summary>
/// <returns></returns>
private SqlConnection CreateConnection()
{
    // Windows 認証で SQLServerに接続するための接続文字列
    string connectionString = @"Data Source=サーバー名;Initial Catalog=データベース名;Integrated Security=True";
    // SQL コネクションの作成
    SqlConnection sqlConnection = new SqlConnection(connectionString);

    return sqlConnection;
}

/// <summary>
/// データテーブルを取得するメソッド
/// </summary>
/// <returns></returns>
public DataTable GetDataTable()
{
    // テーブル名
    string tableName = "T_ALL_COLUMNS";
    // SQLコネクションオブジェクトを作成するメソッドを呼び出す
    SqlConnection sqlConnection = CreateConnection();
    // SQLコマンドとSQLデータアダプターを使用してデータテーブルを取得
    string cmdText = $"select * from dbo.{tableName}";
    SqlCommand selectCommand = new SqlCommand(cmdText, sqlConnection);
    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand);
    DataTable dataTable = new DataTable(tableName);
    sqlDataAdapter.Fill(dataTable);

    return dataTable;
}

/// <summary>

/// SQLコネクションオブジェクトを作成するメソッド

/// </summary>

/// <returns></returns>

private SqlConnection CreateConnection()

{

// Windows 認証で SQLServerに接続するための接続文字列

string connectionString = @"Data Source=サーバー名;Initial Catalog=データベース名;Integrated Security=True";

// SQL コネクションの作成

SqlConnection sqlConnection = new SqlConnection(connectionString);

return sqlConnection;

}

/// <summary>

/// データテーブルを取得するメソッド

/// </summary>

/// <returns></returns>

public DataTable GetDataTable()

{

// テーブル名

string tableName = "T_ALL_COLUMNS";

// SQLコネクションオブジェクトを作成するメソッドを呼び出す

SqlConnection sqlConnection = CreateConnection();

// SQLコマンドとSQLデータアダプターを使用してデータテーブルを取得

string cmdText = $"select * from dbo.{tableName}";

SqlCommand selectCommand = new SqlCommand(cmdText, sqlConnection);

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand);

DataTable dataTable = new DataTable(tableName);

sqlDataAdapter.Fill(dataTable);

return dataTable;

}

取得したデータテーブルのデータカラムをループしてColumnNameとDataTypeを取得した結果、以下のリストのようになりました。

データテーブルの列のデータ型

ColumnName	DataType
C_BIGINT	System.Int64
C_BINARY	System.Byte[]
C_BIT	System.Boolean
C_CHAR	System.String
C_DATE	System.DateTime
C_DATETIME	System.DateTime
C_DATETIME2	System.DateTime
C_DATETIMEOFFSET	System.DateTimeOffset
C_DECIMAL	System.Decimal
C_FLOAT	System.Double
C_GEOGRAPHY	Microsoft.SqlServer.Types.SqlGeography
C_GEOMETRY	Microsoft.SqlServer.Types.SqlGeometry
C_HIERARCHYID	Microsoft.SqlServer.Types.SqlHierarchyId
C_IMAGE	System.Byte[]
C_INT	System.Int32
C_MONEY	System.Decimal
C_NCHAR	System.String
C_NTEXT	System.String
C_NUMERIC	System.Decimal
C_NVARCHAR	System.String
C_REAL	System.Single
C_SMALLDATETIME	System.DateTime
C_SMALLINT	System.Int16
C_SMALLMONEY	System.Decimal
C_SQL_VARIANT	System.Object
C_TEXT	System.String
C_TIME	System.TimeSpan
C_TIMESTAMP	System.Byte[]
C_TINYINT	System.Byte
C_UNIQUEIDENTIFIER	System.Guid
C_VARBINARY	System.Byte[]
C_VARCHAR	System.String
C_XML	System.String

geography, geometry, hierarchyid型の列は、空間データ型を扱う際に使用されるMicrosoft.SqlServer.Types 名前空間のクラス、構造体として取得されるようです。

Entity FrameworkのData Model

参考までにEntity FrameworkのEntity Data Modelクラスを作成した場合の結果を載せておきます。

public partial class T_ALL_COLUMNS
{
    public long C_BIGINT { get; set; }
    public byte[] C_BINARY { get; set; }
    public Nullable<bool> C_BIT { get; set; }
    public string C_CHAR { get; set; }
    public Nullable<System.DateTime> C_DATE { get; set; }
    public Nullable<System.DateTime> C_DATETIME { get; set; }
    public Nullable<System.DateTime> C_DATETIME2 { get; set; }
    public Nullable<System.DateTimeOffset> C_DATETIMEOFFSET { get; set; }
    public Nullable<decimal> C_DECIMAL { get; set; }
    public Nullable<double> C_FLOAT { get; set; }
    public System.Data.Entity.Spatial.DbGeography C_GEOGRAPHY { get; set; }
    public System.Data.Entity.Spatial.DbGeometry C_GEOMETRY { get; set; }
    public byte[] C_IMAGE { get; set; }
    public Nullable<int> C_INT { get; set; }
    public Nullable<decimal> C_MONEY { get; set; }
    public string C_NCHAR { get; set; }
    public string C_NTEXT { get; set; }
    public Nullable<decimal> C_NUMERIC { get; set; }
    public string C_NVARCHAR { get; set; }
    public Nullable<float> C_REAL { get; set; }
    public Nullable<System.DateTime> C_SMALLDATETIME { get; set; }
    public Nullable<short> C_SMALLINT { get; set; }
    public Nullable<decimal> C_SMALLMONEY { get; set; }
    public string C_TEXT { get; set; }
    public Nullable<System.TimeSpan> C_TIME { get; set; }
    public byte[] C_TIMESTAMP { get; set; }
    public Nullable<byte> C_TINYINT { get; set; }
    public Nullable<System.Guid> C_UNIQUEIDENTIFIER { get; set; }
    public byte[] C_VARBINARY { get; set; }
    public string C_VARCHAR { get; set; }
    public string C_XML { get; set; }
}

public partial class T_ALL_COLUMNS

{

public long C_BIGINT { get; set; }

public byte[] C_BINARY { get; set; }

public Nullable<bool> C_BIT { get; set; }

public string C_CHAR { get; set; }

public Nullable<System.DateTime> C_DATE { get; set; }

public Nullable<System.DateTime> C_DATETIME { get; set; }

public Nullable<System.DateTime> C_DATETIME2 { get; set; }

public Nullable<System.DateTimeOffset> C_DATETIMEOFFSET { get; set; }

public Nullable<decimal> C_DECIMAL { get; set; }

public Nullable<double> C_FLOAT { get; set; }

public System.Data.Entity.Spatial.DbGeography C_GEOGRAPHY { get; set; }

public System.Data.Entity.Spatial.DbGeometry C_GEOMETRY { get; set; }

public byte[] C_IMAGE { get; set; }

public Nullable<int> C_INT { get; set; }

public Nullable<decimal> C_MONEY { get; set; }

public string C_NCHAR { get; set; }

public string C_NTEXT { get; set; }

public Nullable<decimal> C_NUMERIC { get; set; }

public string C_NVARCHAR { get; set; }

public Nullable<float> C_REAL { get; set; }

public Nullable<System.DateTime> C_SMALLDATETIME { get; set; }

public Nullable<short> C_SMALLINT { get; set; }

public Nullable<decimal> C_SMALLMONEY { get; set; }

public string C_TEXT { get; set; }

public Nullable<System.TimeSpan> C_TIME { get; set; }

public byte[] C_TIMESTAMP { get; set; }

public Nullable<byte> C_TINYINT { get; set; }

public Nullable<System.Guid> C_UNIQUEIDENTIFIER { get; set; }

public byte[] C_VARBINARY { get; set; }

public string C_VARCHAR { get; set; }

public string C_XML { get; set; }

}

hierarchyid型と、sql_variant型には対応していないようで、生成時に除外されました。

これからO/Rマッパーの実装が佳境に入ります。

みなさん、O/Rマッパーの実装は、可能な限り避けましょう。