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 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));
}
}
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 void SetRegistration(string userID, int contestID, ContestRegistration registration)
{
string command = "UPDATE Rights SET SystemRights = @ro, IsInvisible = @ii WHERE UserID = @uid" +
" AND ContestID = @cid";
ParameterAdder pa = delegate(SqlCommand comm, object customParam)
{
ContestRegistration cr = (ContestRegistration)customParam;
comm.Parameters.AddWithValue("@ro", cr.Rights);
comm.Parameters.AddWithValue("@ii", cr.IsInvisible);
};
pa += FillMultipleIDs;
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
q.ExecuteNonQuery(command, pa, new object[] { userID, contestID });
}
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 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 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 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());
}
public override int GetSubmissionsCount(SubmissionsFilter filter)
{
string command = "SELECT COUNT(*) FROM (SELECT Submissions.ID 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";
using (MsSqlQuery q = new MsSqlQuery(_connectionString))
return((int)q.ExecuteScalar(command, addFilterParams, filter));
}
