WPF開発ノウハウ集

プリペアドパラメータを使ってSQLステートメントに変数を使用する

• パラメータは ＠ で始まる変数名で指定する

    DbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "select * from sampletable where id = @id";
  
    /* プリペアドパラメーター設定 */
    DbParameter param = cmd.CreateParameter();
    param.DbType =  DbType.Int32;
    param.ParameterName = "@id";
    param.Value = 1;
    cmd.Parameters.Add( param );
  
    List<Dictionary<string, object>> recordList = new List<Dictionary<string, object>>();
    DbDataReader reader = cmd.ExecuteReader();
    while( reader .Read() )
    {
        Dictionary<string, object> record = new Dictionary<string, object>();
        for( int i = 0; i < reader.FieldCount; i++ )
        {
            string fieldName = reader.GetName( i );
            object obj = reader.GetFieldValue<object>( i );
            record.Add( fieldName, obj );
        }
        recordList.Add( record );
    }
    cmd.Dispose();
    reader.Close();
    conn.Close();
  
    return recordList;
  

C#の型とDbTypeの紐つけ（参考）

    Dictionary<Type, DbType> map = new Dictionary<Type, DbType> 
    {
        { typeof( Byte ), DbType.Byte },
        { typeof( UInt16 ), DbType.UInt16 },
        { typeof( UInt32 ), DbType.UInt32 },
        { typeof( UInt64 ), DbType.UInt64 },
        { typeof( SByte ), DbType.SByte },
        { typeof( Int16 ), DbType.Int16 },
        { typeof( Int32 ), DbType.Int32 },
        { typeof( Int64 ), DbType.Int64 },
        { typeof( Single ), DbType.Single },
        { typeof( Double ), DbType.Double },
        { typeof( Decimal ), DbType.Decimal },
        { typeof( DateTime ), DbType.DateTime },
        { typeof( Boolean ), DbType.Boolean },
        { typeof( Char ), DbType.StringFixedLength },
        { typeof( String ), DbType.String }
    };
    return map;

• 上のmapの使用例

    int variable = 1;
  
    DbParameter param = cmd.CreateParameter();
    param.DbType =  map[variable.GetType()];
    param.ParameterName = "@id";
    param.Value = variable;