public bool InsertStudent(Student stu)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
using (SqlTransaction tran = con.BeginTransaction())
{
try
{
//con.Open();
SqlCommand _command = new SqlCommand();
string query = "INSERT INTO Student(StudentName,DepartmentId,FatherName,MotherName,DateOfBirth,AdmissionYear,StudentCode,Hall,Address,ContactNo,Email,BloodGroup,Sex,BioPhoto) VALUES ('"
+ stu.StudentName + "','" + stu.DepartmentId + "','" + stu.FatherName + "','" + stu.MotherName + "','" + stu.DateOfBirth + "','" + stu.AdmissionYear + "','" + stu.StudentCode +
"','" + stu.Hall + "','" + stu.Address + "','" + stu.ContactNo + "','" + stu.Email + "','" + stu.BloodGroup +
"','" + stu.Sex + "',@BioPhoto);" + " SELECT SCOPE_IDENTITY();";
_command.Parameters.Add("@BioPhoto", SqlDbType.VarBinary).Value = stu.BioPhoto;
_command.CommandText = query;
_command.Connection = con;
_command.Transaction = tran;
UserInfo usrobj = new UserInfo();
usrobj.UserName = stu.UserName;
usrobj.Password = stu.Password;
usrobj.UserTypeCode = 1; ////student
Int64 ID = Convert.ToInt64(_command.ExecuteScalar()); ////Get id Of StudentTable
usrobj.EmpOrStdId = ID;
UserInfo_DBAccess db_usrinfo = new UserInfo_DBAccess(con, _command);
db_usrinfo.InsertUserInfo(usrobj);
tran.Commit();
///_command.ExecuteNonQuery();
}
catch
{
tran.Rollback();
return(false);
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
}
}
return(true);
}
public bool UpdateStudent(Student stu)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
using (SqlTransaction tran = con.BeginTransaction())
{
try
{
//con.Open();
string query = "UPDATE Student SET StudentName='" + stu.StudentName + "',FatherName='"
+ stu.FatherName + "',MotherName='" + stu.MotherName + "',DateOfBirth='" + stu.DateOfBirth + "',AdmissionYear='"
+ stu.AdmissionYear + "',StudentCode='" + stu.StudentCode + "',DepartmentId='" + stu.DepartmentId + "',Hall='" + stu.Hall + "',Address='" + stu.Address +
"',ContactNo='" + stu.ContactNo + "',Email='" + stu.Email + "',BloodGroup='" + stu.BloodGroup + "',Sex='" + stu.Sex + "',BioPhoto=@BioPhoto" + " WHERE StudentId='" + stu.StudentId + "'";
SqlCommand _command = new SqlCommand();
_command.Parameters.Add("@BioPhoto", SqlDbType.VarBinary).Value = stu.BioPhoto;
_command.Transaction = tran;
_command.CommandText = query;
_command.Connection = con;
_command.ExecuteNonQuery();
UserInfo usrobj = new UserInfo();
usrobj.EmpOrStdId = stu.StudentId;
usrobj.UserName = stu.UserName;
usrobj.Password = stu.Password;
usrobj.UserTypeCode = 1;
UserInfo_DBAccess db_usrinfo = new UserInfo_DBAccess(con, _command);
db_usrinfo.UpdateUserInfo(usrobj);
tran.Commit();
}
catch
{
tran.Rollback();
return(false);
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
}
}
return(true);
}
public bool InsertEmployee(Employee emp)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
//SqlConnection con = new SqlConnection();
//con.ConnectionString = @"Data Source=THE-OPPORTUNIST\SQLEXPRESS; Initial Catalog=OR_DB; Integrated Security=True";
using (SqlTransaction tran = con.BeginTransaction())
{
try
{
//con.Open();
SqlCommand _command = new SqlCommand();
string query = "INSERT INTO Employee(EmployeeName,EmployeeCode,Address,ContactNo,Email,BloodGroup,Sex,JoiningDate,DepartmentId,BioPhoto) VALUES ('" + emp.EmployeeName + "','" + emp.EmployeeCode + "','"
+ emp.Address + "','" + emp.ContactNo + "','" + emp.Email + "','"
+ emp.BloodGroup + "','" + emp.Sex + "','" + emp.JoiningDate + "','"
+ emp.DepartmentId + "',@BioPhoto);" + " SELECT SCOPE_IDENTITY();";
_command.Parameters.Add("@BioPhoto", SqlDbType.VarBinary).Value = emp.BioPhoto;
_command.CommandText = query;
_command.Connection = con;
_command.Transaction = tran;
UserInfo usrobj = new UserInfo();
usrobj.UserName = emp.UserName;
usrobj.Password = emp.Password;
usrobj.UserTypeCode = 2;///////////type=empoyee
Int64 ID = Convert.ToInt64(_command.ExecuteScalar());
usrobj.EmpOrStdId = ID;
UserInfo_DBAccess db_usrinfo = new UserInfo_DBAccess(con, _command);
db_usrinfo.InsertUserInfo(usrobj);
tran.Commit();
}
catch
{
tran.Rollback();
return(false);
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
}
}
return(true);
}
public bool UpdateEmployee(Employee emp)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
using (SqlTransaction tran = con.BeginTransaction())
{
try
{
// con.Open();
SqlCommand _command = new SqlCommand();
string query = "UPDATE Employee SET EmployeeName='" + emp.EmployeeName + "',EmployeeCode='" + emp.EmployeeCode +
"',Address='" + emp.Address + "',ContactNo='" + emp.ContactNo + "',Email='" + emp.Email + "',BloodGroup='"
+ emp.BloodGroup + "',Sex='" + emp.Sex + "',JoiningDate='" + emp.JoiningDate + "',DepartmentId='"
+ emp.DepartmentId + "',BioPhoto=@BioPhoto" + " WHERE EmployeeId='" + emp.EmployeeId + "'";
_command.Parameters.Add("@BioPhoto", SqlDbType.VarBinary).Value = emp.BioPhoto;
_command.CommandText = query;
_command.Connection = con;
_command.Transaction = tran;
_command.ExecuteNonQuery();
UserInfo usrobj = new UserInfo();
usrobj.EmpOrStdId = emp.EmployeeId;
usrobj.UserName = emp.UserName;
usrobj.Password = emp.Password;
usrobj.UserTypeCode = 2;
UserInfo_DBAccess db_usrinfo = new UserInfo_DBAccess(con, _command);
db_usrinfo.UpdateUserInfo(usrobj);
tran.Commit();///my
}
catch
{
tran.Rollback();
return(false);
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
}
}
return(true);
}
public bool DeleteStudent(long StudentId)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
using (SqlTransaction tran = con.BeginTransaction())
{
try
{
// con.Open();
string query = "DELETE FROM Student WHERE StudentId=" + StudentId;
SqlCommand _command = new SqlCommand();
_command.Transaction = tran;
_command.CommandText = query;
_command.Connection = con;
_command.ExecuteNonQuery();
UserInfo_DBAccess db_usrinfo = new UserInfo_DBAccess(con, _command);
db_usrinfo.DeleteUserInfo(StudentId);
tran.Commit();
}
catch
{
tran.Rollback();
return(false);
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
}
}
return(true);
}