private void UpdateVenue(Guid venueId) { if (!_isChanged) { return; } string venueName = txt_Name.Text; if (string.IsNullOrWhiteSpace(venueName)) { MessageBox.Show("Cannot enter empty venue name!"); return; } SqlHelper helper = new SqlHelper(_tournament.Database); Venue venue = _context.Venues.First(v => v.VenueId == venueId); venue.Name = venueName; venue.SpecialNeedsVenue = chk_SpecialNeeds.Checked; venue.Active = chk_Active.Checked; NonQueryResult result = helper.UpdateVenue(venue); _context.Venues = _context.Venues.Where(v => v.VenueId != venueId).ToList(); _context.Venues.Add(venue); ReloadVenues(venue, ActionType.Update); _isChanged = false; }
/// <inheritdoc /> public override async Task <List <NonQueryResult> > ExecuteTransaction(IsolationLevel isolationLevel, params Query[] queries) { List <NonQueryResult> results = new List <NonQueryResult>(); SQLiteTransaction transaction = conn.BeginTransaction(isolationLevel); foreach (Query query in queries) { try { NonQueryResult res = await ExecuteNonQuery(query); results.Add(res); } catch (Exception ex) { transaction.Rollback(); throw ex; } } transaction.Commit(); return(results); }
private void SaveNewSpeaker() { string speakerName = txt_Name.Text; if (string.IsNullOrWhiteSpace(speakerName)) { MessageBox.Show("Name cannot be empty!"); return; } SqlHelper helper = new SqlHelper(_tournament.Database); Institution institution = (Institution)cmb_Institution.SelectedItem; bool specialNeeds = chk_SpecialNeeds.Checked; bool active = chk_Active.Checked; Speaker s = new Speaker() { Active = active, InstitutionId = institution.InstitutionId, Name = speakerName, SpecialNeeds = specialNeeds }; NonQueryResult result = helper.InsertSpeaker(s); _context.Speakers.Add(s); ReloadSpeakers(s, ActionType.Create); CreateNewSpeaker(); }
/// <inheritdoc /> public override async Task <List <NonQueryResult> > ExecuteTransaction(IsolationLevel isolationLevel, params Query[] queries) { List <NonQueryResult> results = new List <NonQueryResult>(); SqlTransaction transaction = conn.BeginTransaction(isolationLevel); foreach (Query query in queries) { try { SqlCommand cmd = GetCommand(query); cmd.Connection = conn; cmd.Transaction = transaction; int rowsAffected = await cmd.ExecuteNonQueryAsync(); NonQueryResult res = new NonQueryResult(rowsAffected, -1); results.Add(res); } catch (Exception ex) { transaction.Rollback(); throw ex; } } transaction.Commit(); return(results); }
public virtual bool Remove(TTargetEntity item) { // parameter bindings IDictionary <string, object> parameters = new Dictionary <string, object>(); parameters.Add("@JoinColumn", RowDataGatewayRegistry <TMappedBy> .GetRowDataGateway().PrimaryKey.Get(mappedBy)); parameters.Add("@InverseJoinColumn", RowDataGatewayRegistry <TTargetEntity> .GetRowDataGateway().PrimaryKey.Get(item)); // run command NonQueryResult result = SimpleQuery.ExecuteNonQuery(RowDataGatewayRegistry <TMappedBy> .GetRowDataGateway().DatabaseName, CommandType.Text, removeSql, removeParameterTemplates, parameters, false); // remove from collection bool removedItem = values.Remove(item); if (result.RowsAffected == 1) { if (removedItem) { return(true); } throw new DataException(SR.GetString(SR.ManyToMany_Db_Remove_NonMember)); } else if (result.RowsAffected == 0) { if (!removedItem) { return(false); } throw new DataException(SR.GetString(SR.ManyToMany_Collection_Remove_NonMember)); } else { throw new DataException(SR.GetString(SR.ManyToMany_Bad_Delete_Count, new object[] { result.RowsAffected, removedItem ? 1 : 0 })); } }
private void SaveNewJudge() { string judgeName = txt_Name.Text; if (string.IsNullOrWhiteSpace(judgeName)) { MessageBox.Show("Name cannot be empty!"); return; } Guid?institutionId = default(Guid?); bool hasInstitution = chk_HasInstitution.Checked; if (hasInstitution) { Institution institution = (Institution)cmb_Institution.SelectedItem; institutionId = institution.InstitutionId; } Judge j = new Judge() { InstitutionId = institutionId, Name = judgeName }; SqlHelper helper = new SqlHelper(_tournament.Database); NonQueryResult result = helper.InsertJudge(j); _context.Judges.Add(j); ReloadJudges(j, ActionType.Create); CreateNewJudge(); }
private void SaveNewInstitution() { string institutionName = txt_Name.Text; if (string.IsNullOrWhiteSpace(institutionName)) { MessageBox.Show("Name cannot be empty!"); return; } if (_context.Institutions.Any(x => x.Name.Equals(institutionName))) { MessageBox.Show("This institution has already been entered!"); return; } Institution i = new Institution() { Name = institutionName }; SqlHelper helper = new SqlHelper(_tournament.Database); NonQueryResult result = helper.InsertInstitution(i); _context.Institutions.Add(i); ReloadInstitutions(i, ActionType.Create); CreateNewInstitution(); }
private void SaveNewVenue() { string venueName = txt_Name.Text; if (string.IsNullOrWhiteSpace(venueName)) { MessageBox.Show("Venue name cannot be empty!"); return; } if (_context.Venues.Any(ven => ven.Name == venueName)) { MessageBox.Show("Venue already exists!"); return; } Venue v = new Venue() { SpecialNeedsVenue = chk_SpecialNeeds.Checked, Name = venueName }; SqlHelper helper = new SqlHelper(_tournament.Database); NonQueryResult result = helper.InsertVenue(v); _context.Venues.Add(v); ReloadVenues(v, ActionType.Create); CreateNewVenue(); }
public async Task DeleteOne(DbClient client) { Query query = client.GetQueryProvider().Delete("people").Where("id", 4); NonQueryResult result = await client.ExecuteNonQuery(query); Assert.AreEqual(1, result.RowsAffected); client.Dispose(); }
private void DeleteSpeaker(Guid speakerId) { SqlHelper helper = new SqlHelper(_tournament.Database); NonQueryResult result = helper.DeleteSpeaker(speakerId); Speaker toBeDeleted = _context.Speakers.First(s => s.SpeakerId == speakerId); _context.Speakers = _context.Speakers.Where(s => s.SpeakerId != speakerId).ToList(); ReloadSpeakers(toBeDeleted, ActionType.Delete); }
/// <summary> /// Executes a SQL command using the specified retry policy and returns a result defined by the specified type <typeparamref name="T"/> /// </summary> /// <typeparam name="T">Either IDataReader, XmlReader or any .NET type defining the type of result to be returned.</typeparam> /// <param name="command">A SqlCommand object containing the SQL command to be executed.</param> /// <param name="retryPolicy">The retry policy defining whether to retry a command if a connection fails while executing the command.</param> /// <param name="behavior">Provides a description of the results of the query and its effect on the database.</param> /// <returns>An instance of an IDataReader, XmlReader or any .NET object containing the result.</returns> private static T ExecuteCommand <T>(IDbCommand command, CommandBehavior behavior = CommandBehavior.Default) { // Make sure the command has been associated with a valid connection. If not, associate it with an opened SQL connection. if (command.Connection == null) { // Open a new connection and assign it to the command object. command.Connection = GetSqlConnection(); } // Verify whether or not the connection is valid and is open. This code may be retried therefore // it is important to ensure that a connection is re-established should it have previously failed. if (command.Connection.State != ConnectionState.Open) { command.Connection.Open(); } Type resultType = typeof(T); if (resultType.IsAssignableFrom(typeof(IDataReader))) { return((T)command.ExecuteReader(behavior)); } if (resultType.IsAssignableFrom(typeof(XmlReader))) { if (command is SqlCommand) { object result = null; XmlReader xmlReader = (command as SqlCommand).ExecuteXmlReader(); // Implicit conversion from XmlReader to <T> via an intermediary object. result = xmlReader; return((T)result); } throw new NotSupportedException(); } if (resultType == typeof(NonQueryResult)) { NonQueryResult result = new NonQueryResult(); result.RecordsAffected = command.ExecuteNonQuery(); return((T)Convert.ChangeType(result, resultType)); } else { object result = command.ExecuteScalar(); if (result != null) { return((T)Convert.ChangeType(result, resultType)); } return(default(T)); } }
private void DeleteVenue(Guid venueId) { SqlHelper helper = new SqlHelper(_tournament.Database); NonQueryResult result = helper.DeleteVenue(venueId); Venue toBeDeleted = _context.Venues.First(v => v.VenueId == venueId); _context.Venues = _context.Venues.Where(v => v.VenueId != venueId).ToList(); ReloadVenues(toBeDeleted, ActionType.Delete); }
private void DeleteInstitution(Guid institutionId) { SqlHelper helper = new SqlHelper(_tournament.Database); NonQueryResult result = helper.DeleteInstitution(institutionId); Institution toBeDeleted = _context.Institutions.First(i => i.InstitutionId == institutionId); _context.Institutions = _context.Institutions.Where(i => i.InstitutionId != institutionId).ToList(); ReloadInstitutions(toBeDeleted, ActionType.Delete); }
private void DeleteJudge(Guid judgeId) { SqlHelper helper = new SqlHelper(_tournament.Database); NonQueryResult result = helper.DeleteJudge(judgeId); Judge toBeDeleted = _context.Judges.First(j => j.JudgeId == judgeId); _context.Judges = _context.Judges.Where(j => j.JudgeId != judgeId).ToList(); ReloadJudges(toBeDeleted, ActionType.Delete); }
public async Task AddKid(HttpRequest req) { req.SetContentType(ContentType.Json); UserSession session = req.Session as UserSession; User user = await session.GetUser(); if (user == null) { req.SetStatusCode(HttpStatusCode.Unauthorized); await req.Close(); return; } if (!await req.HasPOST("name", "birthday", "gender")) { req.SetStatusCode(HttpStatusCode.BadRequest); await req.Close(); return; } string name = await req.POST("name"); DateTime birthday; int gender; if (!DateTime.TryParse(await req.POST("birthday"), out birthday) || !int.TryParse(await req.POST("gender"), out gender) || (gender != 0 && gender != 1)) { req.SetStatusCode(HttpStatusCode.BadRequest); await req.Close(); return; } InsertQuery query = new InsertQuery("kids"); query.Value("name", name) .Value("birthday", birthday) .Value("gender", gender) .Value("parent_id", user.ID); NonQueryResult result = await Program.MySql().ExecuteNonQuery(query); string url = await req.POST("redirect", "/profile"); await req.Redirect(url); }
public async Task UpdateOne(DbClient client) { UpdateQuery query = client.GetQueryProvider().Update("people"); query.Where("id", 2); query.Set("name", "Pepe"); NonQueryResult res = await client.ExecuteNonQuery(query); Assert.AreEqual(1, res.RowsAffected); client.Dispose(); }
public async Task CreateTable(DbClient client) { await client.DropTableIfExists("people"); CreateTableQuery query = client.GetQueryProvider().CreateTable("people"); query.Field <int>("id", fieldProperties: FieldProperties.PrimaryKey | FieldProperties.AutoIncrement) .Field <string>("name") .Field <DateTime>("birthday"); NonQueryResult res = await client.ExecuteNonQuery(query); client.Dispose(); }
public async Task InsertOne(DbClient client) { Query query = client.GetQueryProvider().Query("INSERT INTO people (name) VALUES ('Steve')"); NonQueryResult result = await client.ExecuteNonQuery(query); Assert.AreEqual(1, result.RowsAffected); if (!(client is SqlServer.SqlServerClient)) { Assert.AreEqual(4, result.LastInsertedId); } client.Dispose(); }
private void UpdateJudge(Guid judgeId) { if (!_isChanged) { return; } string judgeName = txt_Name.Text; if (string.IsNullOrWhiteSpace(judgeName)) { MessageBox.Show("Cannot enter empty judge name!"); return; } Guid?institutionId = default(Guid?); bool hasInstitution = chk_HasInstitution.Checked; if (hasInstitution) { Institution i = (Institution)cmb_Institution.SelectedItem; institutionId = i.InstitutionId; } bool active = chk_Active.Checked; Judge judge = _context.Judges.First(j => j.JudgeId == judgeId); judge.Name = judgeName; judge.InstitutionId = institutionId; judge.Active = active; SqlHelper helper = new SqlHelper(_tournament.Database); NonQueryResult result = helper.UpdateJudge(judge); _context.Judges = _context.Judges.Where(j => j.JudgeId != judgeId).ToList(); _context.Judges.Add(judge); ReloadJudges(judge, ActionType.Update); _isChanged = false; }
async Task <bool> ScheduleEvent(int eventId, string dateTime, string recurrence) { DateTime date; EventRecurrence rec; if (!DateTime.TryParse(dateTime, out date) || !Enum.TryParse(recurrence, true, out rec)) { return(false); } InsertQuery query = new InsertQuery("scheduled_events"); query.Value("event_id", eventId) .Value("next_time", date) .Value("recurrence", rec.ToString().ToLower()); NonQueryResult result = await Program.MySql().ExecuteNonQuery(query); return(result.RowsAffected == 1); }
public async Task SerializeInsert(DbClient client) { Person p = new Person() { Name = "Mark", Birthday = DateTime.Now.Date }; InsertQuery <Person> q = client.GetQueryProvider().Insert <Person>(p); NonQueryResult res = await client.Insert(p); Assert.AreEqual(1, res.RowsAffected); if (!(client is SqlServer.SqlServerClient)) { Assert.AreEqual(5, res.LastInsertedId); } client.Dispose(); }
public async Task GetValueFormatting(DbClient client) { PersonName p = new PersonName() { NameAscii = Encoding.ASCII.GetBytes("Cool Dude") }; NonQueryResult res = await client.Insert(p); Assert.AreEqual(1, res.RowsAffected); if (!(client is SqlServer.SqlServerClient)) { Assert.AreEqual(7, res.LastInsertedId); } string name = await client.ExecuteScalar <string>("SELECT name FROM people WHERE id=7"); Assert.AreEqual("Cool Dude", name); client.Dispose(); }
public async Task Insert(DbClient client) { Query q1 = client.GetQueryProvider().Insert("people") .Value("name", "John").Value("birthday", DateTime.Parse("2018-05-26 16:22:54")); Query q2 = client.GetQueryProvider().Insert("people") .Value("name", "Mary").Value("birthday", DateTime.Parse("2018-05-26 16:23:04")); Query q3 = client.GetQueryProvider().Insert("people") .Value("name", "Pete").Value("birthday", DateTime.Parse("2018-05-26 16:23:07")); NonQueryResult r1 = await client.ExecuteNonQuery(q1); Assert.AreEqual(1, r1.RowsAffected); if (!(client is SqlServer.SqlServerClient)) { Assert.AreEqual(1, r1.LastInsertedId); } NonQueryResult r2 = await client.ExecuteNonQuery(q2); Assert.AreEqual(1, r2.RowsAffected); if (!(client is SqlServer.SqlServerClient)) { Assert.AreEqual(2, r2.LastInsertedId); } NonQueryResult r3 = await client.ExecuteNonQuery(q3); Assert.AreEqual(1, r3.RowsAffected); if (!(client is SqlServer.SqlServerClient)) { Assert.AreEqual(3, r3.LastInsertedId); } client.Dispose(); }
private void UpdateSpeaker(Guid speakerId) { if (!_isChanged) { return; } string speakerName = txt_Name.Text; if (string.IsNullOrWhiteSpace(speakerName)) { MessageBox.Show("Cannot enter empty speaker name!"); return; } SqlHelper helper = new SqlHelper(_tournament.Database); Institution institution = (Institution)cmb_Institution.SelectedItem; bool specialNeeds = chk_SpecialNeeds.Checked; bool active = chk_Active.Checked; Speaker temp = new Speaker(); temp.SpeakerId = speakerId; temp.Active = active; temp.Name = speakerName; temp.InstitutionId = institution.InstitutionId; temp.SpecialNeeds = specialNeeds; NonQueryResult result = helper.UpdateSpeaker(temp); _context.Speakers = _context.Speakers.Where(s => s.SpeakerId != speakerId).ToList(); _context.Speakers.Add(temp); ReloadSpeakers(temp, ActionType.Update); _isChanged = false; }
public static async Task <bool> UpdatePassword(User user, string password, string password2) { if (password != password2) { return(false); } Monitor.Enter(coherenceLock); string salt = GenerateSalt(); string passwordHash = GetPasswordHash(password, salt); UpdateQuery <User> query = new UpdateQuery <User>(); query.Where("user_id", user.ID); query.Set("password", passwordHash) .Set("salt", salt); NonQueryResult res = await Program.MySql().ExecuteNonQuery(query); Monitor.Exit(coherenceLock); return(res.RowsAffected == 1); }
private void UpdateInstitution(Guid institutionId) { if (!_isChanged) { return; } string institutionName = txt_Name.Text; if (string.IsNullOrWhiteSpace(institutionName)) { MessageBox.Show("Cannot enter empty institution name!"); return; } if (_context.Institutions.Any(x => x.Name.Equals(institutionName))) { MessageBox.Show("This institution has already been entered!"); return; } Institution temp = new Institution(); temp.InstitutionId = institutionId; temp.Name = institutionName; SqlHelper helper = new SqlHelper(_tournament.Database); NonQueryResult result = helper.UpdateInstitution(temp); _context.Institutions = _context.Institutions.Where(i => i.InstitutionId != institutionId).ToList(); _context.Institutions.Add(temp); ReloadInstitutions(temp, ActionType.Update); _isChanged = false; }
/// <summary> /// Registers a user and returns his ID. /// <para>Returns -1 if the email is already taken.</para> /// </summary> /// <param name="email"></param> /// <param name="password"></param> /// <param name="fullName"></param> /// <param name="role"></param> /// <param name="credits"></param> /// <returns></returns> public static async Task <long> RegisterUser(string email, string password, string fullName, int role, string address = "", int credits = 0) { Monitor.Enter(coherenceLock); if (await EmailTaken(email)) { Monitor.Exit(coherenceLock); return(-1); } string salt = GenerateSalt(); string passwordHash = GetPasswordHash(password, salt); InsertQuery query = new InsertQuery("users"); query.Value("email", email) .Value("password", passwordHash) .Value("salt", salt) .Value("full_name", fullName) .Value("role", role) .Value("address", address) .Value("credits", credits); NonQueryResult result = await Program.MySql().ExecuteNonQuery(query); if (result.RowsAffected != 1) { Console.WriteLine("Tried to register user and got rows affected: " + result.RowsAffected); Console.WriteLine(query.QueryString()); } Monitor.Exit(coherenceLock); return(result.LastInsertedId); }
/// <summary> /// Executes a SQL command and returns the number of rows affected. /// </summary> /// <param name="command">A SqlCommand object containing the SQL command to be executed.</param> /// <returns>The number of rows affected.</returns> private static int ExecuteCommand(IDbCommand command) { NonQueryResult result = ExecuteCommand <NonQueryResult>(command); return(result.RecordsAffected); }
public async Task CreateEvent(HttpRequest req) { UserSession session = req.Session as UserSession; User user = await session.GetUser(); if (user == null) { req.SetStatusCode(HttpStatusCode.Unauthorized); await req.Close(); return; } int eventId = Program.Chance().Natural(); if (!await req.HasPOST("title", "description", "price", "duration", "address", "datetime", "recurrence")) { req.SetStatusCode(HttpStatusCode.BadRequest); await req.Close(); return; } string title = await req.POST("title"); string description = await req.POST("description"); int price; int duration; string address = await req.POST("address"); int genders = 2; Location location = await Google.Geocode(address); int age_min = 4; int age_max = 17; string scheduledTime = await req.POST("datetime"); string recurrence = await req.POST("recurrence"); int.TryParse(await req.POST("genders"), out genders); int.TryParse(await req.POST("age_min"), out age_min); int.TryParse(await req.POST("age_max"), out age_max); if (!int.TryParse(await req.POST("price"), out price) || !int.TryParse(await req.POST("duration"), out duration)) { req.SetStatusCode(HttpStatusCode.BadRequest); await req.Close(); return; } // First try scheduling if (location == null || !await ScheduleEvent(eventId, scheduledTime, recurrence)) { if (location == null) { Console.WriteLine("Bad event location"); } req.SetStatusCode(HttpStatusCode.NotAcceptable); await req.Close(); return; } if (await req.HasPOST("image")) { MemoryStream imgStream = await req.GetContentData("image"); string directory = Options.StoragePath("eventimg"); string filePath = string.Format("{0}/{1}.png", directory, eventId); Directory.CreateDirectory(directory); byte[] buffer = new byte[imgStream.Length]; await imgStream.ReadAsync(buffer, 0, buffer.Length); using (FileStream writer = File.OpenWrite(filePath)) { await writer.WriteAsync(buffer, 0, buffer.Length); await writer.FlushAsync(); } } InsertQuery query = new InsertQuery("events"); query.Value("organizer_id", user.ID) .Value("event_id", eventId) .Value("title", title) .Value("description", description) .Value("price", price) .Value("lat", location.Latitude) .Value("lng", location.Longitude) .Value("address", address) .Value("duration", duration) .Value("age_min", age_min) .Value("age_max", age_max) .Value("genders", genders); NonQueryResult result = await Program.MySql().ExecuteNonQuery(query); int category; if (await req.HasPOST("category") && int.TryParse(await req.POST("category"), out category)) { InsertQuery categoryQuery = new InsertQuery("event_categories"); categoryQuery.Value("event_id", eventId) .Value("category_id", category); await Program.MySql().ExecuteNonQuery(categoryQuery); } await req.Redirect("/event/" + eventId); }
public async Task <T> ExecuteCommandAsync <T>(DbCommand command, RetryPolicy retryPolicy, CommandBehavior behavior) { var actionResult = default(T); var resultType = typeof(T); var hasOpenedConnection = false; var closeOpenedConnectionOnSuccess = false; try { await(retryPolicy ?? RetryPolicy.NoRetry).ExecuteAsync(async() => { actionResult = await this.connectionStringFailoverPolicy.ExecuteAsync <T>(async() => { // Make sure the command has been associated with a valid connection. If not, associate it with an opened SQL connection. if (command.Connection == null) { // Open a new connection and assign it to the command object. command.Connection = await this.OpenAsync(); hasOpenedConnection = true; } // Verify whether or not the connection is valid and is open. This code may be retried therefore // it is important to ensure that a connection is re-established should it have previously failed. if (command.Connection.State != ConnectionState.Open) { await command.Connection.OpenAsync(); hasOpenedConnection = true; } if (typeof(IDataReader).IsAssignableFrom(resultType)) { closeOpenedConnectionOnSuccess = false; return((T)Convert.ChangeType(await command.ExecuteReaderAsync(behavior), resultType)); } if (resultType == typeof(XmlReader)) { return(await ExecuteXmlReaderAsync <T>(command, behavior, closeOpenedConnectionOnSuccess)); } if (resultType == typeof(NonQueryResult)) { var result = new NonQueryResult { RecordsAffected = await command.ExecuteNonQueryAsync() }; closeOpenedConnectionOnSuccess = true; return((T)Convert.ChangeType(result, resultType, CultureInfo.InvariantCulture)); } else { var result = await command.ExecuteScalarAsync(); closeOpenedConnectionOnSuccess = true; if (result != null) { return((T)Convert.ChangeType(result, resultType, CultureInfo.InvariantCulture)); } return(default(T)); } }); }); if (hasOpenedConnection && closeOpenedConnectionOnSuccess && command.Connection != null && command.Connection.State == ConnectionState.Open) { command.Connection.Close(); } } catch (Exception) { if (hasOpenedConnection && command.Connection != null && command.Connection.State == ConnectionState.Open) { command.Connection.Close(); } throw; } return(actionResult); }