-1

I have three column as follows:

  • Month
  • Tech
  • Circle

According to this column I need to fetch the data.

In Month Column, Data is : Jan,feb, March... and so on.
In Tech Column, Data is : Gsmnqi, Gsmboi... and so on.
In Circle Column, Data is : Ap ,Kol, Mumbai.. and so on.

I want to prove four condition as follows:

  1. if I select the month it will fetch the data related to month, tech and circle will not be selected.
  2. if I select the month and tech it will fetch the data related to month and Tech, circle will not be selected.
  3. if I select the month and circle it will fetch the data related to month and circle, Tech will not be selected.
  4. if I select month, tech and circle it will fetch the data related to month, tech and circle.

But my if condition is not working, seriously I get confused in if, else and else if for this four condition.

  if (nqiSqiEntity.Month != string.Empty)
  {
      query.AppendLine("select * from K2_NQISQI with (nolock) where MONTH = '" + nqiSqiEntity.Month + "'  order by id asc");
  }
  else if (nqiSqiEntity.Month != string.Empty && nqiSqiEntity.Tech != string.Empty)
  {
      query.AppendLine("select * from K2_NQISQI with (nolock) where MONTH = '" + nqiSqiEntity.Month + "' and TECH = '" + nqiSqiEntity.Tech + "' order by id asc");
  }
  else if (nqiSqiEntity.Month != string.Empty && nqiSqiEntity.Circle != string.Empty)
  {
      query.AppendLine("select * from K2_NQISQI with (nolock) where MONTH = '" + nqiSqiEntity.Month + "' and CIRCLE = '" + nqiSqiEntity.Circle + "' order by id asc");
  }
  else
  {
      query.AppendLine("select * from K2_NQISQI with (nolock) where MONTH = '" + nqiSqiEntity.Month + "' and CIRCLE = '" + nqiSqiEntity.Circle + "' and TECH '" + nqiSqiEntity.Tech + "' order by id asc");
  }

In condition, instead of string.Empty I need to put the value or I need to check if the value is present then only condition should get executed. Please kindly help me in my understanding this code.

closed as unclear what you're asking by Jesse C. Slicer, user40980, user53019, gnat, Robert Harvey Jul 22 '14 at 3:36

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Please format your posted code – Heslacher Jul 18 '14 at 14:03
  • How to format it Heslacher .Please let me know .In what way you want .. – HPR Jul 18 '14 at 14:21
  • radarbob has already formated your code. – Heslacher Jul 18 '14 at 14:31
  • In the future, you may wish to pass the text of the question through an online spell and grammar checker such as spellcheckplus.com to try to help you with the rules of English. – user40980 Jul 18 '14 at 14:46
  • I agree it is way off topic, but this needed help - desperately. P.S. I think I'm done re-re-re-editing my post below. – radarbob Jul 18 '14 at 14:58
6

If you write a statement

if(A)
{
    // ... foo
}
else if(A && B)
{
    // ... bar
}

the second block will never be executed. Since if A is true, it does not matter what B is, the first block gets executed. if A is false, none of the two conditions is true, so none of the two blocks are executed. What you can do here is switch orders:

if(A && B)
{
    // ... bar
}
else if(A)
{
    // ... foo
}

(the idea is to test the most restrictive condition first, and only if it fails, test the less restrictive condition).

However, in your case the whole logic is unnecessary complicated, and your code contains too many duplicates. The four times repetition of

  query.AppendLine("select * from K2_NQISQI with (nolock) where ...")

is a blatant code smell. I suggest to solve this along the lines of:

 var conditions = new List<string>();
 if (nqiSqiEntity.Month != string.Empty)
     conditions.Add("MONTH = '" + nqiSqiEntity.Month+"'");
 if(nqiSqiEntity.Tech != string.Empty)
     conditions.Add("TECH = '" + nqiSqiEntity.Tech+"'");
 if(nqiSqiEntity.Circle != string.Empty)
     conditions.Add("CIRCLE = '" + nqiSqiEntity.Circle + "'");
 if(conditions.Count>0)
 {
      string finalCondition= "select * from K2_NQISQI with (nolock) WHERE " + 
                            string.Join(" and ", conditions.ToArray())
                            + " order by id asc";
      query.AppendLine(finalCondition);
 }

beware, this is "air code", and it does not deal with the fact that in your question nqiSqiEntity.Month is always required for creating the condition.

0

Do you see that this part is repeated every time?

"select * from K2_NQISQI with (nolock) where MONTH = '"  + nqiSqiEntity.Month 

Do you see that for the other columns you just want to AND them to the core SELECT above?

Do you see that if nqiSqiEntity.Month is string.Empty that you can't build a valid query?

