c# - 需要优化 C# 代码以将 CSV 行插入 MySql

Question

这就是我想要实现的目标......点击按钮，我想要:

1. 将实时“users”表的现有数据复制到“old_users”表以进行备份
2. 截断现有的“new_users”表以准备接受新行
3. 解析 CSV 文件以将数据插入“new_users”表
4. 截断现有的“users”表，然后将“new_users”表的数据复制到“users”表

我不是一名日常程序员，并且已经很长时间没有创建程序了。但我将一些代码拼凑在一起以获得一些可以工作的东西。以下是我当前的代码:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.VisualBasic.FileIO;
using Renci.SshNet;
using Renci.SshNet.Common;
using MySql.Data.MySqlClient;

namespace ValidationImport
{
public partial class ValiationImport : Form
{
    public ValiationImport()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {

    }

    private void btnImportToDatabase_Click(object sender, EventArgs e)
    {
        try
        {
            using (var client = new SshClient("1.1.1.1", "[username]", "[password]")) // establishing ssh connection to server where MySql is hosted
            {
                client.Connect();
                if (client.IsConnected)
                {
                    var portForwarded = new ForwardedPortLocal("127.0.0.1", 3306, "127.0.0.1", 3306);
                    client.AddForwardedPort(portForwarded);
                    portForwarded.Start();
                    using (MySqlConnection con = new MySqlConnection("SERVER=localhost;PORT=3306;UID=[username];PASSWORD=[password];DATABASE=[dbname]")) // MySql database credentials
                    {
                        con.Open();

                        // Copying over the users table (with structure and indexes) to the old_users table. Truncating the new_users table to prepare for new data.
                        using (MySqlCommand cmd = new MySqlCommand("DROP TABLE test_old_users; CREATE TABLE test_old_users LIKE test_users; INSERT test_old_users SELECT * FROM test_users; TRUNCATE TABLE test_new_users;", con))
                        {
                            cmd.Connection = con;
                            cmd.CommandType = CommandType.Text;
                            cmd.ExecuteNonQuery();
                        }

                        string fileName = "";
                        // Select the Validation file to import
                        OpenFileDialog dlg = new OpenFileDialog();
                        if (dlg.ShowDialog() == DialogResult.OK)
                            fileName = dlg.FileName;

                        if (fileName != "")
                        {
                            using (TextFieldParser parser = new TextFieldParser(fileName))
                            {
                                parser.TextFieldType = FieldType.Delimited;
                                parser.SetDelimiters("|");
                                parser.ReadLine(); // Skip the first row of field title headers
                                while (!parser.EndOfData)
                                {
                                    //Copy each row individually over to the MySql table
                                    string[] row = parser.ReadFields();

                                    using (MySqlCommand cmd = new MySqlCommand("INSERT INTO test_new_users (`Indv Id`, `Last Name`, `First Name`, `Middle Name`, `Birth date`, `Indv Addr Line 1`, `Indv Addr Line 2`, `Indv Addr Line 3`, `Indv City`, `Indv State`, `Indv Zip`, `Indv Country`, `Local Id`, `Local Name`, `Local User Id`, `Uniserv Id`, `Uniserv Name`, `Uniserv User Id`, `Chapter Name`, `Chapter User Id`, `Employer Id`, `Employer Name`, `Work Location Id`, `Work Location Name`, `Work Location User Id`, `Group Id`, `Group Name`, `Group Type Id`, `Group Type Name`, `SEA P01`, `Home  Phone`, `Home Phone principal Ind`, `Home Phone Unlisted Ind`, `Mobile Phone`, `Mobile Phone Principal Ind`, `Mobile Phone Unlisted Ind`, `Home Email`, `Home Email Principal Ind`, `Work Email`, `Work Email Principal Ind`, `Other Email`, `Other Email Principal Ind`) VALUES (@IndvId, @LastName, @FirstName, @MiddleName, @BirthDate, @IndvAddrLine1, @IndvAddrLine2, @IndvAddrLine3, @IndvCity, @IndvState, @IndvZip, @IndvCountry, @LocalId, @LocalName, @LocalUserId, @UniservId, @UniservName, @UniservUserId, @ChapterName, @ChapterUserId, @EmployerId, @EmployerName, @WorkLocationId, @WorkLocationName, @WorkLocationUserId, @GroupId, @GroupName, @GroupTypeId, @GroupTypeName, @SEAP01, @HomePhone, @HomePhonePrincipalInd, @HomePhoneUnlistedInd, @MobilePhone, @MobilePhonePrincipalInd, @MobilePhoneUnlistedInd, @HomeEmail, @HomeEmailPrincipalInd, @WorkEmail, @WorkEmailPrincipalInd, @OtherEmail, @OtherEmailPrincipalInd);", con))
                                    {
                                        cmd.CommandType = CommandType.Text;
                                        cmd.Parameters.AddWithValue("@IndvId", row[0]);
                                        cmd.Parameters.AddWithValue("@LastName", row[1]);
                                        cmd.Parameters.AddWithValue("@FirstName", row[2]);
                                        cmd.Parameters.AddWithValue("@MiddleName", row[3]);
                                        cmd.Parameters.AddWithValue("@BirthDate", row[4]);
                                        cmd.Parameters.AddWithValue("@IndvAddrLine1", row[5]);
                                        cmd.Parameters.AddWithValue("@IndvAddrLine2", row[6]);
                                        cmd.Parameters.AddWithValue("@IndvAddrLine3", row[7]);
                                        cmd.Parameters.AddWithValue("@IndvCity", row[8]);
                                        cmd.Parameters.AddWithValue("@IndvState", row[9]);
                                        cmd.Parameters.AddWithValue("@IndvZip", row[10]);
                                        cmd.Parameters.AddWithValue("@IndvCountry", row[11]);
                                        cmd.Parameters.AddWithValue("@LocalId", row[12]);
                                        cmd.Parameters.AddWithValue("@LocalName", row[13]);
                                        cmd.Parameters.AddWithValue("@LocalUserId", row[14]);
                                        cmd.Parameters.AddWithValue("@UniservId", row[15]);
                                        cmd.Parameters.AddWithValue("@UniservName", row[16]);
                                        cmd.Parameters.AddWithValue("@UniservUserId", row[17]);
                                        cmd.Parameters.AddWithValue("@ChapterName", row[18]);
                                        cmd.Parameters.AddWithValue("@ChapterUserId", row[19]);
                                        cmd.Parameters.AddWithValue("@EmployerId", row[20]);
                                        cmd.Parameters.AddWithValue("@EmployerName", row[21]);
                                        cmd.Parameters.AddWithValue("@WorkLocationId", row[22]);
                                        cmd.Parameters.AddWithValue("@WorkLocationName", row[23]);
                                        cmd.Parameters.AddWithValue("@WorkLocationUserId", row[24]);
                                        cmd.Parameters.AddWithValue("@GroupId", row[25]);
                                        cmd.Parameters.AddWithValue("@GroupName", row[26]);
                                        cmd.Parameters.AddWithValue("@GroupTypeId", row[27]);
                                        cmd.Parameters.AddWithValue("@GroupTypeName", row[28]);
                                        cmd.Parameters.AddWithValue("@SEAP01", row[29]);
                                        cmd.Parameters.AddWithValue("@HomePhone", row[30]);
                                        cmd.Parameters.AddWithValue("@HomePhonePrincipalInd", row[31]);
                                        cmd.Parameters.AddWithValue("@HomePhoneUnlistedInd", row[32]);
                                        cmd.Parameters.AddWithValue("@MobilePhone", row[33]);
                                        cmd.Parameters.AddWithValue("@MobilePhonePrincipalInd", row[34]);
                                        cmd.Parameters.AddWithValue("@MobilePhoneUnlistedInd", row[35]);
                                        cmd.Parameters.AddWithValue("@HomeEmail", row[36]);
                                        cmd.Parameters.AddWithValue("@HomeEmailPrincipalInd", row[37]);
                                        cmd.Parameters.AddWithValue("@WorkEmail", row[38]);
                                        cmd.Parameters.AddWithValue("@WorkEmailPrincipalInd", row[39]);
                                        cmd.Parameters.AddWithValue("@OtherEmail", row[40]);
                                        cmd.Parameters.AddWithValue("@OtherEmailPrincipalInd", row[41]);
                                        cmd.ExecuteNonQuery();
                                    }
                                }
                            }
                        }

                        // Copying over the new_users table (with structure and indexes) to the users table.
                        using (MySqlCommand cmd = new MySqlCommand("DROP TABLE test_users; CREATE TABLE test_users LIKE test_new_users; INSERT test_users SELECT * FROM test_new_users;", con))
                        {
                            cmd.Connection = con;
                            cmd.CommandType = CommandType.Text;
                            cmd.ExecuteNonQuery();
                        }
                        con.Close();
                    }
                    client.Disconnect();
                }
                else
                {
                    Console.WriteLine("Client cannot be reached...");
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}
}

一切都“正常”...除了在 Visual Studio 中处于 Debug模式时出现错误。关于处理时间过长的问题。哦，正如您所知，CSV 文件中有大约 38,000 行必须插入到表中。

如果您有任何建议或新代码供我尝试，我将不胜感激!谢谢!

Answer 1

在许多情况下，您最终会循环访问数据，对不同的数据重复运行相同的查询，您可以通过在循环开始之前准备一次查询来帮助加快速度。

using (var cn = new MySqlConnection(...))
using (var cmd = cn.CreateCommand())
{
   // Setup command
   cmd.CommandText = "Some query";
   var param1 = cmd.Parameters.Add("@paramName1", MySQLDbType.sometype);
   var param2 = cmd.Parameters.Add("@paramName2", MySQLDbType.sometype);
   cmd.Prepare();

   // get data

   // loop over data
   foreach(var d in data)
   {
      param1.Value = d.somevalue;
      param2.Value = d.anothervalue;
      cmd.ExecuteNonQuery();
   }
}

您还可以通过索引来寻址参数，例如 cmd.Parameters[0].Value。

以这种方式准备语句可以减少每次迭代解析查询本身的工作开销。

<小时/>

在我必须处理的一种情况下，我发现最好的解决方案是将上述内容与使用多个值列表的 INSERT 查询相结合，其形式为 INSERT INTO X(a,b,c) VALUES ( ?,?,?),(?,?,?),(?,?,?)。它使实际的实现变得更加复杂(必须适当处理最后不会填充所有值列表的“剩余部分”)，并且依赖于按索引寻址参数；但确实有所作为。

<小时/>

编辑:处理 ssh 连接关闭的伪代码

connect = true
Open datasource
while (data remains)
{
   if (connect)
   {
      init ssh client
      init mysql connection
      init mysql command
      connect = false
   }
   while (data remains and enough time remaining)
   {
      get next data
      set command parameters
      execute command
   }
   if (not enough time remaining)
   {
       close current connection and client
       connect = true
   }
}