在visual studio C#中从MySQL获取数据并将其插入MS SQL SERVER

Question

我正在尝试从Mysql数据库中读取一个表，并将其全部存储在ms sql server的C#中。我正在正确地读取Mysql.我的问题是如何将我在ms sql中读取的数据存储在代码字符串constring=ConfigurationManager.ConnectionStrings[“cnxMySQL”].ConnectionString的第二部分中；

        MySqlConnection conn = new MySqlConnection(constring);

        MySqlCommand cmd = new MySqlCommand("SELECT * FROM i_evt WHERE Updt=0",conn);

        conn.Open();


        cmd.ExecuteNonQuery();




        string constring2 = ConfigurationManager.ConnectionStrings["cnxsql"].ConnectionString;

        SqlConnection conn2 = new SqlConnection(constring2);

        SqlCommand cmd2 = new SqlCommand("INSERT INTO i_evt",conn2);

        conn2.Open();




        cmd2.ExecuteNonQuery();

        conn2.Close();
        conn.Close();

Answer 1

参考下面的代码，您可以优化这段代码，因为有大量的空间用于优化，但这对于初学者来说很简单，可以理解基本水平:

MySqlConnection conn = new MySqlConnection(constring);
MySqlCommand cmd = new MySqlCommand("SELECT * FROM i_evt WHERE Updt=0", conn);
conn.Open();
DataSet data;
using (MySqlDataAdapter sqlAdapter = new MySqlDataAdapter(mySqlCommand))
    {
        data = new DataSet();
        sqlAdapter.Fill(data);
    }

string constring2 = ConfigurationManager.ConnectionStrings["cnxsql"].ConnectionString;
SqlConnection conn2 = new SqlConnection(constring2);
conn2.Open();
for (int i = 0; i < data.Tables[0].Rows.Count; i++)
    {
        SqlCommand cmd2 = new SqlCommand("INSERT INTO i_evt(column1,column2) values(@col1,@col1)", conn2);
        cmd2.Parameters.AddWithValue("col1", data.Tables[0].Rows[i][0].ToString());
        cmd2.Parameters.AddWithValue("col12", data.Tables[0].Rows[i][1].ToString());
        cmd2.ExecuteNonQuery();
    }
conn2.Close();
conn.Close();

Answer 2

我有一个相反的问题，我必须将数据从MS SQL Server转移到MySQL。 我已经使用了下面的代码段，但我个人并不建议将其用于生产，因为它会逐行添加数据。 但可以将其用于小型数据集或本地环境。 您可以添加一个try-catch，也可以根据需要进行自定义。 这仅供参考。

public void Main()
{
    var table = GetDataTableFromSQLServer();
    AddToMySQL(table);
}

private DataTable GetDataTableFromSQLServer()
{
    var connection = GetSqlServerConnection();
    string query = "SELECT Column1, Column2 FROM TableName WHERE Column3 is NOT NULL";

    var command = new SqlCommand(query, connection);
    connection.Open();
    var reader = command.ExecuteReader();
    DataTable table = new DataTable();
    table.Load(reader);
    connection.Close();

    return table;
}

private void AddToMySQL(DataTable table)
{
    var connection = GetMySQLConnection();
    connection.Open();
    string query = "INSERT INTO TableName (column1, column2) VALUES(@column1, @column2);";

    int i = 0;
    foreach (DataRow row in table.Rows)
    {
        if (i % 1000 == 0)
        {
            // Closing & Reopening connection after 1000 records
            connection.Close();
            connection.Open();
        }

        Console.WriteLine($"Adding ({++i}/{table.Rows.Count})");

        MySqlCommand command = new MySqlCommand(query, connection);
        command.Parameters.Add(new MySqlParameter("@column1", MySqlDbType.Int64) { Value = row.Field<long>("column1").Trim() });
        command.Parameters.Add(new MySqlParameter("@column2", MySqlDbType.VarChar) { Value = row.Field<string>("column2").Trim() });

        var affectedRows = command.ExecuteNonQuery();
    }

    connection.Close();
}

private SqlConnection GetSqlServerConnection()
{
    string connectionString = @"Data Source=...";
    SqlConnection connection = new SqlConnection(connectionString);
    return connection;
}

private MySqlConnection GetMySQLConnection()
{
    MySqlConnectionStringBuilder connectionBuilder = new MySqlConnectionStringBuilder
    {
        Server = "...",
        Database = "...",
        UserID = "...",
        Password = "...",
        Port = 3306
    };

    MySqlConnection connection = new MySqlConnection(connectionBuilder.ToString());
    return connection;
}