C# adding data to SQL database

Question

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 errors and no exceptions given. That's why im at a loss. I know the code is running all the way through because the other class writes to the DB. — toosweetnitemare
– toosweetnitemare, Commented Mar 3, 2011 at 14:24
Just small tip, move the //if DB is open, close it section to finally block otherwise you'll end up with zombie open connections when there are exceptions.. — user447356
– user447356, Commented Mar 3, 2011 at 14:25
Please specify what are the exceptions that your code throws. Check also if something happens inside the try-catch statements, it sounds like an hidden exception. — as-cii
– as-cii, Commented Mar 3, 2011 at 14:26

Darin Dimitrov · Accepted Answer · 2011-03-03 14:32:37Z

2

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

edited Mar 3, 2011 at 14:32

answered Mar 3, 2011 at 14:26

Darin Dimitrov

1.0m275 gold badges3.3k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

toosweetnitemare Over a year ago

Thanks Darin. Im going to try this re-factoring in a few minutes

Pavlin Marinov Over a year ago

Just a quick note for everyone who is using this answer as a base - there should be a closing parenthesis at the end of the "INSERT INTO" command string.

BlueMonkMN · Accepted Answer · 2011-03-03 14:24:44Z

0

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

answered Mar 3, 2011 at 14:24

BlueMonkMN

25.7k11 gold badges86 silver badges151 bronze badges

2 Comments

toosweetnitemare Over a year ago

ya that was my mistake, trying to sanitize the code. im changing that now.

BlueMonkMN Over a year ago

The value you've provided is still a string and it should be a DateTime. Remove ToString.

Kumar · Accepted Answer · 2011-03-03 14:27:20Z

0

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

answered Mar 3, 2011 at 14:27

Kumar

1,0155 silver badges8 bronze badges

Comments

Valentin Kuzub · Accepted Answer · 2011-03-03 14:38:36Z

0

If you really have no idea you can try

Fix your insert statement so it explicitly names columns maybe your ordering is incorrect and insert statement doesnt work
your setting parameters length to maximum column length values, maybe you need to set them to actual parameter values length
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.

edited Mar 3, 2011 at 14:38

answered Mar 3, 2011 at 14:31

Valentin Kuzub

12.2k7 gold badges60 silver badges95 bronze badges

1 Comment

toosweetnitemare Over a year ago

sorry dates123 was my mistake sanitizing my code. i changed it

Collectives™ on Stack Overflow

C# adding data to SQL database

4 Answers 4

2 Comments

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related