Read I Series (AS/400)

How to call it:

IseriesAccess thing = new IseriesAccess(IseriesAccess.Environment.DEV, "username", "password",IseriesAccess.Architecture.Bit32);

DataTable as400Data = thing.ReadISeriesTable("the library", "the table", "1=1");

This is the library

using System;

using System.Collections.Generic;

using System.Linq;

using System.Data.Odbc;

using System.Data;

namespace ConnectToAS400

{

    class IseriesAccess

    {

        #region Public constants and/or Enums

        /// <summary>

        /// ISeries flag which allows the restriction of the type of sql that can be executed

        /// </summary>

        private enum ConnType

        {

            AllowAnySQL = 0,

            AllowSelectAndCall=1,

            AllowSelectOnly=2

        }

        public enum Environment

        {

            DEV=1,

            FCB=2

        }

        public enum Architecture

        {

            Bit32=32,

            Bit64=64

        }

        #endregion

        #region Private members

        private string _pwd;

        private string _uid;

        private Environment _environment;

        private string _connectionString;

        private Architecture _architecture;

        #endregion

        #region Constructor

        /// <summary>

        /// Initializes ISeries object .

        /// </summary>

        /// <param name="environment">FCB or Dev</param>

        /// <param name="uid">The user under whose context the query will execute</param>

        /// <param name="password">The user's password</param>

        public IseriesAccess(Environment environment, string uid, string password, Architecture architecture )

        {

            _environment = environment;

            _uid = uid;

            _pwd = password;

            _architecture = architecture;

            _connectionString = "Driver={Client Access ODBC Driver (" + (int)_architecture + "-bit)};System=" + _environment.ToString() + ";CONNTYPE=" + ((int)ConnType.AllowSelectAndCall).ToString() + ";TRANSlATE=1;Uid=" + _uid + ";pwd=" + _pwd;

        }

        /// <summary>

        /// Initializes ISeries object .

        /// </summary>

        /// <param name="connectionString">The complete connectionstring. Note - if the ConnType attribute is not included inside the string, it will be appended to the end of the driver string with a value of 2. See IBM docs.</param>

        public IseriesAccess(string connectionString)

        {

            _connectionString = connectionString;

            if (connectionString.IndexOf("ConnType", StringComparison.CurrentCultureIgnoreCase) == -1)

            {

                _connectionString += "ConnType=" + ConnType.AllowSelectAndCall.ToString();

            }

        }

        #endregion

        /// <summary>

        /// Reads all columns from specified ISeries library and table using a specified filter

        /// </summary>

        /// <param name="libraryName">The ISeries Library (database)</param>

        /// <param name="fileName">The ISeries File (table)</param>

        /// <param name="filter">The where Clause. Do no include "Where".</param>

        /// <returns>A filled System.Data.DataTable</returns>

        public DataTable ReadISeriesTable(string libraryName, string fileName, string filter)

        {

            try

            {

                DataTable as400Data = new DataTable();

                string query = String.Format("Select * from {0}.{1}", libraryName, fileName);

                if (!string.IsNullOrEmpty(filter))

                {

                    query = query.Trim() + " where " + filter;

                }

                using (OdbcConnection c = new OdbcConnection(_connectionString))

                {

                    c.Open();

                    using (OdbcCommand selectCMD = new OdbcCommand(query))

                    {

                        selectCMD.Connection = c;

                        OdbcDataReader r = selectCMD.ExecuteReader();

                        as400Data.Load(r);

                    }

                    c.Close();

                }

                return as400Data;

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        /// <summary>

        /// Reads specific columns from specified ISeries library and table using a specified filter

        /// </summary>

        /// <param name="libraryName">The ISeries Library (database)</param>

        /// <param name="fileName">The ISeries File (table)</param>

        /// <param name="columnList">Comma-delimited list of columns to retrieve</param>

        /// <param name="filter">The where Clause. Do no include "Where".</param>

        /// <returns>A filled System.Data.DataTable</returns>

        public DataTable ReadISeriesTable(string libraryName, string fileName, string columnList, string filter)

        {

            try

            {

                DataTable as400Data = new DataTable();

                string query = String.Format("Select columnList from {0}.{1}", libraryName, fileName);

                if (!string.IsNullOrEmpty(filter))

                {

                    query = query.Trim() + " where " + filter;

                }

                using (OdbcConnection c = new OdbcConnection(_connectionString))

                {

                    c.Open();

                    using (OdbcCommand selectCMD = new OdbcCommand(query))

                    {

                        selectCMD.Connection = c;

                        OdbcDataReader r = selectCMD.ExecuteReader();

                        as400Data.Load(r);

                    }

                    c.Close();

                }

                return as400Data;

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

    }

}