title: Attach a file to sp_send_dbmail from C# code behind
body: <p>I'm currently developing an asp.net page where a custom PDF is generated, and then needs to be emailed to customers.  Currently we call the <code>sp_send_dbmail</code> stored procedure to send email, but we haven't had to attach files to it yet.</p>
  
  <p>I've looked all around, but it's looking more and more like <code>sp_send_dbmail</code> can only send attachments that are stored within the database, or on the machine that runs the server.  This would be through the <code>@file_attachments</code> parameter. The other option would be using the <code>@query</code> and <code>@attach_query_result_as_file</code> parameters.</p>
  
  <p>Here is the code we currently use to send emails:</p>
  
  <pre><code>public static void sendEmail(string emailAddress, string body)
  {
  
      Database db = DatabaseFactory.CreateDatabase();
      DbCommand cmd = db.GetStoredProcCommand("msdb.dbo.sp_send_dbmail");
  
      db.AddInParameter(cmd, "@profile_name", DbType.String, "Profile");
      db.AddInParameter(cmd, "@recipients", DbType.String, emailAddress);
      db.AddInParameter(cmd, "@subject", DbType.String, "Report: ");
      db.AddInParameter(cmd, "@body", DbType.String, body);
      db.AddInParameter(cmd, "@body_format", DbType.String, "HTML");
  
      db.ExecuteNonQuery(cmd);
  
  }
  </code></pre>
  
  <p>I've tried adding </p>
  
  <pre><code>db.AddInParameter(cmd, "@file_attachments", DbType.Binary, attachment);
  </code></pre>
  
  <p>and </p>
  
  <pre><code>db.AddInParameter(cmd, "@query", DbType.String, "SELECT " + attachment + " AS 'attachment'");
  db.AddInParameter(cmd, "@attach_query_result_as_file", DbType.Boolean, true);
  db.AddInParameter(cmd, "@query_attachment_filename", DbType.String, "pdf");
  </code></pre>
  
  <p>with <code>byte[] attachment</code> representing the pdf file. Neither has worked.</p>
  
  <p>I've also tried giving <code>@file_attachments</code> the direct path to the pdf, but the pdf is on my local machine, not the SQL server, so it can't find it.</p>