CODEWITHSUNDEEP
c#sql-serverstored-proceduresado.netsqldatareader
muteb
muteb

Reputation: 75

Why ExecuteReader() only returns the first row?

hope you all well. I tried to get all rows from table and then put them into a list but the ExecuteReader only returns the first row. is it something to do with the list or is there any other techniques? can someone help please!

using (SqlConnection mySqlConnection = new SqlConnection(AppConfiguration.ConnectionString))
        {
            SqlCommand myCommand = new SqlCommand("SelectCity", mySqlConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter prmcountrycode = new SqlParameter("@countrycode", SqlDbType.NVarChar, 50);
            prmcountrycode.Value = countrycode;
            myCommand.Parameters.Add(prmcountrycode);

            mySqlConnection.Open();
            List<string> allcitiesnames = new List<string>();

            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                int count = myReader.FieldCount;
                if (myReader.Read())
                {
                     allcitiesnames.Add(myReader.GetString(myReader.GetOrdinal("CityName")));

                }
                else
                {
                    mySqlConnection.Close();
                    InsertNewCity(countrycode);

                }

                myReader.Close();
            }
            mySqlConnection.Close();
        }

Upvotes: 1

Views: 4311

Answers (3)

Dgan
Dgan

Reputation: 10285

Basically SqlDataReader designed to read the database rows one at a time

Change 

if (myReader.Read())
{
    allcitiesnames.Add(myReader.GetString(myReader.GetOrdinal("CityName")));
}
else
{
    mySqlConnection.Close();
    InsertNewCity(countrycode);

}

to

while(myReader.Read())
{
    if (myReader.Read())
    {
        allcitiesnames.Add(myReader.GetString(myReader.GetOrdinal("CityName")));
    }
    else
    {
        mySqlConnection.Close();
        InsertNewCity(countrycode);
    }
}

Update:

Instead of Two Calls to Database I will suggest you to Change Your procedure as

create procedure SelectCity
    @countrycode nvarchar(50)
as 
begin    
    if exists(select top 1 CountryCode from mytable where CountryCode=@countrycode)
    begin
        select * from mytable 
    end
else
begin
    insert into mytable (CountryCode)
    values (@countryCode)
end
end

So this will select all if exists else insert it into table and Rest of Will be Same by using SqlDataReader with ExecuteReader

Upvotes: 0

itzmebibin
itzmebibin

Reputation: 9439

Try this,

while(myReader.Read())
{
  allcitiesnames.Add(myReader.GetString(myReader.GetOrdinal("CityName")));
}

Upvotes: 4

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62488

If you want all rows directly you have to use SqlDataAdapter and use DataTable fill it with data from database and return the DataTable from it.

SqlDataReader gets rows one by one in seqeuntial manner, see here how to use SqlDataAdapter, Also see this MSDN article

Example:

        // Use DataAdapter to fill DataTable
        DataTable t = new DataTable();

         // 1
        // Open connection
        using (SqlConnection c = new SqlConnection("Connection string here"))
        {
          c.Open();
          // 2
          // Create new DataAdapter
          using (SqlDataAdapter a = new SqlDataAdapter("Query Here", c))
             {
               // 3
               // Fill DataTable with data
               a.Fill(t);

             }
         }

Upvotes: 1

Related Questions