（学习笔记）Csharp实现窗口化对MySQL进行简单增删查改

一、安装MySql.Data和SunnyUI

二、源码

2.1  数据库连接

连接方式不同请自行修改server

private void UiButton_Connection_Click(object sender, EventArgs e)
{
    string server = UiTextBox_Server.Text.ToString();
    string port = UiTextBox_Port.Text.ToString();
    string username = UiTextBox_Username.Text.ToString();
    string password = UiTextBox_Password.Text.ToString();
    string database = UiLabel_Database.Text.ToString();
    string table = UiTextBox_Table.Text.ToString();

    ConnectionStringManager.Database = database;
    ConnectionStringManager.Table = table;
    ConnectionStringManager.ConnectionString = $"server={server};port={int.Parse(port)};uid={username};pwd={password};database={database}";
    string connectionString = ConnectionStringManager.ConnectionString;
    MySqlConnection connection = new MySqlConnection(connectionString);

    try
    {
        // 打开连接  
        connection.Open();
        UIMessageBox.Show("连接成功！");
        ConnectionSuccessful?.Invoke();
        Close();
    }
    catch (MySqlException ex)
    {
        // 处理连接错误或其他MySQL相关的异常  
        UIMessageBox.Show($"连接失败: {ex.Message}");
    }
    catch (Exception ex)
    {
        // 处理其他类型的异常  
        UIMessageBox.Show($"发生错误: {ex.Message}");
    }
    finally
    {
        connection.Close();
    }
}

2.2  数据加载

加载数据同时获取表中int型数据的列名（我的表中没有其他的数值类型，如需同时获取long、float、double等自行添加），用来区分列中的数据是数值类型还是字符串类型，因为MySQL语句中字符串类型需要用单引号包括，数值类型则不用。

 public void LoadData()
 {
     string table = ConnectionStringManager.Table;
     string query = $"SELECT * FROM {table}"; // 替换yourtable为你的表名  
     string connectionString = ConnectionStringManager.ConnectionString;
     try
     {
         using (MySqlConnection connection = new MySqlConnection(connectionString))
         {
             connection.Open(); // 打开连接  

             using (MySqlCommand command = new MySqlCommand(query, connection))
             {
                 using (MySqlDataAdapter adapter = new MySqlDataAdapter(command))
                 {
                     DataTable dataTable = new DataTable();
                     adapter.Fill(dataTable); // 执行查询并填充DataTable  
                     UiDataGridView.DataSource = dataTable; // 绑定数据到DataGridView控件，假设控件名为uiDataGridView  
                     UiDataGridView.ReadOnly = true;
                     UiDataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);//调整所有单元格的大小以适应内容
                     UiDataGridView.AllowUserToAddRows = false;
                 }
             }
         }
     }
     catch (Exception ex)
     {
         UIMessageBox.Show(ex.Message); // 显示错误信息  
     }

     IntFieldNote();
 }

 private void IntFieldNote()
 {
     string table = ConnectionStringManager.Table;
     string connectionString = ConnectionStringManager.ConnectionString;

     using (MySqlConnection connection = new MySqlConnection(connectionString))
     {
         connection.Open();

         using (MySqlCommand command = new MySqlCommand($"DESCRIBE {table}", connection))
         using (MySqlDataReader reader = command.ExecuteReader())
         {
             // 用于存储int类型列名的集合  
             ConnectionStringManager.IntColumnNames = new HashSet<string>();

             // 读取结果集  
             while (reader.Read())
             {
                 string fieldType = reader["Type"].ToString();

                 // 检查字段类型是否为int  
                 if (fieldType.StartsWith("int"))
                 {
                     // 如果是int类型，获取列名并添加到集合中  
                     string columnName = reader["Field"].ToString();
                     ConnectionStringManager.IntColumnNames.Add(columnName);
                 }
             }
         }
     }
 }

2.3  数据添加

