Check if user ID exist in SQL database

Question

I'm a beginner programmer and I'm writing a C# application that makes a query to a database. However I am wondering how could I check that an ID exists (ID is entered by user in the console application) and if it doesn't, display a message.

Here's my code:

Console.WriteLine("enter ID");

try
{
    var province_id = Convert.ToInt32(Console.ReadLine());

    var aquery2 = from test in context.BusinessEntityAddress
                  where test.Address.StateProvinceID == province_id
                  group test.BusinessEntity.Person.LastName by new { test.BusinessEntityID, test.BusinessEntity.Person.LastName, test.BusinessEntity.Person.FirstName } 
                        into balk
                  select new {
                               ...
                             };

Didn't paste the whole code but this is the part my question is about. At the line

where test.Address.StateProvinceID == userid

I would like to check if that ID exist in the database, and if it doesn't, display a message. I don't know how to do that.

Note that all the code is already in a try{}catch{} because I also need to ensure that the user input is an integer.

Thank you

well here my variable name are not that good.It compare the userid to a province ID.StateProvinceID contains all the province ID and I check if the user input(userid)== a ProvinceID(in StateProvinceID) — julian, May 09 '16 at 22:04

Xiaoy312 · Answer 1 · 2016-05-10T01:11:44.883

1

You shouldn't use try-catch for a classic user error, like number parsing. int.TryParse() is made for this :

// get rid of the try-catch, you wont need it anymore
int userid;
var input = Console.ReadLine();
if (!int.TryParse(input, out userID))
{
    Console.WriteLine("Invalid input : '{0}' is not a number", input);
    return;
}

var aquery2 = from test in context.BusinessEntityAddress
              where test.Address.StateProvinceID == userid
              group test.BusinessEntity.Person.LastName by new { test.BusinessEntityID, test.BusinessEntity.Person.LastName, test.BusinessEntity.Person.FirstName } into balk
              select new
              {
                  /* ... */
              };
if (!aquery2.Any())
{
    // not found... display a message
}
else
{
    // found... do stuffs
}

edited May 10 '16 at 01:11

answered May 09 '16 at 22:04

Xiaoy312

14,292
1
32
44

I edited my question with some more details.that said,I tried your code but the if (aquery2.Any()) seem to not work,it nevers meet that condition(if I enter a non existing ID,it just come back to the menu).however my try catch statement seem to work(if I enter something else than integers,it displays a message). – julian May 09 '16 at 22:33
@julian I forgot the negation `!`, sorry. – Xiaoy312 May 09 '16 at 22:38
still experiencing the same problem,it displays the try catch error message instead of the if (!aquery2.Any()) error message – julian May 09 '16 at 22:45
@Xiaoy312 you are trying to create something with the user data even if the user doesn't exist, so it won't ever enter in `if (!aquery2.Any())` because it will raise an exception if the user doesn't exist – wajiro_91 May 09 '16 at 22:50
@flechilla The only exception that are likely to raise here would be a `FormatException` due to _PEBKAC_... That `try catch` for input... – Xiaoy312 May 10 '16 at 01:02
@Xiaoy312 it's don't that I care, but I believe that when a user improves his answer with fragments of other answers, should be pointed out after the improvement. – wajiro_91 May 10 '16 at 02:35

score 1 · Answer 2 · answered May 09 '16 at 22:06

It seems like you're trying to do a lot more then just search for a user ID in your question? You seem to be saying StateProvinceId is a user Id? In which case a simple test like this should suffice:

if (!context.Addresses.Any(a => a.StateProvinceID == userid))
{
   Console.WriteLine("User doesn't exist");
}

Although, it would seem more logical to look in a users table. EG, context.Users. Which would hence question why you are doing a group by (which shouldn't be needed).

You will need to add each data object to your context, but if you could elaborate more on what exactly isn't working, we can help more.

I edited my question with some more details.didnt find a solution yet(explanation to other user also applies to your answer).thank you — julian, May 09 '16 at 22:34

wajiro_91 · Answer 3 · 2016-05-09T23:26:48.320

1

You don't need to run the code inside a try for that, instead first you have to check for the existence of the user:

int number;

//check if the userId is an integer
if(!int.TryParse(userId, out number)){
    Console.WriteLine("Please enter a valid interger!!");
    return;
}  

var beAddress = context.BusinessEntityAddress;
//check for the userId exist in the DB
var flag = beAddress.Any(a => a.Address.StateProvinceID == number);

if(flag){
   //do something if the user exist
}
else{
   //do something else if the user doesn't exist
}

For checking if the string is a valid integer you should use int.TryParse("8", NumberStyles.Integer, System.Globalization.NumberFormatInfo.InvariantInfo, out number);, this way you are not just checking if the string is a number but if is an integer.

edited May 09 '16 at 23:26

answered May 09 '16 at 22:11

wajiro_91

170
1
9

I forgot to mention that my whole code is in a try{ } block because I also need to make sure the user enter integers only.that said,your code seem to work(no error),but it doesnt work.If I enter a non-existing ID,it just come back to the menu without saying the message.My try-catch works though,it display a message if user enter something else than integers. – julian May 09 '16 at 22:30
Then you have to check if the user enter an integer. What I mean is that if you can check for possible errors instead of using a `try-catch` statement, you should do it. – wajiro_91 May 09 '16 at 22:37
It should work now. Read this http://stackoverflow.com/questions/1722964/when-to-use-try-catch-blocks – wajiro_91 May 09 '16 at 22:43
the if (!int.TryParse(provinceid, NumberStyles.Integer, new NumberFormatInfo(), out number)) line return me an error "Error 1 The best overloaded method match for 'int.TryParse(string, System.Globalization.NumberStyles, System.IFormatProvider, out int)' has some invalid arguments" – julian May 09 '16 at 23:04
Check it out now. Anyway refer to http://stackoverflow.com/questions/894263/how-to-identify-if-a-string-is-a-number to know more about it. – wajiro_91 May 09 '16 at 23:14
I was using the overload of `int.TryParse` because it might happens that the typed string is a number but not an interger, so is better to check if the number us an integer. Dive more about the error with the overload because that is the one that u should use – wajiro_91 May 09 '16 at 23:16
my input is converted to an integer automatically so its not a string – julian May 09 '16 at 23:27

Kay Lee · Answer 4 · 2016-05-10T00:32:21.937

This is the code which I'm using. I don't know about console application but know little bit about C# and SQL. I'm not sure I understood clearly but just hope this might help you. Thank you.

bool idfound; (declare in the field of a Class)

private void buttonIDcheck_Click(object sender, RoutedEventArgs e)

(a Event which has to be created by Visual Studio, not manually)

{
    SqlConnection Conn = new SqlConnection();
    Conn.ConnectionString = yourConnectionString;
    Conn.Open();

    SqlCommand check_idexistcomm = new SqlCommand();
    check_idexistcomm.Connection = Conn;
    check_idexistcomm.CommandText = "SELECT id FROM yourtable";

    var check_idexistda = new SqlDataAdapter(check_idexistcomm);

    SqlDataReader check_idexistreader = check_idexistcomm.ExecuteReader();
    while (check_idexistreader.Read())
    {
        if (check_idexistreader["id"].ToString()== text value inputed by user here)
        {   
          idfound = true;

          break;
        }
    }
    check_idexistreader.Close();
    Conn.Close();

    if (idfound=true)
    {
        your code here to accept the user as authorized
    }
    else
    {
        MessageBox.Show("Sorry, you're not authorized");
    }
}

Check if user ID exist in SQL database

4 Answers4