/// <summary>
/// Add the specified animal.
/// </summary>
/// <param name='animal'>
/// Animal.
/// </param>
public void Add(CZAnimal animal)
{
/// n.b. look out for quotes causing errors
var s = new SqliteCommand();
/// to prevent an injection attack, we parameterize with @name/etc
s.CommandText = "INSERT INTO animals (id, name, position, year) VALUES (@id, @name, @position, @year)";
// this is a disastrous way to create Ids, but I'm taking a shortcut
s.Parameters.AddWithValue("@id", (new Random()).Next());
s.Parameters.AddWithValue("@name", CultureInfo.CurrentCulture.TextInfo.ToTitleCase(animal.Name));
s.Parameters.AddWithValue("@position", animal.Position);
s.Parameters.AddWithValue("@year", animal.Year);
s.Connection = conn;
// connect
conn.Open();
// execute the command
s.ExecuteNonQuery();
// tidy up
s.Dispose();
conn.Close();
}
private bool ValidInput(CZAnimal animal, bool editingExisting)
{
// basic sensible input checks
if (animal.Name.Trim().Length == 0)
{
ModelState.AddModelError("Name", "Please add a name!");
}
if (animal.Position.GetType() != typeof(int) ||
animal.Position < 1 ||
animal.Position > 12)
{
ModelState.AddModelError(
"Position",
"The position should be an integer 0 < x < 13."
);
}
if (animal.Year.GetType() != typeof(int))
{
ModelState.AddModelError("Year", "The year should be an integer.");
}
// checking against the database
// database matches
var db = new ChiZodiacDb("chinesezodiac.db");
var names = db.Animals.Where <CZAnimal> (x => x.Name == animal.Name).ToList();
var positions = db.Animals.Where <CZAnimal> (x => x.Position == animal.Position)
.ToList();
var years = db.Animals.Where <CZAnimal> (x => x.Year == animal.Year).ToList();
if (names.Count > 0 && (!editingExisting || (editingExisting && names [0].Id != animal.Id)))
{
ModelState.AddModelError(
"Name",
string.Format("This animal is already in the database.")
);
}
if (positions.Count > 0 && (!editingExisting || (editingExisting && positions [0].Id != animal.Id)))
{
ModelState.AddModelError(
"Position",
String.Format("This position already has an animal (the {0}).", positions [0].Name
)
);
}
if (years.Count > 0 && (!editingExisting || (editingExisting && years [0].Id != animal.Id)))
{
ModelState.AddModelError(
"Year",
String.Format(
"This year already has an animal (the {0}).",
years [0].Name
)
);
}
return(ModelState.IsValid);
}
public void Delete(CZAnimal animal)
{
var s = new SqliteCommand();
s.CommandText = "DELETE FROM animals WHERE id = @id";
s.Parameters.AddWithValue("@id", animal.Id);
s.Connection = conn;
conn.Open();
s.ExecuteNonQuery();
s.Dispose();
conn.Close();
}
public ActionResult Create(CZAnimal animal)
{
if (ValidInput (animal, false)) {
var db = new ChiZodiacDb ("chinesezodiac.db");
db.Add (animal);
// show some kind of confirmation
return RedirectToAction (
"Details",
"Animals", new {
name = animal.Name, position = animal.Position, year = animal.Year }
);
} else {
return View (animal);
}
}
public void Update(CZAnimal animal)
{
var s = new SqliteCommand();
s.CommandText = "UPDATE animals SET name = @name, position = @position, year = @year WHERE id = @id";
s.Parameters.AddWithValue("@id", animal.Id);
s.Parameters.AddWithValue("@name", CultureInfo.CurrentCulture.TextInfo.ToTitleCase(animal.Name));
s.Parameters.AddWithValue("@position", animal.Position);
s.Parameters.AddWithValue("@year", animal.Year);
s.Connection = conn;
conn.Open();
s.ExecuteNonQuery();
s.Dispose();
conn.Close();
}
public ActionResult Create(CZAnimal animal)
{
if (ValidInput(animal, false))
{
var db = new ChiZodiacDb("chinesezodiac.db");
db.Add(animal);
// show some kind of confirmation
return(RedirectToAction(
"Details",
"Animals", new {
name = animal.Name, position = animal.Position, year = animal.Year
}
));
}
else
{
return(View(animal));
}
}
public ActionResult Edit(CZAnimal animal)
{
/// Here we validate.
if (ValidInput(animal, true))
{
// call some command on the database
var db = new ChiZodiacDb("chinesezodiac.db");
db.Update(animal);
// show some kind of confirmation
return(RedirectToAction(
"Details",
"Animals", new {
name = animal.Name, position = animal.Position, year = animal.Year
}
));
}
else
{
return(View(animal));
}
}
private bool ValidInput(CZAnimal animal, bool editingExisting)
{
// basic sensible input checks
if (animal.Name.Trim ().Length == 0) {
ModelState.AddModelError ("Name", "Please add a name!");
}
if (animal.Position.GetType () != typeof(int) ||
animal.Position < 1 ||
animal.Position > 12) {
ModelState.AddModelError (
"Position",
"The position should be an integer 0 < x < 13."
);
}
if (animal.Year.GetType () != typeof(int)) {
ModelState.AddModelError ("Year", "The year should be an integer.");
}
// checking against the database
// database matches
var db = new ChiZodiacDb ("chinesezodiac.db");
var names = db.Animals.Where<CZAnimal> (x => x.Name == animal.Name).ToList ();
var positions = db.Animals.Where<CZAnimal> (x => x.Position == animal.Position)
.ToList ();
var years = db.Animals.Where<CZAnimal> (x => x.Year == animal.Year).ToList ();
if (names.Count > 0 && (!editingExisting || (editingExisting && names [0].Id != animal.Id))) {
ModelState.AddModelError (
"Name",
string.Format ("This animal is already in the database.")
);
}
if (positions.Count > 0 && (!editingExisting || (editingExisting && positions [0].Id != animal.Id))) {
ModelState.AddModelError (
"Position",
String.Format ("This position already has an animal (the {0}).", positions [0].Name
)
);
}
if (years.Count > 0 && (!editingExisting || (editingExisting && years [0].Id != animal.Id))) {
ModelState.AddModelError (
"Year",
String.Format (
"This year already has an animal (the {0}).",
years [0].Name
)
);
}
return ModelState.IsValid;
}
public ActionResult Edit(CZAnimal animal)
{
/// Here we validate.
if (ValidInput (animal, true)) {
// call some command on the database
var db = new ChiZodiacDb ("chinesezodiac.db");
db.Update (animal);
// show some kind of confirmation
return RedirectToAction (
"Details",
"Animals", new {
name = animal.Name, position = animal.Position, year = animal.Year }
);
} else {
return View (animal);
}
}
/// <summary>
/// Add the specified animal.
/// </summary>
/// <param name='animal'>
/// Animal.
/// </param>
public void Add(CZAnimal animal)
{
/// n.b. look out for quotes causing errors
var s = new SqliteCommand ();
/// to prevent an injection attack, we parameterize with @name/etc
s.CommandText = "INSERT INTO animals (id, name, position, year) VALUES (@id, @name, @position, @year)";
// this is a disastrous way to create Ids, but I'm taking a shortcut
s.Parameters.AddWithValue ("@id", (new Random()).Next());
s.Parameters.AddWithValue ("@name", CultureInfo.CurrentCulture.TextInfo.ToTitleCase (animal.Name));
s.Parameters.AddWithValue ("@position", animal.Position);
s.Parameters.AddWithValue ("@year", animal.Year);
s.Connection = conn;
// connect
conn.Open ();
// execute the command
s.ExecuteNonQuery ();
// tidy up
s.Dispose ();
conn.Close ();
}
public void Update(CZAnimal animal)
{
var s = new SqliteCommand ();
s.CommandText = "UPDATE animals SET name = @name, position = @position, year = @year WHERE id = @id";
s.Parameters.AddWithValue ("@id", animal.Id);
s.Parameters.AddWithValue ("@name", CultureInfo.CurrentCulture.TextInfo.ToTitleCase (animal.Name));
s.Parameters.AddWithValue ("@position", animal.Position);
s.Parameters.AddWithValue ("@year", animal.Year);
s.Connection = conn;
conn.Open ();
s.ExecuteNonQuery ();
s.Dispose ();
conn.Close ();
}
public void Delete(CZAnimal animal)
{
var s = new SqliteCommand ();
s.CommandText = "DELETE FROM animals WHERE id = @id";
s.Parameters.AddWithValue ("@id", animal.Id);
s.Connection = conn;
conn.Open ();
s.ExecuteNonQuery ();
s.Dispose ();
conn.Close ();
}
