/// <summary> /// Get available slips of a certain dock, if no parameter or null, get all available slips. /// </summary> /// <param name="dockID">If want all available slips, don't pass in parameter.</param> /// <returns>A list of Slip object.</returns> public static List <Slip> GetAvailableSlipsFromDock(int dockID = 0) { var slips = new List <Slip>(); // if dockID is provided, find slips of that dock, otherwise search through all slips var sql = "SELECT ID,Width,Length,DockID FROM Slip " + "WHERE ID NOT IN (SELECT SlipID FROM Lease) " + (dockID != 0 ? $"AND DockID={dockID}" : ""); dbo.ConnectionString = connectionString; dbo.SetProvider("System.Data.SqlClient"); using (var reader = dbo.Query(sql, CommandType.Text, null)) { while (reader.Read()) { var s = new Slip { SlipID = reader.GetInt32(0), Width = reader.GetInt32(1), Length = reader.GetInt32(2), DockID = reader.GetInt32(3) }; slips.Add(s); } } return(slips); }
public static List <Slip> FindLeasingHistory(Customer c) { // set dbo object var leasedSlips = new List <Slip>(); var sql = "SELECT Slip.ID,Width,Length,DockID FROM Lease,Slip WHERE Lease.SlipID=Slip.ID AND CustomerID=@CustomerID"; dbo.ConnectionString = connectionString; dbo.SetProvider("System.Data.SqlClient"); // set parameter var cIDPar = dbo.CreateParameter(); cIDPar.ParameterName = "@CustomerID"; cIDPar.Value = c.CustomerID; var parameters = new IDataParameter[] { cIDPar }; // call execute method using (var reader = dbo.Query(sql, CommandType.Text, parameters)) { while (reader.Read()) { var slip = new Slip { SlipID = reader.GetInt32(0), Width = reader.GetInt32(1), Length = reader.GetInt32(2), DockID = reader.GetInt32(3) }; leasedSlips.Add(slip); } } return(leasedSlips); }
public static bool LeaseSelectedSlip(Slip s, Customer c) { // set DBObject string sql = "INSERT INTO Lease(SlipID,CustomerID) VALUES(@SlipID,@CustomerID)"; dbo.ConnectionString = connectionString; dbo.SetProvider("System.Data.SqlClient"); // set parameters (let database infer DBType) var slipIDPar = dbo.CreateParameter(); slipIDPar.Value = s.SlipID; slipIDPar.ParameterName = "@SlipID"; var custIDPar = dbo.CreateParameter(); custIDPar.Value = c.CustomerID; custIDPar.ParameterName = "@CustomerID"; var parameters = new IDataParameter[] { slipIDPar, custIDPar }; // call execute method var rowsAffected = dbo.NonQuery(sql, CommandType.Text, parameters); return(rowsAffected > 0 ? true : false); }