C# adding data to SQL database

Background: I am trying to add data to a SQL DB with C#. I am currently doing so in my script in another class so im using the same code (the code works). However, my current class is using a bunch of recycled code and i am having issues narrowing down why i can not write to the DB. Below is the code i am using and it is broken down to the bare bones minimum code.

What I'm asking for: anyone to point out some dumb mistake i made or ideas where to troubleshoot. Currently i am just staring at this code and cant see whats wrong.

Thanks in advance!

public void AddAttachmentToDB(XmlNode root, XmlNamespaceManager xmlns, string  MessageID, string MailBoxAliasName)
{
    //open DB
    #region Open DB
    if (DbConnection.State != System.Data.ConnectionState.Open)
    {
        try
        {
            this.DbConnection.ConnectionString = DbConnectionString;
            this.DbConnection.Open();
            MailboxListener._logging.LogWrite("[{0}] opened DB Connection in AddAttachmentToDB!",
                LoggingLevels.Error,
                System.Reflection.MethodBase.GetCurrentMethod().ToString(),
                this.DbConnectionString);
        }
        catch (Exception ex)
        {
            MailboxListener._logging.LogWrite("[{0}] Failed to open DB Connection! For Machine: {1}",
                 LoggingLevels.Error,
                 System.Reflection.MethodBase.GetCurrentMethod().ToString(),
                 this.DbConnectionString);
            MailboxListener._logging.LogWrite("[{0}] Error Returned: {1}",
                LoggingLevels.Error,
                System.Reflection.MethodBase.GetCurrentMethod().ToString(),
                ex.Message.ToString());
        }
    }
    else
    {
        MailboxListener._logging.LogWrite("[{0}] Failed to open DB Connection in AddAttachmentToDB!",
                 LoggingLevels.Error,
                 System.Reflection.MethodBase.GetCurrentMethod().ToString(),
                 this.DbConnectionString);
    }
            #endregion
    //once db is open try this
    try
    {
        //create test variables       
        string strMailBoxAliasName = MailBoxAliasName;
        string AttachmentFilename = string.Empty;
        string strfiletype = string.Empty;
        string AttachmentStream = string.Empty;
        string strAttachmentID = string.Empty;
        string strMessageID = string.Empty;
        strMessageID = MessageID;

        //fill test variables
        AttachmentFilename = "yumyum";
        AttachmentStream = "Cheetos";
        strMessageID = "123";
        strMailBoxAliasName = "user";
        strfiletype = ".txt";
        strAttachmentID = "12345";

        //create sql insert string
        String insString = @"INSERT INTO MailboxListenerAttachments Values (@attachmentfilename, @attachmentbody,
        @messageID, @mailboxname, @filetype, @attachmentID, @DateAddedToDB)";

        //create sql command string
        SqlCommand myCommand = new SqlCommand(insString, this.DbConnection);

        //add fill test variables to sql insert string
        myCommand.Parameters.Add("@attachmentfilename", SqlDbType.VarChar, 100);
        myCommand.Parameters["@attachmentfilename"].Value = AttachmentFilename;
        myCommand.Parameters.Add("@attachmentbody", SqlDbType.VarChar, 8000);
        myCommand.Parameters["@attachmentbody"].Value = AttachmentStream.Trim();
        myCommand.Parameters.Add("@messageID", SqlDbType.VarChar, 500);
        myCommand.Parameters["@messageID"].Value = strMessageID;
        myCommand.Parameters.Add("@mailboxname", SqlDbType.VarChar, 100);
        myCommand.Parameters["@mailboxname"].Value = strMailBoxAliasName;
        myCommand.Parameters.Add("@filetype", SqlDbType.VarChar, 50);
        myCommand.Parameters["@filetype"].Value = strfiletype;
        myCommand.Parameters.Add("@attachmentID", SqlDbType.VarChar, 50);
        myCommand.Parameters["@attachmentID"].Value = strAttachmentID;
       开发者_运维技巧 myCommand.Parameters.Add("@DateAddedToDB", SqlDbType.DateTime);
            myCommand.Parameters["@DateAddedToDB"].Value = DateTime.UtcNow.ToString();

        //run sql command
        myCommand.ExecuteNonQuery();

        //log sql command events
        MailboxListener._logging.LogWrite(
                "[{0}] Added attachment {1} to database for messageID: {2}",
                LoggingLevels.Informational,
                System.Reflection.MethodBase.GetCurrentMethod().ToString(),
                AttachmentFilename,
                strMessageID
            );
        //if DB is open, close it
        if (DbConnection.State == System.Data.ConnectionState.Open)
        {
            this.DbConnection.Close();
        }
    }
    //catch errors 
    catch (Exception ex)
    {
        MailboxListener._logging.LogWrite("[{0}] Error Returned: {1}",
                LoggingLevels.Error,
                System.Reflection.MethodBase.GetCurrentMethod().ToString(),
                ex.Message.ToString());
    }
}

---

No idea why this doesn't work but this code screams for refactoring:

public void AddAttachmentToDB(
    XmlNode root, 
    XmlNamespaceManager xmlns, 
    string MessageID, 
    string MailBoxAliasName
)
{
    var strMailBoxAliasName = MailBoxAliasName;
    var AttachmentFilename = "yumyum";
    var AttachmentStream = "Cheetos";
    var strMessageID = "123";
    var strMailBoxAliasName = "user";
    var strfiletype = ".txt";
    var strAttachmentID = "12345";

    // Use DateTime when working with dates
    var dates123 = new DateTime(2011, 2, 3);

    try
    {
        using (var conn = new SqlConnection(DbConnectionString))
        using (var cmd = conn.CreateCommand())
        {
            conn.Open();
            cmd.CommandText = @"INSERT INTO MailboxListenerAttachments Values (@attachmentfilename, @attachmentbody, @messageID, @mailboxname, @filetype, @attachmentID, @DateAddedToDB";

            cmd.Parameters.AddWithValue("@attachmentfilename", AttachmentFilename);
            cmd.Parameters.AddWithValue("@attachmentbody", AttachmentStream.Trim());
            cmd.Parameters.AddWithValue("@messageID", strMessageID);
            cmd.Parameters.AddWithValue("@mailboxname", strMailBoxAliasName);
            cmd.Parameters.AddWithValue("@filetype", strfiletype);
            cmd.Parameters.AddWithValue("@attachmentID", strAttachmentID);
            cmd.Parameters.AddWithValue("@DateAddedToDB", dates123);

            cmd.ExecuteNonQuery();
        }
    }
    catch (Exception ex)
    {
        // TODO: Log the exception and propagate it
        throw ex;
    }
}

---

Shouldn't Dates123 be of a DateTime type instead of a string?

---

I think you need to specify input/output directions for all of the paramters. for example : myCommand.Parameters["@attachmentfilename"].Direction = ParameterDirection.Input

---

If you really have no idea you can try

1. Fix your insert statement so it explicitly names columns maybe your ordering is incorrect and insert statement doesnt work
2. your setting parameters length to maximum column length values, maybe you need to set them to actual parameter values length
3. I really think that Dates123 is indeed the most important problem here, it should be DateTime not string.

So you changed it but you still call DateTime.UtcNow.ToString() , leave it as DateTime type not string.