copy data from datatable to mysql table in c# throwing fatal exception

Question

I am trying to copy data from sql server to mysql table i have created a mysql table same as sql server. Here is my column name of my both table

EmpCode, Employee name, In Date & Time, Out Date & Time

Here is my copytable method. I am getting fatal error exception. Here with i am copy pasting my insert statement i am getting before insertCmd.ExecuteNonQuery();

insert into sms.empswipe ( EmpCode, Employee name, In Date & Time, Out Date & Time ) values ( @EmpCode, @Employee name, @In Date & Time, @Out Date & Time )

Its giving exception {"Parameter '@Employee' must be defined."}. Its not taking employee name fully, only first word its considering. Anybody help me

 public static void CopyTable(IDbConnection source, IDbConnection destination, String sourceSQL, String destinationTableName)
        {
            var cmd = source.CreateCommand();
            cmd.CommandText = sourceSQL;
            System.Diagnostics.Debug.WriteLine("\tSource SQL: " + sourceSQL);

            try
            {
                source.Open();
                destination.Open();
                var rdr = cmd.ExecuteReader();
                var schemaTable = rdr.GetSchemaTable();


                string paramsSQL = String.Empty;
                string paramsQuoted = String.Empty;
                var insertCmd = destination.CreateCommand();
                //build the insert statement
                foreach (DataRow row in schemaTable.Rows)
                {
                    if (paramsSQL.Length > 0) paramsSQL += ", ";
                    if (paramsQuoted.Length > 0) paramsQuoted += ", ";
                    paramsSQL += "@" + row["ColumnName"];
                    paramsQuoted += "[" + row["ColumnName"] + "]";

                    IDbDataParameter param = insertCmd.CreateParameter();
                    param.ParameterName = "@" + row["ColumnName"];
                    param.SourceColumn = row["ColumnName"].ToString();

                    if (ReferenceEquals(row["DataType"], typeof(DateTime)))
                    {
                        param.DbType = DbType.DateTime;
                    }
                    else if (ReferenceEquals(row["DataType"], typeof(Int32)))
                    {
                        param.DbType = DbType.Int32;
                    }
                    insertCmd.Parameters.Add(param);
                }
                insertCmd.CommandText = String.Format("insert into {0} ( {1} ) values ( {2} )",
                    destinationTableName, paramsSQL.Replace("@", String.Empty),paramsSQL);

                const int counter = 0;
                var errors = 0;

                while (rdr.Read())
                {
                    try
                    {
                        foreach (IDbDataParameter param in insertCmd.Parameters)
                        {
                            object col = rdr[param.SourceColumn];

                            //special check for SQL Server and 
                            //datetimes less than 1753
                            if (param.DbType == DbType.DateTime)
                            {
                                if (col != DBNull.Value)
                                {
                                    //sql server can not have dates less than 1753
                                    if (((DateTime)col).Year < 1753)
                                    {
                                        param.Value = DBNull.Value;
                                        continue;
                                    }
                                }
                            }

                            param.Value = col;

                        }
                        insertCmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        if (errors == 0)
                            System.Diagnostics.Debug.WriteLine(ex.Message.ToString());
                        errors++;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
                System.Diagnostics.Debug.WriteLine(ex);
            }
            finally
            {
                destination.Close();
                source.Close();
            }
        }

score 0 · Answer 1 · edited May 23 '17 at 11:49

0

You need to escape your column names with "[" eg [column name with spaces].

See this answer: How do you deal with blank spaces in column names in SQL Server?

I haven't tried this but I think something like this would work:

param.ParameterName = "@[" + row["ColumnName"]+"]";

edited May 23 '17 at 11:49

Community

1
1

answered Sep 10 '14 at 06:04

iceburg

1,768
3
17
25

how to handle the same for parameter. I have tried to construct my column name like [empcode] but while passing the parameter @@empcode its fine, for other column like employee name its treating it as @employee name if i try to give like @[employee name] throwing exception – user1557020 Sep 10 '14 at 06:12
oh ok, is it the same exception? – iceburg Sep 10 '14 at 06:17

score 0 · Answer 2 · answered Sep 10 '14 at 06:35

0

Try with ? instead of @ Like

insert into sms.empswipe ( EmpCode,Employeename, InDate&Time, OutDate&Time ) values ( ?EmpCode, ?Employeename, ?InDate&Time, ?OutDate&Time )

answered Sep 10 '14 at 06:35

V2Solutions - MS Team

1,129
6
6

copy data from datatable to mysql table in c# throwing fatal exception

2 Answers2