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.

LVL 12
jazzIIIloveAsked:
Who is Participating?
 
AndyAinscowConnect With a Mentor Freelance programmer / ConsultantCommented:
This might sound silly but what connection has ID and price?
Put another way - if you didn't want a 'loop' then check BEFORE the record is added.
0
 
Pratima PharandeCommented:
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")
    }
0
 
jazzIIIloveAuthor Commented:
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.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
jazzIIIloveAuthor Commented:
I mean how can I do before then?

Kind regards.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
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.
0
 
jazzIIIloveAuthor Commented:
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.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
0
 
jazzIIIloveAuthor Commented:
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.
0
 
jazzIIIloveAuthor Commented:
pratima_mcs:
Your code is not reflecting the loopng or self join.

Kind regards.
0
 
jazzIIIloveAuthor Commented:
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.
0
 
jazzIIIloveAuthor Commented:
I have to catch the not allowed one in my query (ies).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.