Inserting Geometry Data into table using MySqlCommand.ExecuteNonQuery

Question

I'm trying to copy a medium-sized (raw dump of 434MB) table from a remote server to a local server programatically. My current approach uses MySqlDataAdapter and DataSets:

        using(MySqlConnection conn_local = new MySqlConnection(localcs))
        {
            conn_local.Open();
            //build an insertcommand
            MySqlCommand insertcommand = new MySqlCommand("INSERT INTO `buildtools`.`straten` (straat, a_naam, e_naam, n_naam, plaats, opr_id, wegtype, wegtype_b, wegtype_a, wegtype_p, rotonde, opaf, opaf_naam, hoogtenummer, knooppunt, rijrichting, eenrichting, regio, layerNr, geom) VALUES (@straat, @a_naam, @e_naam, @n_naam, @plaats, @opr_id, @wegtype, @wegtype_b, @wegtype_a, @wegtype_p, @rotonde, @opaf, @opaf_naam, @hoogtenummer, @knooppunt, @rijrichting, @eenrichting, @regio, @layerNr, @geom);", conn_local);

            //addparameters is a static function to add the necessary parameters to insertcommand
            foreach(some datastructure in some dataset)
            {
               addparameters(insertcommand);
               //Do some work to set parameter values
               insertcommand.ExecuteNonQuery();
               insertcommand.Parameters.Clear;
            }

        }

The addparameters function looks like this:

private static void addparameters(MySqlCommand cmd)
    {
        cmd.Parameters.Add("@straat", MySqlDbType.VarChar, 60, "straat");
        cmd.Parameters.Add("@a_naam", MySqlDbType.VarChar, 60, "a_naam");
        cmd.Parameters.Add("@e_naam", MySqlDbType.VarChar, 20, "e_naam");
        cmd.Parameters.Add("@n_naam", MySqlDbType.VarChar, 20, "n_naam");
        cmd.Parameters.Add("@plaats", MySqlDbType.VarChar, 40, "plaats");
        cmd.Parameters.Add("@opr_id", MySqlDbType.Decimal, 16, "opr_id");
        cmd.Parameters.Add("@wegtype", MySqlDbType.VarChar, 20, "wegtype");
        cmd.Parameters.Add("@wegtype_b", MySqlDbType.VarChar, 20, "wegtype_b");
        cmd.Parameters.Add("@wegtype_a", MySqlDbType.VarChar, 20, "wegtype_a");
        cmd.Parameters.Add("@wegtype_p", MySqlDbType.VarChar, 20, "wegtype_p");
        cmd.Parameters.Add("@rotonde", MySqlDbType.VarChar, 20, "rotonde");
        cmd.Parameters.Add("@opaf", MySqlDbType.VarChar, 20, "opaf");
        cmd.Parameters.Add("@opaf_naam", MySqlDbType.VarChar, 45, "opaf_naam");
        cmd.Parameters.Add("@hoogtenummer", MySqlDbType.Int16, 6, "hoogtenummer");
        cmd.Parameters.Add("@knooppunt", MySqlDbType.VarChar, 45, "knooppunt");
        cmd.Parameters.Add("@rijrichting", MySqlDbType.VarChar, 1, "rijrichting");
        cmd.Parameters.Add("@eenrichting", MySqlDbType.VarChar, 1, "eenrichting");
        cmd.Parameters.Add("@regio", MySqlDbType.VarChar, 30, "regio");
        cmd.Parameters.Add("@layerNr", MySqlDbType.VarChar, 45, "layerNr");
        cmd.Parameters.Add("@geom", MySqlDbType.Geometry, 1, "geom");
    }

I keep getting a System.IndexOutOfRangeException with message Index was outside the bounds of the array when calling insertcommand.ExecuteNonQuery();

Just remove ';' from MySqlCommand insertcommand = new MySqlCommand("INSERT INTO `buildtools`.`straten` (straat, a_naam, e_naam, n_naam, plaats, opr_id, wegtype, wegtype_b, wegtype_a, wegtype_p, rotonde, opaf, opaf_naam, hoogtenummer, knooppunt, rijrichting, eenrichting, regio, layerNr, geom) VALUES (@straat, @a_naam, @e_naam, @n_naam, @plaats, @opr_id, @wegtype, @wegtype_b, @wegtype_a, @wegtype_p, @rotonde, @opaf, @opaf_naam, @hoogtenummer, @knooppunt, @rijrichting, @eenrichting, @regio, @layerNr, @geom)", conn_local); — Pradnya Bolli, May 06 '15 at 11:03
Hi @PradnyaBolli, thanks for your suggestion, unfortunately it still throws the `System.IndexOutOfRangeException` — Nils O, May 06 '15 at 11:05
See this http://stackoverflow.com/questions/20940979/what-is-indexoutofrangeexception-and-how-do-i-fix-it — Pradnya Bolli, May 06 '15 at 11:07
adp_local.Update(db_remote.Tables[0]); in this line it take null value — Pradnya Bolli, May 06 '15 at 11:08
It does not. Debugging shows `db_remote.Tables[0]` definitely has a value (as well as the expected value). The index out of range exception does not occur by calling `db_remote.Tables[0]`, this is clear because calling `DataTable table = db_remote.Tables[0]; adp_local.Update(table);` only throws the exception when the update is called. Again, debugging with this code shows `table` has the expected value when `Update()` is being called. The StackTrace also shows the exception does not occur in my own code. I'll edit my question with the stacktrace. — Nils O, May 06 '15 at 11:13

Nils O · Accepted Answer · 2015-05-06T12:53:42.627

0

When I try to execute the INSERT without the Geometry data it does work.

I tried to change MySqlDbType.Geometry to MySqlDbType.Blob since Geometry objects are basically a special subset of BLOB's and this solves the problem. There may be some problem with MySqlDbType.Geometry.

edited May 06 '15 at 12:53

answered May 06 '15 at 12:18

Nils O

1,321
9
19

Inserting Geometry Data into table using MySqlCommand.ExecuteNonQuery

1 Answers1