c# - 如果记录存在则不插入

Question

我正在尝试一些东西，但无法弄清楚。所以我正在尝试的是，如果用户在 txtISN 中输入某些内容并且它已经存在于数据库中，则不会插入记录。我还希望在未插入记录时弹出错误消息，并且在插入记录时弹出一条消息，表明记录已成功插入。

感谢大家的帮助!

 string _connStr = @"Data Source = EJQ7FRN; Initial Catalog = BES; Integrated Security = True";
                string _query = "INSERT INTO [BES_S] (ISN,Titel,Name) values (@ISN,@Titel,@Name)";
                using (SqlConnection conn = new SqlConnection(_connStr))
                {
                    using (SqlCommand comm = new SqlCommand())
                    {
                        comm.Connection = conn;
                        comm.CommandType = CommandType.Text;
                        comm.CommandText = _query;
                        comm.Parameters.AddWithValue("@ISN", txtISN.Text);
                        comm.Parameters.AddWithValue("@Titel",txtTitel.Text);
                        comm.Parameters.AddWithValue("@Name", txtName.Text);
                        try
                        {
                            conn.Open();
                            comm.ExecuteNonQuery();
                        }
                        catch (SqlException ex)
                        {

                        }
                    }
                }

Answer 1

你需要稍微改变你的查询，但是你可以使用MySql的DUAL执行此操作的关键字:

string _connStr = @"Data Source = EJQ7FRN; Initial Catalog = BES; Integrated Security = True";
string _query = "INSERT INTO [BES_S] (ISN,Titel,Name) ";
_query = _query + " SELECT @ISN, @Titel, @Name FROM DUAL";
_query = _query + " WHERE NOT EXISTS (SELECT ISN WHERE ISN=@ISN)";

using (SqlConnection conn = new SqlConnection(_connStr))
{
    using (SqlCommand comm = new SqlCommand())
    {
        comm.Connection = conn;
        comm.CommandType = CommandType.Text;
        comm.CommandText = _query;
        comm.Parameters.AddWithValue("@ISN", txtISN.Text);
        comm.Parameters.AddWithValue("@Titel",txtTitel.Text);
        comm.Parameters.AddWithValue("@Name", txtName.Text);
        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {

        }
    }
}

DUAL 就像一个虚拟表，您可以使用它来SELECT。