private Group getGroup(String gKey)
{
Group g = new Group();
SqlConnection connection = new SqlConnection(cString);
string sqlString = "SELECT * FROM mGroup WHERE [key] = '" + gKey + "'";
SqlCommand cmd = new SqlCommand(sqlString, connection);
try
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
g.ID = Int32.Parse(reader[0].ToString());
g.key = reader[1].ToString();
g.title = reader[2].ToString();
g.subtitle = reader[3].ToString();
g.backgroundImage = reader[4].ToString();
g.description = reader[5].ToString();
}
reader.Close();
}
catch (Exception e)
{
g.ID = -1;
g.title = e.ToString();
return g;
}
finally
{
connection.Close();
}
return g;
}
public String insertGroup(Group group)
{
fixCORS();
String result;
SqlConnection connection = new SqlConnection(cString);
string sqlString = "DECLARE @max int " +
"SET @max = (SELECT max(id) FROM mGroup) " +
"INSERT INTO mGroup " +
"VALUES (@max+1, " +
"'group' + CONVERT(varchar(10), @max+2), " +
"@title, " +
"@subtitle, " +
"@backgroundImage, " +
"@description)";
SqlCommand cmd = new SqlCommand(sqlString, connection);
cmd.Parameters.AddWithValue("title", group.title);
cmd.Parameters.AddWithValue("subtitle", group.subtitle);
cmd.Parameters.AddWithValue("backgroundImage", group.backgroundImage);
cmd.Parameters.AddWithValue("description", group.description);
string sqlStringForInsertItem = "DECLARE @max int " +
"SET @max = (SELECT max(id) FROM mGroup) " +
"SELECT [key] FROM mGroup WHERE id = @max";
SqlCommand cmdForInsertItem = new SqlCommand(sqlStringForInsertItem, connection);
try
{
connection.Open();
result = cmd.ExecuteNonQuery().ToString();
SqlDataReader reader = cmdForInsertItem.ExecuteReader();
while (reader.Read())
{
insertItem(new Item
{
backgroundImage = "",
content = "",
description = "",
group = new Group
{
key = reader[0].ToString()
},
subtitle = "",
title = ""
});
}
reader.Close();
}
catch (Exception e)
{
return e.ToString();
}
finally
{
connection.Close();
}
return result;
}
public String updateGroup(Group group)
{
fixCORS();
String result;
SqlConnection connection = new SqlConnection(cString);
string sqlString = "UPDATE mGroup " +
"SET title = @title, " +
"subtitle = @subtitle, " +
"backgroundImage = @backgroundImage, " +
"description = @description " +
"WHERE id = @id";
SqlCommand cmd = new SqlCommand(sqlString, connection);
cmd.Parameters.AddWithValue("title", group.title);
cmd.Parameters.AddWithValue("price", group.subtitle);
cmd.Parameters.AddWithValue("backgroundImage", group.backgroundImage);
cmd.Parameters.AddWithValue("description", group.description);
try
{
connection.Open();
result = cmd.ExecuteNonQuery().ToString();
}
catch (Exception e)
{
return e.ToString();
}
finally
{
connection.Close();
}
return result;
}
public List<Group> getAllGroups()
{
fixCORS();
List<Group> toReturn = new List<Group>();
SqlConnection connection = new SqlConnection(cString);
string sqlString = "SELECT * FROM mGroup";
SqlCommand cmd = new SqlCommand(sqlString, connection);
try
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Group g = new Group();
g.ID = Int32.Parse(reader[0].ToString());
g.key = reader[1].ToString();
g.title = reader[2].ToString();
g.subtitle = reader[3].ToString();
g.backgroundImage = reader[4].ToString();
g.description = reader[5].ToString();
toReturn.Add(g);
}
reader.Close();
}
catch (Exception e)
{
Group g = new Group();
g.ID = -1;
g.title = e.ToString();
toReturn.Add(g);
return toReturn;
}
finally
{
connection.Close();
}
return toReturn;
}
