public static void addToDoItem(ToDoItems t)
{
SqlConnection connection = FamilyDB.getConnection();
String query = "Insert into ListItems (familyID, listItem, listType, dueDate, isComplete) values (@familyID, @listItem, @listType, @dueDate, @isComplete)";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@familyID", t.FamilyID);
cmd.Parameters.AddWithValue("@listItem", t.ListItem);
cmd.Parameters.AddWithValue("@listType", t.ListType);
cmd.Parameters.AddWithValue("@dueDate", t.DueDate);
cmd.Parameters.AddWithValue("@isComplete", t.IsComplete);
try
{
if (connection != null)
{
connection.Open();
}
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
public static void addActivity(Activity a)
{
SqlConnection connection = FamilyDB.getConnection();
String query = "Insert into Activity (familyID, activityName, startDate, endDate, time) values (@familyID, @activityName, @startDate, @endDate, @time)";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@familyID", a.FamilyID);
cmd.Parameters.AddWithValue("@activityName", a.ActivityName);
cmd.Parameters.AddWithValue("@startDate", a.StartDate);
cmd.Parameters.AddWithValue("@endDate", a.EndDate);
cmd.Parameters.AddWithValue("@time", a.Time);
try
{
if (connection != null)
{
connection.Open();
}
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
public static void DeleteList(int familyID, string listType, string listName)
{
SqlConnection connection = FamilyDB.getConnection();
String query = "Delete from ListItems where familyID = @familyID and listType = @listType and listItem = @listName";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@familyID", familyID);
cmd.Parameters.AddWithValue("@listType", listType);
cmd.Parameters.AddWithValue("@listName", listName);
try
{
if (connection != null)
{
connection.Open();
}
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
public static void DeleteMember(int familyID, string fname, DateTime bday)
{
SqlConnection connection = FamilyDB.getConnection();
String query = "Delete from FamilyMembers where familyID = @familyID and memberFirst = @memberFirst and memberBirthdate = @memberBirthdate";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@familyID", familyID);
cmd.Parameters.AddWithValue("@memberFirst", fname);
cmd.Parameters.AddWithValue("@memberBirthdate", bday);
try
{
if (connection != null)
{
connection.Open();
}
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
public static void DeleteActivity(int familyID, string activityName, DateTime startDate)
{
SqlConnection connection = FamilyDB.getConnection();
String query = "Delete from Activity where familyID = @familyID and activityName = @activityName and startDate = @startDate";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@familyID", familyID);
cmd.Parameters.AddWithValue("@activityName", activityName);
cmd.Parameters.AddWithValue("@startDate", startDate);
try
{
if (connection != null)
{
connection.Open();
}
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
public static void addMember(Members m)
{
SqlConnection connection = FamilyDB.getConnection();
String query = "Insert into FamilyMembers(familyID, memberFirst, memberLast, memberRole, memberBirthdate) values (@familyID, @memberFirst, @memberLast, @memberRole, @memberBirthdate)";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@familyID", m.FamilyID);
cmd.Parameters.AddWithValue("@memberFirst", m.MemberFirst);
cmd.Parameters.AddWithValue("@memberLast", m.MemberLast);
cmd.Parameters.AddWithValue("@memberRole", m.MemberRole);
cmd.Parameters.AddWithValue("@memberBirthdate", m.MemberBirthdate);
try
{
if (connection != null)
{
connection.Open();
}
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
public static void addFamily(Family f)
{
SqlConnection connection = FamilyDB.getConnection();
String query = "Insert into Family(HOHFirst, HOHLast, subscriptionStart, subscriptionEnd) values (@HOHFirst, @HOHLast, @subscriptionStart, @subscriptionEnd)";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@HOHFirst", f.HOHFirst);
cmd.Parameters.AddWithValue("@HOHLast", f.HOHLast);
cmd.Parameters.AddWithValue("@subscriptionStart", f.SubscriptionStart);
cmd.Parameters.AddWithValue("@subscriptionEnd", f.SubscriptionEnd);
try
{
if (connection != null)
{
connection.Open();
}
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
public static void addGroceryItem(GroceryItems g)
{
SqlConnection connection = FamilyDB.getConnection();
String query = "Insert into ListItems (familyID, listItem, listType, storeName, salePrice, isComplete) values (@familyID, @listItem, @listType, @storeName, @salePrice, @isComplete)";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@familyID", g.FamilyID);
cmd.Parameters.AddWithValue("@listItem", g.ListItem);
cmd.Parameters.AddWithValue("@listType", g.ListType);
cmd.Parameters.AddWithValue("@storeName", g.StoreName);
cmd.Parameters.AddWithValue("@salePrice", g.StorePrice);
cmd.Parameters.AddWithValue("@isComplete", g.IsComplete);
try
{
if (connection != null)
{
connection.Open();
}
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
public static int getFamilyIDbyHOH(string HFirst, string HLast)
{
if (HFirst == null || HLast == null)
{
return(0);
}
Family f = new Family();
SqlConnection connection = FamilyDB.getConnection();
if (connection != null)
{
connection.Open();
}
String query = "SELECT * FROM Family WHERE HOHFirst = @HOHFirst and HOHLast = @HOHLast";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@HOHFirst", HFirst);
cmd.Parameters.AddWithValue("@HOHLast", HLast);
try
{
SqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
f.FamilyID = (int)read["familyID"];
f.SubscriptionStart = (DateTime)read["subscriptionStart"];
f.SubscriptionEnd = (DateTime)read["subscriptionEnd"];
f.HOHFirst = (string)read["HOHFirst"];
f.HOHLast = (string)read["HOHLast"];
}
else
{
return(0);
}
}
catch (SqlException ex)
{
return(0);
Console.WriteLine(ex);
}
catch (Exception ex)
{
return(0);
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
return(f.FamilyID);
}
public static List <GroceryItems> getGroceryList(int famID)
{
if (famID == null)
{
return(null);
}
List <GroceryItems> allGrocery = new List <GroceryItems>();
SqlConnection connection = FamilyDB.getConnection();
if (connection != null)
{
connection.Open();
}
string listType = "grocery";
String query = "SELECT listItem, storeName, salePrice FROM ListItems WHERE familyID = @famID and listType = @listType";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@famID", famID);
cmd.Parameters.AddWithValue("@listType", listType);
try
{
SqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
GroceryItems g = new GroceryItems();
g.ListItem = (string)read["listItem"];
g.StoreName = (string)read["storeName"];
g.StorePrice = (double)read["storePrice"];
allGrocery.Add(g);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
return(null);
}
catch (Exception ex)
{
return(null);
}
finally
{
connection.Close();
}
return(allGrocery);
}
public static bool memberExists(Members m)
{
if (m == null)
{
return(false);
}
SqlConnection connection = FamilyDB.getConnection();
if (connection != null)
{
connection.Open();
}
String query = "SELECT * FROM FamilyMembers WHERE familyID = @famID and memberFirst = @memberFirst and memberBirthdate = @memberBirthdate";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@famID", m.FamilyID);
cmd.Parameters.AddWithValue("@memberFirst", m.MemberFirst);
cmd.Parameters.AddWithValue("@memberBirthdate", m.MemberBirthdate);
try
{
SqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
m.MemberID = (int)read["memberID"];
m.FamilyID = (int)read["familyID"];
m.MemberFirst = (string)read["memberFirst"];
m.MemberLast = (string)read["memberLast"];
m.MemberRole = (string)read["memberRole"];
m.MemberBirthdate = (DateTime)read["memberBirthdate"];
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex);
return(false);
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(false);
}
finally
{
connection.Close();
}
}
public static Users getUserFamilyID(int famID)
{
if (famID == null)
{
return(null);
}
Users u = new Users();
SqlConnection connection = FamilyDB.getConnection();
if (connection != null)
{
connection.Open();
}
String query = "SELECT * FROM Users WHERE familyID = @famID";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@famID", famID);
try
{
SqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
u.UserID = (int)read["userID"];
u.FamilyID = (int)read["familyID"];
u.UserFirst = (string)read["userFirst"];
u.UserLast = (string)read["userLast"];
u.UserLogin = (string)read["userLogin"];
u.UserPassword = (string)read["userPassword"];
u.UserEmail = (string)read["userEmail"];
u.Permissions = (string)read["permissions"];
}
else
{
return(null);
}
}
catch (SqlException ex)
{
return(null);
}
catch (Exception ex)
{
return(null);
}
finally
{
connection.Close();
}
return(u);
}
public static List <Members> getMembersFamilyID(int famID)
{
if (famID == null)
{
return(null);
}
List <Members> allMembers = new List <Members>();
SqlConnection connection = FamilyDB.getConnection();
if (connection != null)
{
connection.Open();
}
String query = "SELECT * FROM FamilyMembers WHERE familyID = @famID";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@famID", famID);
try
{
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
Members m = new Members();
m.MemberID = (int)read["memberID"];
m.FamilyID = (int)read["familyID"];
m.MemberFirst = (string)read["memberFirst"];
m.MemberLast = (string)read["memberLast"];
m.MemberRole = (string)read["memberRole"];
m.MemberBirthdate = (DateTime)read["memberBirthdate"];
allMembers.Add(m);
}
}
catch (SqlException ex)
{
return(null);
}
catch (Exception ex)
{
return(null);
}
finally
{
connection.Close();
}
return(allMembers);
}
public static List <Activity> getActivityByDate(int famID, DateTime startDate)
{
if (famID == 0 || startDate == null)
{
return(null);
}
List <Activity> allActivities = new List <Activity>();
SqlConnection connection = FamilyDB.getConnection();
if (connection != null)
{
connection.Open();
}
String query = "SELECT * FROM FamilyMembers WHERE familyID = @famID";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@famID", famID);
try
{
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
Activity a = new Activity();
a.ActivityID = (int)read["activityID"];
a.FamilyID = (int)read["familyID"];
a.ActivityName = (string)read["activityName"];
a.StartDate = (DateTime)read["startDate"];
a.EndDate = (DateTime)read["endDate"];
a.Time = (DateTime)read["time"];
allActivities.Add(a);
}
}
catch (SqlException ex)
{
return(null);
}
catch (Exception ex)
{
return(null);
}
finally
{
connection.Close();
}
return(allActivities);
}
public static List <ToDoItems> getToDoList(int famID)
{
if (famID == null)
{
return(null);
}
List <ToDoItems> allToDo = new List <ToDoItems>();
SqlConnection connection = FamilyDB.getConnection();
if (connection != null)
{
connection.Open();
}
string listType = "todo";
String query = "SELECT listItem, dueDate FROM ListItems WHERE familyID = @famID and listType = @listType";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@famID", famID);
cmd.Parameters.AddWithValue("@listType", listType);
try
{
SqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
ToDoItems t = new ToDoItems();
t.ListItem = (string)read["listItem"];
t.DueDate = (DateTime)read["dueDate"];
allToDo.Add(t);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
return(null);
}
catch (Exception ex)
{
return(null);
}
finally
{
connection.Close();
}
return(allToDo);
}
public static void addUser(Users u)
{
SqlConnection connection = FamilyDB.getConnection();
String query = "Insert into Users(familyID, userFirst, userLast, userLogin, userPassword, userEmail, permissions) values (@familyID, @userFirst, @userLast, @userLogin, @userPassword, @userEmail, @permissions)";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@familyID", u.FamilyID);
cmd.Parameters.AddWithValue("@userFirst", u.UserFirst);
cmd.Parameters.AddWithValue("@userLast", u.UserLast);
cmd.Parameters.AddWithValue("@userLogin", u.UserLogin);
cmd.Parameters.AddWithValue("@userPassword", u.UserPassword);
cmd.Parameters.AddWithValue("@userEmail", u.UserEmail);
cmd.Parameters.AddWithValue("@permissions", u.Permissions);
try
{
if (connection != null)
{
connection.Open();
}
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
public static bool userExists(string login)
{
Users u = new Users();
bool validate = false;
if (login == null)
{
validate = false;
}
SqlConnection connection = FamilyDB.getConnection();
try
{
connection.Open();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
String query = "SELECT * FROM Users WHERE userLogin = @login";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@login", login);
try
{
SqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
string userLogin = (string)read["userLogin"];
if (login == userLogin)
{
validate = true;
}
else
{
validate = false;
}
}
else
{
validate = false;
}
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
return(validate);
}