private List <LessonPlannerModel> TranslateDataTableToLessonPlannersModel(DataTable dataTable)
{
List <LessonPlannerModel> lessonPlannerModels = new List <LessonPlannerModel>();
foreach (DataRow row in dataTable.Rows)
{
LessonPlannerModel lessonPlannerModel = new LessonPlannerModel();
lessonPlannerModel.LessonPlannerID = row["LessonPlannerID"] != DBNull.Value ? Convert.ToInt64(row["LessonPlannerID"].ToString()) : 0;
lessonPlannerModel.GradeID = row["GradeID"] != DBNull.Value ? Convert.ToInt64(row["GradeID"].ToString()) : 0;
lessonPlannerModel.GradeName = row["GradeName"] != DBNull.Value ? Convert.ToString(row["GradeName"]) : string.Empty;
lessonPlannerModel.SubjectID = row["SubjectID"] != DBNull.Value ? Convert.ToInt64(row["SubjectID"].ToString()) : 0;
lessonPlannerModel.SubjectName = row["SubjectName"] != DBNull.Value ? Convert.ToString(row["SubjectName"]) : string.Empty;
lessonPlannerModel.MainTopicID = row["MainTopicID"] != DBNull.Value ? Convert.ToInt64(row["MainTopicID"].ToString()) : 0;
lessonPlannerModel.MainTopicNumber = row["MainTopicNumber"] != DBNull.Value ? Convert.ToString(row["MainTopicNumber"]) : string.Empty;
lessonPlannerModel.TitleMainTopic = row["MainTopicTitle"] != DBNull.Value ? Convert.ToString(row["MainTopicTitle"]) : string.Empty;
lessonPlannerModel.Introduction = row["Introduction"] != DBNull.Value ? Convert.ToString(row["Introduction"]) : string.Empty;
lessonPlannerModel.Objectives = row["Objectives"] != DBNull.Value ? Convert.ToString(row["Objectives"]) : string.Empty;
lessonPlannerModel.Material = row["Material"] != DBNull.Value ? Convert.ToString(row["Material"]) : string.Empty;
lessonPlannerModels.Add(lessonPlannerModel);
}
return(lessonPlannerModels);
}
public LessonPlanner(LessonPlannerModel lessonPlannerModel) ////EDIT
{
InitializeComponent();
_lessonPlannerID = lessonPlannerModel.LessonPlannerID;
FillGradesCombo(lessonPlannerModel.GradeID);
FillSubjectCombo(lessonPlannerModel.SubjectID);
lblLessonPlannerID.Text = Convert.ToString(lessonPlannerModel.LessonPlannerID);
lblMainTopicID.Text = Convert.ToString(lessonPlannerModel.MainTopicID);
}
public List <LessonPlannerModel> GetAllLessonPlannersByGradeIDandSubjectID(long gradeID, long subjectID)
{
List <LessonPlannerModel> lessonPlannerModels = new List <LessonPlannerModel>();
DataTable dataTable = new DataTable();
try
{
SqlConnection conn = new SqlConnection(DbHelper.ConnectionString);
SqlCommand command = new SqlCommand(@"dbo.uspGetAllLessonPlannersByGradeIDandSubjectID", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@GradeID", gradeID);
command.Parameters.AddWithValue("@SubjectID", subjectID);
conn.Open();
//command.Connection = DatabaseContext.Connection;
//command.Transaction = DatabaseContext.Transaction;
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
foreach (DataRow row in dataTable.Rows)
{
LessonPlannerModel lessonPlannerModel = new LessonPlannerModel();
lessonPlannerModel.LessonPlannerID = row["LessonPlannerID"] != DBNull.Value ? Convert.ToInt64(row["LessonPlannerID"].ToString()) : 0;
lessonPlannerModel.GradeID = row["GradeID"] != DBNull.Value ? Convert.ToInt64(row["GradeID"].ToString()) : 0;
lessonPlannerModel.GradeName = row["GradeName"] != DBNull.Value ? Convert.ToString(row["GradeName"]) : string.Empty;
lessonPlannerModel.SubjectID = row["SubjectID"] != DBNull.Value ? Convert.ToInt64(row["SubjectID"].ToString()) : 0;
lessonPlannerModel.SubjectName = row["SubjectName"] != DBNull.Value ? Convert.ToString(row["SubjectName"]) : string.Empty;
lessonPlannerModel.MainTopicID = row["MainTopicID"] != DBNull.Value ? Convert.ToInt64(row["MainTopicID"].ToString()) : 0;
lessonPlannerModel.MainTopicNumber = row["MainTopicNumber"] != DBNull.Value ? Convert.ToString(row["MainTopicNumber"]) : string.Empty;
lessonPlannerModel.TitleMainTopic = row["MainTopicTitle"] != DBNull.Value ? Convert.ToString(row["MainTopicTitle"]) : string.Empty;
lessonPlannerModel.Introduction = row["Introduction"] != DBNull.Value ? Convert.ToString(row["Introduction"]) : string.Empty;
lessonPlannerModel.Objectives = row["Objectives"] != DBNull.Value ? Convert.ToString(row["Objectives"]) : string.Empty;
lessonPlannerModel.Material = row["Material"] != DBNull.Value ? Convert.ToString(row["Material"]) : string.Empty;
lessonPlannerModels.Add(lessonPlannerModel);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dataTable.Clear();
dataTable = null;
}
return(lessonPlannerModels);
}
private void btnSave_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(cmbGrades.Text))
{
MessageBox.Show("Grade can't be blank!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else if (string.IsNullOrEmpty(cmbSubjects.Text))
{
MessageBox.Show("Subject can't be blank!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else if (string.IsNullOrEmpty(txtTitleMainTopic.Text))
{
MessageBox.Show("Title main topic can't be blank!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else if ((subTopicModelsListGlobal == null || subTopicModelsListGlobal.Count == 0) && txtMaterial.Text == string.Empty)
{
MessageBox.Show("Main topic's material can't be blank!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else
{
LessonPlannerModel lessonPlannerModel = new LessonPlannerModel();
lessonPlannerModel.LessonPlannerID = Convert.ToInt32(lblLessonPlannerID.Text);
lessonPlannerModel.MainTopicID = Convert.ToInt32(lblMainTopicID.Text);
lessonPlannerModel.GradeID = Convert.ToInt64(cmbGrades.SelectedValue);
lessonPlannerModel.SubjectID = Convert.ToInt64(cmbSubjects.SelectedValue);
lessonPlannerModel.MainTopicNumber = txtMainTopic.Text;
lessonPlannerModel.TitleMainTopic = txtTitleMainTopic.Text;
lessonPlannerModel.Introduction = txtIntroduction.Text;
lessonPlannerModel.Objectives = txtObjectives.Text;
lessonPlannerModel.Material = txtMaterial.Text;
lessonPlannerModel.CreatedOn = DateTime.Now;
lessonPlannerModel.CreatedBy = 1; // It will be added from users
lessonPlannerModel.ModifiedOn = DateTime.Now;
lessonPlannerModel.ModifiedBy = 1; // It will be added from users
lessonPlannerModel.subTopicModels = subTopicModelsListGlobal;
lessonPlannerModel.gamesModels = gamesModelsListGlobal;
lessonPlannerModel.moviesModels = moviesModelsListGlobal;
lessonPlannerModel.documentariesModels = documentariesModelsListGlobal;
lessonPlannerModel.booksModels = booksModelsListGlobal;
try
{
LessonPlannerRepository lessonPlannerRepository = new LessonPlannerRepository();
long result = lessonPlannerRepository.SaveUpdateLessonPlanner(lessonPlannerModel);
if (result > 0)
{
ShowStatus(true, "ADD");
}
else
{
ShowStatus(false, string.Empty);
}
lessonPlannerModel.subTopicModels = new List <SubTopicModel>();
lessonPlannerModel.gamesModels = new List <GamesModel>();
lessonPlannerModel.moviesModels = new List <MoviesModel>();
lessonPlannerModel.documentariesModels = new List <DocumentariesModel>();
lessonPlannerModel.booksModels = new List <BooksModel>();
subTopicModelsListGlobal = new List <SubTopicModel>();
gamesModelsListGlobal = new List <GamesModel>();
moviesModelsListGlobal = new List <MoviesModel>();
documentariesModelsListGlobal = new List <DocumentariesModel>();
booksModelsListGlobal = new List <BooksModel>();
}
catch (Exception ex)
{
ShowStatus(false, string.Empty);
}
clearSubTopicFields();
}
}
public long SaveUpdateLessonPlanner(LessonPlannerModel lessonPlannerModel)
{
long result = 0;
if (lessonPlannerModel != null)
{
if (lessonPlannerModel.LessonPlannerID == 0) //Add
{
StringBuilder strSql = new StringBuilder();
strSql.Append("DECLARE @SeedValue INT ");
strSql.Append("SET @SeedValue = (SELECT ISNULL(MAX(LessonPlannerID), 0) FROM dbo.LessonPlanner) ");
strSql.Append("DBCC CHECKIDENT('dbo.LessonPlanner',RESEED,@SeedValue)");
strSql.Append("INSERT INTO LessonPlanner(");
strSql.Append("GradeID,Grade,SubjectID,Introduction,Objectives,");
strSql.Append("Material,CreatedOn,CreatedBy,ModifiedOn,ModifiedBy)");
strSql.Append(" VALUES (");
strSql.Append("@GradeID,@Grade,@SubjectID,@Introduction,@Objectives,@Material,");
strSql.Append("@CreatedOn,@CreatedBy,@ModifiedOn,@ModifiedBy)");
strSql.Append(";SELECT SCOPE_IDENTITY()");
SqlParameter[] parameters =
{
new SqlParameter("@GradeID", SqlDbType.BigInt),
new SqlParameter("@Grade", SqlDbType.NVarChar, 300),
new SqlParameter("@SubjectID", SqlDbType.BigInt),
new SqlParameter("@Introduction", SqlDbType.NVarChar, -1),
new SqlParameter("@Objectives", SqlDbType.NVarChar, -1),
new SqlParameter("@Material", SqlDbType.NVarChar, -1),
new SqlParameter("@CreatedOn", SqlDbType.DateTime),
new SqlParameter("@CreatedBy", SqlDbType.Int),
new SqlParameter("@ModifiedOn", SqlDbType.DateTime),
new SqlParameter("@ModifiedBy", SqlDbType.Int)
};
parameters[0].Value = lessonPlannerModel.GradeID;
parameters[1].Value = lessonPlannerModel.GradeName;
parameters[2].Value = lessonPlannerModel.SubjectID;
parameters[3].Value = lessonPlannerModel.Introduction;
parameters[4].Value = lessonPlannerModel.Objectives;
parameters[5].Value = lessonPlannerModel.Material;
parameters[6].Value = lessonPlannerModel.CreatedOn;
parameters[7].Value = lessonPlannerModel.CreatedBy;
parameters[8].Value = lessonPlannerModel.ModifiedOn;
parameters[9].Value = lessonPlannerModel.ModifiedBy;
object obj = DbHelper.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
result = 0;
}
else
{
result = Convert.ToInt64(obj);
}
}
else
{
StringBuilder strSql = new StringBuilder();
strSql.Append("UPDATE LessonPlanner SET ");
strSql.Append("GradeID=@GradeID,Grade=@Grade,SubjectID=@SubjectID,");
strSql.Append("Introduction=@Introduction,Objectives=@Objectives,");
strSql.Append("Material=@Material,ModifiedOn=@ModifiedOn,ModifiedBy=@ModifiedBy");
strSql.Append(" where LessonPlannerID=@LessonPlannerID");
SqlParameter[] parameters =
{
new SqlParameter("@GradeID", SqlDbType.Int),
new SqlParameter("@Grade", SqlDbType.NVarChar, 300),
new SqlParameter("@SubjectID", SqlDbType.Int),
new SqlParameter("@Introduction", SqlDbType.NVarChar, -1),
new SqlParameter("@Objectives", SqlDbType.NVarChar, -1),
new SqlParameter("@Material", SqlDbType.NVarChar, -1),
new SqlParameter("@CreatedOn", SqlDbType.DateTime),
new SqlParameter("@CreatedBy", SqlDbType.Int),
new SqlParameter("@ModifiedOn", SqlDbType.DateTime),
new SqlParameter("@ModifiedBy", SqlDbType.Int),
new SqlParameter("@LessonPlannerID", SqlDbType.BigInt, 4)
};
parameters[0].Value = lessonPlannerModel.GradeID;
parameters[1].Value = lessonPlannerModel.GradeName;
parameters[2].Value = lessonPlannerModel.SubjectID;
parameters[3].Value = lessonPlannerModel.Introduction;
parameters[4].Value = lessonPlannerModel.Objectives;
parameters[5].Value = lessonPlannerModel.Material;
parameters[6].Value = lessonPlannerModel.CreatedOn;
parameters[7].Value = lessonPlannerModel.CreatedBy;
parameters[8].Value = lessonPlannerModel.ModifiedOn;
parameters[9].Value = lessonPlannerModel.ModifiedBy;
parameters[10].Value = lessonPlannerModel.LessonPlannerID;
result = DbHelper.ExecuteSql(strSql.ToString(), parameters);
result = lessonPlannerModel.LessonPlannerID;
}
}
if (result > 0)
{
AddUpdateMainTopic(lessonPlannerModel, result);
}
return(result);
}
public long AddUpdateMainTopic(LessonPlannerModel lessonPlannerModel, long lessonPlannerID)
{
long result = 0;
if (lessonPlannerModel != null)
{
if (lessonPlannerModel.MainTopicID == 0) //Add
{
StringBuilder strSql = new StringBuilder();
strSql.Append("DECLARE @SeedValue INT ");
strSql.Append("SET @SeedValue = (SELECT ISNULL(MAX(MainTopicID), 0) FROM dbo.MainTopic) ");
strSql.Append("DBCC CHECKIDENT('dbo.MainTopic',RESEED,@SeedValue)");
strSql.Append("INSERT INTO MainTopic(");
strSql.Append("MainTopicNumber,MainTopicTitle,LessonPlannerID,Introduction,Objectives,Material,");
strSql.Append("CreatedOn,CreatedBy,ModifiedOn,ModifiedBy)");
strSql.Append(" VALUES (");
strSql.Append("@MainTopicNumber,@MainTopicTitle,@LessonPlannerID,@Introduction,@Objectives,@Material,");
strSql.Append("@CreatedOn,@CreatedBy,@ModifiedOn,@ModifiedBy)");
strSql.Append(";SELECT SCOPE_IDENTITY()");
SqlParameter[] parameters =
{
new SqlParameter("@MainTopicNumber", SqlDbType.NVarChar, 50),
new SqlParameter("@MainTopicTitle", SqlDbType.NVarChar, -1),
new SqlParameter("@LessonPlannerID", SqlDbType.BigInt),
new SqlParameter("@Introduction", SqlDbType.NVarChar, -1),
new SqlParameter("@Objectives", SqlDbType.NVarChar, -1),
new SqlParameter("@Material", SqlDbType.NVarChar, -1),
new SqlParameter("@CreatedOn", SqlDbType.DateTime),
new SqlParameter("@CreatedBy", SqlDbType.Int),
new SqlParameter("@ModifiedOn", SqlDbType.DateTime),
new SqlParameter("@ModifiedBy", SqlDbType.Int)
};
parameters[0].Value = lessonPlannerModel.MainTopicNumber;
parameters[1].Value = lessonPlannerModel.TitleMainTopic;
parameters[2].Value = lessonPlannerID;
parameters[3].Value = lessonPlannerModel.Introduction;
parameters[4].Value = lessonPlannerModel.Objectives;
parameters[5].Value = lessonPlannerModel.Material;
parameters[6].Value = lessonPlannerModel.CreatedOn;
parameters[7].Value = lessonPlannerModel.CreatedBy;
parameters[8].Value = lessonPlannerModel.ModifiedOn;
parameters[9].Value = lessonPlannerModel.ModifiedBy;
object obj = DbHelper.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
result = 0;
}
else
{
result = Convert.ToInt64(obj);
}
}
else
{
StringBuilder strSql = new StringBuilder();
strSql.Append("UPDATE MainTopic SET ");
strSql.Append("MainTopicNumber=@MainTopicNumber,MainTopicTitle=@MainTopicTitle,");
strSql.Append("LessonPlannerID=@LessonPlannerID,Introduction=@Introduction,Objectives=@Objectives,");
strSql.Append("Material=@Material,ModifiedOn=@ModifiedOn,ModifiedBy=@ModifiedBy");
strSql.Append(" where MainTopicID=@MainTopicID");
SqlParameter[] parameters =
{
new SqlParameter("@MainTopicNumber", SqlDbType.NVarChar, 50),
new SqlParameter("@MainTopicTitle", SqlDbType.NVarChar, -1),
new SqlParameter("@LessonPlannerID", SqlDbType.BigInt),
new SqlParameter("@Introduction", SqlDbType.NVarChar, -1),
new SqlParameter("@Objectives", SqlDbType.NVarChar, -1),
new SqlParameter("@Material", SqlDbType.NVarChar, -1),
new SqlParameter("@CreatedOn", SqlDbType.DateTime),
new SqlParameter("@CreatedBy", SqlDbType.Int),
new SqlParameter("@ModifiedOn", SqlDbType.DateTime),
new SqlParameter("@ModifiedBy", SqlDbType.Int),
new SqlParameter("@MainTopicID", SqlDbType.BigInt),
};
parameters[0].Value = lessonPlannerModel.MainTopicNumber;
parameters[1].Value = lessonPlannerModel.TitleMainTopic;
parameters[2].Value = lessonPlannerModel.LessonPlannerID;
parameters[3].Value = lessonPlannerModel.Introduction;
parameters[4].Value = lessonPlannerModel.Objectives;
parameters[5].Value = lessonPlannerModel.Material;
parameters[6].Value = lessonPlannerModel.CreatedOn;
parameters[7].Value = lessonPlannerModel.CreatedBy;
parameters[8].Value = lessonPlannerModel.ModifiedOn;
parameters[9].Value = lessonPlannerModel.ModifiedBy;
parameters[10].Value = lessonPlannerModel.MainTopicID;
result = DbHelper.ExecuteSql(strSql.ToString(), parameters);
result = lessonPlannerModel.MainTopicID;
}
if (result > 0)
{
DataTable dataTableSubTopics = TranslateSubToicListToDataTable(lessonPlannerModel.subTopicModels, result);
DataTable dataTableGames = TranslateGamesListToDataTable(lessonPlannerModel.gamesModels, result);
DataTable dataTableMovies = TranslateMoviesListToDataTable(lessonPlannerModel.moviesModels, result);
DataTable dataTableDocumentaries = TranslateDocumentariesListToDataTable(lessonPlannerModel.documentariesModels, result);
DataTable dataTableBooks = TranslateBooksListToDataTable(lessonPlannerModel.booksModels, result);
SaveSubTopicsAndExtras(dataTableSubTopics, dataTableGames, dataTableMovies, dataTableDocumentaries, dataTableBooks);
}
}
return(result);
}