protected void Button1_Click(object sender, EventArgs e)
{
if (this.Page.IsValid)
{
BusinessLayer.Student student = new BusinessLayer.Student();
student.FirstName = this.tb_firstname.Text;
student.LastName = this.tb_lastname.Text;
student.BirthDate = this.tb_birthdate.Text;
student.Gender = this.tb_gender.Text;
student.Class = this.tb_class.Text;
int result = DataAccessLayer.UtilityTools.ExecuteInsertStudent(student);
if (result == 1)
{
this.lbl_resultmessage.Text = "Submission Succesful!";
}
/*else if (result == 0)
* this.lbl_resultmessage.Text = "There was an error at the Database level";
* else
* this.lbl_resultmessage.Text = "There was an error at the Method level";*/
else
{
this.lbl_resultmessage.Text = "There was an error.";
}
}
}
public static string BorrowBook(BusinessLayer.Student borrowingStudent, BusinessLayer.Book requestedBook)
{
SqlConnection conn;
using (conn = new SqlConnection(getConnectionString()))
{
string sql = "SP_borrow";
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@studentId", System.Data.SqlDbType.Int);
param[1] = new SqlParameter("@bookId", System.Data.SqlDbType.Int);
param[0].Value = borrowingStudent.stId;
param[1].Value = requestedBook.bkId;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// cmd.ExecuteNonQuery(); // run this if no return expected
return(cmd.ExecuteScalar().ToString());
}
}
}
public static int ExecuteInsertStudent(BusinessLayer.Student student)
{
SqlConnection conn;
SqlCommand cmd;
using (conn = new SqlConnection(GetConnectionString()))
{
try
{
string sql = "INSERT INTO Student (FirstName, LastName, BirthDate, Gender, Class) VALUES (@FirstName, @LastName, @BirthDate, @Gender, @Class)";
//string sql = "sp_InsertStudentInfo";
conn.Open();
using (cmd = new SqlCommand(sql, conn))
{
SqlParameter[] param = new SqlParameter[5];
param[0] = new SqlParameter("@FirstName", System.Data.SqlDbType.VarChar, 20);
param[1] = new SqlParameter("@LastName", System.Data.SqlDbType.VarChar, 20);
param[2] = new SqlParameter("@BirthDate", System.Data.SqlDbType.Date);
param[3] = new SqlParameter("@Gender", System.Data.SqlDbType.Char, 6);
param[4] = new SqlParameter("@Class", System.Data.SqlDbType.VarChar, 20);
param[0].Value = student.FirstName;
param[1].Value = student.LastName;
param[2].Value = student.BirthDate;
param[3].Value = student.Gender;
param[4].Value = student.Class;
foreach (SqlParameter p in param)
{
cmd.Parameters.Add(p);
}
cmd.CommandType = System.Data.CommandType.Text;
return((int)cmd.ExecuteNonQuery());
}
}
catch (SqlException ex)
{
return(ex.ErrorCode);
}
}
}
public static DataSet GetStudentBooks(BusinessLayer.Student returningStudent)
{
SqlConnection conn;
using (conn = new SqlConnection(getConnectionString()))
{
string sql = "SP_getStudentBooks";
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.Add(new SqlParameter("@studentId", returningStudent.stId));
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
}
}
public static string InsertStudent(BusinessLayer.Student newStudent)
{
SqlConnection conn;
using (conn = new SqlConnection(getConnectionString()))
{
string sql = "SP_insertStudent";
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
SqlParameter[] param = new SqlParameter[6];
param[0] = new SqlParameter("@studentId", System.Data.SqlDbType.Int);
param[1] = new SqlParameter("@firstName", System.Data.SqlDbType.NVarChar, 50);
param[2] = new SqlParameter("@lastName", System.Data.SqlDbType.NVarChar, 50);
param[3] = new SqlParameter("@birthdate", System.Data.SqlDbType.Date);
param[4] = new SqlParameter("@gender", System.Data.SqlDbType.Char, 1);
param[5] = new SqlParameter("@class", System.Data.SqlDbType.NVarChar, 50);
param[0].Value = newStudent.stId;
param[1].Value = newStudent.stFirstName;
param[2].Value = newStudent.stLastName;
param[3].Value = newStudent.stBirthDate;
param[4].Value = newStudent.stGender;
param[5].Value = newStudent.stClass;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// cmd.ExecuteNonQuery(); // run this if no return expected
return(cmd.ExecuteScalar().ToString());
}
}
}
