How do I make my query more precise based on my condition to return a string

Question

0.00/5 (No votes)

See more:

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.

Question:

How can I have return proper message regardless I am checking above query for inserting or updating a record and it goes over my requirement. As Top 1 can help me to insert new record checking if name is unique and it will return empty but if I am updating row with its own keeping name as it is and changing other column which is  description. And when I update that I am receiving message "Record exist in the table" which it should not give me and should updating without message.  So how can I make this query check for both the method **inserting and updating** record.

What I have tried:

C#

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.isEnabled" +
				@"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;
}

Posted 28-Feb-17 12:10pm

jateshs9

Updated 2-Mar-17 11:53am

v3

Add a Solution

Comments

CHill60 2-Mar-17 11:38am

There is a problem with your conditions:
1. If the product already exists in the table you should not be "inserting data" but updating instead.
2. Same applies here - it's not clear why you want to know if it is disabled or not
3. You can't update data if the row doesn't already exist, you can only insert.

Can you clarify when you will insert data and when you will update it. There is no need for Top 2 as you are trying to ensure each [name] is unique.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Luis Velarde · Answer 1 · 2017-02-28T13:03:00

Solution 1

It's not necessary make to selects for get the information. I see an issue when you are trying to set the variable @Enable. @Enable is for a unique value, but your select is top 2 and this is a collection, for this reason if you try to run the script this give you a error message. you need to change to top 1. In the last one select you have a convert for a Msg, But in your CASE you have all the values with the type string. It's not necessary. Try the next query:

SQL

SELECT TOP 1 
	CASE 
		WHEN p.is_active = 0 THEN 'Record is disabled'
		WHEN p.is_active = 2 THEN 'Record exist in the table'
		ELSE ''
	END  
FROM 
dbo.[product] p WITH (nolock)
WHERE p.name = @name)

Finally try to use a store procedures in the future because is more secure and faster than command text. Good luck.

Posted 28-Feb-17 13:03pm

Luis Velarde

Comments

jateshs9 1-Mar-17 10:04am

Question: is_active is being renamed to isenabled which has datatype as bit in 0 or 1. It will never be as 2 as in your code. I have made changes based on my previous sentence.

1. Sorry to say this way, but either you didn't understood my question or you didn't look at my requirement properly as your query is wrong. Your query only checks whether isEnable is 0 or 1. But not whether name exist more than one or not.

2. I am not getting any error message I ran it myself in SQL Server and it works for @Enable = 0 but other cases. Command text was like a requirement to use instead of Stored Procedure. I am fully aware of having stored procedure but I had to do it this way.

3. Top 1 will only select one record. 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." This will not compare with other rows to check. If I am updating row 5 with itself changing the description it will show me the message 'Record exist in the table' and thus will not allow me to update the row itself.

Please see image for the point 3 as an example.

Luis Velarde · Answer 2 · 2017-03-02T11:53:00

Ok I think so what do you need

First option:

SQL

--THE BEST OPTION IS USE A UNIIQUE IN THE FIELD OF YOUR TABLE
-- IF THIS EXISTS YOU CAN CATH THE ERROR AN RETURN THIS TO THE USER
CREATE TABLE Products(
	identifier int identity(1,1),
	name	varchar(50)
	UNIQUE (name) --<===THIS IS A CONFIGURATION THAT YOU NEED IN YOUR TABLE
)

INSERT INTO Products(name) VALUES('Product 1')
INSERT INTO Products(name) VALUES('Condiments')
INSERT INTO Products(name) VALUES('Product 3')
INSERT INTO Products(name) VALUES('Dairy Products')

SELECT * FROM Products

--WHEN YOU TRY TO MAKE AN UPDATE
UPDATE Products
SET name = 'Condiments'
WHERE identifier = 4

--THIS OPTION WORK TOO IN AN INSERT
INSERT INTO Products(name) VALUES('Condiments')

DROP TABLE Products

Second Option:

SQL

--THIS IS NOT THE BEST OPTION BECUASE YOU ARE NOT WORKING WITH THE RULES OF DB
--YOU ARE TRYING REPLACE THE FUNCTION OF DB IN CODE AND ONLY WORK IN THE QUERY
CREATE TABLE Products(
	identifier	INT IDENTITY(1,1),
	name		VARCHAR(50)
)
DECLARE @OLDname AS VARCHAR (50),
		@NEWname AS VARCHAR (50)
SET @OLDname = 'Dairy Products'
SET @NEWname = 'Condiments'

INSERT INTO Products(name) VALUES('Product 1')
INSERT INTO Products(name) VALUES('Condiments')
INSERT INTO Products(name) VALUES('Product 3')
INSERT INTO Products(name) VALUES('Dairy Products')



IF EXISTS(SELECT * FROM Products WHERE name = @NEWname)
BEGIN
	SELECT @NEWname + ' EXIST IN THE TABLE'
END
ELSE
BEGIN
	SELECT 'YOU CAN MAKE AN UPDATE FROM ' + @OLDname + ' TO ' + @NEWname
END

DROP TABLE Products