/// <summary>
/// Deletes the user group associated with the given name.
/// All members associated with the group are removed.
/// </summary>
/// <returns><c>true</c>, if user group was deleted, <c>false</c> otherwise.</returns>
/// <param name="groupName">Group name.</param>
/// <param name="excepts">Exceptions.</param>
/*public bool DeleteUserGroup(string groupName, ref List<Exception> excepts)
* {
* const string queryString =
* "DELETE FROM guacamole_entity " +
* "WHERE name=@groupname AND type='USER_GROUP'";
*
* Queue<string> argNames = new Queue<string>();
* argNames.Enqueue("@groupname");
*
* Queue<string> args = new Queue<string>();
* args.Enqueue(groupName);
*
* //Insert the usergroup into the entity table
* return DeleteQuery(queryString, argNames, args, ref excepts);
* }*/
/// <summary>
/// Deletes the connection group associated with the given group name.
/// All connections associated with this connection group are auto deleted
/// </summary>
/// <returns><c>true</c>, if connection group was deleted, <c>false</c> otherwise.</returns>
/// <param name="groupName">Group name.</param>
/// <param name="excepts">Exceptions.</param>
public bool DeleteConnectionGroup(string groupId, ref List <Exception> excepts)
{
const string queryString =
"DELETE FROM guacamole_connection_group " +
"WHERE connection_group_id=@id";
try
{
using (GuacamoleDatabaseConnector gdbc = new GuacamoleDatabaseConnector(ref excepts))
{
using (MySqlCommand query = new MySqlCommand(queryString, gdbc.getConnection()))
{
query.Prepare();
//Add the agrument names and values
query.Parameters.AddWithValue("@id", groupId);
return(query.ExecuteNonQuery() > 0);
}
}
}
catch (Exception e)
{
excepts.Add(e);
return(false);
}
}
/// <summary>
/// Searchs for the name of a specified group in the connection group table.
/// </summary>
/// <returns><c>true</c>, if group name was found, <c>false</c> otherwise.</returns>
/// <param name="groupName">Group name.</param>
public int GetConnectionGroupId(string groupName, ref List <Exception> excepts)
{
const string queryString =
"SELECT connection_group_id FROM guacamole_connection_group " +
"WHERE connection_group_name=@name";
try
{
using (GuacamoleDatabaseConnector gdbc = new GuacamoleDatabaseConnector(ref excepts))
{
using (MySqlCommand query = new MySqlCommand(queryString, gdbc.getConnection()))
{
query.Prepare();
//Add the agruments given
query.Parameters.AddWithValue("@name", groupName);
//Collect the query result column
using (MySqlDataReader reader = query.ExecuteReader())
{
while (reader.Read())
{
return(Int32.Parse(reader.GetValue(0).ToString()));
}
}
}
}
}
catch (Exception e)
{
excepts.Add(e);
}
return(-1);
}
/// <summary>
/// General format for running a search query on the guacamole database
/// </summary>
/// <returns><c>true</c>, if field was found, <c>false</c> otherwise.</returns>
/// <param name="queryString">Query string.</param>
/// <param name="arg">Argument.</param>
/// <param name="excepts">Exceptions.</param>
private bool SearchQuery(string queryString, string arg, ref List <Exception> excepts)
{
try
{
using (GuacamoleDatabaseConnector gdbc = new GuacamoleDatabaseConnector(ref excepts))
{
using (MySqlCommand query = new MySqlCommand(queryString, gdbc.getConnection()))
{
query.Prepare();
//Add the agruments given if found
if (arg != null)
{
query.Parameters.AddWithValue("@input", arg);
}
return((Convert.ToInt32(query.ExecuteScalar()) > 0) ? true : false);
}
}
}
catch (Exception e)
{
excepts.Add(e);
return(false);
}
}
/// <summary>
/// Searchs for the name of a specified group in the connection group table.
/// </summary>
/// <returns><c>true</c>, if group name was found, <c>false</c> otherwise.</returns>
public bool UpdateRemoveConnectionGroupConnection(int connectionId, ref List <Exception> excepts)
{
const string queryString =
"UPDATE guacamole_connection SET parent_id = NULL WHERE connection_id = @connectionId";
try
{
using (GuacamoleDatabaseConnector gdbc = new GuacamoleDatabaseConnector(ref excepts))
{
using (MySqlCommand query = new MySqlCommand(queryString, gdbc.getConnection()))
{
query.Prepare();
//Add the agruments given
query.Parameters.AddWithValue("@connectionId", connectionId);
return(query.ExecuteNonQuery() > 0);
}
}
}
catch (Exception e)
{
excepts.Add(e);
return(false);
}
}
/// <summary>
/// Searchs for the name of a specified group in the connection group table.
/// </summary>
/// <returns><c>true</c>, if group name was found, <c>false</c> otherwise.</returns>
public List <GroupForListDto> GetConnectionGroupInfo(string dawgtag, ref List <Exception> excepts)
{
List <GroupForListDto> userGroupInfo = new List <GroupForListDto>();
const string queryString =
"SELECT guacamole_connection_group.connection_group_id, guacamole_connection_group.connection_group_name, guacamole_connection_group.max_connections, guacamole_connection_group.enable_session_affinity FROM guacamole_entity AS e1, guacamole_entity AS e2, guacamole_connection_group, guacamole_connection_group_permission, guacamole_user_group_member, guacamole_user_group " +
"WHERE e1.name = @name AND e1.entity_id = member_entity_id AND guacamole_user_group_member.user_group_id = guacamole_user_group.user_group_id AND " +
"guacamole_user_group.entity_id = e2.entity_id AND e2.entity_id = guacamole_connection_group_permission.entity_id AND guacamole_connection_group_permission.connection_group_id = guacamole_connection_group.connection_group_id";
try
{
using (GuacamoleDatabaseConnector gdbc = new GuacamoleDatabaseConnector(ref excepts))
{
using (MySqlCommand query = new MySqlCommand(queryString, gdbc.getConnection()))
{
query.Prepare();
//Add the agruments given
query.Parameters.AddWithValue("@name", dawgtag);
//Collect the query result column
using (MySqlDataReader reader = query.ExecuteReader())
{
while (reader.Read())
{
GroupForListDto infoDto = new GroupForListDto();
infoDto.Id = Int32.Parse(reader.GetValue(0).ToString());
infoDto.Name = reader.GetValue(1).ToString();
if (reader.GetValue(2).ToString() != String.Empty)
{
infoDto.Max = Int32.Parse(reader.GetValue(2).ToString());
}
if (reader.GetValue(3).ToString() == "0")
{
infoDto.Affinity = false;
}
else
{
infoDto.Affinity = true;
}
userGroupInfo.Add(infoDto);
}
}
return(userGroupInfo);
}
}
}
catch (Exception e)
{
excepts.Add(e);
Console.WriteLine("\n\n\n\n" + e.Message);
return(null);
}
}
/// <summary>
/// Searchs for the name of a specified group in the connection group table.
/// </summary>
/// <returns><c>true</c>, if group name was found, <c>false</c> otherwise.</returns>
public List <ConnectionForListDto> GetAllGroupConnections(int id, ref List <Exception> excepts)
{
List <ConnectionForListDto> connectionInfo = new List <ConnectionForListDto>();
const string queryString =
"SELECT connection_id, connection_name, protocol, max_connections, parent_id FROM guacamole_connection " +
"WHERE parent_id = @id";
try
{
using (GuacamoleDatabaseConnector gdbc = new GuacamoleDatabaseConnector(ref excepts))
{
using (MySqlCommand query = new MySqlCommand(queryString, gdbc.getConnection()))
{
query.Prepare();
//Add the agruments given
query.Parameters.AddWithValue("@id", id);
//Collect the query result column
using (MySqlDataReader reader = query.ExecuteReader())
{
while (reader.Read())
{
ConnectionForListDto infoDto = new ConnectionForListDto();
infoDto.Id = Int32.Parse(reader.GetValue(0).ToString());
infoDto.Name = reader.GetValue(1).ToString();
infoDto.Protocol = reader.GetValue(2).ToString();
if (reader.GetValue(3).ToString() != String.Empty)
{
infoDto.MaxConnections = Int32.Parse(reader.GetValue(3).ToString());
}
if (reader.GetValue(4).ToString() != String.Empty)
{
infoDto.HasGroup = true;
}
else
{
infoDto.HasGroup = false;
}
connectionInfo.Add(infoDto);
}
}
return(connectionInfo);
}
}
}
catch (Exception e)
{
excepts.Add(e);
Console.WriteLine("\n\n\n\n" + e.Message);
return(null);
}
}
/// <summary>
/// Searchs for the name of a specified group in the connection group table.
/// </summary>
/// <returns><c>true</c>, if group name was found, <c>false</c> otherwise.</returns>
public GroupForListDto GetAllConnectionGroupInfo(int connectionGroupId, ref List <Exception> excepts)
{
GroupForListDto infoDto = new GroupForListDto();
const string queryString =
"SELECT guacamole_connection_group.connection_group_id, guacamole_connection_group.connection_group_name, guacamole_connection_group.max_connections, guacamole_connection_group.enable_session_affinity FROM guacamole_connection_group " +
"WHERE guacamole_connection_group.connection_group_id = @id";
try
{
using (GuacamoleDatabaseConnector gdbc = new GuacamoleDatabaseConnector(ref excepts))
{
using (MySqlCommand query = new MySqlCommand(queryString, gdbc.getConnection()))
{
query.Prepare();
//Add the agruments given
query.Parameters.AddWithValue("@id", connectionGroupId);
//Collect the query result column
using (MySqlDataReader reader = query.ExecuteReader())
{
while (reader.Read())
{
infoDto.Id = Int32.Parse(reader.GetValue(0).ToString());
infoDto.Name = reader.GetValue(1).ToString();
if (reader.GetValue(2).ToString() != String.Empty)
{
infoDto.Max = Int32.Parse(reader.GetValue(2).ToString());
}
if (reader.GetValue(3).ToString() == "0")
{
infoDto.Affinity = false;
}
else
{
infoDto.Affinity = true;
}
}
}
return(infoDto);
}
}
}
catch (Exception e)
{
excepts.Add(e);
Console.WriteLine("\n\n\n\n" + e.Message);
return(null);
}
}
/// <summary>
/// Searchs for the name of a specified group in the connection group table.
/// </summary>
/// <returns><c>true</c>, if group name was found, <c>false</c> otherwise.</returns>
public UserForListDto GetAllConnectionGroupUsers(int id, ref List <Exception> excepts)
{
UserForListDto userInfo = new UserForListDto();
List <string> dawgtags = new List <string>();
const string queryString =
"SELECT guacamole_entity.name, guacamole_entity.entity_id FROM guacamole_connection_group_permission AS permiss, guacamole_user_group, guacamole_user_group_member AS mem, guacamole_entity " +
"WHERE permiss.connection_group_id = @id AND permiss.entity_id = guacamole_user_group.entity_id AND mem.user_group_id = guacamole_user_group.user_group_id AND guacamole_entity.entity_id = mem.member_entity_id";
try
{
using (GuacamoleDatabaseConnector gdbc = new GuacamoleDatabaseConnector(ref excepts))
{
using (MySqlCommand query = new MySqlCommand(queryString, gdbc.getConnection()))
{
query.Prepare();
//Add the agruments given
query.Parameters.AddWithValue("@id", id);
//Collect the query result column
using (MySqlDataReader reader = query.ExecuteReader())
{
while (reader.Read())
{
dawgtags.Add(reader.GetValue(0).ToString());
userInfo.Id = Int32.Parse(reader.GetValue(1).ToString());
}
}
userInfo.Users = dawgtags;
return(userInfo);
}
}
}
catch (Exception e)
{
excepts.Add(e);
Console.WriteLine("\n\n\n\n" + e.Message);
return(null);
}
}
/// <summary>
/// Deletes the user from user group.
/// </summary>
/// <returns><c>true</c>, if user was deleted from the user group, <c>false</c> otherwise.</returns>
/// <param name="groupName">Group name.</param>
/// <param name="dawgtag">Dawgtag.</param>
/// <param name="excepts">Excepts.</param>
public bool DeleteUserFromUserGroup(int groupId, string dawgtag, ref List <Exception> excepts)
{
const string nameQueryString =
"(SELECT connection_group_name FROM guacamole_connection_group " +
"WHERE connection_group_id=@id)";
const string groupIdQueryString =
"(SELECT user_group_id FROM guacamole_user_group, guacamole_entity " +
"WHERE name =" + nameQueryString + " AND type = 'USER_GROUP' " +
"AND guacamole_user_group.entity_id=guacamole_entity.entity_id)";
const string userIdQueryString =
"(SELECT entity_id FROM guacamole_entity " +
"WHERE name = @username AND type = 'USER')";
const string memberQueryString =
"DELETE FROM guacamole_user_group_member " +
"WHERE user_group_id=" + groupIdQueryString + " AND member_entity_id =" + userIdQueryString;
try
{
using (GuacamoleDatabaseConnector gdbc = new GuacamoleDatabaseConnector(ref excepts))
{
using (MySqlCommand query = new MySqlCommand(memberQueryString, gdbc.getConnection()))
{
query.Prepare();
//Add the agrument names and values
query.Parameters.AddWithValue("@id", groupId);
query.Parameters.AddWithValue("@username", dawgtag);
return(query.ExecuteNonQuery() > 0);
}
}
}
catch (Exception e)
{
excepts.Add(e);
return(false);
}
}
/// <summary>
/// General format for running a insertion query on the guacamole database
/// </summary>
/// <returns><c>true</c>, if the values were inserted, <c>false</c> otherwise.</returns>
/// <param name="queryString">Query string.</param>
/// <param name="argNames">Argument names.</param>
/// <param name="args">Arguments.</param>
/// <param name="excepts">Exceptions.</param>
private bool InsertQuery(string queryString, Queue <string> argNames, Queue <string> args, ref List <Exception> excepts)
{
const string exceptMessage = "The database arguments and argument names are not the same size.";
//Validate if the arguments and names are the correct amount
if (args.Count != argNames.Count)
{
excepts.Add(new GuacamoleDatabaseException(exceptMessage));
return(false);
}
//Make a deep copy of the queues to ensure data consistancy
Queue <String> copiedArgNames = new Queue <String>(argNames.ToArray());
Queue <String> copiedArgs = new Queue <String>(args.ToArray());
try
{
using (GuacamoleDatabaseConnector gdbc = new GuacamoleDatabaseConnector(ref excepts))
{
using (MySqlCommand query = new MySqlCommand(queryString, gdbc.getConnection()))
{
query.Prepare();
//Add the agrument names and values NOTE: ORDER MATTERS
while (copiedArgs.Count > 0 && copiedArgNames.Count > 0)
{
Console.Error.Write("Name = " + copiedArgNames.Peek() + " Arg = " + copiedArgs.Peek() + ".\n\n");
query.Parameters.AddWithValue(copiedArgNames.Dequeue(), copiedArgs.Dequeue());
}
return(query.ExecuteNonQuery() > 0);
}
}
}
catch (Exception e)
{
excepts.Add(e);
return(false);
}
}