C# - Insert Multiple Records at once to AS400

Question

I have a problem like this:
1. I retrieve data from MySQL using C# ASP .Net. -- done --
2. All data from no.1 will be inserted into table on AS400. -- I got an error on this step --

Error message says that ERROR [42000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ; was not valid. Valid tokens: <END-OF-STATEMENT>.. It's true that I used semicolon to separate queries with each others, but it's not allowed. I've Googling but I can't find the solution.
My question is what the <END-OF-STATEMENT> means of that error message..?
Here is my source code.

private static void doInsertDOCADM(MySqlConnection conn)
    {
        // Get Temporary table
        String query = "SELECT * FROM TB_T_DOC_TEMPORARY_ADM";
        DataTable dt = CSTDDBUtil.ExecuteQuery(query);

        OdbcConnection as400Con = null;
        as400Con = CSTDDBUtil.GetAS400Connection();
        as400Con.Open();

        if (dt != null && dt.Rows.Count > 0)
        {
            int counter = 1, maxInsertLoop = 50;

            using (OdbcCommand cmd = new OdbcCommand())
            {
                cmd.Connection = as400Con;

                foreach (DataRow dr in dt.Rows)
                {
                    cmd.CommandText += "INSERT INTO DCDLIB.WDFDOCQ VALUES " + "(?,?,?,?);";

                    cmd.Parameters.Add("1", OdbcType.VarChar).Value = dr["PROD_MONTH"].ToString();
                    cmd.Parameters.Add("2", OdbcType.VarChar).Value = dr["NEW_MAIN_DEALER_CD"].ToString();
                    cmd.Parameters.Add("3", OdbcType.VarChar).Value = dr["MODEL_SERIES"].ToString();
                    cmd.Parameters.Add("4", OdbcType.VarChar).Value = dr["MODEL_CD"].ToString();


                    if (counter < maxInsertLoop)
                    {
                        counter++;
                    }
                    else
                    {
                        counter = 1;
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "";
                        cmd.Parameters.Clear();
                    }
                }

                if (counter > 1) cmd.ExecuteNonQuery();
            }
        }

Notes: I used this way (Collect some queries first, and then execute those query) to improve the performance of my application.

Answer 1

As Clockwork-Muse pointed out, the problem is that you can only run a single SQL statement in a command. The iSeries server does not handle multiple statements at once.

If your iSeries server is running V6R1 or later, you can use block inserts to insert multiple rows. I'm not sure if you can do so through the ODBC driver, but since you have Client Access, you should be able to install the iSeries ADO.NET driver. There are not many differences between the ADO.NET iSeries driver and the ODBC one, but with ADO.NET you get access to iSeries specific functions.

With the ADO.NET driver, multiple insert become a simple matter of :

    using (iDB2Connection connection = new iDB2Connection(".... connection string ..."))
        {
            // Create a new SQL command
            iDB2Command command = 
                new iDB2Command("INSERT INTO MYLIB.MYTABLE VALUES(@COL_1, @COL_2", connection);

            // Initialize the parameters collection
            command.DeriveParameters();

            // Insert 10 rows of data at once
            for (int i = 0; i < 20; i++)
            {
                // Here, you set your parameters for a single row
                command.Parameters["@COL_1"].Value = i;
                command.Parameters["@COL_2"].Value = i + 1;
                // AddBatch() tells the command you're done preparing a row
                command.AddBatch();
            }

            // The query gets executed
            command.ExecuteNonQuery();
        }
    }

There is also some reference code provided by IBM to do block inserts using VB6 and ODBC, but I'm not sure it can be easily ported to .NET : http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzaik%2Frzaikextfetch.htm

Hope that helps.