public static Route GetRouteByID_Route(int routeid)
{
Route route = new Route();
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("GetRouteByID");
dbconn.AddParameterToList("routeid", SqlDbType.Int, routeid);
dbconn.SetSqlParameters();
if (dbconn.ExecuteStoredProcedureDataTable().Rows.Count == 1)
{
foreach (DataRow routeDR in dbconn.ExecuteStoredProcedureDataTable().Rows)
{
object dropofftime = ValueValidater.ReturnValue(routeDR["DropOffTime"]);
object dropofflocation = ValueValidater.ReturnValue(routeDR["DropOffLocation"]);
if (dropofftime != null && dropofflocation != null)
{
route = new Route(routeid, routeDR["PickupTime"].ToString(), routeDR["PickUpLocation"].ToString(),
(Status)routeDR["StatusID"], dropofftime.ToString(), dropofflocation.ToString());
}
else
{
route = new Route(routeid, routeDR["PickupTime"].ToString(), routeDR["PickUpLocation"].ToString(),
(Status)routeDR["StatusID"], null, null);
}
}
}
return(route);
}
public static Driver GetDriverByID_Driver(int driverID)
{
Driver newDriver = new Driver();
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("GetDriverByID");
dbconn.AddParameterToList("DriverID", SqlDbType.Int, driverID);
dbconn.SetSqlParameters();
if (dbconn.ExecuteStoredProcedureDataTable().Rows.Count == 1)
{
foreach (DataRow driverDR in dbconn.ExecuteStoredProcedureDataTable().Rows)
{
object dropofftime = ValueValidater.ReturnValue(driverDR["DropOffTime"]);
object dropofflocation = ValueValidater.ReturnValue(driverDR["DropOffLocation"]);
if (dropofftime != null && dropofflocation != null)
{
newDriver = new Driver(driverID, driverDR["FirstName"].ToString(),
driverDR["LastName"].ToString(), driverDR["BadgeNumber"].ToString(),
driverDR["StartDate"].ToString(), driverDR["EndDate"].ToString());
}
else
{
newDriver = new Driver(driverID, driverDR["FirstName"].ToString(),
driverDR["LastName"].ToString(), driverDR["BadgeNumber"].ToString(),
driverDR["StartDate"].ToString(), driverDR["EndDate"].ToString());
}
}
}
return(newDriver);
}
public static List <Driver> GetDrivers_ListDriver()
{
List <Driver> DriverList = new List <Driver>();
Driver newDriver = new Driver();
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("GetDrivers");
foreach (DataRow driverDR in dbconn.ExecuteStoredProcedureDataTable().Rows)
{
object dropofftime = ValueValidater.ReturnValue(driverDR["DropOffTime"]);
object dropofflocation = ValueValidater.ReturnValue(driverDR["DropOffLocation"]);
if (dropofftime != null && dropofflocation != null)
{
newDriver = new Driver((int)driverDR["DriverID"], driverDR["FirstName"].ToString(),
driverDR["LastName"].ToString(), driverDR["BadgeNumber"].ToString(),
driverDR["StartDate"].ToString(), driverDR["EndDate"].ToString());
}
else
{
newDriver = new Driver((int)driverDR["DriverID"], driverDR["FirstName"].ToString(),
driverDR["LastName"].ToString(), driverDR["BadgeNumber"].ToString(),
driverDR["StartDate"].ToString(), driverDR["EndDate"].ToString());
}
}
return(DriverList);
}
public static string DeleteDriverByID_String(int driverid)
{
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("DeleteDriver");
dbconn.AddParameterToList("DriverID", SqlDbType.Int, driverid);
dbconn.SetSqlParameters();
return(dbconn.ExecuteStoredProcedureString());
}
public static string CreateContract_String(Contract contract)
{
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("InsertContract");
dbconn.AddParameterToList("contractname", SqlDbType.Text, contract.ContractName);
dbconn.AddParameterToList("statename", SqlDbType.Text, contract.StateName);
dbconn.SetSqlParameters();
return(dbconn.ExecuteStoredProcedureString());
}
public static string CreateRoutePickUp_String(string pickuplocationname)
{
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("InsertRoutePickUp");
dbconn.AddParameterToList("pickuptime", SqlDbType.DateTime, DateTime.Now);
dbconn.AddParameterToList("pickuplocationname", SqlDbType.Text, pickuplocationname);
dbconn.SetSqlParameters();
return(dbconn.ExecuteStoredProcedureString());
}
public static string CreateLocation_String(Location location)
{
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("InsertLocation");
dbconn.AddParameterToList("LocationName", SqlDbType.Text, location.LocationName);
dbconn.AddParameterToList("Address", SqlDbType.Text, location.Address);
dbconn.SetSqlParameters();
return(dbconn.ExecuteStoredProcedureString());
}
public static string CreateSignee_String(Signee signee)
{
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("InsertSignee");
dbconn.AddParameterToList("FirstName", SqlDbType.Text, signee.FirstName);
dbconn.AddParameterToList("LastName", SqlDbType.Text, signee.LastName);
dbconn.SetSqlParameters();
return(dbconn.ExecuteStoredProcedureString());
}
public static string CreateRouteDropOff_String(int routeid, string dropofflocationname)
{
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("InsertRouteDropOff");
dbconn.AddParameterToList("dropofftime", SqlDbType.DateTime, DateTime.Now);
dbconn.AddParameterToList("dropofflocationname", SqlDbType.Text, dropofflocationname);
dbconn.AddParameterToList("routeid", SqlDbType.Int, routeid);
dbconn.SetSqlParameters();
return(dbconn.ExecuteStoredProcedureString());
}
public static string CreateDriver_String(Driver driver)
{
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("InsertDriver");
dbconn.AddParameterToList("FirstName", SqlDbType.Text, driver.FirstName);
dbconn.AddParameterToList("LastName", SqlDbType.Text, driver.LastName);
dbconn.AddParameterToList("StartDate", SqlDbType.Date, driver.StartDate);
dbconn.AddParameterToList("EndDate", SqlDbType.Date, driver.EndDate);
dbconn.AddParameterToList("BadgeNumber", SqlDbType.Text, driver.BadgeNumber);
dbconn.SetSqlParameters();
return(dbconn.ExecuteStoredProcedureString());
}
public static string UpdateRoute_String(Route route)
{
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("UpdateRoute");
dbconn.AddParameterToList("routeid", SqlDbType.Int, route.ID);
dbconn.AddParameterToList("pickuptime", SqlDbType.DateTime, Convert.ToDateTime(route.PickUpTime));
dbconn.AddParameterToList("dropofftime", SqlDbType.DateTime, Convert.ToDateTime(route.DropOffTime));
dbconn.AddParameterToList("pickuplocation", SqlDbType.Text, route.PickUpLocation.LocationName);
dbconn.AddParameterToList("dropofflocation", SqlDbType.Text, route.DropOffLocation.LocationName);
dbconn.AddParameterToList("status", SqlDbType.Int, (int)route.Status);
dbconn.SetSqlParameters();
return(dbconn.ExecuteStoredProcedureString());
}
public static Signee GetSigneeByID_Signee(int signeeID)
{
Signee newSignee = new Signee();
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("GetSigneeByID");
dbconn.AddParameterToList("SigneeID", SqlDbType.Int, signeeID);
dbconn.SetSqlParameters();
foreach (DataRow signeeDR in dbconn.ExecuteStoredProcedureDataTable().Rows)
{
newSignee = new Signee(signeeID, signeeDR["FirstName"].ToString(),
signeeDR["LastName"].ToString());
}
return(newSignee);
}
public static List <Contract> GetContracts_ListContract()
{
List <Contract> ContractList = new List <Contract>();
Contract newContract = new Contract();
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("GetContracts");
foreach (DataRow contractDR in dbconn.ExecuteStoredProcedureDataTable().Rows)
{
newContract = new Contract((int)contractDR["ContractID"], contractDR["ContractName"].ToString());
ContractList.Add(newContract);
}
return(ContractList);
}
public static Contract GetContractByID_Contract(int contractID)
{
Contract newContract = new Contract();
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("GetContractByID");
dbconn.AddParameterToList("ContractID", SqlDbType.Int, contractID);
dbconn.SetSqlParameters();
foreach (DataRow contractDR in dbconn.ExecuteStoredProcedureDataTable().Rows)
{
newContract = new Contract(contractID, contractDR["ContractName"].ToString(),
contractDR["StateName"].ToString());
}
return(newContract);
}
public static Location GetLocationByID_Location(int locationID)
{
Location newLocation = new Location();
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("GetLocationByID");
dbconn.AddParameterToList("LocationID", SqlDbType.Int, locationID);
dbconn.SetSqlParameters();
foreach (DataRow locationDR in dbconn.ExecuteStoredProcedureDataTable().Rows)
{
newLocation = new Location(locationID, locationDR["LocationName"].ToString(),
locationDR["Address"].ToString());
}
return(newLocation);
}
public static List <Signee> GetSignees_ListSignee()
{
List <Signee> SigneeList = new List <Signee>();
Signee newSignee = new Signee();
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("GetSignees");
foreach (DataRow signeeDR in dbconn.ExecuteStoredProcedureDataTable().Rows)
{
newSignee = new Signee((int)signeeDR["SigneeID"], signeeDR["FirstName"].ToString(),
signeeDR["LastName"].ToString());
SigneeList.Add(newSignee);
}
return(SigneeList);
}
public static List <Location> GetLocations_ListLocation()
{
List <Location> LocationList = new List <Location>();
Location newLocation = new Location();
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("GetLocations");
foreach (DataRow contractDR in dbconn.ExecuteStoredProcedureDataTable().Rows)
{
newLocation = new Location((int)contractDR["LocationID"], contractDR["LocationName"].ToString(),
contractDR["Address"].ToString());
LocationList.Add(newLocation);
}
return(LocationList);
}
public static List <Route> GetRoutes_ListRoute()
{
List <Route> RouteList = new List <Route>();
Route newRoute = new Route();
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("GetRoutes");
foreach (DataRow routeDR in dbconn.ExecuteStoredProcedureDataTable().Rows)
{
newRoute = new Route((int)routeDR["RouteID"], routeDR["PickupTime"].ToString(),
routeDR["PickupLocation"].ToString(), (Status)routeDR["Status"]
, routeDR["DropOffTime"].ToString(), routeDR["DropoffLocation"].ToString());
RouteList.Add(newRoute);
}
return(RouteList);
}
public static Location UpdateLocation_Location(Location location)
{
Location updatedLocation = new Location();
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("UpdateLocation");
dbconn.AddParameterToList("locationid", SqlDbType.Int, location.ID);
dbconn.AddParameterToList("locationname", SqlDbType.Text, location.LocationName);
dbconn.AddParameterToList("address", SqlDbType.Text, location.Address);
dbconn.SetSqlParameters();
foreach (DataRow locationDR in dbconn.ExecuteStoredProcedureDataTable().Rows)
{
updatedLocation = new Location((int)locationDR["LocationID"], locationDR["LocationName"].ToString(),
locationDR["Address"].ToString());
}
return(updatedLocation);
}
public static List <Route> GetRoutesByContractID_ListRoute(int contractid)
{
List <Route> RouteList = new List <Route>();
Route newRoute = new Route();
dbconn = new DBConnectionServices("JCIConnection");
dbconn.SetSqlCommandStoredProcedure("GetRoutesByContract");
dbconn.AddParameterToList("contractid", SqlDbType.Int, contractid);
dbconn.SetSqlParameters();
foreach (DataRow routeDR in dbconn.ExecuteStoredProcedureDataTable().Rows)
{
newRoute = new Route((int)routeDR["RouteID"], routeDR["PickupTime"].ToString(),
routeDR["PickupLocation"].ToString(), (Status)routeDR["StatusID"]
, routeDR["DropOffTime"].ToString(), routeDR["DropoffLocation"].ToString());
RouteList.Add(newRoute);
}
return(RouteList);
}
