public bool AddRecordToRepo(LandingSite ls)
{
if (ls == null)
{
throw new ArgumentNullException("Error: The argument is Null");
}
LandingSiteCollection.Add(ls);
return(_editSuccess);
}
public void DeleteRecordFromRepo(LandingSite ls)
{
if (ls == null)
{
throw new Exception("Landing site cannot be null");
}
int index = 0;
while (index < LandingSiteCollection.Count)
{
if (LandingSiteCollection[index].ID == ls.ID)
{
LandingSiteCollection.RemoveAt(index);
break;
}
index++;
}
}
public bool UpdateRecordInRepo(LandingSite ls)
{
if (ls == null)
{
throw new Exception("Error: Landing site cannot be null");
}
int index = 0;
while (index < LandingSiteCollection.Count)
{
if (LandingSiteCollection[index].ID == ls.ID)
{
LandingSiteCollection[index] = ls;
break;
}
index++;
}
return(_editSuccess);
}
private void LandingSiteCollection_CollectionChanged(object sender, NotifyCollectionChangedEventArgs e)
{
_editSuccess = false;
switch (e.Action)
{
case NotifyCollectionChangedAction.Add:
{
int newIndex = e.NewStartingIndex;
LandingSite newLandingSite = LandingSiteCollection[newIndex];
if (LandingSites.Add(newLandingSite))
{
CurrentEntity = newLandingSite;
_editSuccess = true;
}
}
break;
case NotifyCollectionChangedAction.Remove:
{
List <LandingSite> tempListOfRemovedItems = e.OldItems.OfType <LandingSite>().ToList();
if (LandingSites.Delete(tempListOfRemovedItems[0].ID))
{
_editSuccess = true;
CurrentEntity = null;
}
}
break;
case NotifyCollectionChangedAction.Replace:
{
List <LandingSite> tempList = e.NewItems.OfType <LandingSite>().ToList();
if (LandingSites.Update(tempList[0]))
{
_editSuccess = true;
CurrentEntity = tempList[0];
}
}
break;
}
}
private List <LandingSite> getLandingSites()
{
List <LandingSite> list = new List <LandingSite>();
var dt = new DataTable();
using (var conection = new OleDbConnection(Global.ConnectionString))
{
try
{
conection.Open();
string query = $"Select * from landing_sites";
var adapter = new OleDbDataAdapter(query, conection);
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
list.Clear();
foreach (DataRow dr in dt.Rows)
{
LandingSite ls = new LandingSite();
if (double.TryParse(dr["lat"].ToString(), out double lat))
{
ls.Lat = lat;
}
if (double.TryParse(dr["lon"].ToString(), out double lon))
{
ls.Lon = lon;
}
ls.Name = dr["Name"].ToString();
ls.Municipality = dr["Municipality"].ToString();
ls.Province = dr["Province"].ToString();
ls.ID = int.Parse(dr["ID"].ToString());
list.Add(ls);
}
}
}
catch (OleDbException dbex)
{
switch (dbex.ErrorCode)
{
case -2147217865:
CreateTable();
break;
default:
Logger.Log(dbex);
break;
}
}
catch (Exception ex)
{
switch (ex.HResult)
{
case -2147024809:
if (ModifyTable())
{
return(getLandingSites());
}
break;
default:
Logger.Log(ex);
break;
}
}
return(list);
}
}
public bool Update(LandingSite ls)
{
bool success = false;
using (OleDbConnection conn = new OleDbConnection(Global.ConnectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.Parameters.Add("@name", OleDbType.VarChar).Value = ls.Name;
cmd.Parameters.Add("@mun", OleDbType.VarChar).Value = ls.Municipality;
cmd.Parameters.Add("@prov", OleDbType.VarChar).Value = ls.Province;
if (ls.Lat == null)
{
cmd.Parameters.Add("@lat", OleDbType.Double).Value = DBNull.Value;
}
else
{
cmd.Parameters.Add("@lat", OleDbType.Double).Value = ls.Lat;
}
if (ls.Lon == null)
{
cmd.Parameters.Add("@lon", OleDbType.Double).Value = DBNull.Value;
}
else
{
cmd.Parameters.Add("@lon", OleDbType.Double).Value = ls.Lon;
}
cmd.Parameters.Add("@id", OleDbType.Integer).Value = ls.ID;
//if (ls.Lat != null && ls.Lon != null)
//{
cmd.CommandText = @"UPDATE landing_sites set
Name = @name,
Municipality = @mun,
Province = @prov,
Lat = @lat,
Lon = @lon
WHERE ID = @id";
//}
//else
//{
// cmd.CommandText = @"UPDATE landing_sites set
// Name = @name,
// Municipality = @mun,
// Province = @prov
// WHERE ID = @id";
//}
try
{
success = cmd.ExecuteNonQuery() > 0;
}
catch (OleDbException dbex)
{
Logger.Log(dbex);
}
catch (Exception ex)
{
Logger.Log(ex);
}
}
}
return(success);
}
public bool Add(LandingSite ls)
{
bool success = false;
using (OleDbConnection conn = new OleDbConnection(Global.ConnectionString))
{
conn.Open();
//var sql = "";
var sql = $@"Insert into landing_sites(ID, Name, Lat, Lon, Municipality, Province)
Values (?, ?, ?, ?, ?, ?)";
using (OleDbCommand update = new OleDbCommand(sql, conn))
{
update.Parameters.Add("@id", OleDbType.Integer).Value = ls.ID;
update.Parameters.Add("@name", OleDbType.VarChar).Value = ls.Name;
if (ls.Lat == null)
{
update.Parameters.Add("@lat", OleDbType.Double).Value = DBNull.Value;
}
else
{
update.Parameters.Add("@lat", OleDbType.Double).Value = ls.Lat;
}
if (ls.Lon == null)
{
update.Parameters.Add("@lon", OleDbType.Double).Value = DBNull.Value;
}
else
{
update.Parameters.Add("@lon", OleDbType.Double).Value = ls.Lon;
}
update.Parameters.Add("@mun", OleDbType.VarChar).Value = ls.Municipality;
update.Parameters.Add("@prov", OleDbType.VarChar).Value = ls.Province;
try
{
success = update.ExecuteNonQuery() > 0;
}
catch (OleDbException dbex)
{
switch (dbex.HResult)
{
case -2147217900:
if (ModifyTable())
{
return(Add(ls));
}
break;
}
}
catch (Exception ex)
{
Logger.Log(ex);
}
}
}
return(success);
}
