Store UID from message downloaded using POP protocol C#

Question

I'm here to ask you the following: how do I store the Unique ID from a message in a POP server into my database, when using the OpenPop.Net library? Here's the "FetchMessage" function:

public static List<OpenPop.Mime.Message> FetchUnseenMessages(string hostname, int port, bool useSsl, string username, string password, List<string> seenUids)
    {
        // The client disconnects from the server when being disposed
        using (Pop3Client client = new Pop3Client())
        {
            // Connect to the server
            client.Connect(hostname, port, useSsl);

            // Authenticate ourselves towards the server
            client.Authenticate(username, password, AuthenticationMethod.UsernameAndPassword);

            // Fetch all the current uids seen
            List<string> uids = client.GetMessageUids();



            // Create a list we can return with all new messages
            List<OpenPop.Mime.Message> newMessages = new List<OpenPop.Mime.Message>();

            // All the new messages not seen by the POP3 client
            for (int i = 0; i < uids.Count; i++)
            {
                string currentUidOnServer = uids[i];
                if (!seenUids.Contains(currentUidOnServer))
                {
                    // We have not seen this message before.
                    // Download it and add this new uid to seen uids

                    // the uids list is in messageNumber order - meaning that the first
                    // uid in the list has messageNumber of 1, and the second has 
                    // messageNumber 2. Therefore we can fetch the message using
                    // i + 1 since messageNumber should be in range [1, messageCount]
                    OpenPop.Mime.Message unseenMessage = client.GetMessage(i + 1);

                    // Add the message to the new messages
                    newMessages.Add(unseenMessage);

                    // Add the uid to the seen uids, as it has now been seen
                    seenUids.Add(currentUidOnServer);
                }
            }

            // Return our new found messages
            return newMessages;
        }
    }

And here's part of the "GetEMail" function (the one that calls the above one)(Also the comments are in Portuguese...):

