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