internal void Update(int id, DataGridViewCellCollection cells) { using (var connection = DBTools.GetDBConnection(datasource, database, username, password)) { connection.Open(); var transaction = connection.BeginTransaction(); var cmd = new SqlCommand($"UPDATE MarsApplicants SET name = @name, birthday = @bday, email = @mail, phone = @phone WHERE id = @id;", connection, transaction); cmd.Parameters.AddWithValue("id", id); cmd.Parameters.AddWithValue("name", cells[1].Value.ToString()); cmd.Parameters.AddWithValue("bday", cells[2].Value.ToString()); cmd.Parameters.AddWithValue("mail", cells[3].Value.ToString()); cmd.Parameters.AddWithValue("phone", cells[4].Value.ToString()); var rc = cmd.ExecuteNonQuery(); if (rc != 1) { transaction.Rollback(); throw new Exception("Не получилось обновить данные!"); } transaction.Commit(); } }
internal List <MarsApplicant> GetAll() { var connection = DBTools.GetDBConnection(datasource, database, username, password); connection.Open(); var cmd = new SqlCommand(@"SELECT * FROM MarsApplicants", connection); List <MarsApplicant> applications = new List <MarsApplicant>(); try { using (var reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { applications.Add(new MarsApplicant() { id = reader.GetInt32(reader.GetOrdinal("id")), name = reader.GetString(reader.GetOrdinal("name")), birthday = reader.GetString(reader.GetOrdinal("birthday")), email = reader.GetString(reader.GetOrdinal("email")), phone = reader.GetString(reader.GetOrdinal("phone")) }); } } } } finally { connection.Close(); connection.Dispose(); } return(applications); }
internal void Add(List <MarsApplicant> data) { if (data.Count == 0) { return; } using (var connection = DBTools.GetDBConnection(datasource, database, username, password)) { connection.Open(); var transaction = connection.BeginTransaction(); var cmd = new SqlCommand(); cmd.Connection = connection; cmd.Transaction = transaction; for (int i = 0; i < data.Count; i++) { cmd.CommandText += $"INSERT INTO MarsApplicants VALUES (@name{i}, @bday{i}, @mail{i}, @phone{i});"; cmd.Parameters.AddWithValue($"name{i}", data[i].name); cmd.Parameters.AddWithValue($"bday{i}", data[i].birthday); cmd.Parameters.AddWithValue($"mail{i}", data[i].email); cmd.Parameters.AddWithValue($"phone{i}", data[i].phone); } var rc = cmd.ExecuteNonQuery(); if (rc != data.Count) { transaction.Rollback(); throw new Exception("Не все данные были добавлены!"); } transaction.Commit(); } }