public async Task DeleteAndCreateData(IEnumerable <QuestionData> datas)
{
var dataList = datas.ToList();
const string deleteSql = "DELETE FROM QuestionDatas WHERE Id IN @Ids";
const string insertSql = "INSERT INTO QuestionDatas VALUES " +
"(@Id, @Input, @Output, @MemoryLimitMb, @TimeLimit, @UpdateTicks)";
EnsureConnectionOpen();
using (var tran = _db.BeginTransaction())
{
await _db.ExecuteAsync(deleteSql, new { Ids = dataList.Select(x => x.Id) }, tran);
foreach (var data in dataList)
{
var command = new SqlCeCommand(insertSql, _db, tran);
command.Parameters.AddWithValue("@Id", data.Id);
command.Parameters.AddWithValue("@Input", data.Input ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@Output", data.Output ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@MemoryLimitMb", data.MemoryLimitMb);
command.Parameters.AddWithValue("@TimeLimit", data.TimeLimit);
command.Parameters.AddWithValue("@UpdateTicks", data.UpdateTicks);
await command.ExecuteNonQueryAsync();
}
tran.Commit();
}
}
public static async Task <bool> CreateEmployee(Employee employee)
{
string sql = "INSERT INTO Employee " +
"(Name, Email, Salary, Gender, ContractType, RegisterDate) " +
"VALUES (@Name, @Email, @Salary, @Gender, @ContractType, @RegisterDate";
SqlCeCommand command = new SqlCeCommand(sql, con);
command.Parameters.Add("@Name", employee.Name);
command.Parameters.Add("@Email", employee.Email);
command.Parameters.Add("@Salary", employee.Salary);
command.Parameters.Add("@Gender", employee.Gender);
command.Parameters.Add("@ContractType", employee.ContractType);
command.Parameters.Add("@RegisterDate", employee.RegisterDate);
await con.OpenAsync();
if (await command.ExecuteNonQueryAsync() > 0)
{
con.Close();
return(true);
}
else
{
con.Close();
return(false);
}
}
public async Task DeleteAndCreateProcess2Code(QuestionP2Code data)
{
const string deleteSql = "DELETE FROM QuestionP2Code WHERE QuestionId = @id ";
const string insertSql = "INSERT INTO QuestionP2Code VALUES " +
"(@QuestionId, @Code, @Language, @RunTimes, @TimeLimitMs, @MemoryLimitMb, @UpdateTicks)";
EnsureConnectionOpen();
using (var tran = _db.BeginTransaction())
{
await _db.ExecuteAsync(deleteSql, new { id = data.QuestionId });
var command = new SqlCeCommand(insertSql, _db, tran);
command.Parameters.AddWithValue("@QuestionId", data.QuestionId);
command.Parameters.AddWithValue("@Code", data.Code);
command.Parameters.AddWithValue("@Language", data.Language);
command.Parameters.AddWithValue("@RunTimes", data.RunTimes);
command.Parameters.AddWithValue("@TimeLimitMs", data.TimeLimitMs);
command.Parameters.AddWithValue("@MemoryLimitMb", data.MemoryLimitMb);
command.Parameters.AddWithValue("@UpdateTicks", data.UpdateTicks);
await command.ExecuteNonQueryAsync();
tran.Commit();
}
}
public async static Task ExecuteNonQueryAsync(string query, Dictionary <string, object> parameters = null)
{
using (var cmd = new SqlCeCommand(query, conn))
{
AddParameters(cmd, parameters);
await cmd.ExecuteNonQueryAsync();
}
}
/// <summary>
/// Creates a ticket
/// </summary>
public async Task <int> CreateTicketAsync(int section, int row, int seat)
{
int newTicketID = MINIMUM_ID - 1; // Default settings for PK are (1,1). ID's < 1 are invalid
using (SqlCeConnection connection = new SqlCeConnection(connectionFilePath))
{
await connection.OpenAsync();
// TODO: Establish unique item
string query = "INSERT INTO Tickets(T_SECTION,T_ROW,T_SEAT) VALUES("
+ section + ", "
+ row + ", "
+ seat + ")";
SqlCeCommand command = new SqlCeCommand();
command.Connection = connection;
command.CommandText = query;
if ((await command.ExecuteNonQueryAsync()) < MINIMUM_ID)
{
// Potential to use ID's < 1 as ERROR Codes
newTicketID = -1;
}
else
{
// Get the last created record's PK scoped to the current connection for thread safety
query = "SELECT @@IDENTITY";
command = new SqlCeCommand();
command.Connection = connection;
command.CommandText = query;
newTicketID = Convert.ToInt32((decimal)command.ExecuteScalar());
}
connection.Close();
}
if ((await AddTicketMappingAsync(newTicketID, MINIMUM_ID, TicketStatus.FREE)) < MINIMUM_ID)
{
// Something Illegal happened
return(newTicketID);
}
return(newTicketID);
}
/// <summary>
/// Deletes a ticket/member mapping from the appropriate table
/// </summary>
private async Task <bool> DeleteTicketMappingAsync(int ticketID, int memberID, TicketStatus ticketStatus)
{
bool success = false;
using (SqlCeConnection connection = new SqlCeConnection(connectionFilePath))
{
await connection.OpenAsync();
string query = BuildDeleteTicketTableQuery(ticketID, memberID, ticketStatus);
SqlCeCommand command = new SqlCeCommand();
command.Connection = connection;
command.CommandText = query;
if ((await command.ExecuteNonQueryAsync()) >= MINIMUM_ID)
{
success = true;
}
connection.Close();
}
return(success);
}
/// <summary>
/// Adds a ticket/member mapping to the appropriate table
/// </summary>
private async Task <int> AddTicketMappingAsync(int ticketID, int memberID, TicketStatus ticketStatus)
{
int newTicketMappingID = 0;
using (SqlCeConnection connection = new SqlCeConnection(connectionFilePath))
{
await connection.OpenAsync();
// TODO: Establish unique item
string query = BuildInsertTicketTableQuery(ticketID, memberID, ticketStatus);
SqlCeCommand command = new SqlCeCommand();
command.Connection = connection;
command.CommandText = query;
if ((await command.ExecuteNonQueryAsync()) < MINIMUM_ID)
{
// Potential to use ID's < 1 as ERROR Codes
newTicketMappingID = -1;
}
else
{
// Get the last created record's PK scoped to the current connection for thread safety
query = "SELECT @@IDENTITY";
command = new SqlCeCommand();
command.Connection = connection;
command.CommandText = query;
newTicketMappingID = Convert.ToInt32((decimal)command.ExecuteScalar());
}
connection.Close();
}
return(newTicketMappingID);
}
/// <summary>
/// Creates a member
/// </summary>
public async Task <int> CreateMemberAsync(string memberName)
{
int newMemberID = MINIMUM_ID - 1; // Default settings for PK are (1,1). ID's < 1 are invalid
using (SqlCeConnection connection = new SqlCeConnection(connectionFilePath))
{
await connection.OpenAsync();
// TODO: Establish unique item
string query = "INSERT INTO Members(NAME) VALUES('" + memberName + "')";
SqlCeCommand command = new SqlCeCommand();
command.Connection = connection;
command.CommandText = query;
if ((await command.ExecuteNonQueryAsync()) < MINIMUM_ID)
{
// Potential to use ID's < 1 as ERROR Codes
newMemberID = -1;
}
else
{
// Get the last created record's PK scoped to the current connection for thread safety
query = "SELECT @@IDENTITY";
command = new SqlCeCommand();
command.Connection = connection;
command.CommandText = query;
newMemberID = Convert.ToInt32((decimal)command.ExecuteScalar());
}
connection.Close();
}
return(newMemberID);
}
public async void ExecuteQuerryAsync(string querry)
{
SqlCeCommand command = new SqlCeCommand(querry, dbConnection);
await command.ExecuteNonQueryAsync();
}