public static async Task <List <LookupCountry> > Get(string connectionString)
{
List <LookupCountry> result = new List <LookupCountry>();
await using var conn = new NpgsqlConnection(connectionString);
await conn.OpenAsync();
await using (var cmd = new NpgsqlCommand("SELECT id, external_id, full_name, abbreviation FROM lookup_country", conn))
{
await using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// result.Add(reader.GetString(0));
int id = reader.GetInt32(0);
Guid externalId = new Guid(reader.GetString(1));
string fullName = reader.GetString(2);
string abbreviation = reader.GetString(3);
LookupCountry lookupCountry = new LookupCountry
(
id: id,
externalId: externalId,
fullName: fullName,
abbreviation: abbreviation
);
result.Add(lookupCountry);
}
}
}
return(result);
}
public ActionResult Edit([Bind(Include = "Id,CountryCode,CountryName,IsActive,CreatedOn,CreatedBy,UpdatedOn,UpdatedBy")] LookupCountry lookupCountry)
{
if (ModelState.IsValid)
{
lookupCountry.UpdatedBy = CurrentUser.Id;
lookupCountry.UpdatedOn = DateTime.UtcNow;
db.Entry(lookupCountry).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
ViewBag.CreatedBy = new SelectList(db.Users, "Id", "UserName", lookupCountry.CreatedBy);
ViewBag.UpdatedBy = new SelectList(db.Users, "Id", "UserName", lookupCountry.UpdatedBy);
return(View(lookupCountry));
}
// GET: Admin/Countries/Details/5
public ActionResult Details(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
LookupCountry lookupCountry = db.LookupCountries.Find(id);
if (lookupCountry == null)
{
return(HttpNotFound());
}
return(View(lookupCountry));
}
// GET: Admin/Countries/Edit/5
public ActionResult Edit(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
LookupCountry lookupCountry = db.LookupCountries.Find(id);
if (lookupCountry == null)
{
return(HttpNotFound());
}
ViewBag.CreatedBy = new SelectList(db.Users, "Id", "UserName", lookupCountry.CreatedBy);
ViewBag.UpdatedBy = new SelectList(db.Users, "Id", "UserName", lookupCountry.UpdatedBy);
return(View(lookupCountry));
}
public ActionResult DeleteConfirmed(int id)
{
LookupCountry lookupCountry = db.LookupCountries.Find(id);
try
{
db.LookupCountries.Remove(lookupCountry);
db.SaveChanges();
}
catch (DbUpdateException ex)
{
ModelState.AddModelError("Error", "There are some releted item in database, please delete those first");
return(View("Delete", lookupCountry));
}
return(RedirectToAction("Index"));
}
public static async Task <List <Person> > Get(string connectionString)
{
List <Person> result = new List <Person>();
await using var conn = new NpgsqlConnection(connectionString);
await conn.OpenAsync();
await using (var cmd = new NpgsqlCommand(@"select p.id, p.external_id, p.full_name, p.winnings, c.id, c.external_id, c.full_name, c.abbreviation from person p inner join lookup_country c on p.country = c.external_id;", conn))
{
await using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int personId = reader.GetInt32(0);
Guid personExternalId = new Guid(reader.GetString(1));
string personFullName = reader.GetString(2);
decimal personWinnings = reader.GetDecimal(3);
int countryId = reader.GetInt32(4);
Guid countryExternalId = new Guid(reader.GetString(5));
string countryFullName = reader.GetString(6);
string countryAbbreviation = reader.GetString(7);
LookupCountry country = new LookupCountry
(
id: countryId,
externalId: countryExternalId,
fullName: countryFullName,
abbreviation: countryAbbreviation
);
Person person = new Person
(
id: personId,
externalId: personExternalId,
fullName: personFullName,
winnings: personWinnings,
country: country
);
result.Add(person);
}
}
}
return(result);
}
