public string Save(NewRealization x)
{
try {
string members = JsonConvert.SerializeObject(x.members, Formatting.None);
using (SqlConnection connection = new SqlConnection(G.connectionString)) {
connection.Open();
string sql = null;
if (x.id == null)
{
sql = string.Format(@"INSERT INTO Realizations (GroupId, Leader, Duration, RealizationDate, Description, Note, Substitute, Type, Members)
VALUES ({0}, {1}, {2}, '{3}', N'{4}', N'{5}', N'{6}', {7}, N'{8}')"
, x.groupId, x.leaderId, x.duration, x.date, x.description, x.note, x.substitute, x.type, members);
}
else
{
sql = string.Format(@"UPDATE Realizations SET
GroupId = {1}, Leader = {2}, Duration = {3}, RealizationDate = '{4}', Description = N'{5}', Note = N'{6}', Substitute = N'{7}', Type = N'{8}', Members = N'{9}'
WHERE Id = {0}", x.id, x.groupId, x.leaderId, x.duration, x.date, x.description, x.note, x.substitute, x.type, members);
}
using (SqlCommand command = new SqlCommand(sql, connection)) {
command.ExecuteNonQuery();
}
connection.Close();
}
return(JsonConvert.SerializeObject(LoadData(), Formatting.None));
} catch (Exception e) { return(JsonConvert.SerializeObject(e.Message, Formatting.None)); }
}
protected bool CheckRealizations(NewRealization x)
{
try {
int count = 0;
string sql = string.Format(@"SELECT COUNT(Id) FROM Realizations WHERE GroupId = {0} AND Leader = {1} AND Duration = {2} AND RealizationDate = '{3}'"
, x.groupId, x.leaderId, x.duration, x.date);
using (SqlConnection connection = new SqlConnection(G.connectionString)) {
connection.Open();
using (SqlCommand command = new SqlCommand(sql, connection)) {
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
count = G.ReadI(reader, 0);
}
}
}
connection.Close();
}
if (count == 0)
{
return(true);
}
else
{
return(false);
}
} catch (Exception e) { return(false); }
}
public string Init()
{
NewRealization x = new NewRealization();
x.id = null;
x.groupId = null;
x.club = G.club;
x.groupName = null;
x.leaderId = null;
x.leaderFirstName = null;
x.leaderLastName = null;
x.duration = 0;
x.date = null;
x.description = null;
x.note = null;
x.substitute = null;
x.type = 0;
x.members = new List <Members>();
return(JsonConvert.SerializeObject(x, Formatting.None));
}
public string Get(int id)
{
try {
string sql = string.Format(@"SELECT r.Id, r.GroupId, g.Club, g.GroupName, r.Leader, u.FirstName, u.LastName, r.Duration, r.RealizationDate, r.Description, r.Note, r.Substitute, r.Type, r.Members FROM Realizations r
LEFT OUTER JOIN Groups g
ON r.GroupId = g.Id
LEFT OUTER JOIN Users u
ON r.Leader = u.UserId
WHERE r.Id = {0}", id);
NewRealization x = new NewRealization();
using (SqlConnection connection = new SqlConnection(G.connectionString)) {
connection.Open();
using (SqlCommand command = new SqlCommand(sql, connection)) {
x = new NewRealization();
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
x.id = G.ReadI(reader, 0);
x.groupId = G.ReadI(reader, 1);
x.club = G.ReadS(reader, 2);
x.groupName = G.ReadS(reader, 3);
x.leaderId = G.ReadI(reader, 4);
x.leaderFirstName = G.ReadS(reader, 5);
x.leaderLastName = G.ReadS(reader, 6);
x.duration = G.ReadI(reader, 7);
x.date = G.ReadS(reader, 8);
x.description = G.ReadS(reader, 9);
x.note = G.ReadS(reader, 10);
x.substitute = G.ReadS(reader, 11);
x.type = G.ReadI(reader, 12);
x.members = JsonConvert.DeserializeObject <List <Members> >(G.ReadS(reader, 13));
}
}
}
connection.Close();
}
return(JsonConvert.SerializeObject(x, Formatting.None));
} catch (Exception e) { return(JsonConvert.SerializeObject(e.Message, Formatting.None)); }
}
public List <NewRealization> LoadData()
{
string sql = @"SELECT r.Id, r.GroupId, g.Club, g.GroupName, r.Leader, u.FirstName, u.LastName, r.Duration, r.RealizationDate, r.Description, r.Note, r.Substitute, r.Type, r.Members FROM Realizations r
LEFT OUTER JOIN Groups g
ON r.GroupId = g.Id
LEFT OUTER JOIN Users u
ON r.Leader = u.UserId
ORDER BY r.Id DESC";
List <NewRealization> xx = new List <NewRealization>();
using (SqlConnection connection = new SqlConnection(G.connectionString)) {
connection.Open();
using (SqlCommand command = new SqlCommand(sql, connection)) {
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
NewRealization x = new NewRealization();
x.id = G.ReadI(reader, 0);
x.groupId = G.ReadI(reader, 1);
x.club = G.ReadS(reader, 2);
x.groupName = G.ReadS(reader, 3);
x.leaderId = G.ReadI(reader, 4);
x.leaderFirstName = G.ReadS(reader, 5);
x.leaderLastName = G.ReadS(reader, 6);
x.duration = G.ReadI(reader, 7);
x.date = G.ReadS(reader, 8);
x.description = G.ReadS(reader, 9);
x.note = G.ReadS(reader, 10);
x.substitute = G.ReadS(reader, 11);
x.type = G.ReadI(reader, 12);
x.members = JsonConvert.DeserializeObject <List <Members> >(G.ReadS(reader, 13));
xx.Add(x);
}
}
}
connection.Close();
}
return(xx);
}
