public bool Delete(Fisher fisher)
{
bool success = false;
using (OleDbConnection conn = new OleDbConnection(Global.ConnectionString))
{
conn.Open();
var sql = $"Delete * from fishers where FisherID={fisher.FisherID}";
using (OleDbCommand update = new OleDbCommand(sql, conn))
{
try
{
success = update.ExecuteNonQuery() > 0;
}
catch (OleDbException)
{
success = false;
}
catch (Exception ex)
{
Logger.Log(ex);
success = false;
}
}
}
return(success);
}
public bool Add(Fisher fisher)
{
bool success = false;
using (OleDbConnection conn = new OleDbConnection(Global.ConnectionString))
{
conn.Open();
var sql = "";
sql = $@"Insert into fishers(FisherID, FisherName, Boats, DateAdded)
Values (
{fisher.FisherID},
'{fisher.Name}',
'{fisher.VesselList}',
'{DateTime.Now.ToString("dd-MMMM-yyyyy HH:mm:ss")}'
)";
using (OleDbCommand update = new OleDbCommand(sql, conn))
{
success = update.ExecuteNonQuery() > 0;
}
}
return(success);
}
private void FisherCollection_CollectionChanged(object sender, NotifyCollectionChangedEventArgs e)
{
TypeOfChange c = TypeOfChange.Added;
EditSuccess = false;
switch (e.Action)
{
case NotifyCollectionChangedAction.Add:
{
int newIndex = e.NewStartingIndex;
Fisher newFisher = FisherCollection[newIndex];
if (Fishers.Add(newFisher))
{
CurrentEntity = newFisher;
EditSuccess = true;
}
}
break;
case NotifyCollectionChangedAction.Remove:
{
List <Fisher> tempListOfRemovedItems = e.OldItems.OfType <Fisher>().ToList();
if (Fishers.Delete(tempListOfRemovedItems[0]))
{
EditSuccess = true;
CurrentEntity = null;
}
c = TypeOfChange.Deleted;
}
break;
case NotifyCollectionChangedAction.Replace:
{
List <Fisher> tempList = e.NewItems.OfType <Fisher>().ToList();
if (Fishers.Update(tempList[0]))
{
EditSuccess = true;
CurrentEntity = tempList[0];
}
c = TypeOfChange.Edited;
}
break;
}
if (EditSuccess && EntitiesChanged != null)
{
EntitiesChangedEventArg ece = new EntitiesChangedEventArg
{
TypeOfChange = c,
Entity = CurrentEntity
};
EntitiesChanged(this, ece);
}
}
public bool AddRecordToRepo(Fisher fisher)
{
if (fisher == null)
{
throw new ArgumentNullException("Error: The argument is Null");
}
FisherCollection.Add(fisher);
return(EditSuccess);
}
public void DeleteRecordFromRepo(Fisher fisher)
{
if (fisher == null)
{
throw new Exception("Fisher cannot be null");
}
int index = 0;
while (index < FisherCollection.Count)
{
if (FisherCollection[index].FisherID == fisher.FisherID)
{
FisherCollection.RemoveAt(index);
break;
}
index++;
}
}
public bool UpdateRecordInRepo(Fisher fisher)
{
if (fisher.FisherID == 0)
{
throw new Exception("Error: ID must be greater than zero");
}
int index = 0;
while (index < FisherCollection.Count)
{
if (FisherCollection[index].FisherID == fisher.FisherID)
{
FisherCollection[index] = fisher;
break;
}
index++;
}
return(EditSuccess);
}
public bool Update(Fisher fisher)
{
bool success = false;
using (OleDbConnection conn = new OleDbConnection(Global.ConnectionString))
{
conn.Open();
var sql = $@"Update fishers set
FisherName = '{fisher.Name}',
Boats ='{fisher.VesselList}',
DeviceType = {(int)fisher.DeviceType},
LandingSite = {fisher.LandingSite.ID},
GearCodes = '{fisher.CSV}'
WHERE FisherID = {fisher.FisherID}";
using (OleDbCommand update = new OleDbCommand(sql, conn))
{
success = update.ExecuteNonQuery() > 0;
}
}
return(success);
}
private List <Fisher> getFishers()
{
var thisList = new List <Fisher>();
var dt = new DataTable();
using (var conection = new OleDbConnection(Global.ConnectionString))
{
try
{
conection.Open();
string query = $"Select * from fishers";
var adapter = new OleDbDataAdapter(query, conection);
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
thisList.Clear();
foreach (DataRow dr in dt.Rows)
{
Fisher item = new Fisher();
item.FisherID = int.Parse(dr["FisherID"].ToString());
item.Name = dr["FisherName"].ToString();
if (dr["Boats"].ToString() != "")
{
item.Vessels = dr["Boats"].ToString().Split('|').ToList();
}
item.DeviceIdentifier = dr["DeviceID"].ToString();
if (dr["GearCodes"].ToString() != "")
{
item.GearCodes = dr["GearCodes"].ToString().Split('|').ToList();
}
if (dr["LandingSite"].ToString() != "")
{
item.LandingSite = Entities.LandingSiteViewModel.GetLandingSite((int)dr["LandingSite"]);
}
if (dr["DeviceType"] == null || dr["DeviceType"].ToString() == "")
{
item.DeviceType = DeviceType.DeviceTypeNone;
}
else
{
item.DeviceType = (DeviceType)Enum.Parse(typeof(DeviceType), dr["DeviceType"].ToString());
}
thisList.Add(item);
}
}
}
catch (OleDbException dbex)
{
switch (dbex.ErrorCode)
{
case -2147217865:
CreateTable();
break;
default:
Logger.Log(dbex);
break;
}
}
catch (Exception ex)
{
switch (ex.HResult)
{
case -2147024809:
MakeAdditionalTableFields();
return(getFishers());
//break;
default:
Logger.Log(ex);
break;
}
}
}
return(thisList);
}
