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