因为我的ID唯一且没有-1，所以我通过查找id=-1，让所有列名展示在UiDataGridView中，并添加新的一行用来让用户输入。

 private void Form_AddData_Load(object sender, EventArgs e)
 {
     string table = ConnectionStringManager.Table;
     string query = $"SELECT * FROM {table} WHERE id = -1";

     try
     {
         string connectionString = ConnectionStringManager.ConnectionString;
         using (MySqlConnection connection = new MySqlConnection(connectionString))
         {
             connection.Open();

             using (MySqlCommand command = new MySqlCommand(query, connection))
             {
                 using (MySqlDataAdapter adapter = new MySqlDataAdapter(command))
                 {
                     DataTable dataTable = new DataTable();
                     adapter.Fill(dataTable);

                     DataRow newRow = dataTable.NewRow();
                     newRow.ItemArray = new object[dataTable.Columns.Count]; // 初始化新行的所有列  
                     dataTable.Rows.Add(newRow);

                     // 设置DataGridView的数据源，并禁用用户添加新行  
                     UiDataGridView.DataSource = dataTable;
                     UiDataGridView.AllowUserToAddRows = false;

                     //禁用排序功能
                     foreach (DataGridViewColumn column in UiDataGridView.Columns)
                     {
                         column.SortMode = DataGridViewColumnSortMode.NotSortable;
                     }

                     for (int i = 0; i < UiDataGridView.Rows.Count - 1; i++)
                     {
                         UiDataGridView.Rows[i].ReadOnly = true;
                     }

                     UiDataGridView.Rows[UiDataGridView.Rows.Count - 1].ReadOnly = false;
                     
                     UiDataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
                     foreach (DataGridViewColumn column in UiDataGridView.Columns)
                     {
                         // 获取当前列的宽度  
                         int currentWidth = column.Width;

                         // 计算新的宽度，即当前宽度的两倍  
                         int newWidth = currentWidth * 2;

                         // 设置列的新宽度  
                         column.Width = newWidth;
                     }
                 }
             }
         }
     }
     catch (Exception ex)
     {
         UIMessageBox.Show(ex.Message);
     }


 }

 private void UiButton_AddData_Click(object sender, EventArgs e)
 {
     bool stop = false;
     string newData = null;
     string columnNm = null;
     string table = ConnectionStringManager.Table;
     HashSet<string> intColumnNames = ConnectionStringManager.IntColumnNames;
     if (UiDataGridView.Rows.Count > 0)
     {
         for (int columnIndex = 0; columnIndex < UiDataGridView.ColumnCount; columnIndex++)
         {
             // 获取列头文本    
             string ColumnName = UiDataGridView.Columns[columnIndex].HeaderText;
             // 获取单元格的值  
             object cellValue = UiDataGridView.Rows[UiDataGridView.Rows.Count - 1].Cells[columnIndex].Value;
             // 检查值是否为null或DBNull  
             if (cellValue == null || cellValue == DBNull.Value)
             {
                 UIMessageBox.Show($"数据不能为空>{ColumnName}");
                 stop = true;
                 break;
             }

             // 如果值不是null，则转换为字符串并添加到newData  
             string Data = cellValue.ToString();
             if (intColumnNames.Contains(ColumnName))
             {
                 newData += $"{Data},";
             }
             else
             {
                 newData += $"'{Data}',";
             }
             columnNm += $"{ColumnName},";
         }

         if (stop)
         {
             return;
         }
         // 如果需要，移除最后的逗号  
         if (columnNm.Length > 0)
         {
             columnNm = columnNm.TrimEnd(',');
         }
         if (newData.Length > 0)
         {
             newData = newData.TrimEnd(',');
         }
     }
     else
     {
         // 处理DataGridView没有行的情况  
         UIMessageBox.Show("DataGridView中没有行");
     }

     try
     {
         string connectionString = ConnectionStringManager.ConnectionString;
         using (MySqlConnection connection = new MySqlConnection(connectionString))
         {
             connection.Open();

             string query = $"INSERT INTO {table}({columnNm}) VALUES ({newData})";

             using (MySqlCommand command = new MySqlCommand(query, connection))
             {
                 // 执行更新操作  
                 command.ExecuteNonQuery();

                 // 添加成功后，给出相应的提示  
                 UIMessageBox.Show("数据添加成功！");
                 DataAddedEvent?.Invoke(this, EventArgs.Empty);

                 int rowIndexToClear = UiDataGridView.Rows.Count - 1;
                 DataGridViewRow rowToClear = UiDataGridView.Rows[rowIndexToClear];

                 foreach (DataGridViewCell cell in rowToClear.Cells)
                 {
                     // 检查单元格是否为null  
                     if (cell != null)
                     {
                         if (cell.OwningColumn.ValueType == typeof(int) || cell.OwningColumn.ValueType == typeof(long)) /* 其他不允许null的类型 */
                         {
                             cell.Value = DBNull.Value;
                         }
                         else
                         {
                             cell.Value = string.Empty;
                         }
                     }
                 }
             }
         }
     }
     catch (MySqlException ex)
     {
         UIMessageBox.Show($"添加失败: {ex.Message}");
     }
     catch (Exception ex)
     {
         UIMessageBox.Show($"发生错误: {ex.Message}");
     }
 }

