C# - Cannot implicitly convert type 'IBM.Data.DB2.iSeries.iDB2DataReader' to 'System.Data.SqlClient.SqlDataReader'

Question

I am working in a Ranorex project, but it isn't a Ranorex specific problem. I need to open a connection to an AS400 DB2 Database and then run a SQL query against it.

I have code that successfully opens the connection and returns the results of the SQL query.

iDB2Connection cn = new iDB2Connection("Data Source=as400_DB;User ID=CLIENT;Password=CLIENT;Default Collection=XXXXXX;Naming=System");
 cn.Open();
 iDB2Command cmd = new iDB2Command("select count(*) from ABC_table where xxxx = 'WC' AND xxxx = 'L302328'", cn);
 int count = Convert.ToInt32(cmd.ExecuteScalar());
 Report.Log(ReportLevel.Info, "count", count.ToString());
 cn.Close(); 

In the code, the second method, calls the first method to open the connection, which then allows the second method to run the SQL commands, but I get the following error:

Cannot implicitly convert type 'IBM.Data.DB2.iSeries.iDB2DataReader' to 'System.Data.SqlClient.SqlDataReader'

at the 'return new iDB2Connection(cn.ToString())' point in the first method.

    public static class sQlHelper
{
    private static SqlConnection sQlConnect()
    {   

            iDB2Connection cn = new iDB2Connection("Data Source=as400_DB;User ID=CLIENT;Password=CLIENT;Default Collection=XXXXXX;Naming=System");

            return new iDB2Connection(cn.ToString());

    }

    public static void validateResult()
    {
            var myConnection = sQlConnect();
            myConnection.Open();

            string sqlStatementForCheckHeaders = "select count(*) from ABC_table where xxxx = 'WC' AND xxxx = 'L302328'";
            int count = Convert.ToInt32(cmd.ExecuteScalar());

            SqlDataReader myReader = null;
            iDB2Command myCommand = new iDB2Command(sqlStatementForCheckHeaders, myConnection);
            myReader = myCommand.ExecuteReader();
            while(myReader.Read())
            {
                Console.WriteLine(myReader["Column1"].ToString());
                Console.WriteLine(myReader["Column2"].ToString());
            }

            myConnection.Close();
    }
}

I can't workout how to resolve the issue and I have not been able to find any information on the error itself. I would be grateful for any support/advice people can supply.

Answer 1

You have two flaws in that code. I think you are confusing System.Data.SqlClient namespace with IBM.Data.DB2.iSeries.

First, your sQlConnect method is declared to return a SqlConnection, but returns an iDB2Connection instead. Fix the signature:

private static iDB2Connection sQlConnect() // returns a IDB2Connection!
{   
    return new iDB2Connection("Data Source=as400_DB;User ID=CLIENT;Password=CLIENT;Default Collection=XXXXXX;Naming=System");
}

The second error message is caused by the wrong declaration of myReader. Since myCommand is an iDB2Command (and not a SqlCommand), its ExecuteReader() method returns a IBM.Data.DB2.iSeries.iDB2DataReader, and not a SqlDataReader.

So you just need to change your declaration of myReader:

 iDB2Command myCommand = new iDB2Command(sqlStatementForCheckHeaders, myConnection);
 // use the correct type (or var)
 iDB2DataReader myReader = myCommand.ExecuteReader();
 while(myReader.Read()) { ... }