Copying the data of one database table to another

Question

i'm trying to write a C# application that reads data from one table of OleDb database to another table of OleDb database. The source and destination table will be decided during run time by the user.

The way i'm doing it right now is, reading everything into OleDbDataReader, iterating through each row and inserting into other table.

  // create insert string
  insert = "INSERT INTO " + Dest_Table + " VALUES (";
  for(int i = 0; i < cols.Length; i++)       // cols is array of column names
  {
      string coma = ", ";
      if (i == cols.Length - 1)
      coma = " )";

      insert += "?" + coma;
  }

  // read each row and insert
  while (src_reader.Read())
  {
      dstcmd.CommandText = insert;

      for (int i = 0; i < cols.Length; i++)
      {
          string temp = "@" + cols[i];
          dstcmd.Parameters.AddWithValue(temp, src_reader[cols[i]]);

          // for debug purpose
          Console.Write(temp + "  " + src_reader[cols[i]] + "\n");
      }

      dstcmd.ExecuteNonQuery();

  }

I was wondering, is there a better and more efficient way to do data transfer between tables of different databases? Or is it the only way? I know that in SqlConnection, there's a SqlBulkCopy method that can do this, but what about OleDbConnection? Is there something similar to SqlBulkCopy?

Any help will be appreciated. Thanks.

Answer 1

I also had a problem similar to yours, I will share the function that I did to solve, I hope it helps you

Public bool InsertIntoDB(string tableName, DataTable _dataTable)
    {
        bool Result = false;
        DataTable ColumnsSQL = GetColumns(tableName);
        string CommandInsert = "Insert Into " + tableName + " (";
        string CommandValue = "";
        string commandSQL = "";
        DateTime Helpconstruct = new DateTime();
        String Word = "";
        String ValueToAdd = ""; 
        string connectionString = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
        FuncAuxC2C HELPPLEASE = new FuncAuxC2C();
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(null, conn);

        for (int k = 0; k < _dataTable.Rows.Count; k++)
        {

            for (int i = 0; i < ColumnsSQL.Rows.Count - 1; i++)
            {
                CommandInsert += ColumnsSQL.Rows[i][0].ToString();

                if (_dataTable.Rows[k][i].ToString() != "")
                {
                    ValueToAdd = _dataTable.Rows[k][i].ToString();
                    if (char.IsNumber(ValueToAdd[0]))
                    {
                        bool haveDigits = false;

                        foreach (char c in _dataTable.Rows[k][i].ToString())
                        {
                            if (c < '0' || c > '9')
                            {
                                haveDigits = true;
                                continue;
                            }

                        }

                        if (haveDigits == true && _dataTable.Rows[k][i].ToString().Contains("e+") && _dataTable.Rows[k][i].ToString().Contains(","))
                        {

                            _dataTable.Rows[k][i] = Decimal.Parse(_dataTable.Rows[k][i].ToString(), System.Globalization.NumberStyles.Float).ToString();

                        }
                    }
                }

                if (_dataTable.Columns[i].ColumnName.ToString() == "Data" && _dataTable.Rows[k][i].ToString() != "")
                {
                    //This is just to format date
                    ValueToAdd = HELPPLEASE.alterDataformat(_dataTable.Rows[k][i].ToString(), "yyyyMMdd", "-");

                }
                else if (_dataTable.Columns[i].ColumnName.ToString() == "tipificacao_datetime" && _dataTable.Rows[k][i].ToString() != "" )
                {
                    //This is just to format date 
                    ValueToAdd = HELPPLEASE.alterDataformat(_dataTable.Rows[k][i].ToString(), "yyyyMMddHHMMSS", "-");
                }
                else
                {
                    if (_dataTable.Rows[k][i].ToString().Length < 250)
                    {
                        ValueToAdd = _dataTable.Rows[k][i].ToString();
                    }
                    else
                    {
                        ValueToAdd = _dataTable.Rows[k][i].ToString().Substring(0, 250);
                    }
                }


                cmd.Parameters.AddWithValue("@" + ColumnsSQL.Rows[i][0].ToString(), SqlDbType.VarChar).Value = ValueToAdd;
                ValueToAdd = "";


                CommandValue += "@" + ColumnsSQL.Rows[i][0].ToString();

                if (ColumnsSQL.Rows.Count - 2 != i)
                {
                    CommandInsert += ", ";
                    CommandValue += ", ";
                }

            }



            commandSQL = CommandInsert + ") VALUES (" + CommandValue + ")";
            try
            {

                conn.Open();
                cmd.CommandText = commandSQL;
                cmd.ExecuteNonQuery();
                conn.Close();
                cmd.Parameters.Clear();

                commandSQL = "";
                CommandValue = "";
                CommandInsert = "";
                CommandInsert = "Insert Into " + tableName + " (";
                Result = true;
            }
            catch (Exception ex)
            {
                conn.Close();
                Result = false;
            }
            finally
            {
                cmd.Parameters.Clear();
                commandSQL = "";
                CommandValue = "";
                CommandInsert = "";
                CommandInsert = "Insert Into " + tableName + " (";
                conn.Close();
            }
        }


        return Result;
    }

}