예제 #1
0
파일: CarDAO.cs 프로젝트: datvtp/CarSales
        public List <CarDTO> GetListCar()
        {
            List <CarDTO> list = null;
            string        SQL  = "SELECT ID, Model_Name, Price, Produced_Year, Accquired_Date, Engine, Quantity, " +
                                 "Manufacturer_ID, Tranmission_ID, Type_ID, Category_ID, Fuel_ID, Status_ID FROM Cars";
            SqlConnection cnn = DBUtils.GetConnection();
            SqlCommand    cmd = new SqlCommand(SQL, cnn);

            try
            {
                if (cnn.State == ConnectionState.Closed)
                {
                    cnn.Open();
                    SqlDataReader       rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    Car_ManufacturerDAO car_ManufacturerDAO = new Car_ManufacturerDAO();
                    Car_TranmissionDAO  car_TranmissionDAO  = new Car_TranmissionDAO();
                    Car_TypeDAO         car_TypeDAO         = new Car_TypeDAO();
                    Car_CategoryDAO     car_CategoryDAO     = new Car_CategoryDAO();
                    Car_FuelsDAO        car_FuelsDAO        = new Car_FuelsDAO();
                    Car_StatusDAO       car_StatusDAO       = new Car_StatusDAO();
                    while (rd.Read())
                    {
                        if (list == null)
                        {
                            list = new List <CarDTO>();
                        }
                        CarDTO carDTO = new CarDTO
                        {
                            ID                      = rd.GetInt32(0),
                            Model_Name              = rd.GetString(1),
                            Price                   = rd.GetDouble(2),
                            Produced_Year           = rd.GetInt32(3),
                            Accquired_Date          = rd.GetDateTime(4),
                            Engine                  = rd.GetInt32(5),
                            Quantity                = rd.GetInt32(6),
                            Manufacturer_Name       = car_ManufacturerDAO.GetManufacturerNameByID(rd.GetInt32(7)),
                            Tranmission_Description = car_TranmissionDAO.GetTranmissionDescriptionByID(rd.GetInt32(8)),
                            Type_Description        = car_TypeDAO.GetTypeDescriptionByID(rd.GetInt32(9)),
                            Category_Description    = car_CategoryDAO.GetCategoryDescriptionByID(rd.GetInt32(10)),
                            Fuel_Description        = car_FuelsDAO.GetFuelDescriptionByID(rd.GetInt32(11)),
                            Status_Description      = car_StatusDAO.GetStatusDescriptionByID(rd.GetInt32(12))
                        };

                        list.Add(carDTO);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return(list);
        }
예제 #2
0
파일: CarDAO.cs 프로젝트: datvtp/CarSales
        public List <CarDTO> Search(string manufacturer, string category, string type, double priceFrom, double priceTo)
        {
            List <CarDTO> list = null;

            Car_ManufacturerDAO car_ManufacturerDAO = new Car_ManufacturerDAO();
            Car_TranmissionDAO  car_TranmissionDAO  = new Car_TranmissionDAO();
            Car_TypeDAO         car_TypeDAO         = new Car_TypeDAO();
            Car_CategoryDAO     car_CategoryDAO     = new Car_CategoryDAO();
            Car_FuelsDAO        car_FuelsDAO        = new Car_FuelsDAO();
            Car_StatusDAO       car_StatusDAO       = new Car_StatusDAO();

            string SQL = "SELECT ID, Model_Name, Price, Produced_Year, Accquired_Date, Engine, Quantity, " +
                         "Manufacturer_ID, Tranmission_ID, Type_ID, Category_ID, Fuel_ID, Status_ID FROM Cars";

            if (!manufacturer.Equals("All"))
            {
                int manuID = car_ManufacturerDAO.GetManufacturerIDByName(manufacturer);
                if (!SQL.Contains("WHERE "))
                {
                    SQL = SQL + " WHERE Manufacturer_ID = " + manuID;
                }
            }

            if (!category.Equals("All"))
            {
                int cateID = car_CategoryDAO.GetCategoryIDByDescription(category);
                if (!SQL.Contains("WHERE "))
                {
                    SQL = SQL + " WHERE Category_ID = " + cateID;
                }
                else
                {
                    SQL = SQL + " AND Category_ID = " + cateID;
                }
            }

            if (!type.Equals("All"))
            {
                int typeID = car_TypeDAO.GetTypeIDByDescription(type);
                if (!SQL.Contains("WHERE "))
                {
                    SQL = SQL + " WHERE Type_ID = " + typeID;
                }
                else
                {
                    SQL = SQL + " AND Type_ID = " + typeID;
                }
            }

            if (!(priceFrom == -1 && priceTo == -1))
            {
                if (priceFrom > -1 && priceTo > -1)
                {
                    if (!SQL.Contains("WHERE "))
                    {
                        SQL = SQL + " WHERE Price BETWEEN " + priceFrom + " AND " + priceTo;
                    }
                    else
                    {
                        SQL = SQL + " AND Price BETWEEN " + priceFrom + " AND " + priceTo;
                    }
                }
                else if (priceFrom > -1 && priceTo == -1)
                {
                    if (!SQL.Contains("WHERE "))
                    {
                        SQL = SQL + " WHERE Price >= " + priceFrom;
                    }
                    else
                    {
                        SQL = SQL + " AND Price >= " + priceFrom;
                    }
                }
                else if (priceFrom == -1 && priceTo > -1)
                {
                    if (!SQL.Contains("WHERE "))
                    {
                        SQL = SQL + " WHERE Price <= " + priceTo;
                    }
                    else
                    {
                        SQL = SQL + " AND Price <= " + priceTo;
                    }
                }
            }

            SqlConnection cnn = DBUtils.GetConnection();
            SqlCommand    cmd = new SqlCommand(SQL, cnn);

            try
            {
                if (cnn.State == ConnectionState.Closed)
                {
                    cnn.Open();
                    SqlDataReader rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    while (rd.Read())
                    {
                        if (list == null)
                        {
                            list = new List <CarDTO>();
                        }
                        CarDTO carDTO = new CarDTO
                        {
                            ID                      = rd.GetInt32(0),
                            Model_Name              = rd.GetString(1),
                            Price                   = rd.GetDouble(2),
                            Produced_Year           = rd.GetInt32(3),
                            Accquired_Date          = rd.GetDateTime(4),
                            Engine                  = rd.GetInt32(5),
                            Quantity                = rd.GetInt32(6),
                            Manufacturer_Name       = car_ManufacturerDAO.GetManufacturerNameByID(rd.GetInt32(7)),
                            Tranmission_Description = car_TranmissionDAO.GetTranmissionDescriptionByID(rd.GetInt32(8)),
                            Type_Description        = car_TypeDAO.GetTypeDescriptionByID(rd.GetInt32(9)),
                            Category_Description    = car_CategoryDAO.GetCategoryDescriptionByID(rd.GetInt32(10)),
                            Fuel_Description        = car_FuelsDAO.GetFuelDescriptionByID(rd.GetInt32(11)),
                            Status_Description      = car_StatusDAO.GetStatusDescriptionByID(rd.GetInt32(12))
                        };

                        list.Add(carDTO);
                    }
                }
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return(list);
        }