public string updateGroup(Groups group, string userName)
{
sqlConn.ConnectionString = conn;
DataTable dt = new DataTable();
dt.Clear();
string query = "UPDATE GROUPS SET ID_MAIN_MEMBER=@ID_MAIN_MEMBER, GROUP_NAME=@GROUP_NAME, ID_SECOND_MEMBER=@ID_SECOND_MEMBER";
if (group.idThird != null)
{
query += ", ID_THIRD_MEMBER=@ID_THIRD_MEMBER";
}
else
{
query += ", ID_THIRD_MEMBER=@NULL3";
}
if (group.idFourth != null)
{
query += ", ID_FOURTH_MEMBER=@ID_FOURTH_MEMBER";
}
else
{
query += ", ID_FOURTH_MEMBER=@NULL4";
}
if (group.idFith != null)
{
query += ", ID_FITH_MEMBER=@ID_FITH_MEMBER";
}
else
{
query += ", ID_FITH_MEMBER=@NULL5";
}
if (group.idSixth != null)
{
query += ", ID_SIXTH_MEMBER=@ID_SIXTH_MEMBER";
}
else
{
query += ", ID_SIXTH_MEMBER=@NULL6";
}
query += ", DATE_MODIFIED=@DATE_MODIFIED, MODIFIED_BY=@MODIFIED_BY WHERE ID_GROUP=@ID_GROUP";
SqlCommand cmd = new SqlCommand(query, sqlConn);
cmd.Parameters.AddWithValue("ID_GROUP", group.id);
cmd.Parameters.AddWithValue("@ID_MAIN_MEMBER", group.idMainMember);
cmd.Parameters.AddWithValue("@GROUP_NAME", group.name);
cmd.Parameters.AddWithValue("@ID_SECOND_MEMBER", group.idSecond);
if (group.idThird != null)
{
cmd.Parameters.AddWithValue("@ID_THIRD_MEMBER", group.idThird);
}
else if(group.idThird == null)
{
cmd.Parameters.AddWithValue("@NULL3", DBNull.Value);
}
if (group.idFourth != null)
{
cmd.Parameters.AddWithValue("@ID_FOURTH_MEMBER", group.idFourth);
}
else if (group.idFourth == null)
{
cmd.Parameters.AddWithValue("@NULL4", DBNull.Value);
}
if (group.idFith != null)
{
cmd.Parameters.AddWithValue("@ID_FITH_MEMBER", group.idFith);
}else if(group.idFith == null){
cmd.Parameters.AddWithValue("@NULL5", DBNull.Value);
}
if (group.idSixth != null)
{
cmd.Parameters.AddWithValue("@ID_SIXTH_MEMBER", group.idSixth);
}
else if (group.idSixth == null)
{
cmd.Parameters.AddWithValue("@NULL6", DBNull.Value);
}
cmd.Parameters.AddWithValue("@DATE_MODIFIED", System.DateTime.Today);
cmd.Parameters.AddWithValue("@MODIFIED_BY", userName);
try
{
sqlConn.Open();
cmd.ExecuteNonQuery();
return "The Group has been updated sucessfully.";
}
catch (Exception e)
{
return e.Message.ToString();
}
finally
{
sqlConn.Close();
}
}
public List<Groups> getAllGroups()
{
List<Groups> allGroups = new List<Groups>();
sqlConn.ConnectionString = conn;
DataTable dt = new DataTable();
dt.Clear();
//---------------------------------------------------------------------------
//SELECT SQL, using the ID passed as an argument
SqlCommand cmd = new SqlCommand(string.Format(
@"SELECT * FROM GROUPS ORDER BY GROUP_NAME"), sqlConn);
try
{
sqlConn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
for(int i=0; i<=dt.Rows.Count; i++){
Groups group1 = new Groups();
DataRow row = dt.Rows[i];
group1.id = row[0].ToString();
group1.idMainMember = row[1].ToString();
group1.name = row[2].ToString();
group1.idSecond = row[3].ToString();
group1.idThird = row[4].ToString();
group1.idFourth = row[5].ToString();
group1.idFith = row[6].ToString();
group1.idSixth = row[7].ToString();
allGroups.Add(group1);
}
}
reader.Close();
}
catch (Exception e)
{
string error = e.Message.ToString();
}
finally
{
sqlConn.Close();
}
//---------------------------------------------------------------------
try
{
sqlConn.Open();
foreach (Groups group in allGroups)
{
//---MAIN MEMBER
cmd = new SqlCommand(string.Format(
@"SELECT MEMBER.NAME FROM MEMBER WHERE MEMBER.ID_MEMBER = @ID_MEMBER"), sqlConn);
cmd.Parameters.AddWithValue("@ID_MEMBER", group.idMainMember);
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
if(reader.HasRows)
group.mainMemberName = reader.GetValue(0).ToString();
reader.Close();
//---SECOND MEMBER
cmd = new SqlCommand(string.Format(
@"SELECT MEMBER.NAME FROM MEMBER WHERE MEMBER.ID_MEMBER = @ID_MEMBER"), sqlConn);
cmd.Parameters.AddWithValue("@ID_MEMBER", group.idSecond);
reader = cmd.ExecuteReader();
reader.Read();
if (reader.HasRows)
group.secondMemberName = reader.GetValue(0).ToString();
reader.Close();
//---THIRD MEMBER
cmd = new SqlCommand(string.Format(
@"SELECT MEMBER.NAME FROM MEMBER WHERE MEMBER.ID_MEMBER = @ID_MEMBER"), sqlConn);
cmd.Parameters.AddWithValue("@ID_MEMBER", group.idThird);
reader = cmd.ExecuteReader();
reader.Read();
if (reader.HasRows)
group.thirdMemberName = reader.GetValue(0).ToString();
reader.Close();
//---FOURTH MEMBER
cmd = new SqlCommand(string.Format(
@"SELECT MEMBER.NAME FROM MEMBER WHERE MEMBER.ID_MEMBER = @ID_MEMBER"), sqlConn);
cmd.Parameters.AddWithValue("@ID_MEMBER", group.idFourth);
reader = cmd.ExecuteReader();
reader.Read();
if (reader.HasRows)
group.fourthMemberName = reader.GetValue(0).ToString();
reader.Close();
//---FITH MEMBER
cmd = new SqlCommand(string.Format(
@"SELECT MEMBER.NAME FROM MEMBER WHERE MEMBER.ID_MEMBER = @ID_MEMBER"), sqlConn);
cmd.Parameters.AddWithValue("@ID_MEMBER", group.idFith);
reader = cmd.ExecuteReader();
reader.Read();
if (reader.HasRows)
group.fithMemberName = reader.GetValue(0).ToString();
reader.Close();
//---SIXTH MEMBER
cmd = new SqlCommand(string.Format(
@"SELECT MEMBER.NAME FROM MEMBER WHERE MEMBER.ID_MEMBER = @ID_MEMBER"), sqlConn);
cmd.Parameters.AddWithValue("@ID_MEMBER", group.idSixth);
reader = cmd.ExecuteReader();
reader.Read();
if (reader.HasRows)
group.sixthMemberName = reader.GetValue(0).ToString();
reader.Close();
}
return allGroups;
}
catch (Exception Exception)
{
}
finally
{
sqlConn.Close();
}
return allGroups;
}
public Groups getGroupById(string idGroup)
{
group = new Groups();
sqlConn.ConnectionString = conn;
DataTable dt = new DataTable();
dt.Clear();
//SELECT SQL, using the ID passed as an argument
SqlCommand cmd = new SqlCommand(string.Format(
@"SELECT * FROM GROUPS WHERE ID_GROUP=@ID_GROUP"), sqlConn);
cmd.Parameters.AddWithValue("@ID_GROUP", idGroup);
try
{
sqlConn.Open();
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
//Sets the member's parameters using the reader..
group.id = reader.GetValue(0).ToString();
group.idMainMember = reader.GetValue(1).ToString();
group.name = reader.GetValue(2).ToString();
reader.Close();
}
catch (Exception e)
{
string error = e.Message.ToString();
}
finally
{
sqlConn.Close();
}
return group;
}
public string insertGroup(Groups group, string userName)
{
sqlConn.ConnectionString = conn;
DataTable dt = new DataTable();
dt.Clear();
string query = "INSERT INTO GROUPS (ID_MAIN_MEMBER, GROUP_NAME";
if (!group.idSecond.Equals(""))
{
query += ", ID_SECOND_MEMBER";
}
if (!group.idThird.Equals(""))
{
query += ", ID_THIRD_MEMBER";
}
if (!group.idFourth.Equals(""))
{
query += ", ID_FOURTH_MEMBER";
}
if (!group.idFith.Equals(""))
{
query += ", ID_FITH_MEMBER";
}
if (!group.idSixth.Equals(""))
{
query += ", ID_SIXTH_MEMBER";
}
query += ", DATE_MODIFIED, MODIFIED_BY, DATE_CREATED)";
query += " VALUES (@ID_MAIN_MEMBER, @GROUP_NAME";
if (!group.idSecond.Equals(""))
{
query += ", @ID_SECOND_MEMBER";
}
if (!group.idThird.Equals(""))
{
query += ", @ID_THIRD_MEMBER";
}
if (!group.idFourth.Equals(""))
{
query += ", @ID_FOURTH_MEMBER";
}
if (!group.idFith.Equals(""))
{
query += ", @ID_FITH_MEMBER";
}
if (!group.idSixth.Equals(""))
{
query += ", @ID_SIXTH_MEMBER";
}
query += ", @DATE_MODIFIED, @MODIFIED_BY, @DATE_CREATED)";
SqlCommand cmd = new SqlCommand(query, sqlConn);
cmd.Parameters.AddWithValue("@ID_MAIN_MEMBER", group.idMainMember);
cmd.Parameters.AddWithValue("@GROUP_NAME", group.name);
if (!group.idSecond.Equals(""))
{
cmd.Parameters.AddWithValue("@ID_SECOND_MEMBER", group.idSecond);
}
if (!group.idThird.Equals(""))
{
cmd.Parameters.AddWithValue("@ID_THIRD_MEMBER", group.idThird);
}
if (!group.idFourth.Equals(""))
{
cmd.Parameters.AddWithValue("@ID_FOURTH_MEMBER", group.idThird);
}
if (!group.idFith.Equals(""))
{
cmd.Parameters.AddWithValue("@ID_FITH_MEMBER", group.idThird);
}
if (!group.idSixth.Equals(""))
{
cmd.Parameters.AddWithValue("@ID_SIXTH_MEMBER", group.idThird);
}
cmd.Parameters.AddWithValue("@DATE_MODIFIED", System.DateTime.Now);
cmd.Parameters.AddWithValue("@MODIFIED_BY", userName);
cmd.Parameters.AddWithValue("@DATE_CREATED", System.DateTime.Now);
try
{
sqlConn.Open();
cmd.ExecuteNonQuery();
return "The Group has been created sucessfully.";
}
catch (Exception e)
{
return e.Message.ToString();
}
finally
{
sqlConn.Close();
}
}
protected void updateGroup(object sender, EventArgs e)
{
if (this.mainMember.Text.Equals("") || this.secondMember.Text.Equals(""))
{
this.errorMsg.Visible = true;
this.errorMsg.Text = "At least 2 members have to be selected in order to update a group.";
}
else if (this.groupName.Text.Equals(""))
{
this.errorMsg.Visible = true;
this.errorMsg.Text = "Please insert a name for the Group.";
}
else
{
Groups group = new Groups();
GroupsDAO groupDAO = new GroupsDAO();
group.id = this.GroupID.Text;
group.idMainMember = this.idMainMember.Text;
group.name = this.groupName.Text;
group.idSecond = this.idSecondMember.Text;
if (!this.idThirdMember.Text.Equals("") && !this.idThirdMember.Text.Equals(" "))
{
group.idThird = this.idThirdMember.Text;
}
else
{
group.idThird = null;
}
if (!this.idFourthMember.Text.Equals("") && !this.idFourthMember.Text.Equals(" "))
{
group.idFourth = this.idFourthMember.Text;
}
else
{
group.idFourth = null;
}
if (!this.idFithMember.Text.Equals("") && !this.idFithMember.Text.Equals(" "))
{
group.idFith = this.idFithMember.Text;
}
else
{
group.idFith = null;
}
if (!this.idSixthMember.Text.Equals("") && !this.idSixthMember.Text.Equals(" "))
{
group.idSixth = this.idSixthMember.Text;
}
else
{
group.idSixth = null;
}
string result = groupDAO.updateGroup(group, this.Page.User.Identity.Name.ToString());
this.editGroupForm.Visible = false;
this.response.Visible = true;
this.responseText.Text = result;
}
}