Share: use C # To write an SqlServer trigger (with source code) for sending emails and text messages to new users)

Share: use C # To write an SqlServer trigger (with source code) for sending emails and text messages to new users)

Thank you for your criticism.

// For SqlServer2005 and later versions
// CLR development and testing environment VS2008 + Windows7
// SqlServer test version: SqlServer2005
// Project name: SendSMSTrigger
// Two class libraries will be generated after compilation: 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>
/// Send an email
/// </Summary>
/// <Param name = "tomail"> recipient email address </param>
/// <Param name = "title"> title </param>
/// <Param name = "content"> email body </param>
/// <Param name = "FormUser"> sender account </param>
/// <Param name = "userPwd"> sender password </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, );
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>
/// Send a text message
/// </Summary>
/// <Param name = "m0000number"> mobile phone number </param>
/// <Param name = "sMessageContent"> text message content </param>
/// <Returns> </returns>
Public static string SendSMS (string m1_number, string sMessageContent)
{
SmsInterfaceService SmsInterfaceDemo = new SmsInterfaceService ();
Return SmsInterfaceDemo. clusterSend ("SMS interface User Name", "SMS Interface Password ",
"1360000000", m1_number, sMessageContent, DateTime. Now. ToString ("yyyy-MM-dd hh: mm: ss"), "0 | 0 | 0 | 0 ");
}

/// <Summary>
/// Add CLR triggers for the Users table
/// </Summary>
[SqlTrigger (Name = @ "SendSMSTrigger", Target = "[dbo]. [Users]", Event = "for insert")]
Public static void SendSMS ()
{
String userName;
String realName;
String eMail;
String comment number;
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];
Serial number = (string) reader [3];
Reader. Close ();

If (IsValidEMailAddress (eMail ))
{
// Send a notification email
SendEmail (eMail, realName + "Hello! Congratulations! Registration successful! "," Your userName is: "+ userName," bwsyq@bwsyq.com "," sender password ");
// Send a text message
SendSMS (serial number, realName + "Hello! Congratulations! Registration successful! Your userName is: "+ userName );
// SqlServer pipeline return information
Pipe. Send (realName + "Hello! Congratulations! Registration successful! Your userName is: "+ userName );
}
}
Break;
Default:
Break;
}
}

/// <Summary>
/// Verify whether the email is valid
/// </Summary>
/// <Param name = "email"> email address </param>
/// <Returns> <c> true </c> indicates that the email address format is valid. <c> false </c> indicates that the email address format is invalid. </returns>
Public static bool IsValidEMailAddress (string email)
{
Return Regex. IsMatch (email, @ "^ ([\ w-] + \.)*? [\ W-] + @ [\ w-] + \. ([\ w-] + \.)*? [\ W] + $ ");
}
}

-- Script, code, and description of mounting in SqlServer2005
IF OBJECT_ID (N 'users') IS NOT NULL
Drop table Users
Go
Create table Users -- (for testing) User TABLE
(
UserName nvarchar (200) not null, -- User Name
RealName nvarchar (200) not null, -- real name
EMail nvarchar (200) not null, -- EMail address
Comment Number varchar (20) not null -- mobile phone number
);
GO

-- Create a trigger assembly
Create assembly [SendSMSTrigger. XmlSerializers. dll] from 'e: \ SendSMSTrigger. XmlSerializers. dll'
WITH PERMISSION_SET = UNSAFE;
Go
-- Create a serialization processor assembly
Create assembly SendSMSTrigger from 'e: \ SendSMSTrigger. dll'
WITH PERMISSION_SET = UNSAFE;
Go

-- Upgrade SQL server support version
EXEC sp_dbcmptlevel N 'db _ EMP2 ', 90
Go
-- Activate CLR Permissions
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
-- Create an SqlServer trigger and associate the C # Trigger
Create trigger trig_SendSMSTrigger
ON Users
FOR INSERT
AS
External name SendSMSTrigger. SendSMSTrigger. SendSMS
Go

-- Test and simulate user registration. After successful registration, you will receive a successful registration notification email and a text message notification.
Insert into Users (UserName, RealName, EMail, employee number)
Values ('user0001 ', 'millions of commercial circles', 'bwsyq @ bwsyq.com ', '13818466xxx ')
Go

Copyright: Millions of business circles
Do not reprint without permission, please contact me if you have any questions QQ 99923309 Mail: bwsyq@bwsyq.com

Open-source: fully self-developed Search Engine 1.0 source code and instructions, a single machine 4 million web page, any 50 words search within 20 milliseconds
Open-source: Parallel collector with Word Segmentation developed based on the. NET development framework of millions of business circles

The consequences of all the source code of Tianxin tianzi erp pdk come to me-beyond the WEB tianyun