CODEWITHSUNDEEP
c#sql-server
nebo
nebo

Reputation: 61

Filter duplicate name from database in C#

When a method is called, I want to return true if the name already exists, and false otherize. My sample code is below.

private bool NameDuplicate(string txt)
{
    conn.Open();
    com.Connection = conn;
    com.CommandText = "select Cat_d from Category";

    SqlDataReader dr = com.ExecuteReader();
    bool found = false;

    while (dr.Read())
    {
        if (txt == dr["Cat_d"].ToString())
        {
            found = true;
        }
        else
        {
            found = false;
        }
    }

    conn.Close();
    return found;
}


if (NameDuplicate(cat_txt))
{
    MessageBox.Show("Already exist!");
}

Please help me explained.

Upvotes: 0

Views: 184

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

Below is some code you can use to do this but I want to point out that Realistically you probably wouldn't bother doing this in c#. You would add a UNIQUE constraint/index or primary key in the db and just insert values from c#, handling the case where there is an exception because the value already exists. Implementing a uniqueness logic in c# when sqlserver has been capable of doing it forever really is "buying a dog and barking yourself"

private bool NameDuplicate(string txt)
{
  using(var con = new SqlConnection(YOUR CONNECTION STRING HERE)
  using(var com = new SqlCommand("select count(*) from Category where cat_d = @c", con){
    con.Open();
    com.Parameters.Add("@c", SqlDbType.VarChar, 999).Value = txt;
    var o = com.ExecuteScalar();

    con.Close(); //optional; dispose will do this too
    return Convert.ToInt32(o) != 0;
  }
}


if (NameDuplicate(cat_txt))
{
    MessageBox.Show("Already exist!");
}

Always use using blocks to ensure db related resources are disposed of, and don't reuse them (it seems your connection and command are defined somewhere else and you keep them for a long time/reuse them?

To check for existence we can (simplistically) count the number of entries, executescalar it and check the returned number. It might be slightly faster to use an EXISTS query but I picked this route because it is easy to read and keeps the code simpler.

Here is a query you could use in place of the select count, to achieve the same effect. It would be highly beneficial to use it if the column you're searching isn't indexed because, as Aaron points out, there isn't any point in having sqlserver count all of an entire table when realistically it could stop as soon as it finds the value you're looking for and deliver the same answer:

SELECT CASE WHEN EXISTS(SELECT null FROM Category where cat_d = @c) THEN 1 ELSE 0 END

Upvotes: 2

Related Questions