Example #1
0
        //Save Courses
        private bool SaveCourses()
        {
            bool             retval   = false;
            OleDbConnection  sqlConn  = new OleDbConnection(DB.getConnectionString);
            OleDbTransaction sqlTrans = null;
            int  CourseID             = 0;
            bool toBeInserted         = false;

            try
            {
                sqlConn.Open();
                sqlTrans = sqlConn.BeginTransaction();
                for (int i = 0; i < CourseView.RowCount; i++)
                {
                    if (Convert.ToBoolean(CourseView.GetRowCellValue(i, "Edited")))
                    {
                        if (string.IsNullOrEmpty(CourseView.GetRowCellValue(i, "ID").ToString()))
                        {
                            CourseID = 0;
                            using (OleDbCommand cmd = new OleDbCommand())
                            {
                                cmd.Connection  = sqlConn;
                                cmd.Transaction = sqlTrans;
                                cmd.CommandText = "INSERT INTO tblAdmCourse(" +
                                                  "Name" +
                                                  ",SortCode" +
                                                  ",Description" +
                                                  ",DateStarted" +
                                                  ",DateEnd" +
                                                  ")VALUES(" +
                                                  "@Name" +
                                                  ",@SortCode" +
                                                  ",@Description" +
                                                  ",@DateStarted" +
                                                  ",@DateEnd" +
                                                  ")";
                                cmd.Parameters.AddWithValue("@Name", CourseView.GetRowCellValue(i, "Name"));
                                cmd.Parameters.AddWithValue("@SortCode", CourseView.GetRowCellValue(i, "SortCode"));
                                cmd.Parameters.AddWithValue("@Description", CourseView.GetRowCellValue(i, "Description"));
                                cmd.Parameters.AddWithValue("@DateStarted", CourseView.GetRowCellValue(i, "DateStarted"));
                                cmd.Parameters.AddWithValue("@DateEnd", CourseView.GetRowCellValue(i, "DateEnd"));
                                toBeInserted    = cmd.ExecuteNonQuery().Equals(1);
                                cmd.CommandText = "SELECT @@IDENTITY";
                                CourseID        = (int)cmd.ExecuteScalar();
                            }
                        }
                        else
                        {
                            CourseID = (int)CourseView.GetRowCellValue(i, "ID");
                            using (OleDbCommand cmd = new OleDbCommand())
                            {
                                cmd.Connection  = sqlConn;
                                cmd.Transaction = sqlTrans;
                                cmd.CommandText = "UPDATE tblAdmCourse " +
                                                  " SET [Name] = @Name" +
                                                  ",[SortCode] = @SortCode" +
                                                  ",[Description] = @Description" +
                                                  ",[DateStarted] = @DateStarted" +
                                                  ",[DateEnd] = @DateEnd" +
                                                  ",[DateUpdated] = Now" +
                                                  " WHERE [ID]=@ID";
                                cmd.Parameters.AddWithValue("@Name", CourseView.GetRowCellValue(i, "Name"));
                                cmd.Parameters.AddWithValue("@SortCode", CourseView.GetRowCellValue(i, "SortCode"));
                                cmd.Parameters.AddWithValue("@Description", CourseView.GetRowCellValue(i, "Description"));
                                cmd.Parameters.AddWithValue("@DateStarted", CourseView.GetRowCellValue(i, "DateStarted"));
                                cmd.Parameters.AddWithValue("@DateEnd", CourseView.GetRowCellValue(i, "DateEnd"));
                                cmd.Parameters.AddWithValue("@ID", CourseView.GetRowCellValue(i, "ID"));
                                toBeInserted = cmd.ExecuteNonQuery().Equals(1);
                            }
                        }

                        //Child Sub View
                        DevExpress.XtraGrid.Views.Grid.GridView childView = (DevExpress.XtraGrid.Views.Grid.GridView)CourseView.GetDetailView(i, CourseView.GetRelationIndex(i, "Course_Series"));
                        for (int subViewRow = 0; subViewRow < childView.RowCount; subViewRow++)
                        {
                            if (Convert.ToBoolean(childView.GetRowCellValue(subViewRow, "Edited")))
                            {
                                if (string.IsNullOrEmpty(childView.GetRowCellValue(subViewRow, "ID").ToString()))
                                {
                                    using (OleDbCommand cmd = new OleDbCommand())
                                    {
                                        cmd.Connection  = sqlConn;
                                        cmd.Transaction = sqlTrans;
                                        cmd.CommandText = "INSERT INTO tblCourseSeries(" +
                                                          "[FKeyCourse]" +
                                                          ",[Name]" +
                                                          ",[Description]" +
                                                          ",[SortCode]" +
                                                          ")VALUES(" +
                                                          "@FKeyCourse" +
                                                          ",@Name" +
                                                          ",@Description" +
                                                          ",@SortCode" +
                                                          ")";
                                        cmd.Parameters.AddWithValue("@FKeyCourse", CourseID);
                                        cmd.Parameters.AddWithValue("@Name", childView.GetRowCellValue(subViewRow, "Name"));
                                        cmd.Parameters.AddWithValue("@Description", childView.GetRowCellValue(subViewRow, "Description"));
                                        cmd.Parameters.AddWithValue("@SortCode", childView.GetRowCellValue(subViewRow, "SortCode"));
                                        toBeInserted = cmd.ExecuteNonQuery().Equals(1);
                                    }
                                }
                                else
                                {
                                    using (OleDbCommand cmd = new OleDbCommand())
                                    {
                                        cmd.Connection  = sqlConn;
                                        cmd.Transaction = sqlTrans;
                                        cmd.CommandText = "UPDATE tblCourseSeries " +
                                                          " SET [Name] = @Name" +
                                                          ",[SortCode] = @SortCode" +
                                                          ",[Description] = @Description" +
                                                          ",[DateUpdated] = (Now())" +
                                                          " WHERE ((([ID])=@ID) AND (([FKeyCourse])=@FKeyCourse))";
                                        cmd.Parameters.AddWithValue("@Name", childView.GetRowCellValue(subViewRow, "Name").ToString());
                                        cmd.Parameters.AddWithValue("@SortCode", childView.GetRowCellValue(subViewRow, "SortCode"));
                                        cmd.Parameters.AddWithValue("@Description", childView.GetRowCellValue(subViewRow, "Description").ToString());
                                        cmd.Parameters.AddWithValue("@ID", childView.GetRowCellValue(subViewRow, "ID"));
                                        cmd.Parameters.AddWithValue("@FKeyCourse", childView.GetRowCellValue(subViewRow, "FKeyCourse"));
                                        toBeInserted = cmd.ExecuteNonQuery().Equals(1);
                                    }
                                }
                            }
                        }
                    }
                }
                if (toBeInserted)
                {
                    sqlTrans.Commit();
                    retval = true;
                }
            }
            catch (Exception ex)
            {
                retval = false;
                sqlTrans.Rollback();
                MessageBox.Show(ex.Message, Application.ProductName);
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
            return(retval);
        }