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(); }