使用C＃中的主键将行插入数据库的最佳方法是什么？

Sorry for my vague-ish title. Basically, I have a Call Logging app that uses a database with 2 tables (Customer and Records) to add, remove and search for records. I have a column in Records called 'CallID' that I use to help keep the calls unique and so I can use the CallID to Remove specific records. However, the problem lies on my adding call function. I currently have it so that the CallID is the number of items in the list incremented by 1:

对不起我的模糊标题。基本上,我有一个呼叫记录应用程序,它使用带有2个表(客户和记录)的数据库来添加,删除和搜索记录。我在Records中有一个名为'CallID'的列,用于帮助保持调用的唯一性,因此我可以使用CallID删除特定记录。但是,问题在于我添加了调用函数。我目前拥有它,以便CallID是列表中的项目数增加1:

private void addcall_btn_Click(object sender, EventArgs e)
    {
        try
        {

            //This is my addcall click event that inserts a row in 'Records' table using what the user has entered into the fields
            string sql = "INSERT Records (CustomerID, CallID, CustomerName, Telephone, DateAndTime, Status, Description) VALUES (@CustomerID, @CallID, @CustomerName, @Telephone, @DateAndTime, @Status, @Description)";
            SqlConnection conn = ConnectionString();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Connection = conn;
            cmd.Parameters.AddWithValue("CustomerID", customerID_cb.Text);
            cmd.Parameters.AddWithValue("CallID", (CallListBox.Items.Count + 1));
            cmd.Parameters.AddWithValue("DateAndTime", DateTime.Now);
            cmd.Parameters.AddWithValue("Status", status_cb.Text);
            cmd.Parameters.AddWithValue("Description", description_txt.Text);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

            MessageBox.Show("Record Created");
            this.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

You probably know by now that simply incrementing one to the number of items in the list is a clumsy way of doing things and it leads to problems after records have been removed and you want to add more. I want to know if there is a way to do what I am trying to do but in a much better way :)

您现在可能知道,简单地将一个增加到列表中的项目数是一种笨拙的做事方式,并且在删除记录并且您想要添加更多内容后会导致问题。我想知道是否有办法做我想做的事情但是以更好的方式:)

Thanks!

2 个解决方案

#1

Set CallID to auto increment

将CallID设置为自动递增

ALTER TABLE Records AUTO_INCREMENT=1

Then change your insert statement so that it excludes the field

然后更改您的insert语句,以便它排除该字段

INSERT Records (CustomerID, CustomerName, Telephone, DateAndTime, Status, Description) VALUES (@CustomerID, @CustomerName, @Telephone, @DateAndTime, @Status, @Description)

The value for the CallID field will then be handled by the database for any subsequent rows added.

然后,数据库将为添加的任何后续行处理CallID字段的值。

#2

Proceed in this way(Incase if you're not using auto increment in database), it may help you.

以这种方式继续(如果你没有在数据库中使用自动增量,那么它可能对你有所帮助)。

Bind your last value into label or textbox,

将您的最后一个值绑定到标签或文本框中,

SqlConnection con = new SqlConnection("Your Connection String");
con.Open();
SqlCommand cmd = new SqlCommand("Select Max(CallID) from Yourtablename",con);    
SQlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
    Label1.Text = dr.GetInt32(0).ToString();
}
con.Close();

Change like this in your above code,

在上面的代码中更改如下,

cmd.Parameters.AddWithValue("CallID", Convert.ToInt32(Label1.Text));

Make a datatype to int of your CallID column.

将数据类型设置为CallID列的int。

Please let me know the further issues.

请让我知道进一步的问题。

#1