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 <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 <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 <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 <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 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 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);
}
}