如何防止重复记录在我的数据库，同时更新记录？ [英] How do i prevent duplicate records in my database while updating records?

问题描述

在我的MySQL表我做的样子像这样的

in my mysql table I made look like this one

ControlNum|Title  |NumOfEpis|Description|
001       |naruto |500      |some text  |
002       |conan  |700      |some text  |

现在我做在C＃用户控件其中允许用户更新除了主的所有列ControlNum。所以假设每列都有自己的文本框，上述用户的控制所以为了避免重复在像例如，如果用户编辑的行2标题标题栏，并把它火影忍者也将是duplicated..so创建这种方法被称为checkData（）;搜索结果

now I made a user control in C# where in the user is allowed to update all columns except the primary"ControlNum". so assuming each column has its own textboxes in that said user control so in order to avoid duplication in the title column like for example if the user edit the row 2 title and put it "naruto" also it would be duplicated..so I create this method called checkData();

void checkData()
    {
        SuspendLayout();
        try
        {
            MySqlConnection conn = new MySqlConnection(myConnection);
            conn.Open();
            MySqlCommand command = new MySqlCommand("SELECT * FROM maindatabase.animelist where TitleAnime=?Title;", conn);
            //command.Parameters.AddWithValue("?CN", int.Parse(a.ToString()));
            command.Parameters.AddWithValue("?Title", textBox3.Text);
            MySqlDataReader reader = command.ExecuteReader();

            int ctr = 0;
            while (reader.Read())
            {
                ctr++;

            }
            if (ctr == 1)
            {
                my = Form.ActiveForm as MyList;
                my.msg = new Message_Box();
                my.msg.Descrip.Text = "Record is already in the Database";
                my.msg.Title.Text = "Duplicate Record";
                my.msg.ShowDialog();
            }
            else
            {

                updateData();
            }


            conn.Close();
            ResumeLayout();
        }
        catch (Exception ex)
        {
            MessageBox.Show("" + ex);
        }
    }

结果是工作，但我的问题是什么如果用户只更新EPIS和记述的数量并没有真正打算更新的标题，我的方法仍然检测到有一个重复的，因为我的逻辑是：如果（CTR == 1）..我想我缺少一些方法或我一种错误的做法很here..so我希望有人将启发me..sorry为是小白在这里YY

it was working but my problem is what if the user only update the number of epis and descrip and doesn't really intend to update the title,my method still detect that there's a duplicate since my logic was "if(ctr == 1)"..I think I'm missing some method or I'm in a wrong approach here..so I hope someone will enlighten me..sorry for being noob here Y.Y

推荐答案

如果您的应用程序支持，你需要确保变更不会被你的重复检查和更新数据库之间的另一个用户进行多用户。

If your application supports multiple users you need to ensure changes are not made by another user between your check for duplicates and the database update.

要最简单的方法做到这一点是因为mbeckish建议，创建标题列的唯一约束：

The easiest way to do this is as mbeckish suggested, create a UNIQUE constraint on the title column:

ALTER TABLE maindatabase.animelist 
ADD CONSTRAINT U_animelist_TitleAnime UNIQUE (TitleAnime)

数据库引擎，然后将实施独特的游戏，和你的客户机可以处理用户反馈通过捕获任何约束违反异常：

The database engine will then enforce unique titles and your client can handle user feedback by catching any constraint violation exception:

void checkData()
{
    SuspendLayout();
    try
    {

        updateData();

    }
    catch (Exception ex)
    {
        MySqlException sqlEx = ex as MySqlExecption;
        // If there is a constraint violation error.
        // (I may have the wrong error number, please test.)
        if (sqlEx != null && sqlEx.Number == 1062) 
        {
            my = Form.ActiveForm as MyList;
            my.msg = new Message_Box();
            my.msg.Descrip.Text = "Record is already in the Database";
            my.msg.Title.Text = "Duplicate Record";
            my.msg.ShowDialog();
        } 
        else 
        {
            MessageBox.Show("" + ex);
        }
    }
    finally
    {
        ResumeLayout();
    }
}

这篇关于如何防止重复记录在我的数据库，同时更新记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！