Member Avatar for anthonyjpv

Hi!

I created a really simple CRUD app in C# using MS SQL as back end.

I connected the DB using connection string

and

use dataset, and data adapter to pull data from the db

Im searching for the simplest way of checking record if it exist in a table

before adding a new record or updating it successfully?

Thanks!

  • 3 Contributors
  • 2 Replies
  • 120 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by kamilacbe

Dani AI

Generated

: good question. and are on the right track, but the safest, simplest pattern in production is to let the database enforce uniqueness and handle duplicates in the application — rather than relying only on a pre-check in C#. That avoids race conditions and scales better.

A compact, reliable option is: add a UNIQUE constraint on the column(s) that must be unique, attempt the INSERT, and catch the SQL Server duplicate-key error in C#. Example of handling the insert and duplicate-key errors (2601/2627):

try
{
    using (var cmd = new SqlCommand(
        "INSERT INTO dbo.Users (Username, PasswordHash) VALUES (@u, @p)", conn))
    {
        cmd.Parameters.Add("@u", SqlDbType.NVarChar, 100).Value = username;
        cmd.Parameters.Add("@p", SqlDbType.NVarChar, 200).Value = passwordHash;
        cmd.ExecuteNonQuery();
    }
}
catch (SqlException ex)
{
    if (ex.Number == 2627 || ex.Number == 2601)
    {
        // handle duplicate (user exists)
    }
    else
    {
        throw;
    }
}

If a pre-check is still desired (for UI feedback before attempting insert), use a parameterized EXISTS query — it’s cheap and avoids pulling full rows:

using (var cmd = new SqlCommand(
    "SELECT CASE WHEN EXISTS(SELECT 1 FROM dbo.Users WHERE Username = @u) THEN 1 ELSE 0 END", conn))
{
    cmd.Parameters.Add("@u", SqlDbType.NVarChar, 100).Value = username;
    bool exists = Convert.ToInt32(cmd.ExecuteScalar()) == 1;
}

Practical notes: never concatenate user input into SQL (avoid the pattern shown earlier in the thread), always use parameters, prefer hashing for passwords, and prefer the DB-constraint + try-insert approach for correctness. If you must do check-then-insert in-app, wrap both steps in a transaction with the appropriate isolation level — but even then, a UNIQUE constraint is the final safety net.

Recommended Answers

All 2 Replies

Member Avatar for hericles

do a select statement for that record and use executeScalar to see if it returns 1 row or zero.

Member Avatar for kamilacbe

Hi..

You can keep a particular column as primary which wont support duplication of data..
(OR)you can go for datareader read the data from table and check that data with runtime value if that record doesnot exist then it inserts the record.

Con.open();
cmd.Connection = con;

cmd.CommandText = "select * from login where username='"+TextBox3.Text+"'AND
password='"+TextBox4.Text+"'";
SqlDataReader dr = cmd.ExecuteReader();
if(dr.HasRows)
{
Label3.Text = "Success";
}
else
{
Label3.Text = "Not Success";
}
con.Close();

(OR)

con.ConnectionString =””;
con.Open();

SqlDataAdapter da = new SqlDataAdapter("insert into login
values('" + TextBox3.Text + "','" + TextBox4.Text + "')", con);
DataSet ds = new DataSet();
da.Fill(ds, "login");
if(ds.Tables[0].Rows.Count>=1)
{
Label3.Text = "Success";
}
else
{
Label3.Text = "Not Success";
}
Con.close();
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.