//primeiro obter informação de contas
        SqlCeConnection Con = conecao.ligardb(); //ligar a base de dados
        Con.Open();
        string myQuery = "SELECT * FROM Contas"; //construir query
        SqlCeCommand myCommand = new SqlCeCommand(myQuery, Con); //construir comando
        SqlCeDataReader myDataReader = myCommand.ExecuteReader(); //executar comando e armazenar informações

        while (myDataReader.Read()) //ler data reader repetidamente
        {
            int tipo = Convert.ToInt32(myDataReader["Tipo"]); //obter tipo de conta
            bool seguro = Convert.ToBoolean(myDataReader["Seguro"]); //obter se e seguro ou nao
            int idConta = Convert.ToInt32(myDataReader["ID"]); //obter id de conta

            if (tipo == 1 && seguro == false) //POP3 sem SSL
            {
                string hostname = Convert.ToString(myDataReader["Serv_Recep"]); //obter servidor de recepçao
                string user = Convert.ToString(myDataReader["User"]); //obter id de utilizador
                string pass = Convert.ToString(myDataReader["Pass"]); //obter pass de utilizador

                List<string> Uids = new List<string>(); //lista para obter e-mails ja obtidos

                //Obter pasta
                string query_pasta = "SELECT id FROM Pastas WHERE idContas=@id AND Nome=@nome";
                SqlCeCommand cmd_pasta = new SqlCeCommand(query_pasta, Con);
                cmd_pasta.Parameters.AddWithValue("@id", idConta);
                cmd_pasta.Parameters.AddWithValue("@nome", "Recebidas");


                SqlCeDataReader dr_pasta = cmd_pasta.ExecuteReader();
                dr_pasta.Read();
                int idPasta = Convert.ToInt32(dr_pasta["id"]);

                //obrer e-mails ja obtidos
                string query = "SELECT Uids FROM Mensagens WHERE Conta=@id AND Pasta=@pasta"; //contruir query
                SqlCeCommand command = new SqlCeCommand(query, Con); //construir comando
                //atribuir parametros
                command.Parameters.AddWithValue("@id", idConta);
                command.Parameters.AddWithValue("@pasta", idPasta);

                SqlCeDataReader datareader = command.ExecuteReader(); //executar e ler comando

                while (datareader.Read()) //ler datareader
                {
                    string ids = Convert.ToString(datareader["Uids"]); //obter uid
                    Uids.Add(ids); //adicionar uid

                }

                List<OpenPop.Mime.Message> NewMessage = new List<OpenPop.Mime.Message>(); //criar lista de mensagens
                NewMessage = FetchUnseenMessages(hostname, 110, false, user, pass, Uids); //obter mensagens

                for (int y = 0; y < NewMessage.Count; y++)
                {
                    //Guardar mensagem em disco
                    string file_name = NewMessage[y].Headers.MessageId;
                    file_name = file_name + ".eml";



                    string path = System.Environment.GetFolderPath(Environment.SpecialFolder.Personal) + "\\izzy_mail\\" + Convert.ToString(myDataReader["Endereço"]) + "\\Recebidos\\"; //Criar directorioa de destino do .eml
                    if (!Directory.Exists(path))
                    {
                        Directory.CreateDirectory(path);

                    }

                    path = path + file_name;

                    FileStream File = System.IO.File.Create(path);

                    NewMessage[y].Save(File);




                    //Guardar na base de dados
                    string assunto = NewMessage[y].Headers.Subject;//obter assunto
                    string origem = NewMessage[y].Headers.From.Address; //obter origem
                    string uid = NewMessage[y].Headers.MessageId; //obter id da mensagem
                    //criar nova query
                    string query_ins = "INSERT INTO Mensagens (Assunto, De, Pasta, Uri, Uids, Conta) VALUES (@assunto, @de, @pasta, @uri, @uid, @id)"; //contruir query
                    SqlCeCommand cmd_ins = new SqlCeCommand(query_ins, Con); //construir comando
                    //parametrizar o comando
                    cmd_ins.Parameters.AddWithValue("@assunto", assunto);
                    cmd_ins.Parameters.AddWithValue("@de", origem);
                    cmd_ins.Parameters.AddWithValue("@id", idConta);
                    cmd_ins.Parameters.AddWithValue("@pasta", idPasta);
                    cmd_ins.Parameters.AddWithValue("@uid", uid);
                    cmd_ins.Parameters.AddWithValue("@uri", path);


                    cmd_ins.ExecuteNonQuery(); //Executar insert



                }


            }
            else if

So anyone has an idea of how to solve my problem?

Thanks in advance...

Answer 1

So, with the help of Ben I've come to this solution:

1. I've created a public list named UID

2. Added the following code after newMessage.Add() in the FetchUnseenMessages() function:

   Uid.Add(currentUidOnServer);

3. Replaced

   string uid = NewMessage[y].Headers.MessageId; //obter id da mensagem

With:

string uid = Uid[y]; //obter id da mensagem

And now it works!

Answer 2

Make the DB connection:

protected void Page_Load(object sender, EventArgs e)
    {
        //connection to db;
    }

Create the following stored procedures:

CREATE PROCEDURE dbo.getAllSeenUuids

AS
    SELECT uuid FROM seenUuids
    RETURN

CREATE PROCEDURE dbo.saveToSeenUuids
    (@seenUuid varchar(50))
AS
    INSERT INTO seenUuids (uuid) VALUES (@seenUuid)
    RETURN

Add the following to function FetchUnseenMessages:

DataSet ds = new DataSet();
ds = DB.ExecuteQuery_SP("getAllSeenUuids");
List<string> uuids = pop3Client.GetMessageUids();
List<string> listSeenUuids = new List<string>();
List<string> newListSeenUuids = new List<string>();
List<Message> newMessages = new List<Message>();
List<string> listUnreadUuids = new List<string>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
    listSeenUuids.Add(ds.Tables[0].Rows[i][0].ToString());
}
for (int i = uuids.Count - 1; i >= 0; i--)
{
    if (!listSeenUuids.Contains(uuids[i]))
    {
        Message unseenMessage = pop3Client.GetMessage(i + 1);
        newMessages.Add(unseenMessage);
        object[,] parArray = new object[,] { { "@seenUuid", uuids[i] } };
        //Call Stored Procedure_SP("saveToSeenUuids", parArray);
     }
}

Answer 3

I created a struct that holds both uid and message:

    private struct mailwithuid
    {
        public OpenPop.Mime.Message mail;
        public string uid;
        public mailwithuid(OpenPop.Mime.Message _mail,string _uid)
        {
            mail = _mail;
            uid = _uid;
        }
    }

And modified the FetchUnseenMessages() function:

    private List<mailwithuid> FetchUnseenMessages(string hostname, int port, bool useSsl, string username, string password, List<string> seenUids)
    {
        using (Pop3Client client = new Pop3Client())
        {
            client.Connect(hostname, port, useSsl);
            client.Authenticate(username, password);

            List<string> uids = client.GetMessageUids();

            List<mailwithuid> newMessages = new List<mailwithuid>();

            for (int i = 0; i < uids.Count; i++)
            {
                string currentUidOnServer = uids[i];
                if (!seenUids.Contains(currentUidOnServer))
                {
                    OpenPop.Mime.Message unseenMessage = client.GetMessage(i + 1);
                    newMessages.Add(new mailwithuid(unseenMessage, currentUidOnServer));
                    seenUids.Add(currentUidOnServer);
                }
            }
            return newMessages;
        }
    }

Lastly, I can access both the message and the uid from my code:

        List<mailwithuid> newmessages = FetchUnseenMessages("x", 110, false, "x@x", "x", volt);
        foreach (mailwithuid u2 in newmessages)
        {
            OpenPop.Mime.Message u = u2.mail;
            string uid = u2.uid;
        }