0

I'm moving tables from a SQL Server DB to a MySQL DB programmatically.

I've got a SqlDataReader populated with a table's fields values, and I would like to insert them into the corresponding MySQL table. There aren't primary key and auto increment problems on destination table.

What I'm missing is howo to convert a SqlDataReader field value to a MySqlParameter value. This is a snippet of my code:

Cmd.CommandText = "SELECT a,b,c,d,e FROM sqlTable";
Cmd.Prepare();
dr = Cmd.ExecuteReader();
while (dr.Read())
{
    MySqlConnection mySqlConn = new MySqlConnection(mySQLConnString);
    mySqlConn.Open();
    mySqlCmd.Connection = mySqlConn;
    mySqlCmd.CommandText = "INSERT INTO mysqlTable VALUES (?, ?, ?, ?, ?) ";
    for (int k = 0; k < dr.FieldCount; k++)
    {
         mySqlCmd.Parameters.Add(new MySqlParameter(dr[k].ToString(), dr[k]));
    }
...

A fatal error occurs on the line, without any detail.

mySqlCmd.Parameters.Add(new MySqlParameter(dr[k].ToString(), dr[k]));

I cannot understand how to set dr[k] value acceptable for MySQL.

chridam
  • 100,957
  • 23
  • 236
  • 235
kiks73
  • 3,718
  • 3
  • 25
  • 52
  • 1
    How are you observing this fatal error? Does the application just stop without *any* exception? – Jon Skeet Jun 19 '14 at 13:34
  • 1
    What is the exception message? Did you try to debug the code? Did you try to see what is sent to the server, eg with a profiler? In any case, your code uses the reader's value as the name of the parameter which is definitely wrong – Panagiotis Kanavos Jun 19 '14 at 13:34
  • possible duplicate of [C# MySqlParameter problem](http://stackoverflow.com/questions/2322274/c-sharp-mysqlparameter-problem) – Panagiotis Kanavos Jun 19 '14 at 13:38
  • @PanagiotisKanavos I've got some problems in debugging due to SQL Server and MySQL not reachable concurrently from my dev environment. That syntax was working in a similar context but with ODBCParameter instead of MySqlParameter. – kiks73 Jun 19 '14 at 13:39
  • @kiks73 ODBC is not Connector/.NET. Perhaps the MySQL ODBC driver ignores the parameter names but that's irrelevant. The code is wrong in both cases. The first string parameter in the constructor of both classes is the parameter's name. If it worked with [OdbcParameter(string,object)](http://msdn.microsoft.com/en-us/library/zh148b89.aspx), it was by accident – Panagiotis Kanavos Jun 19 '14 at 13:43
  • @PanagiotisKanavos Ok, I understand. Just trying this way: mySqlCmd.Parameters.Add(new MySqlParameter(dr.GetName(k), dr.GetValue(k))); – kiks73 Jun 19 '14 at 13:51

0 Answers0