C# DatabaseManager.ReturnQuery方法代码示例

 public bool DeleteListing(int listingID)
 {
     int propID = -1;
     int addID = -1;
     int compID = -1;
     DatabaseManager dbManager = new DatabaseManager();
     var propIDs = dbManager.ReturnQuery("SELECT Property_ID FROM Listing WHERE List_ID = " + listingID);
     foreach (var i in propIDs)
     {
         propID = Convert.ToInt32(i[0]);
     }
     var compIDs = dbManager.ReturnQuery("SELECT Complex_ID FROM Property WHERE Property_ID = " + propID);
     foreach (var i in compIDs)
     {
         compID = Convert.ToInt32(i[0]);
     }
     var addIDs = dbManager.ReturnQuery("SELECT Address_ID FROM Property WHERE Property_ID = " + propID);
     foreach (var i in addIDs)
     {
         addID = Convert.ToInt32(i[0]);
     }
     dbManager.NonReturnQuery("DELETE FROM Image WHERE ( Image.Property_ID = " + propID + ");");
     if (countPropertiesInComplex(compID) == 0 && countPropertiesWithAddress(addID) == 0)
         return (dbManager.NonReturnQuery("DELETE FROM Complex, Address, Property, Listing USING Complex, Address, Property, Listing WHERE ( Complex.Complex_ID = Property.Complex_ID AND Property.Property_ID = Listing.Property_ID AND Property.Address_ID = Address.Address_ID AND Listing.List_ID =" + listingID + ");"));
     else if (countPropertiesInComplex(compID) == 0 && countPropertiesWithAddress(addID) != 0)
         return (dbManager.NonReturnQuery("DELETE FROM Complex, Property, Listing USING Complex, Property, Listing WHERE (Complex.Complex_ID = Property.Complex_ID AND Property.Property_ID = Listing.Property_ID AND Listing.List_ID =" + listingID + ");"));
     else if (countPropertiesInComplex(compID) != 0 && countPropertiesWithAddress(addID) == 0)
         return (dbManager.NonReturnQuery("DELETE FROM Address, Property, Listing USING Address, Property, Listing WHERE ( Property.Property_ID = Listing.Property_ID AND Property.Address_ID = Address.Address_ID AND Listing.List_ID =" + listingID + ");"));
     else
         return (dbManager.NonReturnQuery("DELETE FROM Complex, Address, Property, Listing USING Complex, Address, Property, Listing WHERE ( Complex.Complex_ID = Property.Complex_ID AND Property.Property_ID = Listing.Property_ID AND Property.Address_ID = Address.Address_ID AND Listing.List_ID =" + listingID + ");"));
 }

        private string GetPassword(string username, bool admin)
        {
            string retValue = "";

            DatabaseManager dbManager = new DatabaseManager();

            if (admin)
            {
                var result = dbManager.ReturnQuery("SELECT Admin_Password FROM Admin WHERE Admin_Username = '" + username + "';");

                foreach (var r in result)
                {
                    retValue = r[0].ToString();
                    break;
                }
            }
            else
            {
                var result = dbManager.ReturnQuery("SELECT Agent_Password FROM Agent WHERE Agent_Email = '" + username + "';");

                foreach (var r in result)
                {
                    retValue = r[0].ToString();
                    break;
                }
            }

            return retValue;
        }

        public string AreaFullName(int areaID)
        {
            string fullName = "";
            int cityId = -1;
            int provinceID = -1;
            DatabaseManager dbManager = new DatabaseManager();

            var areaName = (dbManager.ReturnQuery("SELECT Area_Name, Area_City_ID FROM Area WHERE Area_ID = " + areaID + ";"));
            foreach (var i in areaName)
            {
                fullName = i[0].ToString();
                cityId = Convert.ToInt32(i[1]);
            }
            var cityName = (dbManager.ReturnQuery("SELECT City_Name, City_Province_ID FROM City WHERE City_ID = " + cityId.ToString() + ";"));
            foreach (var j in cityName)
            {
                fullName = j[0].ToString() + ", " + fullName;
                provinceID = Convert.ToInt32(j[1]);
            }
            var provinceName = (dbManager.ReturnQuery("SELECT Province_Name FROM Province WHERE Province_ID = " + provinceID.ToString() + ";"));
            foreach (var k in provinceName)
            {
                fullName = k[0].ToString() + ", " + fullName;
            }
            return fullName;
        }

 public int GetPreferenceID(int clientID)
 {
     DatabaseManager dbManager = new DatabaseManager();
     int preferenceID = 0;
     var preferences = (dbManager.ReturnQuery("SELECT Preference_ID FROM Preference WHERE Preference_Client_ID = " + clientID.ToString() + " ORDER BY Preference_ID ASC;"));
     foreach (var i in preferences)
     {
         preferenceID = Convert.ToInt32(i[0]);
     }
     return preferenceID;
 }

 public List<int> GetListings(int agentID)
 {
     DatabaseManager dbManager = new DatabaseManager();
     List<int> listing_IDs = new List<int>();
     var listings = (dbManager.ReturnQuery("SELECT List_ID FROM Listing WHERE Agent_ID = " + agentID));
     foreach (var i in listings)
     {
         listing_IDs.Add(Convert.ToInt32(i[0]));
     }
     return listing_IDs;
 }

        public bool CanAddClient(string email)
        {
            DatabaseManager dbManager = new DatabaseManager();

            bool canAdd = true;

            var clientEmails = dbManager.ReturnQuery("SELECT Client_Email FROM Clients WHERE Client_Email = '" + email + "';");

            foreach (var clientEmail in clientEmails)
            {
                canAdd = false;
                break;
            }

            return canAdd;
        }

        public int AreaID(int cityID, string areaName)
        {
            DatabaseManager dbManger = new DatabaseManager();

            int areaID = -1;

            var restult = dbManger.ReturnQuery("SELECT Area_ID FROM Area WHERE Area_Name = '" + areaName + "' AND Area_City_ID = " + cityID.ToString() + ";");

            foreach (var i in restult)
            {
                areaID = int.Parse(i[0]);
                break;
            }

            return areaID;
        }

        public bool CanAddArea(string areaName,string cityName, string provinceName)
        {
            DatabaseManager dbManager = new DatabaseManager();

            bool canAdd = true;

            var result = dbManager.ReturnQuery("SELECT Area_Name FROM Area WHERE Area_Name ='" + areaName + "' AND Area_City_ID = " + CityID(cityName,ProvinceID(provinceName)).ToString() + ";");

            foreach (var i in result)
            {
                canAdd = false;
                break;
            }

            return canAdd;
        }

        public bool CanAddPreference(int clientID, int minBedrooms, int maxBedrooms, int minBathrooms, int maxBathrooms, int minGarages, int maxGarages, int minPlotSize, int maxPlotSize, int minHouseSize, int maxHouseSize, int minPrice, int maxPrice, int hasPool)
        {
            DatabaseManager dbManager = new DatabaseManager();

            bool canAdd = true;

            var preferenceEmails = dbManager.ReturnQuery("SELECT Preference_ID FROM Preference WHERE (Preference_Client_ID = " + clientID + " AND Preference_Min_Bedrooms = " + minBedrooms + " AND Preference_Max_Bedrooms = " + maxBedrooms + " AND Preference_Min_Bathrooms = " + minBathrooms + " AND Preference_Max_Bathrooms = " + maxBathrooms + " AND Preference_Min_Garages = " + minGarages + " AND Preference_Max_Garages = " + maxGarages + " AND Preference_hasPool = " + hasPool + " AND Preference_Min_Plot_Size = " + minPlotSize + " AND Preference_Max_Plot_Size = " + maxPlotSize + " AND Preference_Min_House_Size = " + minHouseSize + " AND Preference_Max_House_Size = " + maxHouseSize + " AND Preference_Min_Price = " + minPrice + " AND Preference_Max_Price = " + maxPrice + ");");

            foreach (var preferenceEmail in preferenceEmails)
            {
                canAdd = false;
                break;
            }

            return canAdd;
        }

        public bool CanAddAdmin(string username)
        {
            DatabaseManager dbManager = new DatabaseManager();

            bool canAdd = true;


            var adminUsernames = dbManager.ReturnQuery("SELECT Admin_Username FROM Admin WHERE Admin_Username = '" + username + "';");

            foreach (var adminUsername in adminUsernames)
            {
                canAdd = false;
                break;
            }

            return canAdd;

        }

 public int GetAgentID(string agentEmail)
 {
     DatabaseManager dbManager = new DatabaseManager();
     int agentID = -1;
     var agent = (dbManager.ReturnQuery("SELECT Agent_ID FROM Agent WHERE Agent_Email = '" + agentEmail + "'"));
     foreach (var i in agent)
     {
         agentID = Convert.ToInt32(i[0]);
     }
     return agentID;
 }

        private void Search()
        {
            new System.Threading.Thread(() =>
            {
                ClearPropertiesGrid();

                int searchColumn = GetSelectedSearchField();

                DatabaseManager dbManager = new DatabaseManager();

                var properties = dbManager.ReturnQuery("SELECT Property_ID, Property_Bedroom_Count, Property_Bathroom_Count, Property_Garage_Count, Property_hasPool, Property_Plot_Size, Property_House_Size FROM Property ORDER BY Property_ID ASC;");

                foreach (var property in properties)
                {
                    var agentIDs = dbManager.ReturnQuery("SELECT Agent_ID FROM Listing WHERE Property_ID = " + Convert.ToInt32(property[0]) + ";");
                    foreach (var agentID in agentIDs)
                    {
                        var agentEmails = dbManager.ReturnQuery("SELECT Agent_Email FROM Agent WHERE Agent_ID = " + Convert.ToInt32(agentID[0]) + ";");
                        foreach (var agentEmail in agentEmails)
                        {
                            if (property[searchColumn].ToString().Contains(GetSearchValue()))
                            {
                                InsertIntoPropertiesGrid(Convert.ToString(agentEmail[0]), Convert.ToInt32(property[1]), Convert.ToInt32(property[2]), Convert.ToInt32(property[3]), Convert.ToInt32(property[4]), Convert.ToInt32(property[5]), Convert.ToInt32(property[6]));
                            }
                        }
                    }
                }
            }).Start();
        }

        private bool UsernameInAdminTable(string username)
        {
            DatabaseManager dbManger = new DatabaseManager();

            var result = dbManger.ReturnQuery("SELECT Admin_Username FROM Admin WHERE Admin_Username = '" + username + "';");

            bool retValue = false;
            foreach (var r in result)
            {
                retValue = true;
                break;
            }
            return retValue;
        }

        private void Search()
        {
            new System.Threading.Thread(() =>
            {
                ClearClientsGrid();

                int searchColumn = GetSelectedSearchField();

                DatabaseManager dbManager = new DatabaseManager();

                var result = dbManager.ReturnQuery("SELECT Client_Name, Client_Surname, Client_Phone, Client_Email FROM Clients ORDER BY Client_Surname, Client_Name;");

                foreach( var client in result)
                {
                    if( client[searchColumn].ToString().Contains(GetSearchValue()) )
                    {
                        InsertIntoClientsGrid(client[0], client[1], client[2], client[3]);
                    }
                }
            }).Start();
        }

        private void RefreshProperties()
        {
            Console.WriteLine("=============================================Refresh");
            new System.Threading.Thread(() =>
            {
                ClearPropertiesGrid();
                DatabaseManager dbManager = new DatabaseManager();
                var properties = dbManager.ReturnQuery("SELECT Property_ID, Property_Bedroom_Count, Property_Bathroom_Count, Property_Garage_Count, Property_hasPool, Property_Plot_Size, Property_House_Size FROM Property ORDER BY Property_ID ASC;");

                foreach (var property in properties)
                {
                    Console.WriteLine("=============================================" + property[0]);
                    DatabaseManager dbManager2 = new DatabaseManager();
                    var agentIDs = dbManager2.ReturnQuery("SELECT Agent_ID FROM Listing WHERE Property_ID = " + Convert.ToInt32(property[0]) + ";");
                    foreach (var agentID in agentIDs)
                    {
                        Console.WriteLine("=============================================" + agentID[0]);
                        DatabaseManager dbManager3 = new DatabaseManager();
                        var agentEmails = dbManager3.ReturnQuery("SELECT Agent_Email FROM Agent WHERE Agent_ID = " + Convert.ToInt32(agentID[0]) + ";");
                        foreach (var agentEmail in agentEmails)
                        {
                            Console.WriteLine("============================================="+agentEmail[0]);
                            InsertIntoPropertiesGrid(Convert.ToString(agentEmail[0]), Convert.ToInt32(property[1]), Convert.ToInt32(property[2]), Convert.ToInt32(property[3]), Convert.ToInt32(property[4]), Convert.ToInt32(property[5]), Convert.ToInt32(property[6]));
                        }
                    }
                }
            }).Start();
        }