//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);
}