分享：用C#寫了一個新增使用者就發送郵件和手機簡訊的SqlServer觸發器(附源碼)

分享：用C#寫了一個新增使用者就發送郵件和手機簡訊的SqlServer觸發器(附源碼)

歡迎大家批評指正

//針對SqlServer2005及已上版本
//CLR開發測試環境 VS2008 + Windows7
//SqlServer測試版本：SqlServer2005
//項目名：SendSMSTrigger
//編譯後會產生2個類庫：SendSMSTrigger.dll SendSMSTrigger.XmlSerializers.dll
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

開源：完全自主研發搜尋引擎1.0原始碼及說明，單機400萬網頁，任意50詞以內的檢索不超過 20毫秒
開源：基於百萬商業圈.NET開發架構開發的並行帶分詞的採集器

天心天字輩ERP全部PDK原始碼到了我手上的後果 - 超越天心之WEB天雲