C# - 为什么我的服务器计算机上的 MySQL 服务器很慢？

Question

我开发了一个类似 POS 的应用程序，在使用 2 台 PC 进行测试期间，我没有遇到任何速度问题。这只是两台计算机之间的简单 LAN 电缆设置。但是当我将它部署在客户端时，它运行得很慢。

客户端有 1 台 PC 作为管理员和主服务器，还有 2 台 PC 作为收银员。全部连接在路由器中。收银员连接到管理员的PC(主服务器)以检索、插入、更新和删除数据。我只是想问一下MySQL中是否有需要完成的流程或者我的代码在连接数据库时是否有问题。

这是我用于连接到数据库的示例代码，我怀疑它有问题，因为这是连接到数据库和添加记录的标准。为了防止我可能对代码感到厌烦，您可以简单地跳转到我发布的第二个代码，我在那里有一条评论询问我的类的初始化是否正确。谢谢大家!

class DBConnection
{
    private MySqlConnection connection;
    private MySqlCommand cmd;
    private MySqlDataReader dr;
    private DataTable tbl;
    private MySqlDataAdapter da;
    private DataSet ds;
    private string connectionString;
    private string server;
    private string database;
    private string uid;
    private string password;

    private frmNotifOk myNotification;
    public DBConnection()
    {
        Initialize();
    }
    private void Initialize()
    {
        server = "CASHIER";
        database = "sampledb";
        uid = "root";
        password = "samplepassword";
        connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
        connection = new MySqlConnection(connectionString);
    }

    private bool OpenConnection()
    {
    try
    {
        connection.Open();
        return true;
    }
    catch (MySqlException ex)
    {
        switch (ex.Number)
        {
            case 0:
                MessageBox.Show("Cannot connect to server.");
                break;
        }
        return false;
    }
}

private void CloseConnection()
{
    try
    {
        connection.Close();
    }
    catch (MySqlException ex)
    {
        MessageBox.Show("Error: " + ex.Message);
    }
}

public void AddRecord(String DBQuery, bool showNotif)
{
    string query = DBQuery;
    bool notify = showNotif;
    try
    {
        if (this.OpenConnection() == true)
        {
            cmd = new MySqlCommand(query, connection);
            cmd.ExecuteNonQuery();
            if (notify)
            {
                MessageBox.Show("Item successfully added.");
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error: " + ex.Message);
    }
    finally
    {
        this.CloseConnection();
    }
}

最后，这是我如何在表单中使用该方法:

public partial class frmNewCashier : Form
{
    private DBConnection dbConnect;
    string sampleDataSource= "SELECT * FROM SampleTable";

    public frmNewCashier()
    {
        InitializeComponent();
        //Is this the correct place of initializing my DBConnection class?
        dbConnect = new DBConnection();
    }

    private void frmCashier_Load(object sender, EventArgs e)
    {
        try
        {
            dgvSearchItems.DataSource = dbConnect.DatabaseToDatagrid(dgvSearchItemsDataSource);
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error: " + ex.Message);
        }
    }

}

我将 DBConnection 类的初始化放在 public frmNewCashier() 中，这是正确的位置还是应该将它放在 Load 事件中或其他地方？我在想这是否与数据库的缓慢有关。除了这个问题之外，你还知道我可能错过了什么导致速度缓慢的事情吗？

Answer 1

DBConnect 类 { 公共(public) MySqlConnection 连接； 私有(private)字符串服务器； 私有(private)字符串数据库； 私有(private)字符串 uid； 私有(private)字符串密码；

    //Constructor
    public DBConnect()
    {
        Initialize();
    }

    //Initialize values
    public void Initialize()
    {
        server = "localhost";
        database = "db_sea_horses";
        uid = "root";
        password = " " ;
        //password = "123";
        string connectionString;
        connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
        connection = new MySqlConnection(connectionString);
    }


    //open connection to database
   public  bool OpenConnection()
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (MySqlException ex)
        { //0: Cannot connect to server.
            //1045: Invalid user name and/or password.
            switch (ex.Number)
            {
                case 0:
                    MessageBox.Show("Cannot connect to server.  Contact administrator");
                    break;

                case 1045:
                    MessageBox.Show("Invalid username/password, please try again");
                    break;
            }
            return false;
        }
    }

    //Close connection
   public bool CloseConnection()
    {
        try
        {
            connection.Close();
            return true;
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);
            return false;
        }
    }

}

<小时/>

类 DBmethods :DBConnect { 数据集dataset2;

 public void input_sql(string query)
 {
     try
     {
       //open connection
         if (this.OpenConnection() == true)
         {
             //create command and assign the query and connection from the constructor
             MySqlCommand cmd = new MySqlCommand(query, connection);

             //Execute command
             int x = cmd.ExecuteNonQuery();
              //close connection
             this.CloseConnection();

         }

     }

     catch(MySqlException myex) 
     {
               MessageBox.Show(ex.Message);
     }
 }

 ///////////////////////////////////////////////
 /////  select 
 /////////////////////////////////////////////

 public DataSet output_sql(string query,String table_name)
 {


         //Open connection
         this.OpenConnection();

         DataSet dataset = new DataSet();

         MySqlDataAdapter adapter = new MySqlDataAdapter();
         adapter.SelectCommand = new MySqlCommand(query, connection);
         adapter.Fill(dataset, table_name);
        //close Connection
         this.CloseConnection();
         //return list to be displayed
     return dataset;


 }

}

}

<小时/>

方法调用示例

1)插入/更新/删除语句

DBmethods dbm = new DBmethods();dbm.input_sql("您可以执行插入/更新/删除查询");

2)选择语句

DataSet ds = dbm.output_sql("select * from storage_bunkers where job_id LIKE '%"+ itemname.Text + "%' ", "storage_bunkers"); DataView myView = ((DataTable)ds.Tables["storage_bunkers"]).DefaultView; dataGridView1.DataSource = myView;