C# DbConnection.CloseConnection方法代码示例

 public static ArrayList retrieveConversation(LoggedUser user, Host host)
 {
     ArrayList conversation = new ArrayList(new ArrayList());
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     int conversation_id = 0;
     String query = "SELECT id FROM Conversation WHERE [email protected] AND [email protected]";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@host", host.loginName);
     command.Parameters.AddWithValue("@user", user.loginName);
     SqlDataReader reader = command.ExecuteReader();
     if (reader.Read())
     {
         conversation_id = reader.GetInt32(0);
     }
     db.CloseConnection();
     db.OpenConnection();
     String query1 = "SELECT id,content,initiator,message_read FROM Message WHERE [email protected]";
     SqlCommand command1 = new SqlCommand(query1, connection);
     command1.Parameters.AddWithValue("@id", conversation_id);
     SqlDataReader reader1 = command1.ExecuteReader();
     while (reader1.Read())
     {
         int id = reader1.GetInt32(0); 
         String message = reader1.GetString(1);
         String initiator = reader1.GetString(2);
         int read = reader1.GetInt32(3);
         ArrayList array = new ArrayList();
         array.Add(message); array.Add(initiator); array.Add(read); array.Add(id);
         conversation.Add(array);
     }
     db.CloseConnection();
     return conversation;
 }

    public Boolean saveImageInDb()
    {
        DbConnection db = new DbConnection();
        SqlConnection connection = db.OpenConnection();
        String query = "INSERT INTO Images (name, contentType, property, userLogin, data) VALUES (@name, @contentType, @property, @user, @data);";
        SqlCommand command = new SqlCommand(query, connection);
        command.Parameters.AddWithValue("@name", name);
        command.Parameters.AddWithValue("@contentType", contentType);
        if (propertyId != -1)
        {
            command.Parameters.AddWithValue("@property", propertyId);
        }
        else
        {
            command.Parameters.AddWithValue("@property", "NULL");
        }
        command.Parameters.AddWithValue("@user", user);
        command.Parameters.AddWithValue("@data", bytes);

        try
        {
            System.Diagnostics.Debug.Write(command.CommandText);
            command.ExecuteNonQuery();
            db.CloseConnection();
            return true;
        }
        catch (Exception ex)
        {
            System.Diagnostics.Debug.Write(ex.Message);
            db.CloseConnection();
            return false;
        }
    }

 /**Check if host has any properties to display in his profile*/
 public Boolean checkProperty(String loginName)
 {
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "SELECT host FROM Property WHERE host = @loginName;";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@loginName", loginName);
     Object id = command.ExecuteScalar();
     db.CloseConnection();
     if (id == null)
         return false;
     else
         return true;
 }

 public Boolean checkLogin(String loginName, String password)
 {
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "SELECT login FROM Host WHERE login = @login AND password = @password;";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@login", loginName);
     command.Parameters.AddWithValue("@password", password);
     Object id = command.ExecuteScalar();
     db.CloseConnection();
     if (id == null)
         return false;
     else
         return true;
 }

 public Boolean SignUp(String loginName, String name, String email, String password)
 {
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "INSERT INTO LoggedUser (login, name, email, password) VALUES (@login, @name, @email, @password);";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@login", loginName);
     command.Parameters.AddWithValue("@name", name);
     command.Parameters.AddWithValue("@email", email);
     command.Parameters.AddWithValue("@password", password);
     int row = command.ExecuteNonQuery();
     db.CloseConnection();
     if (row == 0)
         return false;
     else
         return true;
 }

    public static ArrayList retrieveUsers(int conversationid)
    {
        ArrayList users = new ArrayList();
        DbConnection db = new DbConnection();
        SqlConnection connection = db.OpenConnection();

        String query1 = "SELECT host, loggedUser FROM Conversation WHERE [email protected]";
        SqlCommand command1 = new SqlCommand(query1, connection);
        command1.Parameters.AddWithValue("@id", conversationid);
        SqlDataReader reader1 = command1.ExecuteReader();
        while (reader1.Read())
        {
            String host = reader1.GetString(0);
            String user = reader1.GetString(1);
            Host h = Host.retrieveHost(host);
            LoggedUser u = LoggedUser.retrieveUser(user);
            users.Add(h);
            users.Add(u);
        }
        db.CloseConnection();
        return users;
    }

 /**retrieve properties of a particular host*/
 public List<Property> retrievePropertyByHost(String loginName)
 {
     List<Property> property = new List<Property>();
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "SELECT * FROM Property WHERE host = @loginName;";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@loginName", loginName);
     SqlDataReader reader = command.ExecuteReader();
     while (reader.Read())
     {
         int id = reader.GetInt32(0);
         String name = reader.GetString(1);
         String locat = reader.GetString(2);
         int noRooms = reader.GetInt32(3);
         int noGuests = reader.GetInt32(4);
         double price = reader.GetDouble(5);
         Host host = Host.retrieveHost(loginName);
         Property p = new Property(id, name, locat, noRooms, noGuests, price, null, host);
         property.Add(p);
     }
     db.CloseConnection();
     return property;
 }

 public static List<Booking> getPendingBookings(String hostLogin)
 {
     List<Booking> booking = new List<Booking>();
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "SELECT * FROM Booking INNER JOIN Property ON Property.id = property WHERE Property.host = @host AND status = 'PENDING'";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@host", hostLogin);
     SqlDataReader reader = command.ExecuteReader();
     while (reader.Read())
     {
         int id = reader.GetInt32(0);
         String start = reader.GetDateTime(1).ToShortDateString();
         String end = reader.GetDateTime(2).ToShortDateString();
         String user = reader.GetString(3);
         int propertyId = reader.GetInt32(4);
         String status = reader.GetString(5);
         LoggedUser loggedUser = LoggedUser.retrieveUser(user);
         Property property = Property.retrieveProperty(propertyId);
         booking.Add(new Booking(id, loggedUser, property, start, end, status));
     }
     db.CloseConnection();
     return booking;
 }

 public static List<DateTime> checkPendingBookedDates(int propertyId)
 {
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     List<DateTime> propDates = new List<DateTime>();
     int propCount = isPropertyExist(propertyId);
     if (propCount != 0)
     {
         DateTime propStartDate, propEndDate;
         String query = "SELECT start_date, end_date FROM Booking WHERE property = @propertyId AND status = 'PENDING';";
         SqlCommand command = new SqlCommand(query, connection);
         command.Parameters.AddWithValue("@propertyId", propertyId);
         SqlDataReader reader = command.ExecuteReader();
         while (reader.Read())
         {
             propStartDate = reader.GetDateTime(0);
             propEndDate = reader.GetDateTime(1);
             for (var date = propStartDate; date <= propEndDate; date = date.AddDays(1))
             {
                 propDates.Add(date);
             }
         }
     }
     else
     {
         db.CloseConnection();
         propDates = null;
     }
     db.CloseConnection();
     return propDates;
 }

 public Boolean addProperty(String loginName, Property property)
 {
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "INSERT INTO Property (name, location, no_of_rooms, no_of_guests, price, host) VALUES (@name, @location, @noRooms, @noGuests, @price, @host); SELECT SCOPE_IDENTITY();";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@name", property.name);
     command.Parameters.AddWithValue("@location", property.location);
     command.Parameters.AddWithValue("@noRooms", property.numberRooms);
     command.Parameters.AddWithValue("@noGuests", property.numberGuests);
     command.Parameters.AddWithValue("@price", property.price);
     command.Parameters.AddWithValue("@host", loginName);
     Object id = command.ExecuteScalar();
     db.CloseConnection();
     if (id == null)
         return false;
     else
     {
         return true;
     }
 }

 public List<Property> searchByCompleteCriteria(String location, double maxPrice, int noGuests)
 {
     List<Property> properties = new List<Property>();
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "SELECT * FROM Property WHERE no_of_guests >= @numberGuests AND price <= @maxPrice AND location LIKE @location ORDER BY price ASC;";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@numberGuests", noGuests);
     command.Parameters.AddWithValue("@maxPrice", maxPrice);
     command.Parameters.AddWithValue("@location", "%" + location + "%");
     SqlDataReader reader = command.ExecuteReader();
     while (reader.Read())
     {
         int id = reader.GetInt32(0);
         String name = reader.GetString(1);
         String locat = reader.GetString(2);
         int noRooms = reader.GetInt32(3);
         int nuGuests = reader.GetInt32(4);
         double price = reader.GetDouble(5);
         String hostLoginName = reader.GetString(6);
         Host host = Host.retrieveHost(hostLoginName);
         properties.Add(new Property(id, name, locat, noRooms, nuGuests, price, null, host));
     }
     db.CloseConnection();
     return properties;
 }

 public static Property retrieveProperty(int id)
 {
     Property property = new Property();
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "SELECT * FROM Property WHERE id = @id;";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@id", id);
     SqlDataReader reader = command.ExecuteReader();
     while (reader.Read())
     {
         String name = reader.GetString(1);
         String locat = reader.GetString(2);
         int noRooms = reader.GetInt32(3);
         int noGuests = reader.GetInt32(4);
         double price = reader.GetDouble(5);
         String hostLoginName = reader.GetString(6);
         Host host = Host.retrieveHost(hostLoginName);
         property = new Property(id, name, locat, noRooms, noGuests, price, null, host);
     }
     db.CloseConnection();
     return property;
 }

 public Boolean loadImageFromDb(int i)
 {
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "SELECT * FROM Images WHERE id = @id;";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@id", i);
     SqlDataReader reader = command.ExecuteReader();
     while (reader.Read())
     {
         id = reader.GetInt32(0);
         name = reader.GetString(1);
         contentType = reader.GetString(2);
         long len = reader.GetBytes(5, 0, null, 0, 0);
         bytes = new Byte[len];
         reader.GetBytes(5, 0, bytes, 0, (int)len);
         bytesInBase64 = Convert.ToBase64String(bytes, 0, bytes.Length);
         user = reader.GetString(4);
         propertyId = reader.GetInt32(3);
         db.CloseConnection();
         return true;
     }
     db.CloseConnection();
     return false;
 }

    public static Boolean updateMessageToRead(int id)
    {
        DbConnection db = new DbConnection();
        SqlConnection connection = db.OpenConnection();

        String query = "UPDATE Message SET message_read = 1 WHERE [email protected];";
        SqlCommand command = new SqlCommand(query, connection);
        command.Parameters.AddWithValue("@id", id);
        command.ExecuteNonQuery();
        
        db.CloseConnection();

        return true;
    }

 public List<Image> loadImagesFromDbWithPropertyId(int i)
 {
     List<Image> images = new List<Image>();
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "SELECT * FROM Images WHERE property = @id;";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@id", i);
     SqlDataReader reader = command.ExecuteReader();
     while (reader.Read())
     {
         int id = reader.GetInt32(0);
         String name = reader.GetString(1);
         String contentType = reader.GetString(2);
         long len = reader.GetBytes(5, 0, null, 0, 0);
         byte[] bytes = new Byte[len];
         reader.GetBytes(5, 0, bytes, 0, (int)len);
         int property = reader.GetInt32(3);
         String user = reader.GetString(4);
         images.Add(new Image(id, name, contentType, bytes, user, property));
     }
     db.CloseConnection();
     return images;
 }