主页 / 问题 / 1388067
Accepted
xto
Asked:2022-08-07 09:03:46 +0000 UTC2022-08-07 09:03:46 +0000 UTC

为 .Net C# 中的一个表保存数据更改不起作用

  • 772

我通过单击按钮将更改保存在数据库表中，这是代码本身：

else if (comboBox1.SelectedItem == "Элек")
{
label3.Text = "Электричество";
string script = "SELECT id, name, Mx_pw_VT, Pr_pw_VT, elec_kVT_hr, Print_time_fact, YZV_power_VT FROM elec;";
mycon = new MySqlConnection(connect);
mycon.Open();
MySqlDataAdapter ms_data = new MySqlDataAdapter(script, connect);
var cb = new MySqlCommandBuilder(ms_data);
cb.GetInsertCommand();
ms_data.Update(dataGridView1.DataSource as DataTable);
ms_data.InsertCommand = cb.GetInsertCommand();
ms_data.InsertCommand.CommandText += "; select * from elec where id = last_insert_id();";
ms_data.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
}

但由于某种原因，它给了我一个错误«Нарушение параллелизма: UpdateCommand затронула 0 из ожидаемых 1 записей.» 。我对另一个表有相同的代码块并且它可以工作，这个表中发生了变化，块本身：

else if (comboBox1.SelectedItem == "Рбт")
{
label3.Text = "Рбт";
string script = "SELECT id, name, Model_preparation_R_Hr, Time_for_preparation_hr, Time_for_post_processing_hr, YZV_work FROM rbt;";
mycon = new MySqlConnection(connect);
mycon.Open();
MySqlDataAdapter ms_data = new MySqlDataAdapter(script, connect);
var cb = new MySqlCommandBuilder(ms_data);
cb.GetInsertCommand();
ms_data.Update(dataGridView1.DataSource as DataTable);
ms_data.InsertCommand = cb.GetInsertCommand();
ms_data.InsertCommand.CommandText += "; select * from rbt where id = last_insert_id();";
ms_data.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
}

怎么了？请告诉我

c#

1 个回答

  1. Best Answer

    从数据库获取和转换响应的方法（仅与 SELECT 一起使用）：

    public List<T> SqlQuery<T>(string query)
    {
        var result = new List<T>();

        using (MySqlConnection connection = GetDBConnection(1))
        {
            connection.Open();
            using var command = connection.CreateCommand();
            command.CommandText = query;
            using var reader = command.ExecuteReader();
            var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToArray();
            var properties = typeof(T).GetProperties();

            while (reader.Read())
            {
                var data = new object[reader.FieldCount];
                reader.GetValues(data);

                var instance = (T)Activator.CreateInstance(typeof(T));

                for (var i = 0; i < data.Length; ++i)
                {
                    if (data[i] == DBNull.Value)
                        data[i] = null;

                    var property = properties.SingleOrDefault(x => x.Name.Equals(columns[i], StringComparison.InvariantCultureIgnoreCase));

                    if (property is not null)
                    {
                        var dataValue = Guid.TryParse(data[i].ToString(), out Guid res) is true ? res : data[i];
                        property.SetValue(instance, Convert.ChangeType(dataValue, property.PropertyType));
                    }
                }
                result.Add(instance);
            }
        }
        return result;
    }

    获取插入的 sql 查询行的方法。方便很多。无需为表格编写 1000 和 1 插入行。你只需要创建一个类：

        public static string GetInsertSqlString<TValue>(TValue instance, string tableName)
    {
        var columnsName = instance.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance).Select(x => x.Name).ToArray();
        var values = instance.GetType()
            .GetProperties(BindingFlags.Public | BindingFlags.Instance)
            .Select(x => x.GetValue(instance, null));
        var hasBool = values.FirstOrDefault(x => x.GetType() == typeof(bool)) is not null;
        if (hasBool is false)
            return $"INSERT INTO {tableName}({string.Join(",", columnsName)}) VALUES ('{string.Join("','", values)}')";
        else
        {
            List<string> valuesWithBool = new();
            foreach (var val in values)
                if (val.GetType().Equals(typeof(bool)))
                    valuesWithBool.Add(val.ToString());
                else
                    valuesWithBool.Add($"'{val}'");

            return $"INSERT INTO {tableName}({string.Join(",", columnsName)}) VALUES ({string.Join(",", valuesWithBool)})";
        }
    }

    插入方法：

    public DBResponseStatus ExecuteNonQuery(string query)
    {
        using MySqlConnection myConnection = GetDBConnection();
        try
        {
            if (myConnection.State == ConnectionState.Closed)
                myConnection.Open();

            MySqlCommand command = new(query, myConnection);
            command.ExecuteNonQuery();
            return DBResponseStatus.OK;
        }
        catch (Exception ex)
        {
            Log.Error(ex.ToString());
            return DBResponseStatus.Error;
        }
    }

    从 ExecuteNonQuery 方法返回的枚举：

    public enum DBResponseStatus
{
    OK = 0,
    Error = 1
}

    获取数据库行的 GetDBConnection 方法：

    public MySqlConnection GetDBConnection(int timeOut = 30)
    {
        string connString = "Server=" + Host
            + ";Database=" + DataBaseName
            + ";port=" + Port
            + ";User Id=" + Username
            + ";password=" + Password
            + ";Connection Timeout=" + timeOut;

        MySqlConnection conn = new(connString);

        return conn;
    }

    一个用于方法正常操作的类的例子：

    internal class ProductDB
{
    public Guid OrderId { get; set; }

    public Guid ProductId { get; set; }

    public ProductDB() { }

    public ProductDB(Guid orderId, Guid productID)
    {
        OrderId = orderId;
        ProductId = productID;
    }
}

    为 Select 调用 SqlQuery 方法的示例：

    List<Product> GetProducts() =>
            DB.SqlQuery<Product>("SELECT * FROM products");

    为 Insert 调用 ExecuteNonQuery 方法的示例：

    db.ExecuteNonQuery(SQLString.GetInsertSqlString(*экземпляр класса*, "ordersproducts"));
    • 1

相关问题