------
------------
không cho ngueoif dùng tự động add thêm cột trong datagridview
dgvEmployee.AllowUserToAddRows = false;
------------------------
load tất cả thông tin từ database
public static DataTable getAllEmployeeUsingDataTable()
{
string sql = "select * from Employee";
DataTable dt = DAO.GetDataBySQl(sql);
return dt;
}
----------------------------------------------------
public static int insertEmployee(Employee e)
{
string sql = @"INSERT INTO [Employee]
([EmployeeName]
,[Male]
,[Salary]
,[Phone])
VALUES
(@name,@male,@salary,@phone)";
SqlParameter para1 = new SqlParameter("@name", DbType.String);
para1.Value = e.EmployeeName;
SqlParameter para2 = new SqlParameter("@male", DbType.Boolean);
para2.Value = e.Male;
SqlParameter para3 = new SqlParameter("@salary", DbType.Int32);
para3.Value = e.Salary;
SqlParameter para4 = new SqlParameter("@phone", DbType.String);
para4.Value = e.Phone;
return DAO.ExecuteSql(sql, para1, para2, para3, para4);
}
---------------------------------
public static List<Student> getAllStudent()
{
string sql = "select * from Students";
DataTable dt = DAO.GetDataBySQl(sql);
List<Student> students = new List<Student>();
foreach (DataRow dr in dt.Rows)
{
students.Add(new Student(
Convert.ToInt32(dr["StudentID"]), //vì nó là một object nên phải
convert sang int
dr["Roll#"].ToString(),
dr["FirstName"].ToString(),
dr["MidName"].ToString(),
dr["LastName"].ToString()
));
}
return students;
}
-----------------------------------------------------------------------------------
public static Student getStudentByID(int studentID)
{
string sql = "select * from Students where StudenId = @sid "; //sid là
đặt tên cho tham số
SqlParameter parameter1 = new SqlParameter("@sid", DbType.Int32);
//parameters[0].Value = studentID; //gán gái trị
parameter1.Value = studentID; //dbtuye là
kiểu enum liệt kê các kiểu dữ liệu của sql server
DataTable dt = DAO.GetDataBySQl(sql, parameter1); //liet ke parameter
vao
if (dt.Rows.Count == 0) return null;
DataRow dr = dt.Rows[0];
return new Student(
//tên cột trong csdl
Convert.ToInt32(dr["StudentID"]), //vì nó là một object nên phải
convert sang int
dr["Roll#"].ToString(),
dr["FirstName"].ToString(),
dr["MidName"].ToString(),
dr["LastName"].ToString()
);
}
-----------------------------------------------------------------------------------
--------------
public static DataTable getAllStudentUsingDataTable()
{
string sql = "select * from Students";
DataTable dt = DAO.GetDataBySQl(sql);
return dt;
}
-----------------------------------------------------------------------------
public static int EditStudent(Student student)
{
string sql = @"update Students
set FirstName = @first,
MidName= @mid,
LastName = @last
where StudentId = @sid";
SqlParameter para1 = new SqlParameter("@first",DbType.String);
para1.Value = student.FirstName;
SqlParameter para2 = new SqlParameter("@mid", DbType.String);
para2.Value = student.MidName;
SqlParameter para3 = new SqlParameter("@last", DbType.String);
para3.Value = student.LastName;
SqlParameter para4 = new SqlParameter("@sid", DbType.String);
para4.Value = student.StudentId;
return DAO.ExecuteSql(sql, para1, para2, para3,para4);
}
---------------------------------------------
public static int deleteStudentByStudentID(int studentID)
{
string sql = "DECLARE @studentIDToDelete int = @studentID\n" +
" DELETE FROM [ROLL_CALL_BOOKS] WHERE StudentId =
@studentIDToDelete\n" +
" DELETE FROM [STUDENT_COURSE] WHERE StudentId =
@studentIDToDelete\n" +
" DELETE FROM [STUDENTS] WHERE StudentId =
@studentIDToDelete";
SqlParameter para1 = new SqlParameter("@studentID", DbType.Int32);
para1.Value = studentID;
return DAO.ExecuteSql(sql, para1);
}
-----------------------------------------
//DELETE
BEGIN TRY
BEGIN TRAN
DECLARE @studentIDToDelete int = 1
--delete from child table
DELETE FROM Contract WHERE EmployeeId = @studentIDToDelete
-- delete from parent table
DELETE FROM Employee WHERE EmployeeID = @studentIDToDelete
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
