public static DataTable SelectAll()
{
SqlConnection connection = SchoolData.GetConnection();
string selectStatement
= "SELECT "
+ " [Class ID] = [studentclass].[ClassId] "
+ " ,[Class Name] = [studentclass].[ClassName] "
+ "FROM "
+ " [studentclass] "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
}
reader.Close();
}
catch (SqlException)
{
return(dt);
}
finally
{
connection.Close();
}
return(dt);
}
public static List <result_exam4> List()
{
List <result_exam4> result_examList = new List <result_exam4>();
SqlConnection connection = SchoolData.GetConnection();
string selectStatement
= "SELECT "
+ " [ExamId] "
+ "FROM "
+ " [exam] "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
result_exam4 clsresult_exam = new result_exam4();
while (reader.Read())
{
clsresult_exam = new result_exam4();
clsresult_exam.ExamId = System.Convert.ToInt32(reader["ExamId"]);
result_examList.Add(clsresult_exam);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(result_examList);
}
public static List <students_studentclass> List()
{
List <students_studentclass> students_studentclassList = new List <students_studentclass>();
SqlConnection connection = SchoolData.GetConnection();
string selectStatement
= "SELECT "
+ " [ClassId] "
+ "FROM "
+ " [studentclass] "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
students_studentclass clsstudents_studentclass = new students_studentclass();
while (reader.Read())
{
clsstudents_studentclass = new students_studentclass();
clsstudents_studentclass.ClassId = System.Convert.ToInt32(reader["ClassId"]);
students_studentclassList.Add(clsstudents_studentclass);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(students_studentclassList);
}
public static students Select_Record(students clsstudentsPara)
{
students clsstudents = new students();
SqlConnection connection = SchoolData.GetConnection();
string selectStatement
= "SELECT "
+ " [StudentId] "
+ " ,[ClassId] "
+ " ,[FullName] "
+ " ,[DateOfBirth] "
+ " ,[HomeAddress] "
+ " ,[Gender] "
+ " ,[Father] "
+ " ,[Mother] "
+ " ,[ParentContact] "
+ "FROM "
+ " [students] "
+ "WHERE "
+ " [StudentId] = @StudentId "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
selectCommand.Parameters.AddWithValue("@StudentId", clsstudentsPara.StudentId);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
clsstudents.StudentId = System.Convert.ToInt32(reader["StudentId"]);
clsstudents.ClassId = reader["ClassId"] is DBNull ? null : (Int32?)reader["ClassId"];
clsstudents.FullName = reader["FullName"] is DBNull ? null : reader["FullName"].ToString();
clsstudents.DateOfBirth = reader["DateOfBirth"] is DBNull ? null : (DateTime?)reader["DateOfBirth"];
clsstudents.HomeAddress = reader["HomeAddress"] is DBNull ? null : reader["HomeAddress"].ToString();
clsstudents.Gender = System.Convert.ToString(reader["Gender"]);
clsstudents.Father = reader["Father"] is DBNull ? null : reader["Father"].ToString();
clsstudents.Mother = reader["Mother"] is DBNull ? null : reader["Mother"].ToString();
clsstudents.ParentContact = reader["ParentContact"] is DBNull ? null : reader["ParentContact"].ToString();
}
else
{
clsstudents = null;
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(clsstudents);
}
public static bool Update(studentclass oldstudentclass,
studentclass newstudentclass)
{
SqlConnection connection = SchoolData.GetConnection();
string updateStatement
= "UPDATE "
+ " [studentclass] "
+ "SET "
+ " [ClassName] = @NewClassName "
+ "WHERE "
+ " [ClassId] = @OldClassId "
+ " AND ((@OldClassName IS NULL AND [ClassName] IS NULL) OR [ClassName] = @OldClassName) "
+ "";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.CommandType = CommandType.Text;
if (newstudentclass.ClassName != null)
{
updateCommand.Parameters.AddWithValue("@NewClassName", newstudentclass.ClassName);
}
else
{
updateCommand.Parameters.AddWithValue("@NewClassName", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@OldClassId", oldstudentclass.ClassId);
if (oldstudentclass.ClassName != null)
{
updateCommand.Parameters.AddWithValue("@OldClassName", oldstudentclass.ClassName);
}
else
{
updateCommand.Parameters.AddWithValue("@OldClassName", DBNull.Value);
}
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static teacher Select_Record(teacher clsteacherPara)
{
teacher clsteacher = new teacher();
SqlConnection connection = SchoolData.GetConnection();
string selectStatement
= "SELECT "
+ " [TeacherId] "
+ " ,[ClassId] "
+ " ,[FullName] "
+ " ,[DateOfJoin] "
+ " ,[HomeAddress] "
+ " ,[PhoneNumber] "
+ "FROM "
+ " [teacher] "
+ "WHERE "
+ " [TeacherId] = @TeacherId "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
selectCommand.Parameters.AddWithValue("@TeacherId", clsteacherPara.TeacherId);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
clsteacher.TeacherId = System.Convert.ToInt32(reader["TeacherId"]);
clsteacher.ClassId = reader["ClassId"] is DBNull ? null : (Int32?)reader["ClassId"];
clsteacher.FullName = reader["FullName"] is DBNull ? null : reader["FullName"].ToString();
clsteacher.DateOfJoin = reader["DateOfJoin"] is DBNull ? null : (DateTime?)reader["DateOfJoin"];
clsteacher.HomeAddress = reader["HomeAddress"] is DBNull ? null : reader["HomeAddress"].ToString();
clsteacher.PhoneNumber = reader["PhoneNumber"] is DBNull ? null : reader["PhoneNumber"].ToString();
}
else
{
clsteacher = null;
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(clsteacher);
}
public static bool Add(studentclass clsstudentclass)
{
SqlConnection connection = SchoolData.GetConnection();
string insertStatement
= "INSERT "
+ " [studentclass] "
+ " ( "
+ " [ClassName] "
+ " ) "
+ "VALUES "
+ " ( "
+ " @ClassName "
+ " ) "
+ "";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.CommandType = CommandType.Text;
if (clsstudentclass.ClassName != null)
{
insertCommand.Parameters.AddWithValue("@ClassName", clsstudentclass.ClassName);
}
else
{
insertCommand.Parameters.AddWithValue("@ClassName", DBNull.Value);
}
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool Delete(studentclass clsstudentclass)
{
SqlConnection connection = SchoolData.GetConnection();
string deleteStatement
= "DELETE FROM "
+ " [studentclass] "
+ "WHERE "
+ " [ClassId] = @OldClassId "
+ " AND ((@OldClassName IS NULL AND [ClassName] IS NULL) OR [ClassName] = @OldClassName) "
+ "";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.CommandType = CommandType.Text;
deleteCommand.Parameters.AddWithValue("@OldClassId", clsstudentclass.ClassId);
if (clsstudentclass.ClassName != null)
{
deleteCommand.Parameters.AddWithValue("@OldClassName", clsstudentclass.ClassName);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldClassName", DBNull.Value);
}
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static studentclass Select_Record(studentclass clsstudentclassPara)
{
studentclass clsstudentclass = new studentclass();
SqlConnection connection = SchoolData.GetConnection();
string selectStatement
= "SELECT "
+ " [ClassId] "
+ " ,[ClassName] "
+ "FROM "
+ " [studentclass] "
+ "WHERE "
+ " [ClassId] = @ClassId "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
selectCommand.Parameters.AddWithValue("@ClassId", clsstudentclassPara.ClassId);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
clsstudentclass.ClassId = System.Convert.ToInt32(reader["ClassId"]);
clsstudentclass.ClassName = reader["ClassName"] is DBNull ? null : reader["ClassName"].ToString();
}
else
{
clsstudentclass = null;
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(clsstudentclass);
}
public static exam Select_Record(exam clsexamPara)
{
exam clsexam = new exam();
SqlConnection connection = SchoolData.GetConnection();
string selectStatement
= "SELECT "
+ " [ExamId] "
+ " ,[ExamType] "
+ "FROM "
+ " [exam] "
+ "WHERE "
+ " [ExamId] = @ExamId "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
selectCommand.Parameters.AddWithValue("@ExamId", clsexamPara.ExamId);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
clsexam.ExamId = System.Convert.ToInt32(reader["ExamId"]);
clsexam.ExamType = reader["ExamType"] is DBNull ? null : reader["ExamType"].ToString();
}
else
{
clsexam = null;
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(clsexam);
}
public static DataTable SelectAll()
{
SqlConnection connection = SchoolData.GetConnection();
string selectStatement
= "SELECT "
+ " [Student ID] = [students].[StudentId] "
+ " ,[Class ID] = [studentclass].[ClassId] "
+ " ,[Full Name] = [students].[FullName] "
+ " ,[Date Of Birth] = [students].[DateOfBirth] "
+ " ,[Home Address] = [students].[HomeAddress] "
+ " ,[Gender] = [students].[Gender] "
+ " ,[Father] = [students].[Father] "
+ " ,[Mother] = [students].[Mother] "
+ " ,[Parent Contact] = [students].[ParentContact] "
+ "FROM "
+ " [students] "
+ "LEFT JOIN [studentclass] ON [students].[ClassId] = [studentclass].[ClassId] "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
}
reader.Close();
}
catch (SqlException)
{
return(dt);
}
finally
{
connection.Close();
}
return(dt);
}
public static bool Update(teacher oldteacher,
teacher newteacher)
{
SqlConnection connection = SchoolData.GetConnection();
string updateStatement
= "UPDATE "
+ " [teacher] "
+ "SET "
+ " [ClassId] = @NewClassId "
+ " ,[FullName] = @NewFullName "
+ " ,[DateOfJoin] = @NewDateOfJoin "
+ " ,[HomeAddress] = @NewHomeAddress "
+ " ,[PhoneNumber] = @NewPhoneNumber "
+ "WHERE "
+ " [TeacherId] = @OldTeacherId "
+ " AND ((@OldClassId IS NULL AND [ClassId] IS NULL) OR [ClassId] = @OldClassId) "
+ " AND ((@OldFullName IS NULL AND [FullName] IS NULL) OR [FullName] = @OldFullName) "
+ " AND ((@OldDateOfJoin IS NULL AND [DateOfJoin] IS NULL) OR [DateOfJoin] = @OldDateOfJoin) "
+ " AND ((@OldHomeAddress IS NULL AND [HomeAddress] IS NULL) OR [HomeAddress] = @OldHomeAddress) "
+ " AND ((@OldPhoneNumber IS NULL AND [PhoneNumber] IS NULL) OR [PhoneNumber] = @OldPhoneNumber) "
+ "";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.CommandType = CommandType.Text;
if (newteacher.ClassId.HasValue == true)
{
updateCommand.Parameters.AddWithValue("@NewClassId", newteacher.ClassId);
}
else
{
updateCommand.Parameters.AddWithValue("@NewClassId", DBNull.Value);
}
if (newteacher.FullName != null)
{
updateCommand.Parameters.AddWithValue("@NewFullName", newteacher.FullName);
}
else
{
updateCommand.Parameters.AddWithValue("@NewFullName", DBNull.Value);
}
if (newteacher.DateOfJoin.HasValue == true)
{
updateCommand.Parameters.AddWithValue("@NewDateOfJoin", newteacher.DateOfJoin);
}
else
{
updateCommand.Parameters.AddWithValue("@NewDateOfJoin", DBNull.Value);
}
if (newteacher.HomeAddress != null)
{
updateCommand.Parameters.AddWithValue("@NewHomeAddress", newteacher.HomeAddress);
}
else
{
updateCommand.Parameters.AddWithValue("@NewHomeAddress", DBNull.Value);
}
if (newteacher.PhoneNumber != null)
{
updateCommand.Parameters.AddWithValue("@NewPhoneNumber", newteacher.PhoneNumber);
}
else
{
updateCommand.Parameters.AddWithValue("@NewPhoneNumber", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@OldTeacherId", oldteacher.TeacherId);
if (oldteacher.ClassId.HasValue == true)
{
updateCommand.Parameters.AddWithValue("@OldClassId", oldteacher.ClassId);
}
else
{
updateCommand.Parameters.AddWithValue("@OldClassId", DBNull.Value);
}
if (oldteacher.FullName != null)
{
updateCommand.Parameters.AddWithValue("@OldFullName", oldteacher.FullName);
}
else
{
updateCommand.Parameters.AddWithValue("@OldFullName", DBNull.Value);
}
if (oldteacher.DateOfJoin.HasValue == true)
{
updateCommand.Parameters.AddWithValue("@OldDateOfJoin", oldteacher.DateOfJoin);
}
else
{
updateCommand.Parameters.AddWithValue("@OldDateOfJoin", DBNull.Value);
}
if (oldteacher.HomeAddress != null)
{
updateCommand.Parameters.AddWithValue("@OldHomeAddress", oldteacher.HomeAddress);
}
else
{
updateCommand.Parameters.AddWithValue("@OldHomeAddress", DBNull.Value);
}
if (oldteacher.PhoneNumber != null)
{
updateCommand.Parameters.AddWithValue("@OldPhoneNumber", oldteacher.PhoneNumber);
}
else
{
updateCommand.Parameters.AddWithValue("@OldPhoneNumber", DBNull.Value);
}
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool Update(students oldstudents,
students newstudents)
{
SqlConnection connection = SchoolData.GetConnection();
string updateStatement
= "UPDATE "
+ " [students] "
+ "SET "
+ " [ClassId] = @NewClassId "
+ " ,[FullName] = @NewFullName "
+ " ,[DateOfBirth] = @NewDateOfBirth "
+ " ,[HomeAddress] = @NewHomeAddress "
+ " ,[Gender] = @NewGender "
+ " ,[Father] = @NewFather "
+ " ,[Mother] = @NewMother "
+ " ,[ParentContact] = @NewParentContact "
+ "WHERE "
+ " [StudentId] = @OldStudentId "
+ " AND ((@OldClassId IS NULL AND [ClassId] IS NULL) OR [ClassId] = @OldClassId) "
+ " AND ((@OldFullName IS NULL AND [FullName] IS NULL) OR [FullName] = @OldFullName) "
+ " AND ((@OldDateOfBirth IS NULL AND [DateOfBirth] IS NULL) OR [DateOfBirth] = @OldDateOfBirth) "
+ " AND ((@OldHomeAddress IS NULL AND [HomeAddress] IS NULL) OR [HomeAddress] = @OldHomeAddress) "
+ " AND [Gender] = @OldGender "
+ " AND ((@OldFather IS NULL AND [Father] IS NULL) OR [Father] = @OldFather) "
+ " AND ((@OldMother IS NULL AND [Mother] IS NULL) OR [Mother] = @OldMother) "
+ " AND ((@OldParentContact IS NULL AND [ParentContact] IS NULL) OR [ParentContact] = @OldParentContact) "
+ "";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.CommandType = CommandType.Text;
if (newstudents.ClassId.HasValue == true)
{
updateCommand.Parameters.AddWithValue("@NewClassId", newstudents.ClassId);
}
else
{
updateCommand.Parameters.AddWithValue("@NewClassId", DBNull.Value);
}
if (newstudents.FullName != null)
{
updateCommand.Parameters.AddWithValue("@NewFullName", newstudents.FullName);
}
else
{
updateCommand.Parameters.AddWithValue("@NewFullName", DBNull.Value);
}
if (newstudents.DateOfBirth.HasValue == true)
{
updateCommand.Parameters.AddWithValue("@NewDateOfBirth", newstudents.DateOfBirth);
}
else
{
updateCommand.Parameters.AddWithValue("@NewDateOfBirth", DBNull.Value);
}
if (newstudents.HomeAddress != null)
{
updateCommand.Parameters.AddWithValue("@NewHomeAddress", newstudents.HomeAddress);
}
else
{
updateCommand.Parameters.AddWithValue("@NewHomeAddress", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@NewGender", newstudents.Gender);
if (newstudents.Father != null)
{
updateCommand.Parameters.AddWithValue("@NewFather", newstudents.Father);
}
else
{
updateCommand.Parameters.AddWithValue("@NewFather", DBNull.Value);
}
if (newstudents.Mother != null)
{
updateCommand.Parameters.AddWithValue("@NewMother", newstudents.Mother);
}
else
{
updateCommand.Parameters.AddWithValue("@NewMother", DBNull.Value);
}
if (newstudents.ParentContact != null)
{
updateCommand.Parameters.AddWithValue("@NewParentContact", newstudents.ParentContact);
}
else
{
updateCommand.Parameters.AddWithValue("@NewParentContact", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@OldStudentId", oldstudents.StudentId);
if (oldstudents.ClassId.HasValue == true)
{
updateCommand.Parameters.AddWithValue("@OldClassId", oldstudents.ClassId);
}
else
{
updateCommand.Parameters.AddWithValue("@OldClassId", DBNull.Value);
}
if (oldstudents.FullName != null)
{
updateCommand.Parameters.AddWithValue("@OldFullName", oldstudents.FullName);
}
else
{
updateCommand.Parameters.AddWithValue("@OldFullName", DBNull.Value);
}
if (oldstudents.DateOfBirth.HasValue == true)
{
updateCommand.Parameters.AddWithValue("@OldDateOfBirth", oldstudents.DateOfBirth);
}
else
{
updateCommand.Parameters.AddWithValue("@OldDateOfBirth", DBNull.Value);
}
if (oldstudents.HomeAddress != null)
{
updateCommand.Parameters.AddWithValue("@OldHomeAddress", oldstudents.HomeAddress);
}
else
{
updateCommand.Parameters.AddWithValue("@OldHomeAddress", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@OldGender", oldstudents.Gender);
if (oldstudents.Father != null)
{
updateCommand.Parameters.AddWithValue("@OldFather", oldstudents.Father);
}
else
{
updateCommand.Parameters.AddWithValue("@OldFather", DBNull.Value);
}
if (oldstudents.Mother != null)
{
updateCommand.Parameters.AddWithValue("@OldMother", oldstudents.Mother);
}
else
{
updateCommand.Parameters.AddWithValue("@OldMother", DBNull.Value);
}
if (oldstudents.ParentContact != null)
{
updateCommand.Parameters.AddWithValue("@OldParentContact", oldstudents.ParentContact);
}
else
{
updateCommand.Parameters.AddWithValue("@OldParentContact", DBNull.Value);
}
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static DataTable Search(string sField, string sCondition, string sValue)
{
SqlConnection connection = SchoolData.GetConnection();
string selectStatement = "";
if (sCondition == "Contains")
{
selectStatement
= "SELECT "
+ " [students].[StudentId] "
+ " ,[studentclass].[ClassId] "
+ " ,[students].[FullName] "
+ " ,[students].[DateOfBirth] "
+ " ,[students].[HomeAddress] "
+ " ,[students].[Gender] "
+ " ,[students].[Father] "
+ " ,[students].[Mother] "
+ " ,[students].[ParentContact] "
+ "FROM "
+ " [students] "
+ "LEFT JOIN [studentclass] ON [students].[ClassId] = [studentclass].[ClassId] "
+ "WHERE "
+ " (@StudentId IS NULL OR @StudentId = '' OR [students].[StudentId] LIKE '%' + LTRIM(RTRIM(@StudentId)) + '%') "
+ "AND (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] LIKE '%' + LTRIM(RTRIM(@ClassId)) + '%') "
+ "AND (@FullName IS NULL OR @FullName = '' OR [students].[FullName] LIKE '%' + LTRIM(RTRIM(@FullName)) + '%') "
+ "AND (@DateOfBirth IS NULL OR @DateOfBirth = '' OR [students].[DateOfBirth] LIKE '%' + LTRIM(RTRIM(@DateOfBirth)) + '%') "
+ "AND (@HomeAddress IS NULL OR @HomeAddress = '' OR [students].[HomeAddress] LIKE '%' + LTRIM(RTRIM(@HomeAddress)) + '%') "
+ "AND (@Gender IS NULL OR @Gender = '' OR [students].[Gender] LIKE '%' + LTRIM(RTRIM(@Gender)) + '%') "
+ "AND (@Father IS NULL OR @Father = '' OR [students].[Father] LIKE '%' + LTRIM(RTRIM(@Father)) + '%') "
+ "AND (@Mother IS NULL OR @Mother = '' OR [students].[Mother] LIKE '%' + LTRIM(RTRIM(@Mother)) + '%') "
+ "AND (@ParentContact IS NULL OR @ParentContact = '' OR [students].[ParentContact] LIKE '%' + LTRIM(RTRIM(@ParentContact)) + '%') "
+ "";
}
else if (sCondition == "Equals")
{
selectStatement
= "SELECT "
+ " [students].[StudentId] "
+ " ,[studentclass].[ClassId] "
+ " ,[students].[FullName] "
+ " ,[students].[DateOfBirth] "
+ " ,[students].[HomeAddress] "
+ " ,[students].[Gender] "
+ " ,[students].[Father] "
+ " ,[students].[Mother] "
+ " ,[students].[ParentContact] "
+ "FROM "
+ " [students] "
+ "LEFT JOIN [studentclass] ON [students].[ClassId] = [studentclass].[ClassId] "
+ "WHERE "
+ " (@StudentId IS NULL OR @StudentId = '' OR [students].[StudentId] = LTRIM(RTRIM(@StudentId))) "
+ "AND (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] = LTRIM(RTRIM(@ClassId))) "
+ "AND (@FullName IS NULL OR @FullName = '' OR [students].[FullName] = LTRIM(RTRIM(@FullName))) "
+ "AND (@DateOfBirth IS NULL OR @DateOfBirth = '' OR [students].[DateOfBirth] = LTRIM(RTRIM(@DateOfBirth))) "
+ "AND (@HomeAddress IS NULL OR @HomeAddress = '' OR [students].[HomeAddress] = LTRIM(RTRIM(@HomeAddress))) "
+ "AND (@Gender IS NULL OR @Gender = '' OR [students].[Gender] = LTRIM(RTRIM(@Gender))) "
+ "AND (@Father IS NULL OR @Father = '' OR [students].[Father] = LTRIM(RTRIM(@Father))) "
+ "AND (@Mother IS NULL OR @Mother = '' OR [students].[Mother] = LTRIM(RTRIM(@Mother))) "
+ "AND (@ParentContact IS NULL OR @ParentContact = '' OR [students].[ParentContact] = LTRIM(RTRIM(@ParentContact))) "
+ "";
}
else if (sCondition == "Starts with...")
{
selectStatement
= "SELECT "
+ " [students].[StudentId] "
+ " ,[studentclass].[ClassId] "
+ " ,[students].[FullName] "
+ " ,[students].[DateOfBirth] "
+ " ,[students].[HomeAddress] "
+ " ,[students].[Gender] "
+ " ,[students].[Father] "
+ " ,[students].[Mother] "
+ " ,[students].[ParentContact] "
+ "FROM "
+ " [students] "
+ "LEFT JOIN [studentclass] ON [students].[ClassId] = [studentclass].[ClassId] "
+ "WHERE "
+ " (@StudentId IS NULL OR @StudentId = '' OR [students].[StudentId] LIKE LTRIM(RTRIM(@StudentId)) + '%') "
+ "AND (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] LIKE LTRIM(RTRIM(@ClassId)) + '%') "
+ "AND (@FullName IS NULL OR @FullName = '' OR [students].[FullName] LIKE LTRIM(RTRIM(@FullName)) + '%') "
+ "AND (@DateOfBirth IS NULL OR @DateOfBirth = '' OR [students].[DateOfBirth] LIKE LTRIM(RTRIM(@DateOfBirth)) + '%') "
+ "AND (@HomeAddress IS NULL OR @HomeAddress = '' OR [students].[HomeAddress] LIKE LTRIM(RTRIM(@HomeAddress)) + '%') "
+ "AND (@Gender IS NULL OR @Gender = '' OR [students].[Gender] LIKE LTRIM(RTRIM(@Gender)) + '%') "
+ "AND (@Father IS NULL OR @Father = '' OR [students].[Father] LIKE LTRIM(RTRIM(@Father)) + '%') "
+ "AND (@Mother IS NULL OR @Mother = '' OR [students].[Mother] LIKE LTRIM(RTRIM(@Mother)) + '%') "
+ "AND (@ParentContact IS NULL OR @ParentContact = '' OR [students].[ParentContact] LIKE LTRIM(RTRIM(@ParentContact)) + '%') "
+ "";
}
else if (sCondition == "More than...")
{
selectStatement
= "SELECT "
+ " [students].[StudentId] "
+ " ,[studentclass].[ClassId] "
+ " ,[students].[FullName] "
+ " ,[students].[DateOfBirth] "
+ " ,[students].[HomeAddress] "
+ " ,[students].[Gender] "
+ " ,[students].[Father] "
+ " ,[students].[Mother] "
+ " ,[students].[ParentContact] "
+ "FROM "
+ " [students] "
+ "LEFT JOIN [studentclass] ON [students].[ClassId] = [studentclass].[ClassId] "
+ "WHERE "
+ " (@StudentId IS NULL OR @StudentId = '' OR [students].[StudentId] > LTRIM(RTRIM(@StudentId))) "
+ "AND (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] > LTRIM(RTRIM(@ClassId))) "
+ "AND (@FullName IS NULL OR @FullName = '' OR [students].[FullName] > LTRIM(RTRIM(@FullName))) "
+ "AND (@DateOfBirth IS NULL OR @DateOfBirth = '' OR [students].[DateOfBirth] > LTRIM(RTRIM(@DateOfBirth))) "
+ "AND (@HomeAddress IS NULL OR @HomeAddress = '' OR [students].[HomeAddress] > LTRIM(RTRIM(@HomeAddress))) "
+ "AND (@Gender IS NULL OR @Gender = '' OR [students].[Gender] > LTRIM(RTRIM(@Gender))) "
+ "AND (@Father IS NULL OR @Father = '' OR [students].[Father] > LTRIM(RTRIM(@Father))) "
+ "AND (@Mother IS NULL OR @Mother = '' OR [students].[Mother] > LTRIM(RTRIM(@Mother))) "
+ "AND (@ParentContact IS NULL OR @ParentContact = '' OR [students].[ParentContact] > LTRIM(RTRIM(@ParentContact))) "
+ "";
}
else if (sCondition == "Less than...")
{
selectStatement
= "SELECT "
+ " [students].[StudentId] "
+ " ,[studentclass].[ClassId] "
+ " ,[students].[FullName] "
+ " ,[students].[DateOfBirth] "
+ " ,[students].[HomeAddress] "
+ " ,[students].[Gender] "
+ " ,[students].[Father] "
+ " ,[students].[Mother] "
+ " ,[students].[ParentContact] "
+ "FROM "
+ " [students] "
+ "LEFT JOIN [studentclass] ON [students].[ClassId] = [studentclass].[ClassId] "
+ "WHERE "
+ " (@StudentId IS NULL OR @StudentId = '' OR [students].[StudentId] < LTRIM(RTRIM(@StudentId))) "
+ "AND (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] < LTRIM(RTRIM(@ClassId))) "
+ "AND (@FullName IS NULL OR @FullName = '' OR [students].[FullName] < LTRIM(RTRIM(@FullName))) "
+ "AND (@DateOfBirth IS NULL OR @DateOfBirth = '' OR [students].[DateOfBirth] < LTRIM(RTRIM(@DateOfBirth))) "
+ "AND (@HomeAddress IS NULL OR @HomeAddress = '' OR [students].[HomeAddress] < LTRIM(RTRIM(@HomeAddress))) "
+ "AND (@Gender IS NULL OR @Gender = '' OR [students].[Gender] < LTRIM(RTRIM(@Gender))) "
+ "AND (@Father IS NULL OR @Father = '' OR [students].[Father] < LTRIM(RTRIM(@Father))) "
+ "AND (@Mother IS NULL OR @Mother = '' OR [students].[Mother] < LTRIM(RTRIM(@Mother))) "
+ "AND (@ParentContact IS NULL OR @ParentContact = '' OR [students].[ParentContact] < LTRIM(RTRIM(@ParentContact))) "
+ "";
}
else if (sCondition == "Equal or more than...")
{
selectStatement
= "SELECT "
+ " [students].[StudentId] "
+ " ,[studentclass].[ClassId] "
+ " ,[students].[FullName] "
+ " ,[students].[DateOfBirth] "
+ " ,[students].[HomeAddress] "
+ " ,[students].[Gender] "
+ " ,[students].[Father] "
+ " ,[students].[Mother] "
+ " ,[students].[ParentContact] "
+ "FROM "
+ " [students] "
+ "LEFT JOIN [studentclass] ON [students].[ClassId] = [studentclass].[ClassId] "
+ "WHERE "
+ " (@StudentId IS NULL OR @StudentId = '' OR [students].[StudentId] >= LTRIM(RTRIM(@StudentId))) "
+ "AND (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] >= LTRIM(RTRIM(@ClassId))) "
+ "AND (@FullName IS NULL OR @FullName = '' OR [students].[FullName] >= LTRIM(RTRIM(@FullName))) "
+ "AND (@DateOfBirth IS NULL OR @DateOfBirth = '' OR [students].[DateOfBirth] >= LTRIM(RTRIM(@DateOfBirth))) "
+ "AND (@HomeAddress IS NULL OR @HomeAddress = '' OR [students].[HomeAddress] >= LTRIM(RTRIM(@HomeAddress))) "
+ "AND (@Gender IS NULL OR @Gender = '' OR [students].[Gender] >= LTRIM(RTRIM(@Gender))) "
+ "AND (@Father IS NULL OR @Father = '' OR [students].[Father] >= LTRIM(RTRIM(@Father))) "
+ "AND (@Mother IS NULL OR @Mother = '' OR [students].[Mother] >= LTRIM(RTRIM(@Mother))) "
+ "AND (@ParentContact IS NULL OR @ParentContact = '' OR [students].[ParentContact] >= LTRIM(RTRIM(@ParentContact))) "
+ "";
}
else if (sCondition == "Equal or less than...")
{
selectStatement
= "SELECT "
+ " [students].[StudentId] "
+ " ,[studentclass].[ClassId] "
+ " ,[students].[FullName] "
+ " ,[students].[DateOfBirth] "
+ " ,[students].[HomeAddress] "
+ " ,[students].[Gender] "
+ " ,[students].[Father] "
+ " ,[students].[Mother] "
+ " ,[students].[ParentContact] "
+ "FROM "
+ " [students] "
+ "LEFT JOIN [studentclass] ON [students].[ClassId] = [studentclass].[ClassId] "
+ "WHERE "
+ " (@StudentId IS NULL OR @StudentId = '' OR [students].[StudentId] <= LTRIM(RTRIM(@StudentId))) "
+ "AND (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] <= LTRIM(RTRIM(@ClassId))) "
+ "AND (@FullName IS NULL OR @FullName = '' OR [students].[FullName] <= LTRIM(RTRIM(@FullName))) "
+ "AND (@DateOfBirth IS NULL OR @DateOfBirth = '' OR [students].[DateOfBirth] <= LTRIM(RTRIM(@DateOfBirth))) "
+ "AND (@HomeAddress IS NULL OR @HomeAddress = '' OR [students].[HomeAddress] <= LTRIM(RTRIM(@HomeAddress))) "
+ "AND (@Gender IS NULL OR @Gender = '' OR [students].[Gender] <= LTRIM(RTRIM(@Gender))) "
+ "AND (@Father IS NULL OR @Father = '' OR [students].[Father] <= LTRIM(RTRIM(@Father))) "
+ "AND (@Mother IS NULL OR @Mother = '' OR [students].[Mother] <= LTRIM(RTRIM(@Mother))) "
+ "AND (@ParentContact IS NULL OR @ParentContact = '' OR [students].[ParentContact] <= LTRIM(RTRIM(@ParentContact))) "
+ "";
}
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
if (sField == "Student ID")
{
selectCommand.Parameters.AddWithValue("@StudentId", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@StudentId", DBNull.Value);
}
if (sField == "Class ID")
{
selectCommand.Parameters.AddWithValue("@ClassId", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@ClassId", DBNull.Value);
}
if (sField == "Full Name")
{
selectCommand.Parameters.AddWithValue("@FullName", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@FullName", DBNull.Value);
}
if (sField == "Date Of Birth")
{
selectCommand.Parameters.AddWithValue("@DateOfBirth", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@DateOfBirth", DBNull.Value);
}
if (sField == "Home Address")
{
selectCommand.Parameters.AddWithValue("@HomeAddress", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@HomeAddress", DBNull.Value);
}
if (sField == "Gender")
{
selectCommand.Parameters.AddWithValue("@Gender", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@Gender", DBNull.Value);
}
if (sField == "Father")
{
selectCommand.Parameters.AddWithValue("@Father", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@Father", DBNull.Value);
}
if (sField == "Mother")
{
selectCommand.Parameters.AddWithValue("@Mother", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@Mother", DBNull.Value);
}
if (sField == "Parent Contact")
{
selectCommand.Parameters.AddWithValue("@ParentContact", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@ParentContact", DBNull.Value);
}
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
}
reader.Close();
}
catch (SqlException)
{
return(dt);
}
finally
{
connection.Close();
}
return(dt);
}
public static bool Add(students clsstudents)
{
SqlConnection connection = SchoolData.GetConnection();
string insertStatement
= "INSERT "
+ " [students] "
+ " ( "
+ " [ClassId] "
+ " ,[FullName] "
+ " ,[DateOfBirth] "
+ " ,[HomeAddress] "
+ " ,[Gender] "
+ " ,[Father] "
+ " ,[Mother] "
+ " ,[ParentContact] "
+ " ) "
+ "VALUES "
+ " ( "
+ " @ClassId "
+ " ,@FullName "
+ " ,@DateOfBirth "
+ " ,@HomeAddress "
+ " ,@Gender "
+ " ,@Father "
+ " ,@Mother "
+ " ,@ParentContact "
+ " ) "
+ "";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.CommandType = CommandType.Text;
if (clsstudents.ClassId.HasValue == true)
{
insertCommand.Parameters.AddWithValue("@ClassId", clsstudents.ClassId);
}
else
{
insertCommand.Parameters.AddWithValue("@ClassId", DBNull.Value);
}
if (clsstudents.FullName != null)
{
insertCommand.Parameters.AddWithValue("@FullName", clsstudents.FullName);
}
else
{
insertCommand.Parameters.AddWithValue("@FullName", DBNull.Value);
}
if (clsstudents.DateOfBirth.HasValue == true)
{
insertCommand.Parameters.AddWithValue("@DateOfBirth", clsstudents.DateOfBirth);
}
else
{
insertCommand.Parameters.AddWithValue("@DateOfBirth", DBNull.Value);
}
if (clsstudents.HomeAddress != null)
{
insertCommand.Parameters.AddWithValue("@HomeAddress", clsstudents.HomeAddress);
}
else
{
insertCommand.Parameters.AddWithValue("@HomeAddress", DBNull.Value);
}
insertCommand.Parameters.AddWithValue("@Gender", clsstudents.Gender);
if (clsstudents.Father != null)
{
insertCommand.Parameters.AddWithValue("@Father", clsstudents.Father);
}
else
{
insertCommand.Parameters.AddWithValue("@Father", DBNull.Value);
}
if (clsstudents.Mother != null)
{
insertCommand.Parameters.AddWithValue("@Mother", clsstudents.Mother);
}
else
{
insertCommand.Parameters.AddWithValue("@Mother", DBNull.Value);
}
if (clsstudents.ParentContact != null)
{
insertCommand.Parameters.AddWithValue("@ParentContact", clsstudents.ParentContact);
}
else
{
insertCommand.Parameters.AddWithValue("@ParentContact", DBNull.Value);
}
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool Delete(teacher clsteacher)
{
SqlConnection connection = SchoolData.GetConnection();
string deleteStatement
= "DELETE FROM "
+ " [teacher] "
+ "WHERE "
+ " [TeacherId] = @OldTeacherId "
+ " AND ((@OldClassId IS NULL AND [ClassId] IS NULL) OR [ClassId] = @OldClassId) "
+ " AND ((@OldFullName IS NULL AND [FullName] IS NULL) OR [FullName] = @OldFullName) "
+ " AND ((@OldDateOfJoin IS NULL AND [DateOfJoin] IS NULL) OR [DateOfJoin] = @OldDateOfJoin) "
+ " AND ((@OldHomeAddress IS NULL AND [HomeAddress] IS NULL) OR [HomeAddress] = @OldHomeAddress) "
+ " AND ((@OldPhoneNumber IS NULL AND [PhoneNumber] IS NULL) OR [PhoneNumber] = @OldPhoneNumber) "
+ "";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.CommandType = CommandType.Text;
deleteCommand.Parameters.AddWithValue("@OldTeacherId", clsteacher.TeacherId);
if (clsteacher.ClassId.HasValue == true)
{
deleteCommand.Parameters.AddWithValue("@OldClassId", clsteacher.ClassId);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldClassId", DBNull.Value);
}
if (clsteacher.FullName != null)
{
deleteCommand.Parameters.AddWithValue("@OldFullName", clsteacher.FullName);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldFullName", DBNull.Value);
}
if (clsteacher.DateOfJoin.HasValue == true)
{
deleteCommand.Parameters.AddWithValue("@OldDateOfJoin", clsteacher.DateOfJoin);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldDateOfJoin", DBNull.Value);
}
if (clsteacher.HomeAddress != null)
{
deleteCommand.Parameters.AddWithValue("@OldHomeAddress", clsteacher.HomeAddress);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldHomeAddress", DBNull.Value);
}
if (clsteacher.PhoneNumber != null)
{
deleteCommand.Parameters.AddWithValue("@OldPhoneNumber", clsteacher.PhoneNumber);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldPhoneNumber", DBNull.Value);
}
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static DataTable Search(string sField, string sCondition, string sValue)
{
SqlConnection connection = SchoolData.GetConnection();
string selectStatement = "";
if (sCondition == "Contains")
{
selectStatement
= "SELECT "
+ " [studentclass].[ClassId] "
+ " ,[studentclass].[ClassName] "
+ "FROM "
+ " [studentclass] "
+ "WHERE "
+ " (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] LIKE '%' + LTRIM(RTRIM(@ClassId)) + '%') "
+ "AND (@ClassName IS NULL OR @ClassName = '' OR [studentclass].[ClassName] LIKE '%' + LTRIM(RTRIM(@ClassName)) + '%') "
+ "";
}
else if (sCondition == "Equals")
{
selectStatement
= "SELECT "
+ " [studentclass].[ClassId] "
+ " ,[studentclass].[ClassName] "
+ "FROM "
+ " [studentclass] "
+ "WHERE "
+ " (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] = LTRIM(RTRIM(@ClassId))) "
+ "AND (@ClassName IS NULL OR @ClassName = '' OR [studentclass].[ClassName] = LTRIM(RTRIM(@ClassName))) "
+ "";
}
else if (sCondition == "Starts with...")
{
selectStatement
= "SELECT "
+ " [studentclass].[ClassId] "
+ " ,[studentclass].[ClassName] "
+ "FROM "
+ " [studentclass] "
+ "WHERE "
+ " (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] LIKE LTRIM(RTRIM(@ClassId)) + '%') "
+ "AND (@ClassName IS NULL OR @ClassName = '' OR [studentclass].[ClassName] LIKE LTRIM(RTRIM(@ClassName)) + '%') "
+ "";
}
else if (sCondition == "More than...")
{
selectStatement
= "SELECT "
+ " [studentclass].[ClassId] "
+ " ,[studentclass].[ClassName] "
+ "FROM "
+ " [studentclass] "
+ "WHERE "
+ " (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] > LTRIM(RTRIM(@ClassId))) "
+ "AND (@ClassName IS NULL OR @ClassName = '' OR [studentclass].[ClassName] > LTRIM(RTRIM(@ClassName))) "
+ "";
}
else if (sCondition == "Less than...")
{
selectStatement
= "SELECT "
+ " [studentclass].[ClassId] "
+ " ,[studentclass].[ClassName] "
+ "FROM "
+ " [studentclass] "
+ "WHERE "
+ " (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] < LTRIM(RTRIM(@ClassId))) "
+ "AND (@ClassName IS NULL OR @ClassName = '' OR [studentclass].[ClassName] < LTRIM(RTRIM(@ClassName))) "
+ "";
}
else if (sCondition == "Equal or more than...")
{
selectStatement
= "SELECT "
+ " [studentclass].[ClassId] "
+ " ,[studentclass].[ClassName] "
+ "FROM "
+ " [studentclass] "
+ "WHERE "
+ " (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] >= LTRIM(RTRIM(@ClassId))) "
+ "AND (@ClassName IS NULL OR @ClassName = '' OR [studentclass].[ClassName] >= LTRIM(RTRIM(@ClassName))) "
+ "";
}
else if (sCondition == "Equal or less than...")
{
selectStatement
= "SELECT "
+ " [studentclass].[ClassId] "
+ " ,[studentclass].[ClassName] "
+ "FROM "
+ " [studentclass] "
+ "WHERE "
+ " (@ClassId IS NULL OR @ClassId = '' OR [studentclass].[ClassId] <= LTRIM(RTRIM(@ClassId))) "
+ "AND (@ClassName IS NULL OR @ClassName = '' OR [studentclass].[ClassName] <= LTRIM(RTRIM(@ClassName))) "
+ "";
}
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
if (sField == "Class ID")
{
selectCommand.Parameters.AddWithValue("@ClassId", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@ClassId", DBNull.Value);
}
if (sField == "Class Name")
{
selectCommand.Parameters.AddWithValue("@ClassName", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@ClassName", DBNull.Value);
}
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
}
reader.Close();
}
catch (SqlException)
{
return(dt);
}
finally
{
connection.Close();
}
return(dt);
}
public static bool Delete(students clsstudents)
{
SqlConnection connection = SchoolData.GetConnection();
string deleteStatement
= "DELETE FROM "
+ " [students] "
+ "WHERE "
+ " [StudentId] = @OldStudentId "
+ " AND ((@OldClassId IS NULL AND [ClassId] IS NULL) OR [ClassId] = @OldClassId) "
+ " AND ((@OldFullName IS NULL AND [FullName] IS NULL) OR [FullName] = @OldFullName) "
+ " AND ((@OldDateOfBirth IS NULL AND [DateOfBirth] IS NULL) OR [DateOfBirth] = @OldDateOfBirth) "
+ " AND ((@OldHomeAddress IS NULL AND [HomeAddress] IS NULL) OR [HomeAddress] = @OldHomeAddress) "
+ " AND [Gender] = @OldGender "
+ " AND ((@OldFather IS NULL AND [Father] IS NULL) OR [Father] = @OldFather) "
+ " AND ((@OldMother IS NULL AND [Mother] IS NULL) OR [Mother] = @OldMother) "
+ " AND ((@OldParentContact IS NULL AND [ParentContact] IS NULL) OR [ParentContact] = @OldParentContact) "
+ "";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.CommandType = CommandType.Text;
deleteCommand.Parameters.AddWithValue("@OldStudentId", clsstudents.StudentId);
if (clsstudents.ClassId.HasValue == true)
{
deleteCommand.Parameters.AddWithValue("@OldClassId", clsstudents.ClassId);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldClassId", DBNull.Value);
}
if (clsstudents.FullName != null)
{
deleteCommand.Parameters.AddWithValue("@OldFullName", clsstudents.FullName);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldFullName", DBNull.Value);
}
if (clsstudents.DateOfBirth.HasValue == true)
{
deleteCommand.Parameters.AddWithValue("@OldDateOfBirth", clsstudents.DateOfBirth);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldDateOfBirth", DBNull.Value);
}
if (clsstudents.HomeAddress != null)
{
deleteCommand.Parameters.AddWithValue("@OldHomeAddress", clsstudents.HomeAddress);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldHomeAddress", DBNull.Value);
}
deleteCommand.Parameters.AddWithValue("@OldGender", clsstudents.Gender);
if (clsstudents.Father != null)
{
deleteCommand.Parameters.AddWithValue("@OldFather", clsstudents.Father);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldFather", DBNull.Value);
}
if (clsstudents.Mother != null)
{
deleteCommand.Parameters.AddWithValue("@OldMother", clsstudents.Mother);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldMother", DBNull.Value);
}
if (clsstudents.ParentContact != null)
{
deleteCommand.Parameters.AddWithValue("@OldParentContact", clsstudents.ParentContact);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldParentContact", DBNull.Value);
}
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool Add(teacher clsteacher)
{
SqlConnection connection = SchoolData.GetConnection();
string insertStatement
= "INSERT "
+ " [teacher] "
+ " ( "
+ " [ClassId] "
+ " ,[FullName] "
+ " ,[DateOfJoin] "
+ " ,[HomeAddress] "
+ " ,[PhoneNumber] "
+ " ) "
+ "VALUES "
+ " ( "
+ " @ClassId "
+ " ,@FullName "
+ " ,@DateOfJoin "
+ " ,@HomeAddress "
+ " ,@PhoneNumber "
+ " ) "
+ "";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.CommandType = CommandType.Text;
if (clsteacher.ClassId.HasValue == true)
{
insertCommand.Parameters.AddWithValue("@ClassId", clsteacher.ClassId);
}
else
{
insertCommand.Parameters.AddWithValue("@ClassId", DBNull.Value);
}
if (clsteacher.FullName != null)
{
insertCommand.Parameters.AddWithValue("@FullName", clsteacher.FullName);
}
else
{
insertCommand.Parameters.AddWithValue("@FullName", DBNull.Value);
}
if (clsteacher.DateOfJoin.HasValue == true)
{
insertCommand.Parameters.AddWithValue("@DateOfJoin", clsteacher.DateOfJoin);
}
else
{
insertCommand.Parameters.AddWithValue("@DateOfJoin", DBNull.Value);
}
if (clsteacher.HomeAddress != null)
{
insertCommand.Parameters.AddWithValue("@HomeAddress", clsteacher.HomeAddress);
}
else
{
insertCommand.Parameters.AddWithValue("@HomeAddress", DBNull.Value);
}
if (clsteacher.PhoneNumber != null)
{
insertCommand.Parameters.AddWithValue("@PhoneNumber", clsteacher.PhoneNumber);
}
else
{
insertCommand.Parameters.AddWithValue("@PhoneNumber", DBNull.Value);
}
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
