using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Net.Mail;

public class SendSMSTrigger
{
    /// <summary>
    /// 发送邮件
    /// </summary>
    /// <param name="tomail">收件人邮件地址</param>
    /// <param name="title">标题</param>
    /// <param name="content">邮件正文</param>
    /// <param name="FormUser">发件人账号</param>
    /// <param name="userPwd">发件人密码</param>
    public static void sendEmail(string tomail, string title, string content, string FormUser, string userPwd)
    {
        MailAddress from = new MailAddress(FormUser + "@bwsyq.com");
        MailAddress to = new MailAddress(tomail);
        MailMessage MyMessage = new MailMessage(from, to);
        MyMessage.Priority = MailPriority.Normal;
        MyMessage.Priority = MailPriority.Normal;

        MyMessage.IsBodyHtml = false;
        MyMessage.IsBodyHtml = true;
        MyMessage.Body = content;
        MyMessage.BodyEncoding = System.Text.Encoding.UTF8;
        MyMessage.Subject = title;

        string SmtpServer = "mail.bwsyq.com";
        SmtpClient client = new SmtpClient(SmtpServer);
        System.Net.NetworkCredential cred = new System.Net.NetworkCredential(FormUser, userPwd);
        client.Credentials = cred;
        client.Send(MyMessage);

    }

    /// <summary>
    /// 发送手机短信
    /// </summary>
    /// <param name="mMobilNumber">手机号码</param>
    /// <param name="sMessageContent">短信内容</param>
    /// <returns></returns>
    public static string SendSMS(string mMobilNumber, string sMessageContent)
    {
        SmsInterfaceService SmsInterfaceDemo = new SmsInterfaceService();
        return SmsInterfaceDemo.clusterSend("短信接口用户名", "短信接口密码",
                "1360000000", mMobilNumber, sMessageContent, DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"), "0|0|0|0");
    }

    /// <summary>
    /// 针对表Users的新增CLR触发器
    /// </summary>
    [SqlTrigger(Name = @"SendSMSTrigger", Target = "[dbo].[Users]", Event = "FOR INSERT")]
    public static void SendSMS()
    {
        string userName;
        string realName;
        string eMail;
        string mobilNumber;
        SqlCommand command;
        SqlTriggerContext triggContext = SqlContext.TriggerContext;
        SqlPipe pipe = SqlContext.Pipe;
        SqlDataReader reader;

        switch (triggContext.TriggerAction)
        {
            case TriggerAction.Insert:
                // Retrieve the connection that the trigger is using
                using (SqlConnection connection
                   = new SqlConnection(@"context connection=true"))
                {
                    connection.Open();
                    command = new SqlCommand(@"SELECT * FROM INSERTED;",
                       connection);
                    reader = command.ExecuteReader();
                    reader.Read();
                    userName = (string)reader[0];
                    realName = (string)reader[1];
                    eMail = (string)reader[2];
                    mobilNumber = (string)reader[3];
                    reader.Close();

                    if (IsValidEMailAddress(eMail))
                    {
                        //发通知邮件
                        sendEmail(eMail, realName + "您好！恭喜注册成功!", " 您的用户名是：" + userName, "bwsyq@bwsyq.com", "发件人密码");
                        //发手机短信
                        SendSMS(mobilNumber, realName + "您好！恭喜注册成功! 您的用户名是：" + userName);
                        //SqlServer 管道返回信息
                        pipe.Send(realName + "您好！恭喜注册成功! 您的用户名是：" + userName);
                    }
                }
                break;
            default:
                break;
        }
    }

    /// <summary>
    /// 验证邮件是否合法
    /// </summary>
    /// <param name="email">邮件地址</param>
    /// <returns><c>true</c>表示邮件地址格式合法 <c>false</c>表示邮件地址格式不合法<</returns>
    public static bool IsValidEMailAddress(string email)
    {
        return Regex.IsMatch(email, @"^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$");
    }
}

--SqlServer2005 中的挂接脚本、代码、相关说明
IF OBJECT_ID(N'Users') IS NOT NULL
drop table Users
go
CREATE TABLE Users                    --(测试用)用户表
(
    UserName nvarchar(200) NOT NULL,  --用户名
    RealName nvarchar(200) NOT NULL,  --真实姓名
    EMail nvarchar(200) NOT NULL,     --邮件地址
    MobilNumber varchar(20) not null  --手机号码
);
GO

--建立触发器程序集
CREATE ASSEMBLY [SendSMSTrigger.XmlSerializers.dll] from 'E:\SendSMSTrigger.XmlSerializers.dll'
WITH PERMISSION_SET = UNSAFE;
go
--建立序列化处理器程序集
CREATE ASSEMBLY SendSMSTrigger from 'E:\SendSMSTrigger.dll'
WITH PERMISSION_SET = UNSAFE;
go

--提升SqlServer支持版本
EXEC sp_dbcmptlevel N'DB_EMP2', 90
go
--开通CLR权限
EXEC sp_configure 'show advanced options' , '1';
GO
RECONFIGURE;
GO
EXEC sp_configure 'clr enabled' , '1'
GO
RECONFIGURE;
GO

IF OBJECT_ID(N'trig_SendSMSTrigger') IS NOT NULL
drop TRIGGER trig_SendSMSTrigger
go
--建立SqlServer触发器并 C#触发器关联
CREATE TRIGGER trig_SendSMSTrigger
ON Users
FOR INSERT
AS
EXTERNAL NAME SendSMSTrigger.SendSMSTrigger.SendSMS
go

--测试，模拟用户注册，成功后您将收到 注册成功通知邮件和手机短信通知
insert into Users (UserName,RealName,EMail,MobilNumber)
values('USer0001', '百万商业圈', 'bwsyq@bwsyq.com', '13818466XXX')
go

版权所有：百万商业圈
未经许可不得转载，有任何疑问请与我本人联系 QQ 99923309 Mail：bwsyq@bwsyq.com