Exemplo n.º 1
0
        //receives supplier object, deletes record in pps table
        public static void DeletePackageProductSuppliersBySupplier(Supplier s)
        {
            SqlConnection dbConn = TravelExpertsDB.GetConnection();
            string        sql    =
                "DELETE pps " +
                "FROM " +
                "[Packages_Products_Suppliers] pps " +
                "inner join[Products_Suppliers] ps " +
                "ON pps.ProductSupplierId = ps.ProductSupplierId " +
                "INNER JOIN[Suppliers] s " +
                "ON s.SupplierId = ps.SupplierId " +
                "WHERE ps.SupplierId = @SupplierID";
            SqlCommand cmdDelete = new SqlCommand(sql, dbConn);

            cmdDelete.Parameters.AddWithValue("@SupplierID", s.SupplierId);

            try
            {
                dbConn.Open();
                cmdDelete.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
Exemplo n.º 2
0
        //search through suppliers given search string, returns list of suppliers
        public static List <Supplier> GetSuppliersByName(string suppName)
        {
            List <Supplier> suppliers = new List <Supplier>();
            SqlConnection   dbConn    = TravelExpertsDB.GetConnection();
            string          sql       =
                "SELECT SupplierId, SupName " +
                "FROM Suppliers " +
                "WHERE SupName LIKE '%" + suppName + "%' ";
            SqlCommand cmdSelect = new SqlCommand(sql, dbConn);

            try
            {
                dbConn.Open();
                SqlDataReader dbReader = cmdSelect.ExecuteReader();
                while (dbReader.Read())
                {
                    Supplier supplier = new Supplier();
                    supplier.SupplierId = Convert.ToInt32(dbReader["SupplierId"]);
                    supplier.SupName    = Convert.ToString(dbReader["SupName"]);
                    suppliers.Add(supplier);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
            return(suppliers);
        }
Exemplo n.º 3
0
        //receives supplier object and deletes supplier contact record
        public static void DeleteProductSuppliersBySupplier(Supplier s)
        {
            DeletePackageProductSuppliersBySupplier(s);// Delete links first
            SqlConnection dbConn = TravelExpertsDB.GetConnection();
            string        sql    =
                "DELETE FROM Products_Suppliers " +
                "WHERE SupplierId = @SupplierID";
            SqlCommand cmdDelete = new SqlCommand(sql, dbConn);

            cmdDelete.Parameters.AddWithValue("@SupplierID", s.SupplierId);

            try
            {
                dbConn.Open();
                cmdDelete.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
Exemplo n.º 4
0
        //receives supplier object, deletes supplier record
        public static void DeleteSupplier(Supplier ds)
        {
            DeleteSupplierContactsBySupplier(ds);
            DeleteProductSuppliersBySupplier(ds); // Delete links first
            SqlConnection dbConn = TravelExpertsDB.GetConnection();
            string        sql    =
                "DELETE FROM Suppliers " +
                "WHERE SupplierID = @SupplierID " +
                " AND SupName = @SupName"; //concurrency checking
            SqlCommand cmdDelete = new SqlCommand(sql, dbConn);

            cmdDelete.Parameters.AddWithValue("@SupplierID", ds.SupplierId);
            cmdDelete.Parameters.AddWithValue("@SupName", ds.SupName);
            try
            {
                dbConn.Open();
                cmdDelete.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
Exemplo n.º 5
0
        //receives old and new supplier object, updates a supplier record
        public static void UpdateSupplier(Supplier old_Supplier, Supplier new_Supplier)
        {
            SqlConnection dbConn = TravelExpertsDB.GetConnection();
            string        sql    = "UPDATE Suppliers SET " +
                                   " SupName = @NewSupName " +
                                   "WHERE SupplierID = @SupplierID " +
                                   " AND SupName = @OldSupName"; //concurrency checking
            SqlCommand cmdUpdate = new SqlCommand(sql, dbConn);

            cmdUpdate.Parameters.AddWithValue("@NewSupName", new_Supplier.SupName);
            cmdUpdate.Parameters.AddWithValue("@SupplierID", new_Supplier.SupplierId);
            cmdUpdate.Parameters.AddWithValue("@OldSupName", old_Supplier.SupName);
            try
            {
                dbConn.Open();
                cmdUpdate.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
Exemplo n.º 6
0
        //find supplier by name returns supplier object
        public static Supplier GetSupplierByName(string n)
        {
            Supplier      supplier = new Supplier();
            SqlConnection dbConn   = TravelExpertsDB.GetConnection();
            string        sql      =
                "SELECT SupplierId, SupName " +
                "FROM Suppliers " +
                "WHERE SupName = @SupName";
            SqlCommand cmdSelect = new SqlCommand(sql, dbConn);

            cmdSelect.Parameters.AddWithValue("@SupName", n);
            try
            {
                dbConn.Open();
                SqlDataReader dbReader = cmdSelect.ExecuteReader();
                while (dbReader.Read())
                {
                    supplier.SupplierId = Convert.ToInt32(dbReader["SupplierId"]);
                    supplier.SupName    = Convert.ToString(dbReader["SupName"]);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
            return(supplier);
        }
Exemplo n.º 7
0
 //find and return a list of suppliers that is not in the Packages_Products_Suppliers table for the given package and the given product
 public static List <Supplier> GetSuppliersByProductNotInDetails(Product p, Package pkg)
 {
     if (p == null)
     {
         //throw new Exception("null passed to supplier");
         return(null);
     }
     else
     {
         List <Supplier> suppliers = new List <Supplier>();
         SqlConnection   dbConn    = TravelExpertsDB.GetConnection();
         string          sql       =
             "SELECT s.SupplierId, s.SupName " +
             "FROM Suppliers s " +
             "inner join Products_Suppliers ps " +
             "on ps.SupplierId = s.SupplierId " +
             "WHERE ps.ProductId = @ProductId " +
             "and s.SupplierId " +
             "not in " +
             "(SELECT s.SupplierId " +
             "FROM Packages pk " +
             "inner join Packages_Products_Suppliers pps " +
             "ON pps.PackageId = pk.PackageId " +
             "inner join Products_Suppliers ps " +
             "ON pps.ProductSupplierId = ps.ProductSupplierId " +
             "inner join Products p " +
             "ON p.ProductId = ps.ProductId " +
             "inner join Suppliers s " +
             "ON s.SupplierId = ps.SupplierId " +
             "WHERE pk.PackageId = @PackageId " +
             "and p.ProductId = @ProductId)";
         SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
         cmdSelect.Parameters.AddWithValue("@PackageId", pkg.PackageId);
         cmdSelect.Parameters.AddWithValue("@ProductId", p.ProductId);
         try
         {
             dbConn.Open();
             SqlDataReader dbReader = cmdSelect.ExecuteReader();
             while (dbReader.Read())
             {
                 Supplier supplier = new Supplier();
                 supplier.SupplierId = Convert.ToInt32(dbReader["SupplierId"]);
                 supplier.SupName    = Convert.ToString(dbReader["SupName"]);
                 suppliers.Add(supplier);
             }
         }
         catch (SqlException ex)
         {
             throw ex;
         }
         finally
         {
             dbConn.Close();
         }
         return(suppliers);
     }
 }
Exemplo n.º 8
0
        //return a list of PackageProductSupplier data for the given package
        public static List <PackageProductSupplier> GetProductSuppliersByPackage(Package pkg)
        {
            if (pkg == null)
            {
                return(null);
            }
            else
            {
                List <PackageProductSupplier> ppss = new List <PackageProductSupplier>();
                SqlConnection dbConn = TravelExpertsDB.GetConnection();
                string        sql    =
                    "SELECT pk.PackageId, pk.PkgName, pps.ProductSupplierId, p.ProdName, s.SupName " +
                    "FROM Packages pk " +
                    "inner join Packages_Products_Suppliers pps " +
                    "ON pps.PackageId = pk.PackageId " +
                    "inner join Products_Suppliers ps " +
                    "ON pps.ProductSupplierId = ps.ProductSupplierId " +
                    "inner join Products p " +
                    "ON p.ProductId = ps.ProductId " +
                    "inner join Suppliers s " +
                    "ON s.SupplierId = ps.SupplierId " +
                    "WHERE pk.PackageId = @Package " +
                    "ORDER BY p.ProdName";
                SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
                cmdSelect.Parameters.AddWithValue("@Package", pkg.PackageId);
                try
                {
                    dbConn.Open();
                    SqlDataReader dbReader = cmdSelect.ExecuteReader();
                    while (dbReader.Read())
                    {
                        PackageProductSupplier pps = new PackageProductSupplier();
                        pps.PackageId         = Convert.ToInt32(dbReader["PackageId"]);
                        pps.ProductSupplierId = Convert.ToInt32(dbReader["ProductSupplierId"]);
                        pps.PkgName           = Convert.ToString(dbReader["PkgName"]);
                        pps.ProdName          = Convert.ToString(dbReader["ProdName"]);
                        pps.SupName           = Convert.ToString(dbReader["SupName"]);

                        ppss.Add(pps);
                    }
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbConn.Close();
                }
                return(ppss);
            }
        }
Exemplo n.º 9
0
        //find and return a list of prodcuts that is not in the Packages_Products_Suppliers table for the given package
        public static List <Product> GetProductsNotInDetails(Package pkg)
        {
            List <Product> products = new List <Product>();
            SqlConnection  dbConn   = TravelExpertsDB.GetConnection();
            string         sql      =
                "SELECT[ProductId], [ProdName] " +
                "FROM[Products] " +
                "where ProductId " +
                "not in " +
                "(SELECT p.ProductId " +
                "FROM Packages pk " +
                "inner join Packages_Products_Suppliers pps " +
                "ON pps.PackageId = pk.PackageId " +
                "inner join Products_Suppliers ps " +
                "ON pps.ProductSupplierId = ps.ProductSupplierId " +
                "inner join Products p " +
                "ON p.ProductId = ps.ProductId " +
                "inner join Suppliers s " +
                "ON s.SupplierId = ps.SupplierId " +
                "WHERE pk.PackageId = @PackageId)";
            SqlCommand cmdSelect = new SqlCommand(sql, dbConn);

            cmdSelect.Parameters.AddWithValue("@PackageId", pkg.PackageId);
            try
            {
                dbConn.Open();
                SqlDataReader dbReader = cmdSelect.ExecuteReader();
                while (dbReader.Read())
                {
                    Product product = new Product();
                    product.ProductId = Convert.ToInt32(dbReader["ProductId"]);
                    product.ProdName  = Convert.ToString(dbReader["ProdName"]);
                    products.Add(product);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
            return(products);
        }
Exemplo n.º 10
0
 //receives supplier object, returns corresponding list of products
 public static List <Product> GetProductSuppliersBySupplier(Supplier s)
 {
     if (s == null)
     {
         //throw new Exception("null passed to supplier");
         return(null);
     }
     else
     {
         List <Product> products = new List <Product>();
         SqlConnection  dbConn   = TravelExpertsDB.GetConnection();
         string         sql      =
             "SELECT p.ProductId, p.ProdName " +
             "FROM Products p " +
             "inner join Products_Suppliers ps " +
             "ON p.ProductId = ps.ProductId " +
             "inner join Suppliers s " +
             "ON s.SupplierId = ps.SupplierId " +
             "WHERE s.SupplierId = @SupplierID ";
         SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
         cmdSelect.Parameters.AddWithValue("@SupplierID", s.SupplierId);
         try
         {
             dbConn.Open();
             SqlDataReader dbReader = cmdSelect.ExecuteReader();
             while (dbReader.Read())
             {
                 Product product = new Product();
                 product.ProductId = Convert.ToInt32(dbReader["ProductId"]);
                 product.ProdName  = Convert.ToString(dbReader["ProdName"]);
                 products.Add(product);
             }
         }
         catch (SqlException ex)
         {
             throw ex;
         }
         finally
         {
             dbConn.Close();
         }
         return(products);
     }
 }
Exemplo n.º 11
0
 public static List <Supplier> GetProductSuppliersByProduct(Product p)
 {
     if (p == null)
     {
         return(null);
     }
     else
     {//call the suppliers list
         List <Supplier> suppliers = new List <Supplier>();
         SqlConnection   dbConn    = TravelExpertsDB.GetConnection();
         string          sql       =
             "SELECT s.SupplierId, s.SupName " +
             "FROM Suppliers s " +
             "inner join Products_Suppliers ps " +
             "ON s.SupplierId = ps.SupplierId " +
             "inner join Products p " +
             "ON p.ProductId = ps.ProductId " +
             "WHERE p.ProductId = @ProductId";//get teh suppliers that carry the product by id
         SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
         cmdSelect.Parameters.AddWithValue("@ProductId", p.ProductId);
         try
         {
             dbConn.Open();
             SqlDataReader dbReader = cmdSelect.ExecuteReader();
             while (dbReader.Read())
             {
                 Supplier supplier = new Supplier();
                 supplier.SupplierId = Convert.ToInt32(dbReader["SupplierId"]);
                 supplier.SupName    = Convert.ToString(dbReader["SupName"]);
                 suppliers.Add(supplier);
             }
         }
         catch (SqlException ex)
         {
             throw ex;
         }
         finally
         {
             dbConn.Close();
         }
         return(suppliers);
     }
 }
Exemplo n.º 12
0
        //receives product and returns a list of suppliers that are not in the current dgv
        public static List <Supplier> GetSuppliersNotInList(Product p)
        {
            List <Supplier> suppliers = new List <Supplier>();
            SqlConnection   dbConn    = TravelExpertsDB.GetConnection();
            string          sql       =
                "SELECT [SupplierId], [SupName] " +
                "FROM[Suppliers] s " +
                "where s.SupplierId " +
                "not in" +
                "(SELECT s.SupplierId " +
                "FROM Suppliers " +
                "inner join Products_Suppliers ps " +
                "ON s.SupplierId = ps.SupplierId " +
                "inner join Products p " +
                "on p.ProductId = ps.ProductId " +
                "where p.ProductId = @ProductId)";
            SqlCommand cmdSelect = new SqlCommand(sql, dbConn);

            cmdSelect.Parameters.AddWithValue("@ProductId", p.ProductId);
            try
            {
                dbConn.Open();
                SqlDataReader dbReader = cmdSelect.ExecuteReader();
                while (dbReader.Read())
                {
                    Supplier supplier = new Supplier();
                    supplier.SupplierId = Convert.ToInt32(dbReader["SupplierId"]);
                    supplier.SupName    = Convert.ToString(dbReader["SupName"]);
                    suppliers.Add(supplier);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
            return(suppliers);
        }
Exemplo n.º 13
0
        public static List <Product> GetProductsNotInList(Supplier s)
        {//load products from database
            List <Product> products = new List <Product>();
            SqlConnection  dbConn   = TravelExpertsDB.GetConnection();
            string         sql      =
                "SELECT [ProductId], [ProdName] " +
                "FROM[Products] p " +
                "where p.ProductId " +
                "not in " +
                "(SELECT p.ProductId " +
                "FROM Products " +
                "inner join Products_Suppliers ps " +
                "ON p.ProductId = ps.ProductId " +
                "inner join Suppliers s " +
                "ON s.SupplierId = ps.SupplierId " +
                "WHERE s.SupplierId = @SupplierId)";//find a product that doesn't have a supplier
            SqlCommand cmdSelect = new SqlCommand(sql, dbConn);

            cmdSelect.Parameters.AddWithValue("@SupplierId", s.SupplierId);
            try
            {
                dbConn.Open();
                SqlDataReader dbReader = cmdSelect.ExecuteReader();
                while (dbReader.Read())
                {
                    Product product = new Product();
                    product.ProductId = Convert.ToInt32(dbReader["ProductId"]);
                    product.ProdName  = Convert.ToString(dbReader["ProdName"]);
                    products.Add(product);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
            return(products);
        }
Exemplo n.º 14
0
        //delete the package record from package table
        public static void DeletePackage(Package dp)
        {
            DeletePackagesProductsSuppliersByPackage(dp); // Delete links first
            SqlConnection dbConn = TravelExpertsDB.GetConnection();
            string        sql    =
                "DELETE FROM Packages " +
                "WHERE PackageId = @PackageId " +
                "AND PkgName = @oPkgName " + //check concurrency
                "AND (PkgStartDate = @oPkgStartDate OR " +
                "PkgStartDate IS NULL AND @oPkgStartDate IS NULL) " +
                "AND (PkgEndDate = @oPkgEndDate OR " +
                "PkgEndDate IS NULL AND @oPkgEndDate IS NULL) " +
                "AND (PkgDesc = @oPkgDesc OR " +
                "PkgDesc IS NULL AND @oPkgDesc IS NULL) " +
                "AND PkgBasePrice = @oPkgBasePrice " +
                "AND (PkgAgencyCommission = @oPkgAgencyCommission OR " +
                "PkgAgencyCommission IS NULL AND @oPkgAgencyCommission IS NULL)";
            SqlCommand cmdDelete = new SqlCommand(sql, dbConn);

            cmdDelete.Parameters.AddWithValue("@PackageId", dp.PackageId);
            cmdDelete.Parameters.AddWithValue("@oPkgName", dp.PkgName);
            cmdDelete.Parameters.AddWithValue("@oPkgStartDate", (object)dp.PkgStartDate ?? DBNull.Value);
            cmdDelete.Parameters.AddWithValue("@oPkgEndDate", (object)dp.PkgEndDate ?? DBNull.Value);
            cmdDelete.Parameters.AddWithValue("@oPkgDesc", (object)dp.PkgDesc ?? DBNull.Value);
            cmdDelete.Parameters.AddWithValue("@oPkgBasePrice", dp.PkgBasePrice);
            cmdDelete.Parameters.AddWithValue("@oPkgAgencyCommission", (object)dp.PkgAgencyCommission ?? DBNull.Value);
            try
            {
                dbConn.Open();
                cmdDelete.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
Exemplo n.º 15
0
        //receives supplier object and inserts record into supplier table
        public static void InsertSupplier(Supplier ns)
        {
            SqlConnection dbConn = TravelExpertsDB.GetConnection();
            string        sql    = "INSERT Suppliers (SupName) " +
                                   "VALUES(@SupName)";
            SqlCommand cmdInsert = new SqlCommand(sql, dbConn);

            cmdInsert.Parameters.AddWithValue("@SupName", ns.SupName);
            try
            {
                dbConn.Open();
                cmdInsert.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
Exemplo n.º 16
0
        public static void InsertProduct(Product np)
        {//load all products by name from database
            SqlConnection dbConn = TravelExpertsDB.GetConnection();
            string        sql    = "INSERT Products (ProdName) " +
                                   "VALUES (@ProdName)";
            SqlCommand cmdInsert = new SqlCommand(sql, dbConn);//insert values

            cmdInsert.Parameters.AddWithValue("@ProdName", np.ProdName);

            try
            {
                dbConn.Open();
                cmdInsert.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
Exemplo n.º 17
0
        //return a list of all package data from packages table in the database
        public static List <Package> GetAllPackages()
        {
            List <Package> packages = new List <Package>();
            SqlConnection  dbConn   = TravelExpertsDB.GetConnection();
            string         sql      =
                "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
                "FROM Packages " +
                "ORDER BY PackageId";
            SqlCommand cmdSelect = new SqlCommand(sql, dbConn);

            try
            {
                dbConn.Open();
                SqlDataReader dbReader = cmdSelect.ExecuteReader();
                while (dbReader.Read())
                {
                    Package package = new Package();
                    package.PackageId           = Convert.ToInt32(dbReader["PackageId"]);
                    package.PkgName             = Convert.ToString(dbReader["PkgName"]);
                    package.PkgStartDate        = dbReader["PkgStartDate"] == DBNull.Value ? null : (DateTime?)dbReader["PkgStartDate"];
                    package.PkgEndDate          = dbReader["PkgEndDate"] == DBNull.Value ? null : (DateTime?)(dbReader["PkgEndDate"]);
                    package.PkgDesc             = Convert.ToString(dbReader["PkgDesc"] == DBNull.Value ? null : dbReader["PkgDesc"]);
                    package.PkgBasePrice        = Convert.ToDecimal(dbReader["PkgBasePrice"]);
                    package.PkgAgencyCommission = dbReader["PkgAgencyCommission"] == DBNull.Value ? null : (decimal?)dbReader["PkgAgencyCommission"];
                    packages.Add(package);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
            return(packages);
        }
Exemplo n.º 18
0
        public static void InsertProductSupplier(ProductSupplier nps)
        {
            SqlConnection dbConn = TravelExpertsDB.GetConnection();

            string sql = "INSERT Products_Suppliers (ProductId,SupplierId) " +
                         "VALUES (@npsPid, @npsSid)";
            SqlCommand cmdInsert = new SqlCommand(sql, dbConn);           //get connection

            cmdInsert.Parameters.AddWithValue("@npsPid", nps.ProductId);  //assign value with parameter
            cmdInsert.Parameters.AddWithValue("@npsSid", nps.SupplierId); //assign value with parameter
            try
            {
                dbConn.Open();               //connection open
                cmdInsert.ExecuteNonQuery(); //execute query
            }
            catch (SqlException ex)
            {
                throw ex;//throw exception
            }
            finally
            {
                dbConn.Close();//close connection
            }
        }
Exemplo n.º 19
0
        //delete data from Packages_Products_Suppliers for the given package
        public static void DeletePackagesProductsSuppliersByPackage(Package p)
        {
            SqlConnection dbConn = TravelExpertsDB.GetConnection();
            string        sql    =
                "DELETE FROM Packages_Products_Suppliers " +
                "WHERE PackageId = @PackageId";
            SqlCommand cmdDelete = new SqlCommand(sql, dbConn);

            cmdDelete.Parameters.AddWithValue("@PackageId", p.PackageId);

            try
            {
                dbConn.Open();
                cmdDelete.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
Exemplo n.º 20
0
        //find package by the given id
        public static Package GetPackageById(int id)
        {
            Package       package = new Package();
            SqlConnection dbConn  = TravelExpertsDB.GetConnection();
            string        sql     =
                "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
                "FROM Packages " +
                "WHERE PackageId = @ID ";
            SqlCommand cmdSelect = new SqlCommand(sql, dbConn);

            cmdSelect.Parameters.AddWithValue("@ID", id);
            try
            {
                dbConn.Open();
                SqlDataReader dbReader = cmdSelect.ExecuteReader();
                while (dbReader.Read())
                {
                    package.PackageId           = Convert.ToInt32(dbReader["PackageId"]);
                    package.PkgName             = Convert.ToString(dbReader["PkgName"]);
                    package.PkgStartDate        = dbReader["PkgStartDate"] == DBNull.Value ? null : (DateTime?)dbReader["PkgStartDate"];
                    package.PkgEndDate          = dbReader["PkgEndDate"] == DBNull.Value ? null : (DateTime?)(dbReader["PkgEndDate"]);
                    package.PkgDesc             = Convert.ToString(dbReader["PkgDesc"] == DBNull.Value ? null : dbReader["PkgDesc"]);
                    package.PkgBasePrice        = Convert.ToDecimal(dbReader["PkgBasePrice"]);
                    package.PkgAgencyCommission = dbReader["PkgAgencyCommission"] == DBNull.Value ? null : (decimal?)dbReader["PkgAgencyCommission"];
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
            return(package);
        }