Insert outlook email data to mysql database

Question

I have a requirement of reading outlook emails and pushing that data into MySQL database.

My mail looks like this:

SLA warning for INC1234567 Ticket Details: Short description: ship-to 1137..... Caller: Octavian Sam Priority: 3 - Moderate Business service: Lmkp

I have created a table using Maria db in mysql workbench.

I have created a connection from visual studio to mysql.

My code till now is:

String connectingstring = "Server=localhost;Port=3306;UID=root;PWD=*******;Database=emaildatabase;Allow Zero Datetime=True";
            MySqlConnection connection = null;


            using (connection = new MySqlConnection(connectingstring))
            {

                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                try
                {

                    string colName = "TicketID, Caller, Priority, BuisnessService";
                    string paramName = "@TicketID, @Caller, @Priority, @BuisnessService";
                    string tableName = "emailtable";

                    MySqlCommand com = new MySqlCommand("insert into " + tableName + " (" + colName + ") values (" + paramName + ")", connection);
                    MySqlDataReader read = null;
 com.Parameters.Clear();

                   
                 
 foreach (var dr in  **?**   )
                    {
                        com.Parameters.Add("@TicketID", MySqlDbType.VarChar).Value = dr.ItemArray[0].ToString();

                        read = com.ExecuteReader();
                        read.Close();

                    }

                    foreach (var dr in )
                    {
                        com.Parameters.Add("@Caller", MySqlDbType.VarChar).Value = dr.ItemArray[1].ToString();

                        read = com.ExecuteReader();
                        read.Close();
           }
 foreach ()
                    {
                        com.Parameters.Add("@Priority", MySqlDbType.VarChar).Value = dr.ItemArray[2].ToString();

                        read = com.ExecuteReader();
                        read.Close();
                    }

                    foreach ()
                    {
                        com.Parameters.Add("@BuisnessService", MySqlDbType.VarChar).Value = dr.ItemArray[3].ToString();

                        read = com.ExecuteReader();
                        read.Close();
                    }
                }

                catch (Exception ex)
                {

                    throw ex;
  }
                finally
                {
                    connection.Close();
                }
I have a problem going about what i need to enter in foreach such that values can be entered into database when i execute this code.
Thank you.

Answer 1

Hello,

The code sample below was done with SQL-Server, to make it work for MySQL is to change 

• SqlConnection to MySqlConnection
• SqlCommand to MySqlCommand 

Then create a class for passing data to the method DataOperations.InsertTicket

/// <summary>
/// Represents data to insert into database table
/// - each property many not be strings, adjust as needed
/// </summary>
public class Ticket
{
    public string TicketId { get; set; }
    public string Caller { get; set; }
    public string Priority { get; set; }
    public string BusinessService { get; set; }
}

Code to handle insert operation, as stated above you need to change the connection and command to the proper type for MySql which you already have.

public class DataOperations
{
    private static string ConnectionString = 
        "Server = localhost; " + 
        "Port=3306;UID=root;PWD=*******;" + 
        "Database=emaildatabase;" + 
        "Allow Zero Datetime=True";

    /// <summary>
    /// Insert new record for mail item
    /// </summary>
    /// <param name="ticket">Contains information used to insert new record</param>
    public static void InsertTicket(Ticket ticket)
    {
        using (var cn = new SqlConnection() {ConnectionString = ConnectionString})
        {
            using (var cmd = new SqlCommand() {Connection = cn})
            {
                cmd.CommandText = "INSERT INTO emailtable TicketID, Caller, Priority, BuisnessService " +
                                  "VALUES (@TicketID, @Caller, @Priority, @BuisnessService)";


                cmd.Parameters.AddWithValue("@TicketID", ticket.TicketId);
                cmd.Parameters.AddWithValue("@Caller", ticket.Caller);
                cmd.Parameters.AddWithValue("@Priority", ticket.Priority);
                cmd.Parameters.AddWithValue("@BuisnessService", ticket.BusinessService);

                try
                {
                    cn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    // Decide how to handle runtime error
                }
            }
        }
    }
}

Calling the method above

var ticket = new Ticket()
{
    TicketId = "TODO",
    Caller = "TODO",
    BusinessService = "TODO",
    Priority = "TODO"
};

DataOperations.InsertTicket(ticket);

What's missing? What happens if there is a runtime error? Decide how to handle.

---

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

Answer 2

https://stackoverflow.com/questions/64567381/insert-outlook-email-data-to-mysql-database