// retrieve slip with given ID
public static List <Slip> GetSlipsByDock(int dockID)
{
List <Slip> sls = new List <Slip>();
Slip sl = null;
// create connection
SqlConnection connection = MarinaDB.GetConnection();
// create SELECT command
string query = "SELECT ID,Width,Length,DockID " +
"FROM Slip " +
"WHERE DockID = @DockID";
SqlCommand cmd = new SqlCommand(query, connection);
// supply parameter value
cmd.Parameters.AddWithValue("@DockID", dockID);
// run the SELECT query
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//add slips to the list
while (reader.Read()) //while there are slips
{
sl = new Slip();
sl.SlipID = (int)reader["ID"];
sl.Width = (int)reader["Width"];
sl.Length = (int)reader["Length"];
sl.DockID = (int)reader["DockID"];
}
reader.Close();
return(sls);
}
// retrieve customer with given ID
public static List <Dock> GetDocks()
{
List <Dock> docks = new List <Dock>();
Dock dck;
SqlConnection connection = MarinaDB.GetConnection();
string query = "SELECT ID , Name FROM Dock " +
"ORDER by Name";
SqlCommand command = new SqlCommand(query, connection);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
dck = new Dock();
dck.Name = reader["Name"].ToString();
dck.DockID = (int)reader["ID"];
docks.Add(dck);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(docks);
}
// retrieve slip with given ID
public static Slip GetSlip(int slipID)
{
Slip sl = null;
// create connection
SqlConnection connection = MarinaDB.GetConnection();
// create SELECT command
string query = "SELECT ID,Width,Length,DockID " +
"FROM Slip " +
"WHERE ID = @SlipID";
SqlCommand cmd = new SqlCommand(query, connection);
// supply parameter value
cmd.Parameters.AddWithValue("@SlipID", slipID);
// run the SELECT query
try
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build slip object to return
if (reader.Read()) // if there is a slip with this ID
{
sl = new Slip();
sl.SlipID = (int)reader["ID"];
sl.Width = (int)reader["Width"];
sl.Length = (int)reader["Length"];
sl.DockID = (int)reader["DockID"];
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(sl);
}
public static int AddSlip(Slip sl)
{
int slipID = 0;
// create connection
SqlConnection connection = MarinaDB.GetConnection();
// create INSERT command
// SlipID is IDENTITY so no value provided
string insertStatement =
"INSERT INTO Slip(Width,Length,DockID) " +
"OUTPUT inserted.ID " +
"VALUES(@Width, @Length, @DockID)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@Width", sl.Width);
cmd.Parameters.AddWithValue("@Length", sl.Length);
cmd.Parameters.AddWithValue("@DockID", sl.DockID);
try
{
connection.Open();
// execute insert command and get inserted ID
slipID = (int)cmd.ExecuteScalar();
//cmd.ExecuteNonQuery();
// retrieve generate Slip ID to return
//string selectStatement =
// "SELECT IDENT_CURRENT('Slips')";
//SqlCommand selectCmd = new SqlCommand(selectStatement, connection);
//custID = Convert.ToInt32(selectCmd.ExecuteScalar()); // returns single value
// // (int) does not work in this case
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(slipID);
}