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