public static List<User> GetUsersConnections() { string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["kkcloudFreeDB"].ConnectionString; connection = new SqlConnection(connectionString); List<User> users = new List<User>(); try { SqlCommand command = connection.CreateCommand(); if (connection.State != ConnectionState.Open) connection.Open(); command.CommandType = CommandType.StoredProcedure; command.CommandText = "GetUsersConnections"; SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { string userID = "0"; while (reader.Read()) { Connection conn = new Connection{ ConnectionID = reader.GetString(1), UserAgent = reader.GetString(2), Timestamp = reader.GetDateTime(3), Connected = reader.GetBoolean(4) }; if (userID != reader.GetString(0)) { User u = new User(); u.ID = reader.GetString(0); users.Add(u); } users.Last().Connections.Add(conn); userID = reader.GetString(0); } } } catch (Exception ex) { throw ex; } finally { if (connection != null) if (connection.State != ConnectionState.Closed) connection.Close(); } return users; }
public static void UpdateUserConnection(string userID, Connection conn) { string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["kkcloudFreeDB"].ConnectionString; connection = new SqlConnection(connectionString); try { SqlCommand command = connection.CreateCommand(); if (connection.State != ConnectionState.Open) connection.Open(); //command.Parameters.AddWithValue("@bathId", bathroom.ID); //command.Parameters.AddWithValue("@occupiedTime", bathroom.LastOccupiedTime); command.CommandType = CommandType.StoredProcedure; command.CommandText = "UpdateUserConnection"; command.Parameters.AddWithValue("@UserID", userID); command.Parameters.AddWithValue("@ConnectionID", conn.ConnectionID); command.Parameters.AddWithValue("@UserAgent", conn.UserAgent); command.Parameters.AddWithValue("@TimeStamp", DateTime.Now); command.Parameters.AddWithValue("@Connected", conn.Connected); command.ExecuteNonQuery(); //command.Parameters.AddWithValue("@freedTime", DateTime.Now); } catch (Exception ex) { throw ex; } finally { if (connection != null) if (connection.State != ConnectionState.Closed) connection.Close(); } }