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