mercredi, mars 07, 2007

Send SqlDatabaseMail/CLR


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure(Name = "SendSqlDatabaseMail")]
public static void SendSqlDatabaseMail(SqlString MessageTo, SqlString MessageCc, SqlString MessageSubject, SqlString MessageBody)
{

SqlPipe sqlPip = SqlContext.Pipe;

SqlConnection conn = new SqlConnection("context connection=true;");
SqlCommand cmd = conn.CreateCommand();

try
{

conn.Open();

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "msdb.dbo.sp_send_dbmail";

SqlParameter profile_name = new SqlParameter("@profile_name", SqlDbType.VarChar, 8000);
profile_name.Direction = ParameterDirection.Input;
profile_name.Value = "DbMailProfile";
cmd.Parameters.Add(profile_name);

SqlParameter recipients = new SqlParameter("@recipients", SqlDbType.VarChar, 8000);
recipients.Direction = ParameterDirection.Input;
recipients.Value = MessageTo;
cmd.Parameters.Add(recipients);

SqlParameter copy_recipients = new SqlParameter("@copy_recipients", SqlDbType.VarChar, 8000);
copy_recipients.Direction = ParameterDirection.Input;
copy_recipients.Value = MessageCc;
cmd.Parameters.Add(copy_recipients);

SqlParameter subject = new SqlParameter("@subject", SqlDbType.VarChar, 255);
subject.Direction = ParameterDirection.Input;
subject.Value = MessageSubject;
cmd.Parameters.Add(subject);

SqlParameter Msgbody = new SqlParameter("@body", SqlDbType.VarChar);
Msgbody.Direction = ParameterDirection.Input;
Msgbody.Value = MessageBody;
cmd.Parameters.Add(Msgbody);

SqlParameter body_format = new SqlParameter("@body_format", SqlDbType.VarChar, 20);
body_format.Direction = ParameterDirection.Input;
body_format.Value = "HTML";
cmd.Parameters.Add(body_format);

SqlParameter Msgimportance = new SqlParameter("@importance", SqlDbType.VarChar, 6);
Msgimportance.Direction = ParameterDirection.Input;
Msgimportance.Value = "High";
cmd.Parameters.Add(Msgimportance);

SqlParameter Msgsensitivity = new SqlParameter("@sensitivity", SqlDbType.VarChar, 12);
Msgsensitivity.Direction = ParameterDirection.Input;
Msgsensitivity.Value = "Personal";
cmd.Parameters.Add(Msgsensitivity);

SqlContext.Pipe.Send("Sending Mail : " + cmd.ExecuteNonQuery().ToString());

}
catch (Exception ex)
{
SqlContext.Pipe.Send("Error" + ex.Message);
}
finally
{
conn.Close();
}


}

}

Aucun commentaire: