private StudentBase Get(string WhereClause, params SqlParameter[] commandparameters)
{
StudentBase mClass = null;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["csSwim"].ConnectionString);
string sql = "SELECT db.StudentId, db.FirstName, db.LastName, db.StudentName, db.Address1, db.Address2, db.Suburb, db.City, db.Phone, db.Mobile, db.Email, db.Password, db.Active, db.Created " +
"FROM Student db " + WhereClause;
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader rdr = null;
foreach (var parameter in commandparameters)
{
cmd.Parameters.Add(parameter);
}
try
{
con.Open();
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
mClass = LoadRow(rdr);
}
}
catch (Exception ex)
{
mClass = null;
Console.WriteLine(ex.ToString());
}
finally
{
con.Close();
con.Dispose();
cmd.Dispose();
}
return(mClass);
}
private void Populate(StudentBase mClass)
{
this.StudentId = mClass.StudentId;
this.FirstName = mClass.FirstName;
this.LastName = mClass.LastName;
this.StudentName = mClass.StudentName;
this.Address1 = mClass.Address1;
this.Address2 = mClass.Address2;
this.Suburb = mClass.Suburb;
this.City = mClass.City;
this.Phone = mClass.Phone;
this.Mobile = mClass.Mobile;
this.Email = mClass.Email;
this.Password = mClass.Password;
this.Active = mClass.Active;
this.Created = mClass.Created;
}
public int Add(StudentBase mClass)
{
int StudentId = 0;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["csSwim"].ConnectionString);
string sql = "INSERT INTO Student (FirstName, LastName, StudentName, Address1, Address2, Suburb, City, Phone, Mobile, Email, Password, Active)" +
" VALUES (@FirstName, @LastName, @StudentName, @Address1, @Address2, @Suburb, @City, @Phone, @Mobile, @Email, @Password, @Active)" +
" SELECT SCOPE_IDENTITY()";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = mClass.FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = mClass.LastName;
cmd.Parameters.Add("@StudentName", SqlDbType.VarChar).Value = mClass.StudentName;
cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = mClass.Address1;
cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = mClass.Address2;
cmd.Parameters.Add("@Suburb", SqlDbType.VarChar).Value = mClass.Suburb;
cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = mClass.City;
cmd.Parameters.Add("@Phone", SqlDbType.VarChar).Value = mClass.Phone;
cmd.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = mClass.Mobile;
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = mClass.Email;
cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = mClass.Password;
cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = mClass.Active;
try
{
con.Open();
StudentId = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
StudentId = -1;
Console.WriteLine(ex.ToString());
}
finally
{
con.Close();
con.Dispose();
cmd.Dispose();
}
return(StudentId);
}
public bool Update(StudentBase mClass)
{
bool mResult = false;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["csSwim"].ConnectionString);
string sql = "UPDATE Student SET FirstName = @FirstName, LastName = @LastName, StudentName = @StudentName, Address1 = @Address1, Address2 = @Address2, Suburb = @Suburb, City = @City, Phone = @Phone, Mobile = @Mobile, Email = @Email, Password = @Password, Active = @Active " +
"WHERE StudentId = @StudentId;";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@StudentId", SqlDbType.Int).Value = mClass.StudentId;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = mClass.FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = mClass.LastName;
cmd.Parameters.Add("@StudentName", SqlDbType.VarChar).Value = mClass.StudentName;
cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = mClass.Address1;
cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = mClass.Address2;
cmd.Parameters.Add("@Suburb", SqlDbType.VarChar).Value = mClass.Suburb;
cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = mClass.City;
cmd.Parameters.Add("@Phone", SqlDbType.VarChar).Value = mClass.Phone;
cmd.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = mClass.Mobile;
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = mClass.Email;
cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = mClass.Password;
cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = mClass.Active;
try
{
con.Open();
cmd.ExecuteNonQuery();
mResult = true;
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
con.Close();
con.Dispose();
cmd.Dispose();
}
return(mResult);
}