C#: Trying to find duplicate upon inserting/updating records

Ravi Patel

I have been trying to find an answer where I am creating string function which checks duplicate record before inserting or updating a record but I have a condition with it. Before giving conditions, I have a table called Products (name, description, type, isenabled, InsertedDate, UpdatedDate). Isenabled is in bit datatype (0 or 1). And InsertedDate, UpdatedDate are auto generated.

In isEnables column

0 means disable

1 means enable

Before inserting, check whether name is unique but if name exist more than one then,

My conditions

1. Check whether product is isEnabled = 1 then while inserting data should display message "Product exist in the table."

2. Check whether product is isEnabled = 0 then while inserting data should display message "Product exist but it is disabled."

3. Else Insert/Update the data.

Below function only check whether record exist or not and return message accordingly.

private string GetName(string name, int id)

{

using (var connection = connection string)
using (var command = new SqlCommand())
{
command.Connection = connection;
command.CommandText =

@"DECLARE @Enabled INT; " +
@"SET @Enabled = " +

@"( " +
@"SELECT TOP 2 p.is_active " +
@"FROM dbo.[product] p WITH (nolock) " +
@"WHERE p.name = @name " +
@"); " +

@"SELECT Msg = CONVERT( VARCHAR(32), " +
@"CASE " +

@"WHEN @Enabled = 0 " +
@"THEN 'Record is disabled' " +
@"WHEN @Enabled = 2 " +
@"THEN 'Record exist in the table' " +

@"ELSE ''";
@"END);";

command.Parameters.AddWithValue("name", name);
command.CommandTimeout = 100;
connection.Open();
command.ExecuteNonQuery();
string message = (string)command.ExecuteScalar();
connection.Close();
return message;
}
return string.Empty;

}

Question: I am unable to return message "Record exist in the table". I know my query is wrong but how can I make this query proper in which it returns message based on my condition

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Accepted answer
I think, all you want to do is ensure that name is unique throughout the table. Is it irrespective of is_active flag? If yes, why not just create a unique index on name?

If you insist on checking manually, I still don't understand the bold part. "For inserting (even for updating the record), check whether name is unique but if name exist more than one then,". Because it will never exist more than one. Do you mean "if name exist then"?

If yes, can we not use something like as follows:
declare @Name varchar(20)

if exists (select 1 from [dbo].[Product] where [Name] = @Name and [is_active] = 1)
  select 'Product exists in the table';
else
  if exists (select 1 from [dbo].Product] where [Name] = @Name and [is_active] = 0)
    select 'Product exists but it is disabled';
  else
    -- Your insert / update goes here...
I am not sure we have to differenciate between insert and update.
One more thing, may I please request you to be bit more polite in conversations? Statements like "you didn't look at my last..." and "please check my query properly" are rude even if put "please" in it.
Ravi Patel9y ago
I apologize if I was impolite. I didn't mean to do that. If I have done that I am again sorry regarding to it. Well it is respective of active_flag. Creating an index is not an option for me as I cannot do that. Besides that you gave somewhat answer to what I was looking for. I really appreciate your effort in helping me.
Again I apologize if I have done any misbehavior.
Ravi Patel9y ago
Mr Nitin, First of all please check my query properly. is_active will set row to enable or disable as bit 1 or 0 which is for front end display.
Now in my query @"SET @Enabled = will based on my Select Top query and then based on that Case is seleted.
Now for inserting I am fine with Top 1 but my query does not work when it come for updating the record.
My Requirement:
For inserting (even for updating the record), check whether name is unique but if name exist more than one then,

My conditions
1. Check whether product is isEnabled = 1 then while inserting data should display message "Product exist in the table."
2. Check whether product is isEnabled = 0 then while inserting data should display message "Product exist but it is disabled."
3. Else Insert/Update the data.

If you still understood my query then forget the Query check my example table and condtion and help me out here to return proper message.
Nitin Sontakke9y ago
I think I am confused. You say that isEnabled is of data type bit. You only mention that it can have value 0 or 1. You only showing 0s and 1s in sample data. Then from where did = 2 came from?

You are assigning a the value of SELECT TOP 2 which is not possible. You can assign value of ONLY one row. Where did is_active coming from?
Ravi Patel9y ago
Mr Nitin, you didn't looked at my last sentence which is expectation. I am able to recognise WHEN @Enabled = 0 but not WHEN @Enabled = 2.

Just an example

This is my table WHEN @Enabled = 2 means it should check whether there is no other row having same name.

Let's say I am updating a name in row 4 with from "Dairy Products" to "Condiments". Here "Condiments" already exist in row 2. Therefore update should not occur and should return a message "Record exist in the table."

Note: This is like a requirement for this issue. I am fully aware of stored precodure but had to do it this only.
Nitin Sontakke9y ago
Two ways to resolve the issue:

1/ Do not assign message text to a variable at all
command.CommandText =
@"DECLARE @Enabled INT; " +
@"SET @Enabled = " +
@"( " +
@"SELECT TOP 2 p.is_active " +
@"FROM dbo.[product] p WITH (nolock) " +
@"WHERE p.name = @name " +
@"); " +
@"SELECT CONVERT( VARCHAR(32), " +
@"CASE " +
@"WHEN @Enabled = 0 " +
    @"THEN 'Record is disabled' " +
@"WHEN @Enabled = 2 " +
    @"THEN 'Record exist in the table' " +
          @"ELSE ''";
@"END);";
2/ Or SELECT the assigned variable at the end:
command.CommandText =
@"DECLARE @Enabled INT; " +
@"SET @Enabled = " +
@"( " +
@"SELECT TOP 2 p.is_active " +
@"FROM dbo.[product] p WITH (nolock) " +
@"WHERE p.name = @name " +
@"); " +
@"SELECT Msg = CONVERT( VARCHAR(32), " +
@"CASE " +
@"WHEN @Enabled = 0 " +
    @"THEN 'Record is disabled' " +
@"WHEN @Enabled = 2 " +
    @"THEN 'Record exist in the table' " +
          @"ELSE ''";
@"END);" +
@"SELECT Msg;";
Please rectify any minor syntax errors you get, if any.
Why don't you write a stored procedure for this? Instead of cluttering all your C# code with this SQL Statement. Very difficult to maintain in the long run.