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