예제 #1
0
        // 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);
        }
예제 #2
0
        // 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);
        }
예제 #3
0
        // 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);
        }
예제 #4
0
        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);
        }