用C#写了一个新增用户就发送邮件和手机短信的SqlServer触发器

来源：互联网发布：摄像头中文数据手册编辑：程序博客网时间：2024/04/29 19:40

http://topic.csdn.net/u/20110816/00/332B6697-31F6-48DD-9F80-9B2479B7E1EA.html

分享：用C#写了一个新增用户就发送邮件和手机短信的SqlServer触发器(附源码)

欢迎大家批评指正

//针对SqlServer2005及已上版本
//CLR开发测试环境 VS2008 + Windows7
//SqlServer测试版本：SqlServer2005
//项目名：SendSMSTrigger
//编译后会产生2个类库：SendSMSTrigger.dll SendSMSTrigger.XmlSerializers.dll

短信用的是是移动公司提供的短信WebServices接口中的
WebServices地址是：http://ws.iems.net.cn/GeneralSMS/ws/SmsInterface?wsdl

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