public static IList <Dock> GetDocks()
{
DataGateWay access = new DataGateWay(connectionName);
IList <Dock> Docks = new List <Dock>();
string sql = @"use InlandMarina
SELECT * FROM Dock";
Dock dock = null;
try
{
using (var reader = access.ExecuteQuery(sql, null, CommandType.Text))
{
while (reader.Read())
{
dock = new Dock()
{
ID = reader.GetInt32(reader.GetOrdinal("ID")),
Name = reader.GetString(reader.GetOrdinal("Name")),
LocationId = reader.GetInt32(reader.GetOrdinal("LocationId")),
WaterService = reader.GetBoolean(reader.GetOrdinal("WaterService")),
ElectricalService = reader.GetBoolean(reader.GetOrdinal("ElectricalService"))
};
Docks.Add(dock);
}
}
}
catch (Exception ex)
{
throw new Exception("Error retrieving data from database !", ex);
}
return(Docks);
}
public static IList <Customer> GetAllCustomers()
{
DataGateWay access = new DataGateWay(connectionName);
IList <Customer> Customers = new List <Customer>();
string sql = @"use InlandMarina
SELECT * FROM Customer";
Customer cust = null;
try
{
using (var reader = access.ExecuteQuery(sql, null, CommandType.Text))
{
while (reader.Read())
{
cust = new Customer()
{
ID = reader.GetInt32(reader.GetOrdinal("ID")),
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
Phone = reader.GetString(reader.GetOrdinal("Phone")),
City = reader.GetString(reader.GetOrdinal("City"))
};
Customers.Add(cust);
}
}
}
catch (Exception ex)
{
throw new Exception("Error retrieving data from database !", ex);
}
return(Customers);
}
public static IList <LeaseType> GetLeaseTypes()
{
DataGateWay access = new DataGateWay(connectionName);
IList <LeaseType> Types = new List <LeaseType>();
string sql = @"use InlandMarina
SELECT * FROM LeaseType";
LeaseType type = null;
try
{
using (var reader = access.ExecuteQuery(sql, null, CommandType.Text))
{
while (reader.Read())
{
type = new LeaseType()
{
ID = reader.GetInt32(reader.GetOrdinal("ID")),
Name = reader.GetString(reader.GetOrdinal("Name")),
StandardRateAmount = reader.GetDecimal(reader.GetOrdinal("StandardRateAmount"))
};
Types.Add(type);
}
}
}
catch (Exception ex)
{
throw new Exception("Error retrieving data from database !", ex);
}
return(Types);
}
public static IList <Location> GetLocations()
{
DataGateWay access = new DataGateWay(connectionName);
IList <Location> Locations = new List <Location>();
string sql = @"use InlandMarina
SELECT * FROM Location";
Location location = null;
try
{
using (var reader = access.ExecuteQuery(sql, null, CommandType.Text))
{
while (reader.Read())
{
location = new Location()
{
ID = reader.GetInt32(reader.GetOrdinal("ID")),
Name = reader.GetString(reader.GetOrdinal("Name"))
};
Locations.Add(location);
}
}
}
catch (Exception ex)
{
throw new Exception("Error retrieving data from database !", ex);
}
return(Locations);
}
public static IList <Boat> GetAllBoats()
{
DataGateWay access = new DataGateWay(connectionName);
IList <Boat> Boats = new List <Boat>();
string sql = @"use InlandMarina
SELECT * FROM Boat";
Boat boat = null;
try
{
using (var reader = access.ExecuteQuery(sql, null, CommandType.Text))
{
while (reader.Read())
{
boat = new Boat()
{
ID = reader.GetInt32(reader.GetOrdinal("ID")),
RegistrationNumber = reader.GetString(reader.GetOrdinal("RegistrationNumber")),
Manufacturer = reader.GetString(reader.GetOrdinal("Manufacturer")),
ModelYear = reader.GetInt32(reader.GetOrdinal("ModelYear")),
Length = reader.GetInt32(reader.GetOrdinal("Length")),
CustomerID = reader.GetInt32(reader.GetOrdinal("CustomerID"))
};
Boats.Add(boat);
}
}
}
catch (Exception ex)
{
throw new Exception("Error retrieving data from database !", ex);
}
return(Boats);
}
public static IList <Slip> GetAllSlips()
{
DataGateWay access = new DataGateWay(connectionName);
IList <Slip> Slips = new List <Slip>();
string sql = @"use InlandMarina
SELECT * FROM Slip";
Slip slip = null;
try
{
using (var reader = access.ExecuteQuery(sql, null, CommandType.Text))
{
while (reader.Read())
{
slip = new Slip()
{
ID = reader.GetInt32(reader.GetOrdinal("ID")),
Width = reader.GetInt32(reader.GetOrdinal("Width")),
Length = reader.GetInt32(reader.GetOrdinal("Length")),
DockID = reader.GetInt32(reader.GetOrdinal("DockID"))
};
Slips.Add(slip);
}
}
}
catch (Exception ex)
{
throw new Exception("Error retrieving data from database !", ex);
}
return(Slips);
}
public Interactor(DataGateWay gate, OutputBoundary output)
{
_iDatagateway = gate;
_iOutput = output;
//
_iDatagateway.ConnectionChanged += _iDatagateway_ConnectionChanged;
}
public static string UpdateCustomer(int ID, string FirstName, string LastName, string Phone, string City)
{
try
{
DataGateWay access = new DataGateWay(connectionName);
string sql = @"use InlandMarina
UPDATE Customer
SET FirstName = @FirstName,
LastName = @LastName,
Phone = @Phone,
City = @City
WHERE ID = @Id";
IDataParameter IdPar = access.CreateParameter;
IdPar.ParameterName = "@Id";
IdPar.DbType = DbType.Int32;
IdPar.Value = ID;
IDataParameter firstnamePar = access.CreateParameter;
firstnamePar.ParameterName = "@FirstName";
firstnamePar.DbType = DbType.String;
firstnamePar.Value = FirstName;
IDataParameter lastnamePar = access.CreateParameter;
lastnamePar.ParameterName = "@LastName";
lastnamePar.DbType = DbType.String;
lastnamePar.Value = LastName;
IDataParameter PhonePar = access.CreateParameter;
PhonePar.ParameterName = "@Phone";
PhonePar.DbType = DbType.String;
PhonePar.Value = Phone;
IDataParameter CityPar = access.CreateParameter;
CityPar.ParameterName = "@City";
CityPar.DbType = DbType.String;
CityPar.Value = City;
IDataParameter[] pars =
new IDataParameter[] { IdPar, firstnamePar, lastnamePar, PhonePar, CityPar };
access.ExecuteNonQuery(sql, pars, CommandType.Text);
return("Success");
}
catch
{
return("Update failed ! Please contact Administrator !");
}
}
public static string UpdateBoat(int ID, string RegistrationNumber, string Manufacturer, int ModelYear, int Length)
{
try
{
DataGateWay access = new DataGateWay(connectionName);
string sql = @"use InlandMarina
UPDATE Boat
SET RegistrationNumber = @RegistrationNumber,
Manufacturer = @LastName,
ModelYear = @Phone,
Length = @City
WHERE ID = @ID";
IDataParameter RegisterNoPar = access.CreateParameter;
RegisterNoPar.ParameterName = "@RegistrationNumber";
RegisterNoPar.DbType = DbType.String;
RegisterNoPar.Value = RegistrationNumber;
IDataParameter ManufacturerPar = access.CreateParameter;
ManufacturerPar.ParameterName = "@Manufacturer";
ManufacturerPar.DbType = DbType.String;
ManufacturerPar.Value = Manufacturer;
IDataParameter YearPar = access.CreateParameter;
YearPar.ParameterName = "@ModelYear";
YearPar.DbType = DbType.Int32;
YearPar.Value = ModelYear;
IDataParameter LengthPar = access.CreateParameter;
LengthPar.ParameterName = "@Length";
LengthPar.DbType = DbType.Int32;
LengthPar.Value = Length;
IDataParameter CustIDPar = access.CreateParameter;
CustIDPar.ParameterName = "@ID";
CustIDPar.DbType = DbType.Int32;
CustIDPar.Value = ID;
IDataParameter[] pars =
new IDataParameter[] { RegisterNoPar, ManufacturerPar, YearPar, LengthPar, CustIDPar };
access.ExecuteNonQuery(sql, pars, CommandType.Text);
return("Success");
}
catch
{
return("Failed to update record ! Please contact Administrator !");
}
}
public static string AddLease(DateTime startDate, DateTime endDate, int slipID, int customerID, int leaseTypeID)
{
try
{
DataGateWay access = new DataGateWay(connectionName);
string sql = @"use InlandMarina
INSERT INTO Lease(StartDate,EndDate,SlipID,CustomerID,LeaseTypeID)
VALUES(@StartDate,@EndDate,@SlipID,@CustomerID,@LeaseTypeID)";
IDataParameter StartDatePar = access.CreateParameter;
StartDatePar.ParameterName = "@StartDate";
StartDatePar.DbType = DbType.DateTime;
StartDatePar.Value = startDate;
IDataParameter EndDatePar = access.CreateParameter;
EndDatePar.ParameterName = "@EndDate";
EndDatePar.DbType = DbType.DateTime;
EndDatePar.Value = endDate;
IDataParameter SlipPar = access.CreateParameter;
SlipPar.ParameterName = "@SlipID";
SlipPar.DbType = DbType.Int32;
SlipPar.Value = slipID;
IDataParameter CustomerIDPar = access.CreateParameter;
CustomerIDPar.ParameterName = "@CustomerID";
CustomerIDPar.DbType = DbType.Int32;
CustomerIDPar.Value = customerID;
IDataParameter LeaseTypePar = access.CreateParameter;
LeaseTypePar.ParameterName = "@LeaseTypeID";
LeaseTypePar.DbType = DbType.Int32;
LeaseTypePar.Value = leaseTypeID;
IDataParameter[] pars =
new IDataParameter[] { StartDatePar, EndDatePar, SlipPar, CustomerIDPar, LeaseTypePar };
access.ExecuteNonQuery(sql, pars, CommandType.Text);
return("Success");
}
catch
{
return("Failed to add record ! Please contact Administrator !");
}
}
public static string AddBoat(string registerNumber, string manufacturer, int modelYear, int length, int customerID)
{
try
{
DataGateWay access = new DataGateWay(connectionName);
string sql = @"use InlandMarina
INSERT INTO Boat(RegistrationNumber,Manufacturer,ModelYear,Length,CustomerID)
VALUES(@RegistrationNumber,@Manufacturer,@ModelYear,@Length,@CustomerID)";
IDataParameter RegisterNoPar = access.CreateParameter;
RegisterNoPar.ParameterName = "@RegistrationNumber";
RegisterNoPar.DbType = DbType.String;
RegisterNoPar.Value = registerNumber;
IDataParameter ManufacturerPar = access.CreateParameter;
ManufacturerPar.ParameterName = "@Manufacturer";
ManufacturerPar.DbType = DbType.String;
ManufacturerPar.Value = manufacturer;
IDataParameter YearPar = access.CreateParameter;
YearPar.ParameterName = "@ModelYear";
YearPar.DbType = DbType.Int32;
YearPar.Value = modelYear;
IDataParameter LengthPar = access.CreateParameter;
LengthPar.ParameterName = "@Length";
LengthPar.DbType = DbType.Int32;
LengthPar.Value = length;
IDataParameter CustIDPar = access.CreateParameter;
CustIDPar.ParameterName = "@CustomerID";
CustIDPar.DbType = DbType.Int32;
CustIDPar.Value = customerID;
IDataParameter[] pars =
new IDataParameter[] { RegisterNoPar, ManufacturerPar, YearPar, LengthPar, CustIDPar };
access.ExecuteNonQuery(sql, pars, CommandType.Text);
return("Success");
}
catch
{
return("Failed to add record ! Please contact Administrator !");
}
}
public static Customer Authenticate(string username, string password)
{
DataGateWay access = new DataGateWay(connectionName);
string sql = @"use InlandMarina
SELECT cust.ID, cust.FirstName, cust.LastName,
cust.Phone, cust.City, auth.ID
FROM Customer cust INNER JOIN
Authorize auth ON cust.ID = auth.CustomerID
WHERE auth.UserName = @Username
AND auth.Password = @Password";
IDataParameter UsernamePar = access.CreateParameter;
UsernamePar.ParameterName = "@Username";
UsernamePar.DbType = DbType.String;
UsernamePar.Value = username;
IDataParameter PasswordPar = access.CreateParameter;
PasswordPar.ParameterName = "@Password";
PasswordPar.DbType = DbType.String;
PasswordPar.Value = password;
IDataParameter[] pars =
new IDataParameter[] { UsernamePar, PasswordPar };
var reader = access.ExecuteQuery(sql, pars, CommandType.Text);
Customer cust = null;
if (reader.Read())
{
cust = new Customer(reader.GetInt32(0), reader.GetString(1), reader.GetString(2),
reader.GetString(3), reader.GetString(4),
new Authorize(reader.GetInt32(5), username, password));
return(cust);
}
else
{
return(null);
}
}
public static string AddCustomer(string FirstName, string LastName, string Phone, string City)
{
try
{
DataGateWay access = new DataGateWay(connectionName);
string sql = @"use InlandMarina
INSERT INTO Customer(FirstName,LastName,Phone,City)
VALUES(@FirstName,@LastName,@Phone,@City);
SELECT SCOPE_IDENTITY()";
IDataParameter firstnamePar = access.CreateParameter;
firstnamePar.ParameterName = "@FirstName";
firstnamePar.DbType = DbType.String;
firstnamePar.Value = FirstName;
IDataParameter lastnamePar = access.CreateParameter;
lastnamePar.ParameterName = "@LastName";
lastnamePar.DbType = DbType.String;
lastnamePar.Value = LastName;
IDataParameter PhonePar = access.CreateParameter;
PhonePar.ParameterName = "@Phone";
PhonePar.DbType = DbType.String;
PhonePar.Value = Phone;
IDataParameter CityPar = access.CreateParameter;
CityPar.ParameterName = "@City";
CityPar.DbType = DbType.String;
CityPar.Value = City;
IDataParameter[] pars =
new IDataParameter[] { firstnamePar, lastnamePar, PhonePar, CityPar };
var idReturned = access.ExecuteScalar(sql, pars, CommandType.Text);
return(idReturned);
}
catch
{
return("Failed to add record ! Please contact Administrator !");
}
}
public static string UpdateAuthorize(string username, string password, int CustomerID)
{
try
{
DataGateWay access = new DataGateWay(connectionName);
string sql = @"use InlandMarina
UPDATE Authorize
SET UserName = @UserName,
Password = @Password
WHERE CustomerID = @CustomerID";
IDataParameter UserNamePar = access.CreateParameter;
UserNamePar.ParameterName = "@UserName";
UserNamePar.DbType = DbType.String;
UserNamePar.Value = username;
IDataParameter PasswordPar = access.CreateParameter;
PasswordPar.ParameterName = "@Password";
PasswordPar.DbType = DbType.String;
PasswordPar.Value = password;
IDataParameter CustomerPar = access.CreateParameter;
CustomerPar.ParameterName = "@CustomerID";
CustomerPar.DbType = DbType.Int32;
CustomerPar.Value = CustomerID;
IDataParameter[] pars =
new IDataParameter[] { UserNamePar, PasswordPar, CustomerPar };
access.ExecuteNonQuery(sql, pars, CommandType.Text);
return("Success");
}
catch
{
return("Failed to update record ! Please contact Administrator !");
}
}
public InteractorMain(DataGateWay iData, OutputBoundary output)
{
_interactor = new Interactor(iData, output);
}
