public ValidationResult <List <TableStaffHall> > GetSingleTable()
{
if (sqlConnect.GetConnect)
{
sqlConnect.OpenConn();
}
TableStaffHall tableStaffHall;
ValidationResult <List <TableStaffHall> > result = new ValidationResult <List <TableStaffHall> >()
{
IsValid = true,
ResultObject = new List <TableStaffHall>()
};
try
{
string commPart = "SELECT sh.\"HallId\", s.\"StaffId\" || \'. \' || (\"FullName\").\"FirstName\" || \' \' || (\"FullName\").\"SecondName\" || \' \' || (\"FullName\").\"Patronymic\" as staff " +
"FROM maindb.\"StaffHall\" as sh " +
"INNER JOIN maindb.\"Staff\" as s " +
"ON sh.\"StaffId\" = s.\"StaffId\" " +
"ORDER BY sh.\"HallId\"";
NpgsqlCommand command = new NpgsqlCommand(commPart, sqlConnect.GetNewSqlConn().GetConn);
NpgsqlDataReader readerTable = command.ExecuteReader();
foreach (DbDataRecord dbDataRecord in readerTable)
{
tableStaffHall = new TableStaffHall(
dbDataRecord["HallId"].ToString(),
dbDataRecord["staff"].ToString()
);
result.ResultObject.Add(tableStaffHall);
}
readerTable.Close();
}
catch (PostgresException exp)
{
result = new ValidationResult <List <TableStaffHall> >
{
IsValid = false,
Errors = new List <string> {
exp.SqlState
}
};
}
finally
{
if (!sqlConnect.GetConnect)
{
sqlConnect.CloseConn();
}
}
return(result);
}
public ValidationResult <List <TableHall> > GetSingleTable()
{
if (sqlConnect.GetConnect)
{
sqlConnect.OpenConn();
}
TableHall tableHall;
ValidationResult <List <TableHall> > result = new ValidationResult <List <TableHall> >()
{
IsValid = true,
ResultObject = new List <TableHall>()
};
try
{
string commPart = "SELECT \"HallId\", \"Numbers_of_rows\", \"Number_of_seats_in_a_row\"" +
"FROM maindb.\"Hall\"" +
"ORDER BY \"HallId\"";
NpgsqlCommand command = new NpgsqlCommand(commPart, sqlConnect.GetNewSqlConn().GetConn);
NpgsqlDataReader readerTable = command.ExecuteReader();
foreach (DbDataRecord dbDataRecord in readerTable)
{
tableHall = new TableHall(
dbDataRecord["HallId"].ToString(),
dbDataRecord["Numbers_of_rows"].ToString(),
dbDataRecord["Number_of_seats_in_a_row"].ToString()
);
result.ResultObject.Add(tableHall);
}
readerTable.Close();
}
catch (PostgresException exp)
{
result = new ValidationResult <List <TableHall> >
{
IsValid = false,
Errors = new List <string> {
exp.SqlState
}
};
}
finally
{
if (!sqlConnect.GetConnect)
{
sqlConnect.CloseConn();
}
}
return(result);
}
public ValidationResultString InsertTicketss(TicketsMember ticketsMember)
{
if (sqlConnect.GetConnect)
{
sqlConnect.OpenConn();
}
ValidationResultString result = new ValidationResultString()
{
IsValid = true
};
try
{
string commPart = "INSERT INTO maindb.\"Tickets\" ( \"SessionId\", \"Row\", \"Seat\", \"Zone\", \"Booking\", \"Recall\") VALUES " +
" (@1, @2, @3, @4, @5, @6)";
NpgsqlCommand command = new NpgsqlCommand(commPart, sqlConnect.GetNewSqlConn().GetConn);
command.Parameters.AddWithValue("@1", Convert.ToInt32(ticketsMember.SessionId));
command.Parameters.AddWithValue("@2", Convert.ToInt32(ticketsMember.Row));
command.Parameters.AddWithValue("@3", Convert.ToInt32(ticketsMember.Seat));
command.Parameters.AddWithValue("@4", Convert.ToInt32(ticketsMember.Zone));
command.Parameters.AddWithValue("@5", ticketsMember.Booking);
command.Parameters.AddWithValue("@6", Convert.ToInt32(ticketsMember.Recall));
NpgsqlDataReader readerTable = command.ExecuteReader();
readerTable.Close();
}
catch (Npgsql.PostgresException exp)
{
return(new ValidationResultString
{
Errors = new List <string> {
exp.SqlState
}
});
}
finally
{
if (!sqlConnect.GetConnect)
{
sqlConnect.CloseConn();
}
}
return(result);
}
public ValidationResult <List <TableSession> > GetSingleTable()
{
if (sqlConnect.GetConnect)
{
sqlConnect.OpenConn();
}
TableSession tableSession;
ValidationResult <List <TableSession> > result = new ValidationResult <List <TableSession> >()
{
IsValid = true,
ResultObject = new List <TableSession>()
};
try
{
string commPart = "SELECT \"SessionId\", \"FilmId\", \"Date\",\"Time\", \"HallId\" " +
"FROM maindb.\"Session\"" +
"ORDER BY \"SessionId\"";
NpgsqlCommand command = new NpgsqlCommand(commPart, sqlConnect.GetNewSqlConn().GetConn);
NpgsqlDataReader readerTable = command.ExecuteReader();
foreach (DbDataRecord dbDataRecord in readerTable)
{
tableSession = new TableSession(
dbDataRecord["SessionId"].ToString(),
dbDataRecord["FilmId"].ToString(),
dbDataRecord["Date"].ToString(),
dbDataRecord["Time"].ToString(),
dbDataRecord["HallId"].ToString()
);
result.ResultObject.Add(tableSession);
}
readerTable.Close();
}
catch (PostgresException exp)
{
result = new ValidationResult <List <TableSession> >
{
IsValid = false,
Errors = new List <string> {
exp.SqlState
}
};
}
finally
{
if (!sqlConnect.GetConnect)
{
sqlConnect.CloseConn();
}
}
return(result);
}
public ValidationResult <List <TableTimetable> > GetSingleTable()
{
if (sqlConnect.GetConnect)
{
sqlConnect.OpenConn();
}
TableTimetable tableTimetable;
ValidationResult <List <TableTimetable> > result = new ValidationResult <List <TableTimetable> >()
{
IsValid = true,
ResultObject = new List <TableTimetable>()
};
try
{
string commPart = "SELECT f.\"Title\", ses.\"Date\", ses.\"Time\"+f.\"Duration\" as Time, ses.\"HallId\" FROM maindb.\"Staff\" as s INNER JOIN maindb.\"StaffHall\" as sh ON s.\"StaffId\" = sh.\"StaffId\" INNER JOIN maindb.\"Hall\" as h ON sh.\"HallId\" = h.\"HallId\" INNER JOIN maindb.\"Session\" as ses ON h.\"HallId\" = ses.\"HallId\" INNER JOIN maindb.\"Films\" as f ON ses.\"FilmId\" = f.\"FilmId\" WHERE s.login = '******'";
NpgsqlCommand command = new NpgsqlCommand(commPart, sqlConnect.GetNewSqlConn().GetConn);
NpgsqlDataReader readerTable = command.ExecuteReader();
foreach (DbDataRecord dbDataRecord in readerTable)
{
tableTimetable = new TableTimetable(
dbDataRecord["Title"].ToString(),
dbDataRecord["Date"].ToString(),
dbDataRecord["Time"].ToString(),
dbDataRecord["HallId"].ToString()
);
result.ResultObject.Add(tableTimetable);
}
readerTable.Close();
}
catch (PostgresException exp)
{
result = new ValidationResult <List <TableTimetable> >
{
IsValid = false,
Errors = new List <string> {
exp.SqlState
}
};
}
finally
{
if (!sqlConnect.GetConnect)
{
sqlConnect.CloseConn();
}
}
return(result);
}
public ValidationResult <List <TableTickets> > GetSingleTable()
{
if (sqlConnect.GetConnect)
{
sqlConnect.OpenConn();
}
TableTickets tableTickets;
ValidationResult <List <TableTickets> > result = new ValidationResult <List <TableTickets> >()
{
IsValid = true,
ResultObject = new List <TableTickets>()
};
try
{
string commPart = "SELECT \"TicketID\", \"SessionId\", \"Row\",\"Seat\", \"Zone\", \"Booking\", \"Recall\"" +
"FROM maindb.\"Tickets\"";
NpgsqlCommand command = new NpgsqlCommand(commPart, sqlConnect.GetNewSqlConn().GetConn);
NpgsqlDataReader readerTable = command.ExecuteReader();
foreach (DbDataRecord dbDataRecord in readerTable)
{
tableTickets = new TableTickets(
dbDataRecord["TicketID"].ToString(),
dbDataRecord["SessionId"].ToString(),
dbDataRecord["Row"].ToString(),
dbDataRecord["Seat"].ToString(),
dbDataRecord["Zone"].ToString(),
(bool)dbDataRecord["Booking"],
dbDataRecord["Recall"].ToString()
);
result.ResultObject.Add(tableTickets);
}
readerTable.Close();
}
catch (PostgresException exp)
{
result = new ValidationResult <List <TableTickets> >
{
IsValid = false,
Errors = new List <string> {
exp.SqlState
}
};
}
finally
{
if (!sqlConnect.GetConnect)
{
sqlConnect.CloseConn();
}
}
return(result);
}
public ValidationResult <List <TableFilms> > GetSingleTable()
{
if (sqlConnect.GetConnect)
{
sqlConnect.OpenConn();
}
TableFilms tableFilms;
ValidationResult <List <TableFilms> > result = new ValidationResult <List <TableFilms> >()
{
IsValid = true,
ResultObject = new List <TableFilms>()
};
try
{
string commPart = "SELECT \"FilmId\", \"Title\", \"Country\",\"Producer\", \"Duration\", \"ReleaseDate\", \"Genre\", \"BasePrice\" " +
"FROM maindb.\"Films\"" +
"ORDER BY \"FilmId\"";
NpgsqlCommand command = new NpgsqlCommand(commPart, sqlConnect.GetNewSqlConn().GetConn);
NpgsqlDataReader readerTable = command.ExecuteReader();
foreach (DbDataRecord dbDataRecord in readerTable)
{
tableFilms = new TableFilms(
dbDataRecord["FilmId"].ToString(),
dbDataRecord["Title"].ToString(),
dbDataRecord["Country"].ToString(),
dbDataRecord["Producer"].ToString(),
dbDataRecord["Duration"].ToString(),
dbDataRecord["ReleaseDate"].ToString(),
dbDataRecord["Genre"].ToString(),
dbDataRecord["BasePrice"].ToString()
);
result.ResultObject.Add(tableFilms);
}
readerTable.Close();
}
catch (PostgresException exp)
{
result = new ValidationResult <List <TableFilms> >
{
IsValid = false,
Errors = new List <string> {
exp.SqlState
}
};
}
finally
{
if (!sqlConnect.GetConnect)
{
sqlConnect.CloseConn();
}
}
return(result);
}
public ValidationResult <List <TableStaff> > GetSingleTable()
{
if (sqlConnect.GetConnect)
{
sqlConnect.OpenConn();
}
TableStaff tableStaff;
ValidationResult <List <TableStaff> > result = new ValidationResult <List <TableStaff> >()
{
IsValid = true,
ResultObject = new List <TableStaff>()
};
try
{
string commPart = "SELECT \"StaffId\", (\"FullName\").\"FirstName\", (\"FullName\").\"SecondName\", (\"FullName\").\"Patronymic\", \"Position\", \"BirthDate\",\"EntryDate\", us.IDLogin, us.Vacant " +
"FROM maindb.\"Staff\" as s " +
"INNER JOIN login.userpass as us " +
"ON s.login = us.IDLogin";
NpgsqlCommand command = new NpgsqlCommand(commPart, sqlConnect.GetNewSqlConn().GetConn);
NpgsqlDataReader readerTable = command.ExecuteReader();
foreach (DbDataRecord dbDataRecord in readerTable)
{
tableStaff = new TableStaff(
dbDataRecord["StaffId"].ToString(),
dbDataRecord["FirstName"].ToString(),
dbDataRecord["SecondName"].ToString(),
dbDataRecord["Patronymic"].ToString(),
dbDataRecord["Position"].ToString(),
dbDataRecord["BirthDate"].ToString(),
dbDataRecord["EntryDate"].ToString(),
dbDataRecord["IDLogin"].ToString(),
dbDataRecord["Vacant"].ToString()
);
result.ResultObject.Add(tableStaff);
}
readerTable.Close();
}
catch (PostgresException exp)
{
result = new ValidationResult <List <TableStaff> >
{
IsValid = false,
Errors = new List <string> {
exp.SqlState
}
};
}
finally
{
if (!sqlConnect.GetConnect)
{
sqlConnect.CloseConn();
}
}
return(result);
}
public ValidationResult <List <TableLogin> > Authorization(LoginMember loginMember)
{
if (sqlConnect.GetConnect)
{
sqlConnect.OpenConn();
}
TableLogin tableLogin;
ValidationResult <List <TableLogin> > result = new ValidationResult <List <TableLogin> >()
{
IsValid = true,
ResultObject = new List <TableLogin>()
};
try
{
string commPart = "SELECT * " +
"FROM login.UserPass " +
"WHERE IDLogin = @1 AND Pass = @2";
NpgsqlCommand command = new NpgsqlCommand(commPart, sqlConnect.GetNewSqlConn().GetConn);
command.Parameters.AddWithValue("@1", Convert.ToString(loginMember.Login));
command.Parameters.AddWithValue("@2", Convert.ToString(loginMember.Password));
NpgsqlDataReader readerTable = command.ExecuteReader();
foreach (DbDataRecord dbDataRecord in readerTable)
{
tableLogin = new TableLogin(
dbDataRecord["IDLogin"].ToString(),
dbDataRecord["pass"].ToString(),
dbDataRecord["vacant"].ToString()
);
result.ResultObject.Add(tableLogin);
}
readerTable.Close();
}
catch (PostgresException exp)
{
result = new ValidationResult <List <TableLogin> >
{
IsValid = false,
Errors = new List <string> {
exp.SqlState
}
};
}
finally
{
if (!sqlConnect.GetConnect)
{
sqlConnect.CloseConn();
}
}
return(result);
}
