Check if query result is null in c#

Question

Hi i am trying to create a validation that checks if its an Unsuccessful SQL Query - if no results are returned, it should catch this error and display a message on screen 'No record found for Client ID: [number]'

The query i currently have fetching the information is below.

protected void ClientSearchBtn_Click(object sender, EventArgs e)
        {
            //string ClientID = ClientIDTxt.Text;

            //Database connection. Calls from web.config.
            string MyConnectionString = ConfigurationManager.ConnectionStrings
                    ["RCADSCONNECTION"].ConnectionString;

            //SQL Connection
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = MyConnectionString;
            //myConnection.Open();

            //SQL string
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT CN.ClientID, CI.NNN, CN.GivenName1, CN.Surname, CI.DateOfBirth, CI.Gender FROM [RioOds].dbo.ClientIndex CI LEFT JOIN [RioOds].[dbo].[ClientName] CN ON CN.ClientID = CI.ClientID AND CN.AliasType = '1' AND CN.EndDate IS NULL WHERE CN.ClientID = @clientid", myConnection);
                cmd.Parameters.Add("@clientid", SqlDbType.Int).Value = ClientIDTxt.Text;
                myConnection.Open();

                var reader = cmd.ExecuteReader();
                StringBuilder sb = new StringBuilder();

                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        if (i != 0)
                        {
                            sb.Append(" | ");
                        }
                        sb.Append(reader[i].ToString());
                    }
                    sb.AppendLine();

                    ClientIDCell.Text = reader[0].ToString();
                    NNNCell.Text = reader[1].ToString();
                    FirstNameCell.Text = reader[2].ToString();
                    SurnameCell.Text = reader[3].ToString();
                    DobCell.Text = reader[4].ToString();
                    GenderCell.Text = reader[5].ToString();
                }


                //Show the results table
                queryResultsTable.Visible = true;

                ResultsLabel.Text = sb.ToString();

                submitButton.Enabled = true;
                resultsButton.Enabled = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                myConnection.Close();
            }

            myConnection.Close();
        }

I am unsure how to go by doing this. I do understand itll be an if statement but unsure how to compare an sql query return to being null.

Answer 1

reader.Read() returns true if there are more rows to read. First time only if it is false that means reader has no data.

         if(!reader.Read())
         //Your message 
         else
         {
            do
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    if (i != 0)
                    {
                        sb.Append(" | ");
                    }
                    sb.Append(reader[i].ToString());
                }
                sb.AppendLine();

                ClientIDCell.Text = reader[0].ToString();
                NNNCell.Text = reader[1].ToString();
                FirstNameCell.Text = reader[2].ToString();
                SurnameCell.Text = reader[3].ToString();
                DobCell.Text = reader[4].ToString();
                GenderCell.Text = reader[5].ToString();
            } while (reader.Read());
        }

Another option is to check the property HasRows on reader. It's true when there is data in it.

if(!reader.HasRows)
  //Your error message goes from here
else
 //Do your stuff