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

Question

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