Then the code would look like this:

  if (nqiSqiEntity.Month != string.Empty)
  {
      query.AppendLine("select * from K2_NQISQI with (nolock) where MONTH = '" + nqiSqiEntity.Month + "'");

      if (nqiSqiEntity.Tech != string.Empty)
      {
          query.AppendLine("' and TECH = '" + nqiSqiEntity.Tech + "'");
      }

      if (nqiSqiEntity.Circle != string.Empty)
      {
          query.AppendLine("' and CIRCLE = '" + nqiSqiEntity.Circle ");
      }

      query.AppendLine( "'  order by id asc");
  }

Below is a better way to write the appends. It avoids all the double-quote, single-quote, "+", and difficulty and error-prone string concatination.

  if (nqiSqiEntity.Month != string.Empty)
  {
      query.AppendFormat("select * from K2_NQISQI with (nolock) where MONTH = '{0}'",nqiSqiEntity.Month);
      if (nqiSqiEntity.Tech != string.Empty)
      {
          query.AppendFormat("' and TECH = '{0}'", nqiSqiEntity.Tech);
      }

      if (nqiSqiEntity.Circle != string.Empty)
      {
          query.AppendFormat("' and CIRCLE = '{0}'", nqiSqiEntity.Circle);
      }

      query.AppendLine( " order by id asc");
  }

Oh, I think you want the query to be flexible for all 3 columns...

  query.AppendLine("select * from K2_NQISQI with (nolock)");

  // if at least 1 of the columns is not empty add a where clause
  if (! (nqiSqiEntity.Month != string.Empty &&
      nqiSqiEntity.Tech != string.Empty  &&
      nqiSqiEntity.Circle != string.Empty)) {

      query.AppendLine (" where ");  // we are going to add on something

      if (nqiSqiEntity.Month != string.Empty)
      {
          query.AppendFormat(" MONTH = '{0}'",nqiSqiEntity.Month);
      }

      if (nqiSqiEntity.Tech != string.Empty)
      {
          query.AppendFormat("' and TECH = '{0}'", nqiSqiEntity.Tech);
      }

      if (nqiSqiEntity.Circle != string.Empty)
      {
          query.AppendFormat("' and CIRCLE = '{0}'", nqiSqiEntity.Circle);
      }
  }

  query.AppendLine( " order by id asc");

This is still messed up. So maybe the below will be less confusing, but still, lots of ifing going on!

Final Answer

  query.AppendLine("select * from K2_NQISQI with (nolock)");

  // if at least 1 of the columns is not empty add a where clause
  if (! (nqiSqiEntity.Month != string.Empty &&
      nqiSqiEntity.Tech != string.Empty  &&
      nqiSqiEntity.Circle != string.Empty)) {

      query.AppendLine (" where ");  // we are going to add on something

      if (nqiSqiEntity.Month != string.Empty)
      {
          query.AppendFormat(" MONTH = '{0}'",nqiSqiEntity.Month);

          // will there be any "and"ing ?
          if (nqiSqiEntity.Tech != string.Empty || nqiSqiEntity.Circle != string.Empty) 
              query.AppendLine(" and " );
      }

      if (nqiSqiEntity.Tech != string.Empty)
      {
          query.AppendFormat(" TECH = '{0}'", nqiSqiEntity.Tech);

          if (nqiSqiEntity.Circle != string.Empty)
              query.AppendLine(" and " );
      }

      if (nqiSqiEntity.Circle != string.Empty)
      {
          query.AppendFormat(" CIRCLE = '{0}'", nqiSqiEntity.Circle);
      }
  }

  query.AppendLine( " order by id asc");
  • ITS WORKING SIR bUT THERE IS A TWIST IN MY CONDITION.sometime i am getting nqiSqiEntity.Circle = "Select" ,if this include it should not satisfy the Circle Condition.I wrote this code if (nqiSqiEntity.Circle != string.Empty || nqiSqiEntity.Circle !="Select") dont read the Circle Value ,But its reading the Circle Value .Please Guide me Where I am getting Wrong. Thanks In Advance Harshal. – HPR Jul 18 '14 at 14:40
  • HPR, I've been massively re-editing since your comment above. Please look to my "Final Answer". Doc Brown gave a good answer, far less conditional switching making it cleaner and easier to understand. – radarbob Jul 18 '14 at 15:05
  • No I dont need the Code Which Doc Brown has wrote .The Code Which radarbob wrote is working only the fact is if i get nqiSqiEntity.Circle ="Select" then i dont want to execute that if condition .Please help me i Dont want to get confuse anymore..Thanks in Advance Harshal – HPR Jul 18 '14 at 15:13
  • 1
    HPR, You'll have to add the necessary code yourself. You said precisely what you want in words, just convert that into a C# statement - or add that bit to the appropriate existing if statement. – radarbob Jul 18 '14 at 15:17
  • @HPR: what exactly is wrong with the code I suggested to solve your problem? I am pretty sure my solution is shorter, simpler, and without any twisted conditionals. – Doc Brown Jul 18 '14 at 19:31

Not the answer you're looking for? Browse other questions tagged or ask your own question.