Problem with data reader

Question

  string Path = TextBox3.Text;
        // initialize the Excel Application class
        Excel.ApplicationClass app = new ApplicationClass();
        // create the workbook object by opening the excel file.
        Excel.Workbook workBook = app.Workbooks.Open(Path,
                                                     0,
                                                     true,
                                                     5,
                                                     "",
                                                     "",
                                                     true,
                                                     Excel.XlPlatform.xlWindows,
                                                     "\t",
                                                     false,
                                                     false,
                                                     0,
                                                     true,
                                                     1,
                                                     0);
        // get the active worksheet using sheet name or active sheet
        Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
        int index = 2;
        // This row,column index should be changed as per your need.
        // i.e. which cell in the excel you are interesting to read.
        object rowIndex = (object)index;

        object colIndex1 = 1;
        object colIndex2 = 2;
        SqlDataReader reader;
        try
        {
            SqlConnection sqlConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Examination_Cell;Integrated Security=True");
            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            string x = TextBox1.Text;
            string y = x.Substring(1, 1);
            string z = x.Substring(0, 1);


            TextBox4.Text = ("Roll No. \t Marks\n");
            string RollNo = "";
            string Marks = "";
            while (((Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2 != null)
            {

                RollNo = ((Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2.ToString();
                Marks = ((Excel.Range)workSheet.Cells[rowIndex, colIndex2]).Value2.ToString();
                TextBox4.Text += (RollNo + "     \t     " + Marks + "\n");

                index++;
                rowIndex = index;

                sqlCommand.CommandText = "select StudentID from Student where APID=" + int.Parse(y)+ "and Semester=" + int.Parse(z) + "and Roll_No=" + int.Parse(RollNo);
                reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    // Console.WriteLine(reader["Group"]);
                    int a = (int)reader["StudentID"];
                    SqlCommand cmd2 = new SqlCommand();
                    cmd2.Connection = sqlConnection;
                    cmd2.CommandText = "insert into Result values('" + TextBox1.Text + "'," + int.Parse(TextBox2.Text) + "," + a + "," + RollNo + "," + Marks + ")";
                    cmd2.ExecuteNonQuery();

                }

            }
            //Console.ReadLine();



        sqlConnection.Close();
        }
        catch (Exception ex)
        {
            app.Quit();
            Console.WriteLine(ex.Message);
            Console.ReadLine();
        }
    }

Here, when I place a breakpoint at "while(reader.Read())", the while loop is entered only once and after that it directly proceeds to "catch" block. The program is actually about fetching data from an excel sheet and StudentID from student table and then insert the data into another table "Result". The data gets fetched from the excel sheet as well as the Student table, but it is not inserted into the Result table. One more thing, I've not given any relationship between Student and Result table. So, is it ok or the relationship needs to be given? Thanks in advance.........

Answer 1

I don't see any call to Reader.Read() in your code sample.

Also, I would strongly recommend using the best practice for ADO.NET - wrap your connection, command and data readers into using() { ... } blocks to automatically free them when no longer needed. Also, you ought to use parameters instead of stringing together your SQL select statement.

So your code should be something like:

string query = "SELECT StudentID FROM Student WHERE APID = @APID AND Semester  = @Semester AND Roll_No = @RollNo";

using(SqlConnection _con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Examination_Cell;Integrated Security=True"))
using(SqlCommand _cmd = new SqlCommand(query, _con))
{
   _cmd.Parameters.Add("@APID", SqlDbType.Int).Value = ......;
   _cmd.Parameters.Add("@Semester", SqlDbType.Int).Value = ......;
   _cmd.Parameters.Add("@RollNo", SqlDbType.Int).Value = ......;

   _con.Open();
   using(SqlDataReader reader = _cmd.ExecuteReader())
   {
       while (reader.Read())
       {
          // do something
       }
   }

   _con.Close();
}