Checking if int exists in array C#

Question

I don’t know if this is the correct way to be doing this but I'm trying to get all Job Numbers from a database table and checking that what the user inputs is in the database. I’m doing this by sending all data to an array and checking if it exists in there. However I’m sure there will be an easier way. This is the code I have so far:

public class IDNo
{
    public int Col1 { get; set; }
}

private void button3_Click(object sender, EventArgs e)
{
    String check = "SELECT * FROM Job";

    using (SqlConnection con = new SqlConnection(str))
    {
        using (SqlCommand cmd = new SqlCommand(check, con))
        {
            con.Open();
            var listOfId = new List<IDNo>();
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var id = new IDNo();
                    id.Col1 = Convert.ToInt32(reader["JobNo"]);
                    listOfId.Add(id);
                }
            }

            string JN = textBox10.Text;
            int JoNo = Int32.Parse(JN);

            if (JoNo == IDNo)
            {
                MessageBox.Show("No job number found, please try again!");
            }
            else
            {
                DO SOMETHING HERE WHEN CORRECT
            }
        }
    }
}

I would just like some help on how to check if the number the user inputs exists in the array.

Why don't you ask you database if a record with the typed Job number exists or not? They are made for this kind of task not just to hold all your job data — Steve
– Steve, Commented Feb 17, 2016 at 10:05
Why not just ask the database to do the search? (SELECT * FROM Job where JobNo = @JobNo and adding the @JobNo parameter to your command object) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Feb 17, 2016 at 10:05
If you're going to query the database, why don't you just send an SQL query that figures out in one line whether the entry is there? Remember to parameterize the query. — Carlos
– Carlos, Commented Feb 17, 2016 at 10:06

Community · Accepted Answer · 2017-05-23 12:15:34Z

1

It is the database engine that should answer your question, not simply give you back all your job records and force your code through a, possible, very lengthy search of the matching data in your records...

 int JoNo;
 if(!Int32.TryParse(textBox10.Text, out JoNo))
 {
      MessageBox.Show("Not a valid number");
      return;
 }
 String check = @"IF EXISTS( SELECT 1 FROM Job WHERE JobNo=@num) 
                  SELECT 1 ELSE SELECT 0";
 using (SqlConnection con = new SqlConnection(str))
 using (SqlCommand cmd = new SqlCommand(check, con))
 {
     con.Open();
     cmd.Parameters.Add("@num", SqlDbType.Int).Value = JoNo;
     int result = (int)cmd.ExecuteScalar();
     if(result == 0)
          MessageBox.Show("No job number found, please try again!");
     else
          .....
  }

First, you test if the user input is a valid number without throwning exceptions (Int32.TryParse) but just informing your user of the error, then you build an IF EXISTS query because you are just interested to know if the job number exists or not and you don't need to retrieve that value. Finally, the execution is done using ExecuteScalar because you are interested only in getting the single value 1 (for an existing JobNo or 0 for a not exisisting JobNo.

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Feb 17, 2016 at 10:11

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Wilkinson Over a year ago

Oh ok, i under stand now. Thanks, that helped alot and cut back the work alot.

Rahul Tripathi · Accepted Answer · 2016-02-17 10:12:44Z

0

You can use the jobNo which is sent by the user as an input parameter for your search query in database. So you can simply do it using the query:

SqlCommand check = new SqlCommand("SELECT * FROM Job where JobNo = @JobNo" , conn);
check.Parameters.AddWithValue("@JobNo", id.Text);
int exists = (int)check.ExecuteScalar();

if(exists > 0)
{
   //job no exist
}
else
{
   //job no doesn't exist.
}

edited Feb 17, 2016 at 10:12

answered Feb 17, 2016 at 10:08

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

1 Comment

Matt Wilkinson Over a year ago

So how do i do this? like how does it define what @JobNo is? and then how do i make it throw and error back if it doesnt exist?

Collectives™ on Stack Overflow

Checking if int exists in array C#

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related