public static IEnumerable <ChatRoomModel> LoadAll()
{
using (var connection = new MySqlConnection(Settings.Default.ConnectionString))
{
connection.Open();
var query = "SELECT ChatRoom.Id as Id "
+ "FROM ChatRoom "
+ "INNER JOIN ChatMember ON Chatroom.Id = ChatMember.Chatroom_Id "
+ "WHERE ChatMember.Employee_Id = @id";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.Parameters.Add(new MySqlParameter("@id", MySqlDbType.Int32));
cmd.Prepare();
cmd.Parameters["@id"].Value = EmployeeModel.CurrentEmployee.Id;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
var chatRoom = new ChatRoomModel
{
Id = reader.GetInt32("Id")
};
chatRoom.Load();
yield return(chatRoom);
}
}
}
}
public static ChatRoomModel CreateChat(EmployeeModel with)
{
using (var connection = new MySqlConnection(Settings.Default.ConnectionString))
{
connection.Open();
//New Chatroom
var query = "INSERT INTO ChatRoom VALUES(null, @topic)";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.Parameters.Add(new MySqlParameter("@topic", MySqlDbType.String));
cmd.Prepare();
cmd.Parameters["@topic"].Value = $"Chat between {EmployeeModel.CurrentEmployee.Name} and {with.Name}";
cmd.ExecuteNonQuery();
//Get Chatroom-ID
query = "SELECT LAST_INSERT_ID() as Id";
cmd = new MySqlCommand(query, connection);
var reader = cmd.ExecuteReader();
int chatId = 0;
if (reader.Read())
{
chatId = reader.GetInt32("Id");
}
reader.Close();
//New ChatMember
cmd.CommandText = "INSERT INTO ChatMember VALUES(null, @member, @chatroom)";
cmd.Parameters.Add(new MySqlParameter("@member", MySqlDbType.Int32));
cmd.Parameters.Add(new MySqlParameter("@chatroom", MySqlDbType.Int32));
cmd.Prepare();
cmd.Parameters["@chatroom"].Value = chatId;
//Add me
cmd.Parameters["@member"].Value = EmployeeModel.CurrentEmployee.Id;
cmd.ExecuteNonQuery();
//Add with
cmd.Parameters["@member"].Value = with.Id;
cmd.ExecuteNonQuery();
var newChatModel = new ChatRoomModel
{
Id = chatId
};
newChatModel.Load();
return(newChatModel);
}
}
public static ChatRoomModel CreateChat(EmployeeModel with)
{
using (var connection = new MySqlConnection(Settings.Default.ConnectionString))
{
connection.Open();
//New Chatroom
var query = "INSERT INTO ChatRoom VALUES(null, @topic)";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.Parameters.Add(new MySqlParameter("@topic", MySqlDbType.String));
cmd.Prepare();
cmd.Parameters["@topic"].Value = $"Chat between {EmployeeModel.CurrentEmployee.Name} and {with.Name}";
cmd.ExecuteNonQuery();
//Get Chatroom-ID
query = "SELECT LAST_INSERT_ID() as Id";
cmd = new MySqlCommand(query, connection);
var reader = cmd.ExecuteReader();
int chatId = 0;
if (reader.Read())
{
chatId = reader.GetInt32("Id");
}
reader.Close();
//New ChatMember
cmd.CommandText = "INSERT INTO ChatMember VALUES(null, @member, @chatroom)";
cmd.Parameters.Add(new MySqlParameter("@member", MySqlDbType.Int32));
cmd.Parameters.Add(new MySqlParameter("@chatroom", MySqlDbType.Int32));
cmd.Prepare();
cmd.Parameters["@chatroom"].Value = chatId;
//Add me
cmd.Parameters["@member"].Value = EmployeeModel.CurrentEmployee.Id;
cmd.ExecuteNonQuery();
//Add with
cmd.Parameters["@member"].Value = with.Id;
cmd.ExecuteNonQuery();
var newChatModel = new ChatRoomModel
{
Id = chatId
};
newChatModel.Load();
return newChatModel;
}
}
public static IEnumerable<ChatRoomModel> LoadAll()
{
using (var connection = new MySqlConnection(Settings.Default.ConnectionString))
{
connection.Open();
var query = "SELECT ChatRoom.Id as Id "
+ "FROM ChatRoom "
+ "INNER JOIN ChatMember ON Chatroom.Id = ChatMember.Chatroom_Id "
+ "WHERE ChatMember.Employee_Id = @id";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.Parameters.Add(new MySqlParameter("@id", MySqlDbType.Int32));
cmd.Prepare();
cmd.Parameters["@id"].Value = EmployeeModel.CurrentEmployee.Id;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
var chatRoom = new ChatRoomModel
{
Id = reader.GetInt32("Id")
};
chatRoom.Load();
yield return chatRoom;
}
}
}
}