Error when editing a datagridview with a connected MySQL database. An error appears when trying to save

Question

When trying to edit the datagridview, an error appears "The CommandText property has not been properly initialized.". I read that some other stored procedure is needed, which as input parameters it takes the first name, last name and patronymic and phone number of the user and returns his id, if so, how to implement it on mysql link to the source: https://metanit.com/sharp/adonet/2.11.php , https://metanit.com/sharp/adonet/3.5.php Program code:

    using MySql.Data.MySqlClient;
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 SD = System.Data;

namespace DBredaction
{
    public partial class Form1 : Form
    {
            DataSet ds;
            MySqlDataAdapter adapter;
            MySqlCommandBuilder commandBuilder;
            string connectionString = "Server=localhost;Database=catalog;Uid=root;pwd=;charset=utf8;";
            string sql = "SELECT * FROM employee";
            public Form1()
        {
            InitializeComponent();

            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.AllowUserToAddRows = false;

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                adapter = new MySqlDataAdapter(sql, connection);

                ds = new DataSet();
                adapter.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                // делаем недоступным столбец id для изменения
                dataGridView1.Columns["Id"].ReadOnly = true;
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
        //public MySqlConnection mycon;
        //public MySqlCommand mycom;
        //public string connect = "Server=localhost;Database=catalog;Uid=root;pwd=;charset=utf8;";
        //public SD.DataSet ds;
        //public MySqlCommand mycon2;otchestvo

        private void button1_Click(object sender, EventArgs e)
        {
            try { 
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                adapter = new MySqlDataAdapter(sql, connection);
                commandBuilder = new MySqlCommandBuilder(adapter);
                adapter.InsertCommand = new MySqlCommand("", connection);
                adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
                adapter.InsertCommand.Parameters.Add(new MySqlParameter("@imia",    MySqlDbType.VarChar, 50, "Имя"));
                adapter.InsertCommand.Parameters.Add(new MySqlParameter("@familia", MySqlDbType.VarChar, 50, "Фамилия"));
                adapter.InsertCommand.Parameters.Add(new MySqlParameter("@otchestvo", MySqlDbType.VarChar, 50, "Отчество"));
                adapter.InsertCommand.Parameters.Add(new MySqlParameter("@telephon", MySqlDbType.VarChar, 11, "Телефон"));
                

                MySqlParameter parameter = adapter.InsertCommand.Parameters.Add("@id", MySqlDbType.Int16, 0, "Id");
                parameter.Direction = ParameterDirection.Output;

                adapter.Update(ds);
            }
        }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    MessageBox.Show("DB CONNECT");
                    connection.Close();
                }
            }
            catch
            {
                MessageBox.Show("Connection lost");
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow row in dataGridView1.SelectedRows)
            {
                dataGridView1.Rows.Remove(row);
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            DataRow row = ds.Tables[0].NewRow(); 
            ds.Tables[0].Rows.Add(row);
        }
    }
    }

Answer 1

It doesn't work quite like that; if you're assigning a command builder to an adapter you don't then also set the XxxCommand DML properties yourself; the command builder does that from looking at the SelectCommand, working out the table schema and writing the queries.

I'd have the adapter at class level in a dedicated repository class, where the select, connstr and command builder are set:

//in the constructor of the repo class
_myDataAdapter = new MySqlDataAdapter("SELECT here", "connstr here");
_commandBuilder = new MySqlCommandBuilder(_myDataAdapter);

the fill code runs:

//in a GetData method of the repo class

DatTable dt = new DataTable();
_myDataAdapter.Fill(dt);
return dt;

and the relevant I/U/D commands can be triggered:

//in a SaveChanges(DataTable) method of the repo class
_myDataAdapter.Update(dt);

See https://www.devart.com/dotconnect/mysql/docs/Devart.Data.MySql~Devart.Data.MySql.MySqlCommandBuilder.html for more background info on the CB; their example code is based on a complete programmatic "fill, change, save" workflow so it's all in one method, but your workflow is essentially interrupted by the user needing to do the changes in the grid, hence breaking it up.

If you want to do it in one method, you can make your adapter, make your command builder and then set the I/U/D commands on the adapter by calling the relevant GetXxx on the command builder

Note that the command builder doesn't use stored procedures; that thing you've read about needing to make a stored procedure to update/insert data isn't the only way to save data to a db, and if you have created a sproc and are hoping to use it, forget a command builder; you'll have to do the command setup yourself