2.4  数据删除

因为我的id是唯一的，所以直接获取选中行的id，然后执行删除语句

private void DeleteData()
{
    // 检查是否有选中的行  
    if (UiDataGridView.SelectedRows.Count != 1)
    {
        UIMessageBox.Show("请选择要删除的一行。");
        return;
    }

    // 获取选中行的id值  
    int idToDelete = Convert.ToInt32(UiDataGridView.SelectedRows[0].Cells[0].Value);
    string connectionString = ConnectionStringManager.ConnectionString;
    MySqlConnection connection = new MySqlConnection(connectionString);

    try
    {
        connection.Open();

        string table = ConnectionStringManager.Table;
        string query = $"DELETE FROM {table} WHERE id = {idToDelete}";
        MySqlCommand command = new MySqlCommand(query, connection);

        // 执行删除操作  
        int rowsDeleted = command.ExecuteNonQuery();
        if (rowsDeleted > 0)
        {
            UIMessageBox.Show("数据删除成功");

            LoadData();
        }
        else
        {
            UIMessageBox.Show("没有数据被删除。");
        }
    }
    catch (MySqlException ex)
    {
        UIMessageBox.Show("数据删除失败:" + ex.Message);
    }
    finally
    {
        if (connection.State == ConnectionState.Open)
        {
            connection.Close();
        }
    }
}

2.5  数据查询

因为我的表中目前没有小数，所以我直接通过强转int型来判断用户是否输入的是数值类型，然后拼接字符串来得到MySQL语句

public void QueryData()
{
    string table = ConnectionStringManager.Table;
    string connectionString = ConnectionStringManager.ConnectionString;
    string Equation = null;
    HashSet<string> intColumnNames = ConnectionStringManager.IntColumnNames;

    try
    {
        string searchValue = UiTextBox.Text.Trim();
        string query = $"SELECT * FROM {table} WHERE ";

        bool success = int.TryParse(UiTextBox.Text, out int num);
        if (success)
        {
            for (int columnIndex = 0; columnIndex < UiDataGridView.ColumnCount; columnIndex++)
            {
                string ColumnName = UiDataGridView.Columns[columnIndex].HeaderText;

                if (intColumnNames.Contains(ColumnName))
                {

                    Equation += $"{ColumnName} = {searchValue} OR ";
                }
                else
                {
                    Equation += $"{ColumnName} LIKE '%{searchValue}%' OR ";
                }
            }
        }
        else
        {
            for (int columnIndex = 0; columnIndex < UiDataGridView.ColumnCount; columnIndex++)
            {
                string ColumnName = UiDataGridView.Columns[columnIndex].HeaderText;

                if (intColumnNames.Contains(ColumnName))
                {

                }
                else
                {
                    Equation += $"{ColumnName} LIKE '%{searchValue}%' OR ";
                }
            }
        }
        Equation = Equation.TrimEnd(' ', 'O', 'R');

        string queryFinal = query + Equation;

        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open(); // 打开连接  

            using (MySqlCommand command = new MySqlCommand(queryFinal, connection))
            {
                using (MySqlDataAdapter adapter = new MySqlDataAdapter(command))
                {
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable); // 执行查询并填充DataTable  
                    UiDataGridView.DataSource = dataTable; // 绑定数据到UiDataGridView控件
                    UiDataGridView.ReadOnly = true;
                    UiDataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);//调整所有单元格的大小以适应内容
                    UiDataGridView.AllowUserToAddRows = false;

                    if (!string.IsNullOrEmpty(searchValue))
                    {
                        // 执行模糊搜索并高亮显示结果  
                        PerformFuzzySearchAndHighlight(UiDataGridView, searchValue);
                    }
                    else
                    {
                        // 清除高亮显示（如果需要的话）  
                        ClearHighlighting(UiDataGridView);
                    }

                    UiTextBox.Clear();
                }
            }
        }
    }
    catch (SqlException ex)
    {
        // 处理数据库异常  
        MessageBox.Show("数据库错误: " + ex.Message);
    }
}

