tupled list not working for all records in database c#

Question

I have some code that is updating the records in the database on basis of some calculation but unexpectedly it is updating first 4 records in the table, then updating 7th record in the table and more amazingly none of other records are being updated conforming the similar situation as of updated records as well. Here is the code.

 public class CS_Temp
    {
        static void Main(string[] args)
        {

            Program p = new Program();

            var lines = System.IO.File.ReadLines(@"C:\Users\Malik\Desktop\research_fields.txt");

            var dd = new List<Tuple<string, double, string>>();

            try
            {
                SqlConnection con = new SqlConnection("Data Source=KHIZER;Initial Catalog=subset_aminer;Integrated Security=True");

                con.Open();

                SqlCommand query = con.CreateCommand();
                query.CommandText = "select p_abstract from sub_aminer_paper where id between 1 and 500 and DATALENGTH(p_abstract) != 0";

                SqlDataReader reader = query.ExecuteReader();

                string summary = null;

                while (reader.Read())
                {
                    summary = reader["p_abstract"].ToString();
                    dd.AddRange(lines.Select(line => Tuple.Create(line, p.calculate_CS(line, summary), summary)));

                    var top_value = dd.OrderByDescending(x => x.Item2).FirstOrDefault();

                    if (top_value != null && top_value.Item2 > 0)
                    {
                        var abstrct = top_value.Item3.ToString();
                        var r_field = top_value.Item1.ToString();

                        write_To_Database(abstrct, r_field);
                    }
                    else
                    {
                        reader.Close();
                    }

                }
                reader.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception: " + e.Message);
            }
            finally
            {
                Console.WriteLine("Executing finally block.");

            }
        }


        public static void write_To_Database(string document, string research_field)
        {
            int result = 0; 
            try
             {
                 string connection = "Data Source=KHIZER;Initial Catalog=subset_aminer;Integrated Security=True;MultipleActiveResultSets=True;";

                 using (SqlConnection con = new SqlConnection(connection))
                 {
                     con.Open();

                     string query = "select id from sub_aminer_paper where pid between 1 and 500 and DATALENGTH(p_abstract) != 0 and p_abstract LIKE @p_abstract";
                     using (SqlCommand cmd = new SqlCommand(query, con))
                     {
                         string st = document;
                         cmd.Parameters.AddWithValue("@p_abstract", st);

                         int id = 0;
                         using (SqlDataReader reader = cmd.ExecuteReader())
                         {
                             while (reader.Read())
                             {
                                 id = reader.GetInt32(0);
                             }

                             reader.Close();

                             string update_query = "update sub_aminer_paper set research_area = @research_area where id = @id";
                             using (SqlCommand cmd_update = new SqlCommand(update_query, con))
                             {
                                 int identity = id;
                                 string r_field = research_field;

                                 cmd_update.Parameters.AddWithValue("@id", identity);
                                 cmd_update.Parameters.AddWithValue("@research_area", r_field);
                                 //cmd_update.CommandTimeout = 20;
                                 result = cmd_update.ExecuteNonQuery();
                             }
                         }
                     }
                     con.Close();
                 }  
             }
             catch (Exception e)
             {
               Console.WriteLine("Exception: " + e.Message);
             }
        }
    }

The problem is in these two lines in while loop i.e.

dd.AddRange(lines.Select(line => Tuple.Create(line, p.calculate_CS(line, summary), summary)));

var top_value = dd.OrderByDescending(x => x.Item2).FirstOrDefault();

I have checked while debugging that it doesn't update the top_value properly for next item that is 5th, 6th record, it takes top_value same as of 4th record repeatedly and similarly it takes top_value same as 7th record for 8th, 9th, 10th and so on, no update occurs. Please help and suggest the proper changes. Thanks

Answer 1

You do something very strange. Don't do this. It is very bad idea - open dbdatareader, then open another two, do some work, insert some records etc, then close all readers. Instead of that, you should use one of two approach:

1. post your data to stored procedure and let this procedure do all work on sql server (so almost all this code should be written on SQL)

2. get data from sql (not open reader, but get datatable), close connection, calculate what you want in .net code and insert calculated rows to database.