Beispiel #1
0
        // Delete object
        // return indicator of success
        public static bool Delete(Agencies obj)
        {
            bool success = false;

            // create connection
            SqlConnection connection = TravelExperts.GetConection();

            // create DELETE command
            string deleteStatment =
                "DELETE FROM Agencies " +
                "WHERE AgencyId = @AgencyId " +       // needed for identification of object
                "AND AgncyAddress = @AgncyAddress " + // the rest - for optimistic concurrency
                "AND AgncyCity = @AgncyCity " +
                "AND AgncyProv = @AgncyProv " +
                "AND AgncyPostal = @AgncyPostal " +
                "AND AgncyCountry = @AgncyCountry " +
                "AND AgncyPhone = @AgncyPhone " +
                "AND AgncyFax = @AgncyFax ";
            SqlCommand cmd = new SqlCommand(deleteStatment, connection);

            // suply perameter value
            cmd.Parameters.AddWithValue("@AgencyId", obj.AgencyId);
            cmd.Parameters.AddWithValue("@AgncyAddress", obj.AgncyAddress);
            cmd.Parameters.AddWithValue("@AgncyCity", obj.AgncyCity);
            cmd.Parameters.AddWithValue("@AgncyProv", obj.AgncyProv);
            cmd.Parameters.AddWithValue("@AgncyPostal", obj.AgncyPostal);
            cmd.Parameters.AddWithValue("@AgncyCountry", obj.AgncyCountry);
            cmd.Parameters.AddWithValue("@AgncyPhone", obj.AgncyPhone);
            cmd.Parameters.AddWithValue("@AgncyFax", obj.AgncyFax);

            // execute the command
            try
            {
                // open the conection
                connection.Open();

                // execute the command
                int count = cmd.ExecuteNonQuery();

                // check if successful
                if (count > 0)
                {
                    success = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally // executes always
            {
                connection.Close();
            }

            // retrieve generated customer nID to return
            return(success);
        }
Beispiel #2
0
        // retrieve object with given ID
        public static Agencies GetValue(int objID)
        {
            Agencies obj = null;

            // create connection
            SqlConnection connection = TravelExperts.GetConection();

            // create SELECT command
            string query =
                "SELECT AgencyId, AgncyAddress, AgncyCity, AgncyProv, AgncyPostal, AgncyCountry, AgncyPhone, AgncyFax " +
                "FROM Agencies " +
                "WHERE AgencyId = @AgencyId ";
            SqlCommand cmd = new SqlCommand(query, connection);

            // suply perameter value
            cmd.Parameters.AddWithValue("@AgencyId", objID);

            // run the SELECT query
            try
            {
                // open the conection
                connection.Open();

                // run the command
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                // build object object to return
                if (reader.Read()) // if there is a object with this ID
                {
                    obj              = new Agencies();
                    obj.AgencyId     = Convert.ToInt32(reader["AgencyId"]);
                    obj.AgncyAddress = reader["AgncyAddress"].ToString();
                    obj.AgncyCity    = reader["AgncyCity"].ToString();
                    obj.AgncyProv    = reader["AgncyProv"].ToString();
                    obj.AgncyPostal  = reader["AgncyPostal"].ToString();
                    obj.AgncyCountry = reader["AgncyCountry"].ToString();
                    obj.AgncyPhone   = reader["AgncyPhone"].ToString();
                    obj.AgncyFax     = reader["AgncyFax"].ToString();
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally // executes always
            {
                connection.Close();
            }

            return(obj);
        }
Beispiel #3
0
        // makes identival copy of Customer
        public Agencies Clone()
        {
            Agencies copy = new Agencies();

            copy.AgencyId     = this.AgencyId;
            copy.AgncyAddress = this.AgncyAddress;
            copy.AgncyCity    = this.AgncyCity;
            copy.AgncyProv    = this.AgncyProv;
            copy.AgncyPostal  = this.AgncyPostal;
            copy.AgncyCountry = this.AgncyCountry;
            copy.AgncyPhone   = this.AgncyPhone;
            copy.AgncyFax     = this.AgncyFax;
            return(copy);
        }
Beispiel #4
0
        // insert new row to table
        // return new object
        public static int Add(Agencies obj)
        {
            int custID = 0;

            // create connection
            SqlConnection connection = TravelExperts.GetConection();

            // create INSERT command
            // CustomerID is IDENTITY so no value provided
            string insertStatment =
                "INSERT INTO Agencies(AgencyId, AgncyAddress, AgncyCity, AgncyProv, AgncyPostal, AgncyCountry, AgncyPhone, AgncyFax) " +
                "OUTPUT inserted.[AgencyId] " +
                "VALUES(@AgencyId, @AgencyId) ";
            SqlCommand cmd = new SqlCommand(insertStatment, connection);

            // suply perameter value
            cmd.Parameters.AddWithValue("@AgencyId", obj.AgencyId);
            cmd.Parameters.AddWithValue("@AgncyAddress", obj.AgncyAddress);
            cmd.Parameters.AddWithValue("@AgncyCity", obj.AgncyCity);
            cmd.Parameters.AddWithValue("@AgncyProv", obj.AgncyProv);
            cmd.Parameters.AddWithValue("@AgncyPostal", obj.AgncyPostal);
            cmd.Parameters.AddWithValue("@AgncyCountry", obj.AgncyCountry);
            cmd.Parameters.AddWithValue("@AgncyPhone", obj.AgncyPhone);
            cmd.Parameters.AddWithValue("@AgncyFax", obj.AgncyFax);

            // execute the INSERT command
            try
            {
                // open the conection
                connection.Open();

                // execute insert command
                custID = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally // executes always
            {
                connection.Close();
            }
            // retrieve generated customer nID to return
            return(custID);
        }
Beispiel #5
0
        // retrieve all objects
        public static List <Agencies> GetAll()
        {
            // create SELECT command
            string query =
                "SELECT AgencyId, AgncyAddress, AgncyCity, AgncyProv, AgncyPostal, AgncyCountry, AgncyPhone, AgncyFax " +
                "FROM Agencies ";
            SqlConnection   connection = TravelExperts.GetConection();
            List <Agencies> dataList   = new List <Agencies>(); // epmty list
            Agencies        data;                               // for reading
                                                                // create connection



            SqlCommand cmd = new SqlCommand(query, connection);

            // open the conection
            connection.Open();

            // run the command
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            // build object list to return
            while (reader.Read()) // if there is a object with this ID
            {
                data              = new Agencies();
                data.AgencyId     = Convert.ToInt32(reader["AgencyId"]);
                data.AgncyAddress = reader["AgncyAddress"].ToString();
                data.AgncyCity    = reader["AgncyCity"].ToString();
                data.AgncyProv    = reader["AgncyProv"].ToString();
                data.AgncyPostal  = reader["AgncyPostal"].ToString();
                data.AgncyCountry = reader["AgncyCountry"].ToString();
                data.AgncyPhone   = reader["AgncyPhone"].ToString();
                data.AgncyFax     = reader["AgncyFax"].ToString();
                dataList.Add(data);
            }

            return(dataList);
        }
Beispiel #6
0
        // Update object
        // return indicator of success
        public static bool Update(Agencies oldObj, Agencies newObj)
        {
            bool success = false; // did not update

            // create connection
            SqlConnection connection = TravelExperts.GetConection();

            // create UPDATE command
            string updateStatment =
                "UPDATE Agencies SET " +
                "AgencyId = @NewAgencyId, " +
                "AgncyAddress = @NewAgncyAddress, " +
                "AgncyCity = @NewAgncyCity, " +
                "AgncyProv = @NewAgncyProv, " +
                "AgncyPostal = @NewAgncyPostal, " +
                "AgncyCountry = @NewAgncyCountry, " +
                "AgncyPhone = @NewAgncyPhone, " +
                "AgncyFax = @NewAgncyFax " +
                "WHERE AgencyId = @OldAgencyId " +       // needed for identification of object
                "AND AgncyAddress = @OldAgncyAddress " + // the rest - for optimistic concurrency
                "AND AgncyCity = @OldAgncyCity " +
                "AND AgncyProv = @OldAgncyProv " +
                "AND AgncyPostal = @OldAgncyPostal " +
                "AND AgncyCountry = @OldAgncyCountry " +
                "AND AgncyPhone = @OldAgncyPhone " +
                "AND AgncyFax = @OldAgncyFax ";
            SqlCommand cmd = new SqlCommand(updateStatment, connection);

            // suply perameter value

            // New object Values
            cmd.Parameters.AddWithValue("@NewAgencyId", newObj.AgencyId);
            cmd.Parameters.AddWithValue("@NewAgncyAddress", newObj.AgncyAddress);
            cmd.Parameters.AddWithValue("@NewAgncyCity", newObj.AgncyCity);
            cmd.Parameters.AddWithValue("@NewAgncyProv", newObj.AgncyProv);
            cmd.Parameters.AddWithValue("@NewAgncyPostal", newObj.AgncyPostal);
            cmd.Parameters.AddWithValue("@NewAgncyCountry", newObj.AgncyCountry);
            cmd.Parameters.AddWithValue("@NewAgncyPhone", newObj.AgncyPhone);
            cmd.Parameters.AddWithValue("@NewAgncyFax", newObj.AgncyFax);
            // ID
            cmd.Parameters.AddWithValue("@OldAgencyId", oldObj.AgencyId);
            // Old object Values
            cmd.Parameters.AddWithValue("@OldAgncyAddress", oldObj.AgncyAddress);
            cmd.Parameters.AddWithValue("@OldAgncyCity", oldObj.AgncyCity);
            cmd.Parameters.AddWithValue("@OldAgncyProv", oldObj.AgncyProv);
            cmd.Parameters.AddWithValue("@OldAgncyPostal", oldObj.AgncyPostal);
            cmd.Parameters.AddWithValue("@OldAgncyCountry", oldObj.AgncyCountry);
            cmd.Parameters.AddWithValue("@OldAgncyPhone", oldObj.AgncyPhone);
            cmd.Parameters.AddWithValue("@OldAgncyFax", oldObj.AgncyFax);

            // execute the UPDATE command
            try
            {
                // open the conection
                connection.Open();

                // execute the command
                int count = cmd.ExecuteNonQuery();

                // check if successful
                if (count > 0)
                {
                    success = true;            // updated
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally // executes always
            {
                connection.Close();
            }
            // retrieve generated object ID to return
            return(success);
        }