Esempio n. 1
0
        public String update_marks(iMarks temp)
        {
            string query = "select MarksDistribution.TotalMarks from MarksDistribution where MarksDistribution.MDID = " + temp.MDID.ToString(); bool all_checked = true;
            string connectionString = ConfigurationManager.AppSettings["SqlDBConn"].ToString();

            foreach (var data in temp.SMs)
            {
                query = " select CourseEnrollment.StudentID from MarksRecord, MarksDistribution, FacultySections, CourseEnrollment where MarksRecord.MDID = MarksDistribution.MDID and MarksDistribution.FSID = FacultySections.FSID and FacultySections.FSID = CourseEnrollment.FSID and MarksRecord.StudentID = CourseEnrollment.StudentID and" +
                        " MarksDistribution.TotalMarks <= " + data.ObtainedMarks.ToString() + " and MarksDistribution.MDID = " + temp.MDID.ToString() + " and CourseEnrollment.StudentID = " + data.StudentID.ToString();
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    bool       any_record = false;
                    SqlCommand command    = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value");
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            any_record = true;
                        }
                        if (any_record)
                        {
                            all_checked = false;
                            break;
                        }
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
            }
            if (all_checked)
            {
                foreach (var data in temp.SMs)
                {
                    query = "update MarksRecord set ObtainedMarks = " + data.ObtainedMarks.ToString() + " where MarksRecord.MDID =" + temp.MDID.ToString() + " and MarksRecord.StudentID=" + data.StudentID.ToString();
                    SqlConnection connection = new SqlConnection(connectionString);
                    connection.Open();
                    SqlCommand cmd          = new SqlCommand(query, connection);
                    int        return_value = cmd.ExecuteNonQuery();
                    connection.Close();
                }
                return("Success");
            }
            else
            {
                return("Failed");
            }
        }
Esempio n. 2
0
        //Given list of MDID and student marks uploads it to the database
        public String insert_marks(iMarks temp)
        {
            string query= "select MarksDistribution.TotalMarks from MarksDistribution where MarksDistribution.MDID = "+temp.MDID.ToString();bool all_checked=true;
            bool no_repeat=true;
            string connectionString = ConfigurationManager.AppSettings["SqlDBConn"].ToString();
            double total_marks = 0;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);
                command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value");
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        total_marks = reader.GetDouble(reader.GetOrdinal("TotalMarks"));
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
            //check if MDID not already inserted then use update request
            query = " select distinct(MarksRecord.MDID) from MarksRecord where MarksRecord.MDID = "+temp.MDID.ToString();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                /*bool any_record = true;*/
                SqlCommand command = new SqlCommand(query, connection);
                command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value");
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        no_repeat = false;
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
            if(no_repeat== false)
            {
                return "Please use update query cause record already exists";
            }
            //check if their marks < total and student exist in course
            foreach (var data in temp.SMs)
            {
                /*
                query= " select CourseEnrollment.StudentID from MarksRecord, MarksDistribution, FacultySections, CourseEnrollment where MarksRecord.MDID = MarksDistribution.MDID and MarksDistribution.FSID = FacultySections.FSID and FacultySections.FSID = CourseEnrollment.FSID and MarksRecord.StudentID = CourseEnrollment.StudentID and"+
                    " MarksDistribution.TotalMarks <= "+data.ObtainedMarks.ToString()+" and MarksDistribution.MDID = "+temp.MDID.ToString()+" and CourseEnrollment.StudentID = "+data.StudentID.ToString();
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    bool any_record = false;
                    SqlCommand command = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value");
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            any_record = true;
                        }
                        if(any_record)
                        {
                            all_checked = false;
                            break;
                        }
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
                */
                if ((data.ObtainedMarks > total_marks) || (data.ObtainedMarks < 0 )) return "One or more of the Students Marks are greater than total marks";
            }

            if (all_checked)
            {
                foreach (var data in temp.SMs)
                {
                    query = "insert into MarksRecord values (" + data.ObtainedMarks.ToString() + "," + temp.MDID.ToString() + "," + data.StudentID.ToString() + ")";
                    SqlConnection connection = new SqlConnection(connectionString);
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(query, connection);
                    int return_value = cmd.ExecuteNonQuery();
                    connection.Close();
                }
                return "Success";
            }
            else return "Failed";
        }
Esempio n. 3
0
        public String update_marks(iMarks temp)
        {
            string query = "select MarksDistribution.TotalMarks from MarksDistribution where MarksDistribution.MDID = " + temp.MDID.ToString(); bool all_checked = true;
            string connectionString = ConfigurationManager.AppSettings["SqlDBConn"].ToString();
            double total_marks      = 0;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);
                command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value");
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        total_marks = reader.GetDouble(reader.GetOrdinal("TotalMarks"));
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
            foreach (var data in temp.SMs)
            {
                /*
                 * query = " select CourseEnrollment.StudentID from MarksRecord, MarksDistribution, FacultySections, CourseEnrollment where MarksRecord.MDID = MarksDistribution.MDID and MarksDistribution.FSID = FacultySections.FSID and FacultySections.FSID = CourseEnrollment.FSID and MarksRecord.StudentID = CourseEnrollment.StudentID and" +
                 *  " MarksDistribution.TotalMarks <= " + data.ObtainedMarks.ToString() + " and MarksDistribution.MDID = " + temp.MDID.ToString() + " and CourseEnrollment.StudentID = " + data.StudentID.ToString();
                 * using (SqlConnection connection = new SqlConnection(connectionString))
                 * {
                 *  bool any_record = false;
                 *  SqlCommand command = new SqlCommand(query, connection);
                 *  command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value");
                 *  connection.Open();
                 *  SqlDataReader reader = command.ExecuteReader();
                 *  try
                 *  {
                 *      while (reader.Read())
                 *      {
                 *          any_record = true;
                 *      }
                 *      if (any_record)
                 *      {
                 *          all_checked = false;
                 *          break;
                 *      }
                 *  }
                 *  finally
                 *  {
                 *      reader.Close();
                 *  }
                 * }
                 */
                if ((data.ObtainedMarks > total_marks) || (data.ObtainedMarks < 0))
                {
                    return("One or more of the Students Marks are greater than total marks");
                }
            }
            if (all_checked)
            {
                foreach (var data in temp.SMs)
                {
                    query = "update MarksRecord set ObtainedMarks = " + data.ObtainedMarks.ToString() + " where MarksRecord.MDID =" + temp.MDID.ToString() + " and MarksRecord.StudentID=" + data.StudentID.ToString();
                    SqlConnection connection = new SqlConnection(connectionString);
                    connection.Open();
                    SqlCommand cmd          = new SqlCommand(query, connection);
                    int        return_value = cmd.ExecuteNonQuery();
                    connection.Close();
                }
                return("Success");
            }
            else
            {
                return("Failed");
            }
        }