首页 新闻 搜索 专区 学院

C#新增用户就发送邮件和手机短信的SqlServer触发器,不知道这个SmsInterfaceService是怎么生成的?

0
悬赏园豆:5 [已解决问题] 解决于 2013-06-21 15:29

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

c#
YoMe的主页 YoMe | 小虾三级 | 园豆:545
提问于:2013-06-01 18:21
< >
分享
最佳答案
0

是个webservice吧,添加web引用就OK了。短信平台提供商会给SDK和API调用demo。

收获园豆:5
数据酷软件 | 初学一级 |园豆:130 | 2013-06-01 20:38

我添加了SMS下的api

UTF-8编码发送接口地址:
http://utf8.sms.webchinese.cn/?Uid=本站用户名&Key=接口安全密码&smsMob=手机号码&smsText=短信内容,当添加服务应用的时候添加不上啊

HTML 文档不包含 Web 服务发现信息。
元数据包含无法解析的引用:“http://utf8.sms.webchinese.cn/?Uid=本站用户名&Key=接口安全密码&smsMob=手机号码&smsText=短信内容”。
响应消息的内容类型 text/html 与绑定(application/soap+xml; charset=utf-8)的内容类型不匹配。如果使用自定义编码器,请确保正确实现 IsContentTypeSupported 方法。响应的前 2 个字节为:“-4”。
如果该服务已在当前解决方案中定义,请尝试生成该解决方案,然后再次添加服务引用。

YoMe | 园豆:545 (小虾三级) | 2013-06-01 20:55
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册