C# Classes.DatabaseManager类代码示例

        public bool EditPassword(string email, string newPassword)
        {
            DatabaseManager dbManager = new DatabaseManager();
            Cryptography crypto = new Cryptography();

            return (dbManager.NonReturnQuery("UPDATE Clients set Client_Password = '" + crypto.EncryptString(newPassword) + "' WHERE Client_Email ='" + email + "';"));
        }

        public bool EditPassword(string email, string newPassword)
        {
            DatabaseManager dbManager = new DatabaseManager();
            Cryptography crypto = new Cryptography();

            return (dbManager.NonReturnQuery("UPDATE Agent set Agent_Password = '" + Cryptography.CreateHash(newPassword) + "' WHERE Agent_Email ='" + email + "';"));
        }

        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 bool AddAdmin(string username, string password)
        {
            DatabaseManager dbManager = new DatabaseManager();
            Cryptography crypto = new Cryptography();

            return (dbManager.NonReturnQuery("INSERT INTO Admin (Admin_Username , Admin_Password) VALUES ('" + username + "','" + crypto.EncryptString(password) + "');"));
        }

        public bool EditPassword(string username, string newPassword)
        {
            DatabaseManager dbManager = new DatabaseManager();
            Cryptography crypto = new Cryptography();

            return (dbManager.NonReturnQuery("UPDATE Admin set Admin_Password = '" + crypto.EncryptString(newPassword) + "' WHERE Admin_Username ='" + username + "';"));
        }

 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 + ");"));
 }

        public bool AddClient(string name, string surname, string phone, string email, string password)
        {
            DatabaseManager dbManager = new DatabaseManager();
            Cryptography crypto = new Cryptography();

            return (dbManager.NonReturnQuery("INSERT INTO Clients (Client_Name , Client_Surname, Client_Phone, Client_Email, Client_Password) VALUES ('" + name + "','" + surname + "','" + phone + "','" + email + "','" + crypto.EncryptString(password) + "');"));
        }

        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 bool AddAgent(string name, string surname, string phone, string email, string password)
        {
            DatabaseManager dbManager = new DatabaseManager();
            Cryptography crypto = new Cryptography();
            MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand();

            return (dbManager.NonReturnQuery("INSERT INTO Agent (Agent_Name , Agent_Surname, Agent_Phone, Agent_Email, Agent_Password) VALUES ('" + name + "','" + surname + "','" + phone + "','" + email + "','" + Cryptography.CreateHash(password).ToString() + "');"));
        }

        public bool AddPreferenceArea(int preferenceID, string areaIn)
        {
            string[] area;
            string[] dell = new string[] { ", " };
            area = areaIn.Split(dell, System.StringSplitOptions.None);
            DatabaseManager dbManager = new DatabaseManager();
            LocationManager locManager = new LocationManager();

            return (dbManager.NonReturnQuery("INSERT INTO Preference_Area (Preference_ID, Area_ID) values (" + preferenceID + "," + locManager.AreaID(locManager.CityID(area[1], locManager.ProvinceID(area[0])), area[2]) + ");"));
        }

        public bool DeletePreferenceArea(int preferenceID, string areaIn)
        {
            string[] area;
            string[] dell = new string[] { ", " };
            area = areaIn.Split(dell, System.StringSplitOptions.None);
            DatabaseManager dbManager = new DatabaseManager();
            LocationManager locManager = new LocationManager();

            return (dbManager.NonReturnQuery("DELETE FROM Preference_Area WHERE Preference_ID = " + preferenceID + " AND Area_ID = " + locManager.AreaID(locManager.CityID(area[1], locManager.ProvinceID(area[0])), area[2]) + ";"));
        }

 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 int GetAgentID(string email)
        {
            DatabaseManager dbManager = new DatabaseManager();

            int agentID = -1;

            var agentIDs = dbManager.ReturnQuery("SELECT Agent_ID FROM Agent WHERE Agent_Email = '" + email + "';");

            foreach (var id in agentIDs)
            {
                agentID = Convert.ToInt32(id[0]);
            }

            return agentID;
        }

        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;
        }