這兩天都在搞這個東西,從開始的一點不懂,到現(xiàn)在自己可以獨立的完成這個功能!在這個過程中,CSDN的好多牛人都給了我很大的幫助,在此表示十二分的感謝!寫這篇文章,一是為了鞏固一下,二嘛我也很希望我寫的這點小東西能幫助遇到同樣問題的朋友們!當然這里有一部分是從網(wǎng)上的摘錄的
實現(xiàn)一個類似于注冊平臺的功能:比如注冊了一個用戶,就會向注冊郵箱里發(fā)送一封郵件。首先是要搭建一個自動發(fā)送郵件的平臺,這個用sql server 2008(sql server 2005也有)的database mail就能很方便的實現(xiàn)。這個完成了以后就能夠發(fā)送簡單的郵件了,但是這樣在應(yīng)用中往往是不夠,我們還要需要建一個job來實現(xiàn)自動發(fā)送的功能。
database mail的構(gòu)建有兩種方法,一是用configure database mail,另外一種是用sql語句完成。
這兩種方法中的服務(wù)器名字,我剛開始以為是隨便起一個就可以的,(以為sql server的database mail會自動把這個服務(wù)器名申請為一個可用的smtp服務(wù)器)后來貌似不是的。這里好像必須要是一個存在并且可用的smtp服務(wù)器名
如果想要刪除建立好的profile和account可以用如下語句
復(fù)制代碼 代碼如下:
DECLARE
@account_name sysname,
@profile_name sysname;
SELECT
@account_name = N'want_to_delete_Account'
@profile_name = N'want_to_delete_Profile';
-- 從郵件配置文件中刪除數(shù)據(jù)庫郵件帳號
EXEC msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name;
-- 刪除數(shù)據(jù)庫郵件帳號
EXEC msdb.dbo.sysmail_delete_account_sp
@account_name = @account_name;
-- 刪除數(shù)據(jù)庫郵件配置文件
EXEC msdb.dbo.sysmail_delete_profile_sp
@profile_name = @profile_name;
完成了database mail的搭建。下面我們就要開始完善自動發(fā)送郵件的功能。
我們要實現(xiàn)發(fā)送郵件的格式是:
尊敬的×××:---------------------------------------------titleName
感謝您的注冊,你的用戶名是××,密碼是××--------------------------object
你的滿意是我們最大的動力。----------------------------------------------------Conclusion
某某公司 ----------------------------------------------------------Inscribe
首先我們可以建兩個表:一個是AutoSendEmail記錄用戶ID和Email的信息;一個是EmailText,一個static table,里面存儲的是我們要發(fā)送的郵件的格式。
AutoSendEmail的主要字段有UserID,EmailAddress,EmailTextID(EmailText的一個外鍵)。這里的UserID對應(yīng)的是一個User表,里面存有用戶的登錄名及登錄密碼。
EmailText的主要字段有EmailTextID,Subject(主題),titleName(稱呼),object(內(nèi)容),Conclusion(結(jié)束語),Inscribe(落款)??赡芸吹竭@里,有人會認為EmailText表沒必要設(shè)計的這么麻煩。有EmailTextID,Subject和一個body就夠了,確實,但是這樣的話,內(nèi)容中的排版格式比較麻煩。這樣設(shè)計一是為了格式方便,一個是為了和發(fā)送其他郵件格式統(tǒng)一。插入一行數(shù)據(jù)到EmailText表中
insert into EmailText (EmailTextID,Subject,TitleName,Object,Conclusion,Inscribe) values(1,'注冊成功','尊敬的UserName>:','
感謝您申請注冊某某公司會員! 你的注冊帳號為LoginName>,密碼為Password>,請妥善保管您的密碼!','
感謝您對我們公司的支持!如果任何意見或建議,歡迎來電咨詢:123-456789。請不要回復(fù)此郵件!',' 某某公司')
這樣,用job每次遍歷AutoSendEmail,從中得到UserID和EmailTextID,然后根據(jù)UserID可以得到相應(yīng)的登錄名和登錄密碼。同時根據(jù)EmailTextID,取出里面的titleName(稱呼),object(內(nèi)容),Conclusion(結(jié)束語),Inscribe(落款)四個字段,用得到的登錄名和登錄密碼去替換UserName> ,LoginName>,Password>這樣就可以拼湊成一封郵件的完整body。
為此,先建立一個procedure
復(fù)制代碼 代碼如下:
CREATE PROCEDURE [Services].[SendRegisteredEmail]
(
@UserID int,
@EmailAddress nvarchar(100)
-- @mailitem_id int OUTPUT
)
AS
declare @userName nvarchar(30)
declare @Login nvarchar(50)
declare @password nvarchar(50)
declare @regUserID int
declare @titleName nvarchar(100)
declare @titleResult nvarchar(100)
declare @object nvarchar(600)
declare @objectResult nvarchar(600)
declare @Subject1 nvarchar(100)
declare @Conclusion nvarchar(600)
declare @Inscrible nvarchar(100)
declare @result int
--declare @time datetime
declare @body1 nvarchar(max)
begin
select @userName=LastName from [Users].[User] where UserID=@UserID
select @Subject1=Subject,@titleName=TitleName ,@object=Object,@Conclusion=Conclusion,@Inscrible=Inscribe from [Services].[EmailText] where EmailTypeID=1
select @titleResult=replace(@titleName,'UserName>',@userName)
select @Login=Login ,@password=Password from [Users].[RegisteredUser] where UserID=@UserID
select @objectResult=replace(@object,'LoginName>',@Login)
select @objectResult=replace(@objectResult,'LoginName>',@Login)
set @body1=@titleResult+N'br>'+@objectResult+N'br>'+@Conclusion+N'br>'+@Inscrible
EXEC @result=msdb.dbo.sp_send_dbmail @profile_name='SurmountGroupProfile',
@recipients=@EmailAddress,
@subject= @Subject1,
@body = @body1,
@body_format = 'HTML';
return @result
end
上面代碼用了一個sql里的替換函數(shù)replace,該函數(shù)的用法是
用第三個表達式替換第一個字符串表達式中出現(xiàn)的所有第二個給定字符串表達式。
語法 REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )
參數(shù) 'string_expression1' 待搜索的字符串表達式。string_expression1 可以是字符數(shù)據(jù)或二進制數(shù)據(jù)。
'string_expression2' 待查找的字符串表達式。string_expression2 可以是字符數(shù)據(jù)或二進制數(shù)據(jù)。
'string_expression3' 替換用的字符串表達式。string_expression3 可以是字符數(shù)據(jù)或二進制數(shù)據(jù)。
返回類型
如果 string_expression(1、2 或 3)是支持的字符數(shù)據(jù)類型之一,則返回字符數(shù)據(jù)。如果 string_expression(1、2 或 3)是支持的 binary 數(shù)據(jù)類型之一,則返回二進制數(shù)據(jù)。
msdb.dbo.sp_send_dbmail存儲過程是用于執(zhí)行發(fā)送郵件的,返回值為0或是1。0代表發(fā)送成功,1為發(fā)送失敗
郵件的格式是用HTML形式顯示
有了這個存儲過程,那么只要用個建個job就可以了。job主要是為了實現(xiàn)某些需要數(shù)據(jù)庫自動處理的功能。
job里的command輸入如下代碼:
復(fù)制代碼 代碼如下:
declare @recordID int
declare @userID int
declare @email nvarchar(100)
declare @typeID int
declare @result int
declare EmailCursor cursor for select RecordID,UserID,EmailAddress,EmailTextID from AutoSendEmail
open EmailCursor
fetch next from EmailCursor into @recordID,@userID,@email,@typeID
While ( @@Fetch_Status=0 )
begin
if( @typeID=1)
begin
exec @result=SendRegisteredEmail @userID,@email
if(@result=0)
begin
delete from [Services].[AutoSendEmail] where RecordID=@recordID
end
end
fetch next from EmailCursor into @recordID,@userID,@email,@typeID
end
close EmailCursor
Deallocate EmailCursor
這里用游標遍歷AutoSendEmail表,job設(shè)定為每分鐘遍歷一次,如果發(fā)送成功,就把該行記錄給刪除。否則保留在表中,下次遍歷時,再次發(fā)送。
這樣一個簡單的應(yīng)用型發(fā)送郵件功能就實現(xiàn)好了!
您可能感興趣的文章:- SQL server 表數(shù)據(jù)改變觸發(fā)發(fā)送郵件的方法
- 使用Python發(fā)送郵件附件以定時備份MySQL的教程
- Python讀取ini文件、操作mysql、發(fā)送郵件實例
- sqlserver數(shù)據(jù)庫使用存儲過程和dbmail實現(xiàn)定時發(fā)送郵件
- 使用sqlserver存儲過程sp_send_dbmail發(fā)送郵件配置方法(圖文)
- 通過sql存儲過程發(fā)送郵件的方法
- SQL Server 使用觸發(fā)器(trigger)發(fā)送電子郵件步驟詳解