在C#中，如何从表中读取数据，并在经过一些计算后将这些数据保存到另一个表中

Question

我是C#中的一个新成员。我正在尝试从名为"tblAddResult"的表中读取数据。在这个表中，我有一些列，我想使tblAddResult的平均值为3列，并将其保存到一个名为tblResult的新表中。

其他一些计算也想在那里进行，比如取tblAddResult列数据的50%。

我是这样做的（代码放在下面），但我收到了一个错误"当阅读器关闭时，调用read的尝试无效"。我甚至不知道这是否是正确的方式，如果没有人能帮助我写东西或对这种方式提出任何建议的话。对于这个问题我真的很无奈。

     private void button1_Click(object sender, EventArgs e)
       {
        con.Open();
        cmd = new SqlCommand("SELECT   tblAddResult.* FROM  tblAddResult", con);
        SqlDataReader sdr = cmd.ExecuteReader();
        while (sdr.Read())
        {
            String subject = sdr.GetString(2);
            int january = sdr.GetInt32(3);
            int february = sdr.GetInt32(4);
            int march = sdr.GetInt32(5);
            int average = (january + february + march) / 3;
            int average40= average*40/(100);
            int marks = sdr.GetInt32(6);
            int marks50 = marks * 50 / 100;
            int WorkingDay = sdr.GetInt32(7);
            int Attandence = sdr.GetInt32(8);
            int Attendence10 = Attandence * 10 / 100;
            int totalMarks = average40 + marks50 + Attendence10;
            string grade = "" ;
            if (totalMarks < 51) { grade = "C"; }
            else if (totalMarks < 61) { grade = "B"; }
            else if (totalMarks < 71) { grade = "A-"; }
            else if (totalMarks < 81) { grade = "A"; }
            else if (totalMarks < 91) { grade = "A+"; }
            else if (totalMarks <= 100) { grade = "A++"; }
            con.Close();
            con.Open();
            SqlCommand comnd = new SqlCommand("INSERT INTO tblResult (Subject, [Full Marks], January, February, March, [Average Class Perfomance], [Earned Marks], [Working Day], Attendence, [Cls Attendence(40%)], [Exam Perfomance(50%)], [Attendence(10%)], [Marks(%)], Grade)VALUES  ('"+subject+"','"+"100"+"','"+january+"','"+february+"','"+march+"','"+average+"','"+marks+"','"+WorkingDay+"','"+Attandence+"','"+average40+"','"+marks50+"','"+Attendence10+"','"+totalMarks+"','"+grade+"')",con);
            comnd.ExecuteNonQuery();
            MessageBox.Show("Successfull!");
        }

Answer 1

为了更好的安全性，编辑如下代码。发生错误的原因是DataReader操作只有在"打开连接"状态下才能工作。

        con.Open();
        cmd = new SqlCommand("SELECT tblAddResult.* FROM  tblAddResult", con);
        SqlDataReader sdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//this will close the DataReader along with Connection close.
        while (sdr.Read())
        {
            String subject = sdr.GetString(2);
            int january = sdr.GetInt32(3);
            int february = sdr.GetInt32(4);
            int march = sdr.GetInt32(5);
            int average = (january + february + march) / 3;
            int average40= average*40/(100);
            int marks = sdr.GetInt32(6);
            int marks50 = marks * 50 / 100;
            int WorkingDay = sdr.GetInt32(7);
            int Attandence = sdr.GetInt32(8);
            int Attendence10 = Attandence * 10 / 100;
            int totalMarks = average40 + marks50 + Attendence10;
            string grade = "" ;
            if (totalMarks < 51) { grade = "C"; }
            else if (totalMarks < 61) { grade = "B"; }
            else if (totalMarks < 71) { grade = "A-"; }
            else if (totalMarks < 81) { grade = "A"; }
            else if (totalMarks < 91) { grade = "A+"; }
            else if (totalMarks <= 100) { grade = "A++"; }
            cmd = new SqlCommand("INSERT INTO tblResult (Subject, [Full Marks], January, February, March, [Average Class Perfomance], [Earned Marks], [Working Day], Attendence, [Cls Attendence(40%)], [Exam Perfomance(50%)], [Attendence(10%)], [Marks(%)], Grade)VALUES  ('"+subject+"','"+"100"+"','"+january+"','"+february+"','"+march+"','"+average+"','"+marks+"','"+WorkingDay+"','"+Attandence+"','"+average40+"','"+marks50+"','"+Attendence10+"','"+totalMarks+"','"+grade+"')",con);
            cmd.ExecuteNonQuery();           
        }
        con.Close();
        MessageBox.Show("Successfull!");

编辑：

不要使用DataReader方法，而是使用DataTable，下面给出了一个示例。希望它能有所帮助。

cmd = new SqlCommand("SELECT * FROM  Test_Table1", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            foreach (DataRow sdr in dt.Rows)
            {
                String subject = sdr[0].ToString();
                int january = Convert.ToInt32(sdr[1]);
                int february = Convert.ToInt32(sdr[2]);
                int march = 0;
                int average = (january + february + march) / 3;
                int average40 = average * 40 / (100);
                cmd = new SqlCommand("INSERT INTO Test_Table2 (Test_Col1, Test_Col2) VALUES  ('" + subject + "'," + average40 + ")", con);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }

Answer 2

您收到此错误是因为您在读取器返回/完成之前关闭了连接。删除两行con.Close();和con.Open();，并在读卡器完成后（即while循环之外）clsoe连接。