如何检查表中的字段是否已经存在?

my question is very simple: i have a SQL Table with a column name 'lastname' with fields lastname1,lastname2,lastname3...

我的问题很简单:我有一个SQL表，上面有一个名为lastname1、lastname2、lastname3…

In my c# code, i have a method that inserts in the table a row only if the field of the column lastname is not present in the table. This method in input has lastname, so for my INSERT is a parameter.

在我的c#代码中，我有一种方法，只在表中不存在列lastname字段的情况下，在表中插入一行。这个输入的方法有lastname，所以我的INSERT是一个参数。

How can i compare and conseguently check if the field lastname is already in table?

我如何比较和间接检查字段lastname是否已经在表中?

Thanks

谢谢

3 个解决方案

#1

You should always use unique constraints in the table if a field must be unique. On that way you prevent duplicates always, even if the input was directly from SSMS or another application.

如果字段必须是惟一的，则应该始终在表中使用惟一的约束。这样，即使输入直接来自ssm或其他应用程序，也可以始终避免重复。

Then the easiest would be to handle the sql-exception that is raised according to it's number.

然后最简单的方法是处理根据它的编号而引发的sql异常。

....

try
{
    int inserted = cmd.ExecuteNonQuery();
} catch (SqlException ex)
{
    if (ex.Number == 2601)
    {
        // show meaningful error message
        MessageBox.Show("Cannot insert duplicate key row in object"); 
    }
    else
        throw;
}

....

#2

This SQL will insert a new record only if the value isn't already in the table:

只有当值不在表中时，此SQL才会插入一个新记录:

INSERT INTO Your_Table ( LastName )
SELECT @NewLastName
WHERE NOT EXISTS( SELECT * FROM Your_Table WHERE LastName = @NewLastName )

#3

There are two option one is from sql side another way is from code behind.

有两种选择，一种来自sql，另一种来自代码。

unfortunately you can't change your sql code i agree with @David.

不幸的是，您不能更改您的sql代码，我同意@David。

from code behind you have to do something like this.

从后面的代码，你必须做这样的事情。

First you have to select all the data from your table and check that data. something like this.

首先，必须从表中选择所有数据并检查数据。是这样的。

    SqlConnection con = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con; //Your connection string"
    cmd.CommandText = "Select * from table1"; // your query
    cmd.CommandType = CommandType.Text;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    DataTable dt = new DataTable();
    dt = ds.Tables[0];
    int count=0;
    for (int i = 0; i > dt.Rows.Count; i++)
    {
        if (Convert.ToString(dt.Rows[i]["LastName"]) == Lastname)
        {
            count++;
        }
    }
    if (count > 0)
    {
        //insert code for data
    }
    else
    {
        var script = "alert('"+ Lastname + "already exist.');";
        ClientScript.RegisterStartupScript(typeof(Page), "Alert", script, true);
        // or you can use here your Update statement
    }

May this will help you and you can understand.

这对你有帮助，你也能理解。

#1