asked on

C# recursive check for a column (or with a loop check) and inform the user

Hi there;

In MSSQL, I have a table having 2 columns, 1 is the id as primary key, whereas the other is just price and both are int.

The question is that I want the user to be informed if there is a loop in this table.

The loop can be such:

id price
1 2
3 5
5 1

As seen, the id value having 1 has the loop in the third line with price value having 1. In such case, I want the user to be informed in C#.

How can I do that? I am a little illiterate regarding connecting the database and DML in MSSQL for C#.

Would be good with a code snippet.

Kind regards.

Pratima

you mean to say you need to just get result teu or false if loopong in there ?

try this

string strConnectionString = "connetion string here";
SqlConnection cnn = new SqlConnection(strConnectionString);
SqlClient.SqlCommand cmd = new SqlClient.SqlCommand("SELECT count(*) FROM tablename where id=price", cnn);
cmd.CommandType = CommandType.Text;
int cnt;
cnt = cmd.ExcuteScalar();
if(cnt > 0 )
{
REsponse.Write("There is loopong")
}

ASKER CERTIFIED SOLUTION

AndyAinscow

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jazzIIIlove

ASKER

Thanks pratima_mcs.

Yes, exactly AndyAinscow!

I am doing this after the record is added and have to also deal with before the record is added.

I mean in fact, what I want to achieve is that I don't want let the user add such looping in the table for those columns (but don't want to bug with you with that).

Kind regards.

jazzIIIlove

ASKER

I mean how can I do before then?

Kind regards.

AndyAinscow

Have a query before you add such as
SELECT * FROM tbl WHERE ((col1 = 1) OR (col2 = 2))
you need to replace tbl, col and 1/2 with the real values.
If the query returns any records then that value already exists.

jazzIIIlove

ASKER

But the values and the repetition is example. Is it still OK with the above code as the loop may appear, maybe nth row in a thousand and couple of times?

Regards and thanks for your interest and patience.

AndyAinscow

That is really suited for before the record is added - a check.

For an existing table you would have to construct a different query - a self join

jazzIIIlove

ASKER

Yes, that was the term, self join! (i have been thinking it for an hour) :/

How can i do that for this code you provided.

Regards.

jazzIIIlove

ASKER

pratima_mcs:
Your code is not reflecting the loopng or self join.

Kind regards.

jazzIIIlove

ASKER

ah, my mistake regarding the question is that I didn't mention the following, as I am stuck with the query:

1 2
2 4
3 5
4 5
5 1

5 1 is not allowed but the rest above is allowed, meaning since 1 is used before in the first column (1, 2), it cannot be used in the second column (5,1 case) later on.

Regards.

jazzIIIlove

ASKER

I have to catch the not allowed one in my query (ies).