2.6  数据修改

获取原id后，通过原id进行数据修改

private void UiButton_Confirm_Click(object sender, EventArgs e)
{
    bool stop = false;
    string newData = null;
    string table = ConnectionStringManager.Table;
    HashSet<string> intColumnNames = ConnectionStringManager.IntColumnNames;
    if (UiDataGridView.Rows.Count > 0)
    {
        for (int columnIndex = 0; columnIndex < UiDataGridView.ColumnCount; columnIndex++)
        {
            // 获取列头文本    
            string ColumnName = UiDataGridView.Columns[columnIndex].HeaderText;
            // 获取单元格的值  
            object cellValue = UiDataGridView.Rows[UiDataGridView.Rows.Count - 1].Cells[columnIndex].Value;
            // 检查值是否为null或DBNull  
            if (cellValue == null || cellValue == DBNull.Value)
            {
                UIMessageBox.Show($"数据不能为空>{ColumnName}");
                stop = true;
                break;                       
            }

            if (ColumnName == "birth_date")
            {
                if (cellValue is DateTime dateTimeValue)
                {
                    // 转换日期值为只包含日期的字符串格式  
                    cellValue = dateTimeValue.ToString("yyyy-MM-dd");
                }
                else
                {
                    UIMessageBox.Show($"时间输入错误");
                    stop = true;
                    break;
                }
            }


            // 如果值不是null，则转换为字符串并添加到newData  
            string Data = cellValue.ToString();
            if (intColumnNames.Contains(ColumnName))
            {
                newData += $"{ColumnName} = {Data},";
            }
            else
            {
                newData += $"{ColumnName} = '{Data}',";
            }


        }

        if(stop)
        {
            return;
        }
        // 如果需要，移除最后的逗号  
        if (newData.Length > 0)
        {
            newData = newData.TrimEnd(',');
        }
    }
    else
    {
        // 处理DataGridView没有行的情况  
        UIMessageBox.Show("DataGridView中没有行");
    }

    try
    {
        string connectionString = ConnectionStringManager.ConnectionString;
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            string query = $"UPDATE {table} SET {newData} WHERE id = {UpdataId}";

            using (MySqlCommand command = new MySqlCommand(query, connection))
            {
                // 执行更新操作  
                command.ExecuteNonQuery();
                
                // 更新成功后，给出相应的提示  
                UIMessageBox.Show("数据更新成功！");
                                       
            }
        }
        Close();
    }
    catch (MySqlException ex)
    {
        UIMessageBox.Show($"修改失败: {ex.Message}");
    }
    catch (Exception ex)
    {
        UIMessageBox.Show($"发生错误: {ex.Message}");
    }
}

三、效果展示

3.1  未连接时主窗口以及连接输入窗口展示

3.2  已连接后主窗口展示

（所有数据均AI生成虚假数据）

已上传源码，其他功能请自行试验