none
Why my code only read one row? RRS feed

  • Question

  • My table have 10 récords and when i use my method only have a "0" that means only read one record because only the first row field is null another fields is not nulls 

    Query 

    string query = "SELECT * FROM Movies";

    Method

    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
    
                            if (string.IsNullOrEmpty(sdr["Code"].ToString()))
                            {
                                C_E = "0";
                                this.Update(C_E);
                                //ViewBag.Message = "cero";
                            }
                            else
                            {
                                C_E = sdr["Code"].ToString();
                                this.Update(C_E);
                                // ViewBag.Message = C_E;
                            }
    
    
                        }

    Tuesday, October 30, 2018 12:04 AM

All replies

  • My table have 10 récords and when i use my method only have a "0" that means only read one record because only the first row field is null another fields is not nulls 

    Query 

    string query = "SELECT * FROM Movies";

    Method

    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
    
                            if (string.IsNullOrEmpty(sdr["Code"].ToString()))
                            {
                                C_E = "0";
                                this.Update(C_E);
                                //ViewBag.Message = "cero";
                            }
                            else
                            {
                                C_E = sdr["Code"].ToString();
                                this.Update(C_E);
                                // ViewBag.Message = C_E;
                            }
    
    
                        }

    Hi Neraks,

    According to your code, you used Sqlcommand.ExecuteReader to obtain a row from query result, The SqlDataReader Object is a stream-based , forward-only, read-only retrieval of query results from the Data Source, which do not update the data it contains.

    But I am not sure what your problem is,  think your sql query code is correct. Like this:

    string str = @"Data Source=(LOCALDB)\MSSQLLOCALDB;Initial Catalog=Testlocaldb;Integrated Security=True";
                string sql = "select * from test7";
                using (SqlConnection con=new SqlConnection(str))
                {
                    con.Open();
                    using (SqlCommand cmd=new SqlCommand(sql,con))
                    {
                        SqlDataReader reader = cmd.ExecuteReader();
                        while(reader.Read())
                        {
                            Console.WriteLine(reader["Firstname"].ToString());
                        }
                    }
                }

    It can read data line by line.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 30, 2018 5:35 AM
  • Your code is reading all the rows. The problem is, I suspect you're looking at C_E. So each time through your loop you're updating a single variable (C_E) and then calling this.Update() with it. So that will get called each time (put a breakpoint on that line to verify). If that method is updating a single value then each time through the loop you'll be stomping over the values from the previous iteration.

    Also note that your if statement isn't technically correct. You are checking for null or empty but ToString won't return null. If sdr["Code"] is null then your app will crash. When working with a data reader you need to handle DBNull.

    var ordinalCode = -1;
    while (sdr.Read())
    {
       //Most reader functions are ordinal based, unfortunately
       if (ordinalCode < 0)
          ordinalCode = sdr.GetOrdinal("Code");
    
       var code = !sdr.IsDBNull(ordinalCode) ? sdr.Field<string>(ordinalCode) : null;
       
       //For each record you're stomping over the previous value here...
       C_E = !String.IsNullOrEmpty(code) ? code : "0";
       this.Update(C_E);
    };
    Note that you'll be doing this enough that I strongly recommend you use some extension methods to clean this code up. I have some extensions defined for DataReader in my personal library that you can take if you want.



    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, October 30, 2018 3:26 PM
    Moderator