C# DbConnection.OpenConnection方法代码示例

 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 host=[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 uniqness of username return ture if the username is unique*/
 public Boolean checkUsername(String loginName)
 {
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "SELECT login FROM LoggedUser WHERE login = @login;";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@login", loginName);
     Object id = command.ExecuteScalar();
     if (id == null)
         return true;
     else
         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 Boolean createBooking(LoggedUser loggedUser, Property property, String startDate, String endDate)
 {
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "INSERT INTO Booking (start_date, end_date, loggedUser, property, status) VALUES (@startDate, @endDate, @user, @property, @status);";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@startDate", Convert.ToDateTime(startDate));
     command.Parameters.AddWithValue("@endDate", Convert.ToDateTime(endDate));
     command.Parameters.AddWithValue("@user", loggedUser.loginName);
     command.Parameters.AddWithValue("@property", property.propertyId);
     command.Parameters.AddWithValue("@status", "PENDING");
     int row = command.ExecuteNonQuery();
     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;
    }

 public static Property retrieveProperty(String name)
 {
     Property property = new Property();
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "SELECT * FROM Property WHERE name = @name;";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@name", name);
     SqlDataReader reader = command.ExecuteReader();
     while (reader.Read())
     {
         int id = reader.GetInt32(0);
         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 static List<Booking> getAcceptedBookings(String userLogin)
 {
     List<Booking> booking = new List<Booking>();
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "SELECT * FROM Booking WHERE status = 'ACCEPTED' AND DATEDIFF(DAY, GETDATE(), start_date) >= 0 AND loggedUser = @user";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@user", userLogin);
     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<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 static Host retrieveHost(String loginName)
 {
     Host result = new Host();
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "SELECT * FROM Host WHERE login = @login;";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@login", loginName);
     SqlDataReader reader = command.ExecuteReader();
     while (reader.Read())
     {
         String name = reader.GetString(1);
         String email = reader.GetString(2);
         String password = reader.GetString(3);
         String contactNo = reader.GetString(4);
         result = new Host(1, loginName, name, email, password, contactNo);
     }
     db.CloseConnection();
     return result;
 }

 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 List<Property> searchProperty(double maxPrice)
 {
     List<Property> properties = new List<Property>();
     DbConnection db = new DbConnection();
     SqlConnection connection = db.OpenConnection();
     String query = "SELECT * FROM Property WHERE price <= @maxPrice;";
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.AddWithValue("@maxPrice", maxPrice);
     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);
         String hostLoginName = reader.GetString(6);
         Host host = Host.retrieveHost(hostLoginName);
         properties.Add(new Property(id, name, locat, noRooms, noGuests, price, null, host));
     }
     db.CloseConnection();
     return properties;
 }