ananth reddy ananth reddy - 1 month ago 7
SQL Question

Searching database doesn't return duplicates

In c# i have program through which we can add a contact (name and phone number) to database and also search for a particular contact. But while searching the command doesn't return duplicate rows. Example: if there are two or multiple contacts with same name only one is returned. Why am I not seeing all the duplicates. Here is the code that I am using to search

using (SqlConnection con1 = new SqlConnection(str))

{
con1.Open();
using (SqlCommand query1 = new SqlCommand("SELECT * FROM ContactBook WHERE Name=@Name", con1))
{
query1.Parameters.Add("Name", SqlDbType.NVarChar).Value = SearchbyName.Name;
using (SqlDataReader dr = query1.ExecuteReader())
{
if (dr.Read())
{
Console.WriteLine(dr[0] + " " + dr[1]);
}
else
{
Console.WriteLine("No contact found with name {0}", SearchbyName.Name);
}
}
}
}

Answer

if(dr.Read()) will only read one time and so you are only getting one time. dr.Read() is used to read data from data reader.

using (SqlConnection con1 = new SqlConnection(str))

{
 con1.Open();
 using (SqlCommand query1 = new SqlCommand("SELECT * FROM ContactBook WHERE Name=@Name", con1))
     {
       query1.Parameters.Add("Name", SqlDbType.NVarChar).Value = SearchbyName.Name;
       using (SqlDataReader dr = query1.ExecuteReader())
        {
          while(dr.Read())
          {
            Console.WriteLine(dr[0] + " " + dr[1]);
          }
       }
     } 
 }