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.

is if (i != 0) in your loop through the fields returned by reader already doing this? — Alfie Goodacre
– Alfie Goodacre, Commented Apr 6, 2016 at 9:55
@AlfieGoodacre ah, so i would just need to return a messagebox to state if there is no id — user6164871
– user6164871, Commented Apr 6, 2016 at 9:56
reader.HasRows returns false if there are no rows to read from the DataReader. Just check on MSDN — Steve
– Steve, Commented Apr 6, 2016 at 9:58

M.S. · Accepted Answer · 2016-04-06 10:01:43Z

2

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

edited Apr 6, 2016 at 10:01

answered Apr 6, 2016 at 9:56

M.S.

4,4531 gold badge22 silver badges43 bronze badges

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

3 Comments

user6164871 Over a year ago

Could you comment on what this is doing for me please, to get a better understanding.

user6164871 Over a year ago

is there a way of breaking out or returning from if(!reader.HasRows)

M.S. Over a year ago

use return value; to return and to break use break;

Collectives™ on Stack Overflow

Check if query result is null in c#

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related