public override ContestRegistration GetRegistration(string userID, int contestID)
{
string command = "SELECT SystemRights,IsInvisible FROM Rights WHERE ContestID = @cid AND UserID = @uid";
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
using (SqlDataReader rdr = q.ExecuteReader(command, FillMultipleIDs,
new object[] { userID, contestID }))
return(RegistrationFromReader(rdr));
}
public override Contest GetContest(int contestID)
{
string command = "SELECT * FROM Contests WHERE ID=@id";
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
using (SqlDataReader rdr = q.ExecuteReader(command, FillID, contestID))
{
rdr.Read();
return(FromReader(rdr));
}
}
public override Language GetLanguage(string languageID)
{
string command = "SELECT * FROM Languages WHERE ID=@id";
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
using (SqlDataReader rdr = q.ExecuteReader(command, FillID, languageID))
{
rdr.Read();
return(FromReader(rdr));
}
}
public override Submission GetSubmission(int submissionID)
{
string command = "SELECT ID,ProblemID,UserID,LanguageID,Time,Outcome FROM Submissions " +
"WHERE ID=@id";
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
using (SqlDataReader rdr = q.ExecuteReader(command, FillID, submissionID))
{
rdr.Read();
return(FromReader(rdr));
}
}
public override Contest[] GetContests(ContestTime type)
{
string[] conds = new string[]
{
"(Beginning <= GETDATE() AND Ending >= GETDATE())",
"Ending < GETDATE()",
"Beginning > GETDATE()"
};
string command = "SELECT * FROM Contests WHERE ";
bool first = true;
int[] inds = new int[] { 1, 2, 4 };
for (int i = 0; i < 3; i++)
{
if (((int)type & inds[i]) != 0)
{
if (!first)
{
command += " OR ";
}
first = false;
command += conds[i];
}
}
List <Contest> ret = new List <Contest>();
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
using (SqlDataReader rdr = q.ExecuteReader(command, null, null))
{
while (rdr.Read())
{
ret.Add(FromReader(rdr));
}
}
ret.Sort(delegate(Contest a, Contest b)
{
if (a.Time > b.Time)
{
return(1);
}
if (a.Time < b.Time)
{
return(-1);
}
return(0);
});
return(ret.ToArray());
}
public override Problem GetProblem(int problemID)
{
string command = String.Format("{0} WHERE ID=@id", SELECT_STRING);
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
{
using (SqlDataReader rdr = q.ExecuteReader(command, FillID, problemID))
{
rdr.Read();
return(FromReader(rdr));
}
}
}
public override Test GetTest(int problemID, int testNumber)
{
string command = "SELECT ProblemID,TestNumber,Description,Points FROM Tests WHERE ProblemID=@pid AND TestNumber=@tn";
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
{
using (SqlDataReader rdr = q.ExecuteReader(command, FillID, new int[] { problemID, testNumber }))
{
rdr.Read();
return(FromReader(rdr));
}
}
}
public override byte[] GetCheckerBytes(int problemID)
{
string command = "SELECT CheckerBytes FROM Problems WHERE ID=@id";
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
using (SqlDataReader rdr = q.ExecuteReader(command, FillID, problemID))
{
rdr.Read();
long len = rdr.GetBytes(0, 0, null, 0, 0);
byte[] buf = new byte[len];
rdr.GetBytes(0, 0, buf, 0, buf.Length);
return(buf);
}
}
public override User GetUser(string userID)
{
string command = "SELECT * FROM Users WHERE ID = @id";
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
using (SqlDataReader rdr = q.ExecuteReader(command, FillID, userID))
{
if (rdr.Read())
{
return(UserFromReader(rdr));
}
return(null);
}
}
public override Message GetMessage(int messageID)
{
string command = "SELECT * FROM Messages WHERE ID = @id";
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
using (SqlDataReader rdr = q.ExecuteReader(command, delegate(SqlCommand comm, object customParam)
{
comm.Parameters.AddWithValue("@id", messageID);
}, null))
{
rdr.Read();
return(FromReader(rdr));
}
}
byte[] GetTestBytes(string field, int problemID, int testNumber)
{
string command = String.Format("SELECT {0} FROM Tests WHERE ProblemID=@pid AND TestNumber=@tn", field);
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
using (SqlDataReader rdr = q.ExecuteReader(command, FillID, new int[] { problemID, testNumber }))
{
rdr.Read();
long len = rdr.GetBytes(0, 0, null, 0, 0);
byte[] buf = new byte[len];
rdr.GetBytes(0, 0, buf, 0, buf.Length);
return(buf);
}
}
public override User[] GetUsers()
{
string command = "SELECT * FROM Users";
List <User> us = new List <User>();
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
using (SqlDataReader rdr = q.ExecuteReader(command, null, null))
{
while (rdr.Read())
{
us.Add(UserFromReader(rdr));
}
}
return(us.ToArray());
}
public override Language[] GetLanguages()
{
string command = "SELECT * FROM Languages";
List <Language> tmp = new List <Language>();
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
using (SqlDataReader sr = q.ExecuteReader(command, null, null))
{
while (sr.Read())
{
tmp.Add(FromReader(sr));
}
}
return(tmp.ToArray());
}
public override Problem[] GetProblems(int contestID)
{
string command = String.Format("{0} WHERE ContestID=@id ORDER BY ShortName", SELECT_STRING);
List <Problem> problems = new List <Problem>();
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
{
using (SqlDataReader rdr = q.ExecuteReader(command, FillID, contestID))
{
while (rdr.Read())
{
problems.Add(FromReader(rdr));
}
}
}
return(problems.ToArray());
}
public override Submission[] GetSubmissions(SubmissionsFilter filter)
{
string command = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID DESC)" +
" AS RowID,ID,ProblemID,UserID,LanguageID,Time,Outcome FROM (SELECT Submissions.ID,Submissions.ProblemID," +
"Submissions.UserID,Submissions.LanguageID,Submissions.Time,Submissions.Outcome " +
"FROM Submissions,Contests,Problems" +
" WHERE Submissions.ProblemID=Problems.ID AND Problems.ContestID = Contests.ID AND Contests.ID=@cid";
if (filter.RequiredUserID)
{
command += " AND Submissions.UserID=@uid";
}
if (filter.RequiredOutcome)
{
command += " AND Submissions.Outcome=@ou";
}
if (filter.RequiredProblemID)
{
command += " AND Submissions.ProblemID = @pid";
}
command += ") AS T2) AS T";
if (filter.RequiredPaging)
{
command += string.Format(" WHERE RowID between {0} and {1}", filter.From, filter.To);
}
else
{
command += " ORDER BY RowID DESC";
}
List <Submission> list = new List <Submission>();
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
using (SqlDataReader rdr = q.ExecuteReader(command.ToString(), addFilterParams, filter))
{
while (rdr.Read())
{
list.Add(FromReader(rdr));
}
}
return(list.ToArray());
}
public override Message[] GetMessages(MessagesFilter filter)
{
List <Message> messages = new List <Message>();
string command = null;
if (filter.ReguiredProblemID)
{
command = "SELECT * FROM Messages WHERE ProblemID = @pid AND Type = @type";
}
else
{
command = "SELECT Messages.ID,Messages.ProblemID,Messages.UserID,Messages.Time,Messages.Type," +
"Messages.ContestantMessage,Messages.JuryMessage FROM Messages,Contests,Problems " +
"WHERE Messages.Type = @type AND Messages.ProblemID = Problems.ID AND Problems.ContestID = Contests.ID AND Contests.ID = @cid";
}
if (filter.RequiredEmptyJuryMessage)
{
command += " AND Messages.JuryMessage = ''";
}
else
{
command += " AND Messages.JuryMessage != ''";
}
if (filter.RequiredUserID)
{
command += " AND Messages.UserID=@uid";
}
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
using (SqlDataReader rdr = q.ExecuteReader(command, delegate(SqlCommand comm, object customParam)
{
comm.Parameters.AddWithValue("@type", filter.Type.ToString());
comm.Parameters.AddWithValue("@pid", filter.ProblemID);
comm.Parameters.AddWithValue("@cid", filter.ContestID);
if (filter.RequiredUserID)
{
comm.Parameters.AddWithValue("@uid", filter.UserID);
}
}, null))
while (rdr.Read())
{
messages.Add(FromReader(rdr));
}
return(messages.ToArray());
}
public override Test[] GetTests(int problemID)
{
string command = "SELECT ProblemID,TestNumber,Description,Points FROM Tests WHERE ProblemID=@pid";
List <Test> tests = new List <Test>();
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
{
using (SqlDataReader rdr = q.ExecuteReader(command, delegate(SqlCommand comm, object p)
{
comm.Parameters.AddWithValue("@pid", problemID);
}, null))
{
while (rdr.Read())
{
tests.Add(FromReader(rdr));
}
}
}
return(tests.ToArray());
}