Check if duplicate exists with condition

Question

I have a datatable that populates from my database. I have a function to add and update data, but now I would like to check if the data exists or not. But I have to check two things.

If there is data in the database, make sure that it is turned on or off, if it is turned on, then you need to return the message = "Data exists in the table" else "Data is disabled."

    bool nameExists;

        connection statement
        {
            command.Connection = connection;
            command.CommandText =
                @"SELECT TOP 1 0 " +
                @"FROM [dbo].[products] p WITH (NOLOCK) " +
                @"WHERE [p].[name] = @name";
            command.Parameters.AddWithValue("name", name);
            command.CommandTimeout = 100;
            connection.Open();

            using (SqlDataReader reader = command.ExecuteReader())
            {
                nameExists = reader.HasRows;
            }
            connection.Close();
        }

How to increase my request to check the above conditions?

Note Please do not degrade this post, but post a comment if you think this post is not good mail. Also forgive me for my grammatical errors, if they are in my post.

Answer 1

Enabled int, 0 - , 1 - , , :

declare @Enabled int;
set @Enabled = (
  select top 1
    p.Enabled 
  from dbo.products p with (nolock)
  where p.name = @name
  );

select 
    msg = convert(varchar(32), case 
      when @Enabled is null 
        then 'Data does not exist'
      when @Enabled = 0
        then 'Data is disabled'
      when @Enabled = 1
        then 'Data exist in the table'
    end);

Answer 2

SqlCommand.ExecuteScalar Method(). null, .

using (var connection = new SqlConnection(connectionString))
{
    string sql = "SELECT enabled FROM dbo.products WITH(nolock) WHERE name = @name";
    using (var command = new SqlCommand(sql, connection)) {
        command.Parameters.AddWithValue("name", name);
        command.CommandTimeout = 100;
        connection.Open();
        object enabled = command.ExecuteScalar();
        if (enabled == null) {
            ... // Product does not exist.
        } else if ((int)enabled == 0) {
            ... // Product is disabled.
        } else {
            ... // Product is enabled.
        }
    }
}

.

Answer 3

You can change as below:

        bool nameExists;

        connection statement
        {
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText =
                @"SELECT TOP 1 Enabled " +
                @"FROM [dbo].[products] p WITH (NOLOCK) " +
                @"WHERE [p].[name] = @name";
            command.Parameters.AddWithValue("name", name);
            command.CommandTimeout = 100;
            connection.Open();

            using (SqlDataReader reader = command.ExecuteReader())
            {
                nameExists = reader.HasRows;

                if (nameExists)
                {
                    reader.Read();
                    //bool Enabled = (reader.GetInt32(0) == 1);
                    bool Enabled = reader.GetBooean(0);
                    if (Enabled)
                        return "Data exist in the table";
                    else
                        return "Data is disabled.";
                }
                else
                {
                    throw new Exception ("Data not exist in the table");
                }
            }
            connection.Close();
        }