C50108
Search…
職訓局里程碑
2019/0401/SQL觸發程序&Asp.net自動寄信&認證信

預存程序-> 結果
函數-> 值
觸發程序Trigger-> 最大差別在於,沒法透過呼叫,而是某個事件觸發後自動執行。
Ex. Updata delete 對資料定義或對資料有動作時觸發程序。

Update 從執行到 commit (交易成功),中間會檢測很多事情,
又稱為Transaction;access無法undo,交易一旦成功無法回復,
交易的特點(ACID),想像一下幾百萬人在使用資料庫,改了又改會怎樣?
中間會檢測很多事情,檢測什麼呢?會有很多檢核點,以UPDATA舉例
1.新增一筆新資料
2.將新資料丟入暫存資料inesrted,在最後commit成功時增加
3.將即將被刪除的資料放於deteted,再commit成功時刪除舊資料,
如果這三個步驟有一個錯,就會rollBACK,正確就會進入一堆檢核點,
Ex.各種下的條件約束check … ,最後會走到Trigger最後才會到commit(交易成功)。
Undo則是每一個步驟都有進行暫存。
前面檢核點無法檢核的商業邏輯可以藉由trigger做檢核,如沒過就可以rollback。
trigger分為兩種一種是跟著資料庫的,也有跟著資料表的

--觸發程序Trigger
update 客戶 set 姓名='江大魚',電話='07-7777777'
output inserted.姓名 as New_Name, inserted.電話 as New_Tel, deleted.姓名, deleted.電話
where 客戶編號='C011'
-------------------------------------------------
create trigger show客戶更新資料 on 客戶
after update
as
begin
select inserted.姓名 as New_Name, inserted.電話 as New_Tel from inserted
select deleted.姓名 as Old_Name, deleted.電話 as Old_Tel from deleted
end
--------------
update 客戶 set 姓名='江小龜',電話='08-8888888'
where 客戶編號='C011'
觸發程序執行順序大於Commit

---------------------------------------
create trigger show新增課程 on 課程
after insert
as
begin
select * from inserted
end
----------------------
insert into 課程 values('CS999','safasfsfsdafsdaggfhghj',3)
update 課程 set 名稱='54656787879',學分=2
where 課程編號='CS999'
剛剛所有的例子都是 after trigger for insert

--after trigger
--for insert
alter trigger 選課數檢查 on 班級
after insert
as
begin
declare @num int
select @num=count(*) from 班級
where 學號=(select 學號 from inserted)
--where 學號='S001'
select 學號 from inserted
--where 學號=(select 學號 from inserted)
if @num=5
begin
print '已選滿5門課,課先退選後再進行加選!!'
--rollback
end
end
insert into 班級 values('I001','S001','CS205','2000-1-1','aaaa')
--select * from 班級 where 學號='S001'

create trigger 學分數檢查 on 課程
after update
as
begin
declare @old int , @new int
select @old=學分 from deleted
select @new=學分 from inserted
if @new<@old
begin
print '學分數不可比原來低!!原學分數為'+ cast(@old as char)
rollback
end
end
update 課程 set 學分=2 where 課程編號='CS213'

如果員工也是學生就不可更改與刪除資料。
create trigger Check員工 on 員工
after update,delete
as
begin
if exists(select * from 學生 where 姓名=(select 姓名 from deleted))
begin
rollback
end
end
update 員工 set 薪水=薪水*1.03
where 身份證字號='F332213046'
delete from 員工 where 身份證字號='F332213046'

不管再新增或是修改,沒有的資料就新增,既有的資料就修改。
如果課程資料表內的課程編號欄位內的課程編號,沒有就增加,有就修改
一個資料表配一個操作時只能用一次 instead
如果真的要使用兩次可以使用view,select 抓成一個view…
View通常拿來檢視資料,但其實可以新增修改刪除,
但沒有key則不會新增成功。
--Instead Trigger
--把原來的操作取代掉
create trigger add_Course on 課程
instead of insert
as
begin
if exists(select * from 課程 where 課程編號=(select 課程編號 from inserted))
begin
--如果課程已存在,用update操作來取代原本要做的insert
update 課程 set 名稱=inserted.名稱, 學分=inserted.學分
from 課程 inner join inserted on 課程.課程編號=inserted.課程編號
end
else
begin
insert into 課程
select * from inserted
end
end
insert into 課程 values('CS998','bbbbbbb',3)

接下來為資料庫觸發程序

但也不是所有的ddl 都可以放 trigger ,
且不能用instead(定義就是定義,並沒有執行取代資料)
--DDL Trigger
--不可使用 instead
create trigger readonly_table on database
for drop_table,alter_table
as
begin
rollback
end
drop table 課程3

停止資料表層級的觸發程序相當於修改TABLE的定義。
--停用trigger
alter table 課程
disable trigger show新增課程
--啟用trigger
alter table 課程
enable trigger show新增課程
停止資料庫層級的觸發程序相當於修改SERVER上的定義。
--停用database層級 trigger
disable trigger readonly_table
on database
--啟用database層級 trigger
enable trigger readonly_table
on database
一般 select * from table
一次全部讀取 -> 佔資源

相對於 asp.net sqldatareder

宣告指標會佔資源,打開關閉後還必須要消除指標。
動指標 fetch 從第幾筆開始 位置為
不知道有幾筆 while
如果有讀到資料 @@FETCH_STATUS=0
沒資料的時候結束 @@FETCH_STATUS=-1
--資料指標Cursor
--宣告靜態資料指標
declare stuCursor cursor
static
for select 學號,姓名,電話 from 學生
open stuCursor
declare @id char(4), @name varchar(12), @tel varchar(15)
fetch first from stuCursor into @id,@name,@tel
while @@FETCH_STATUS=0
begin
fetch next from stuCursor into @id,@name,@tel
end
close stuCursor
deallocate stuCursor

不可亂寄匿名信具有刑責
Mail server SMTP :兩台局端伺服器互相寄信,個人電腦是先寄到個人的Mail server局端,再藉由局對局做SMTP,以前可以匿名,因為濫用廣告信現在不能了,以前是使用POP3現在沒再用了,因為收取後就會消失;WebMail則是連線到伺服器去做寄送。

在後端建造郵件伺服器位址物件
查詢domain cmd -> nslookup msa.hinet.net
簡單寄法 ( 純文字 )
public partial class _34Smtp_Client : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SmtpClient myMail = new SmtpClient("msa.hinet.net");
string from = "[email protected]";
string to = "[email protected]";
string subject = "邀請您一同來參加我們的派對!!";
string body = "ghhghghgkhgjjhghjrfdhg\nghfgdfdsgdhgjhgr\njhhgrfgfjhgj";
myMail.Send(from, to, subject, body);
}
}
myMail.send寄信的方法

gmail會放在垃圾桶顯示

雅虎會錯殺一百收不到
第四層傳輸層 port
Ip 高雄港 > port 35號碼頭
ip分享器 第三層
Mac 網路卡 第二層
交換器(Switch) 第一層找到第七層
Google api 文件有說明如何登入個人gmail寄送信件
Using System.Net.Mail
Using System.Net
myMail.Credentials=new NetworkCredential帳號密碼方法
一般來講會放在資料庫中將帳號密碼讀近來
打開SSL進行加密myMail.EnableSsl = ture

每一個都要建構為物件Ex.收件人 …
類別有兩種 mailaddress、mailmessage
建構完後ide就能顯示信件相關屬性做選擇
如果裡面要有圖則要用 html去寫,並且要打開html。
Msg.IsBodyHtml = ture; ( 有危險性,可以編寫惡意檔案。 )
myMail.Send(Msg) 有兩種輸入參數的方法,這裡一種上面一種。
protected void Page_Load(object sender, EventArgs e)
{
//SmtpClient myMail = new SmtpClient("msa.hinet.net");
SmtpClient myMail = new SmtpClient("smtp.gmail.com",587);
myMail.Credentials = new NetworkCredential("YourAccount","YourPassword");
myMail.EnableSsl = true;
//純文字
//string from = "[email protected]";
//string to = "[email protected]";
//string subject = "邀請您一同來參加我們的派對!!";
//string body = "ghhghghgkhgjjhghjrfdhg\nghfgdfdsgdhgjhgr\njhhgrfgfjhgj";
//myMail.Send(from, to, subject, body);
MailAddress from = new MailAddress("[email protected]", "億載金城武");
MailAddress to = new MailAddress("[email protected]");
MailMessage Msg = new MailMessage(from, to);
Msg.Subject = "邀請您一同來參加我們的派對!!";
Msg.Body = "ghhghghgkhgjjhghjrfdhg\nghfgdfdsgdhgjhgr\njhhgrfgfjhgj<img src='' />";
Msg.IsBodyHtml = true;
myMail.Send(Msg);
}
}

Google 開啟 允許存取帳號 - Google Search
Google

簡單方式 資料庫欄位預設未認證,發送信件附上超鏈結,點擊後判斷已認證。
自行研究

增加@IsAuth欄位
將自動寄驗證信重新弄個函數

貼上34code
記得using
讀不到值 補傳入參數
修改34內容文本 認證還沒做 認證先做到這個地方
上面邏輯一進資料庫就呼叫此函數傳email參數值給此函數
完成認證只是將@IsAuth改為true

先回到33加上認證信網址
現在是測試所以網址為localhost,如果是實務則是公司的domain
從資料庫傳email與帳號近來
Response.write(“ <script>alert(‘註冊完成已送出認證信’)</script> ”);
等等要秀 33 跟 35 的程式碼

33用網址帶參數過去35
QueryString抓網址上的參數集合包含(帳號)
有讀到就是有再回寫回去 (記得要用參數避免指令與值互用造成安全性問題) 秀認證完成訊息

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="33Member_Registeration.aspx.cs" Inherits="ASPnet._33Member_Registeration" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
<style>
#tbMember {
width: 450px;
height: 500px;
margin: auto;
border:3px double;
}
#tbMember table {
width: 100%;
}
#tbMember>tbody>tr>td:first-child {
text-align: right;
}
#tbMember>tbody>tr:last-child > td {
text-align: center;
}
#tbMember table>tbody>tr>td {
text-align: center;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<table id="tbMember">
<caption>註冊會員</caption>
<tr>
<td>帳號:</td>
<td>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:TextBox ID="txtAccount" runat="server" placeholder="5-10碼"></asp:TextBox><asp:Button ID="Button2" runat="server" Text="檢查帳號可用性" ValidationGroup="abc123" />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" ControlToValidate="txtAccount" Display="Dynamic" runat="server" ErrorMessage="姓名為必填欄位" Text="(必填)" ForeColor="Red" Font-Size="10pt"></asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" ControlToValidate="txtAccount" Display="Dynamic" ValidationExpression="[A-Za-z][A-Za-z0-9]{4,9}" runat="server" ErrorMessage="(格式有誤)" ForeColor="Red" Font-Size="10pt"></asp:RegularExpressionValidator>
<asp:CustomValidator ID="CustomValidator1" runat="server" ErrorMessage="(帳號重複)" ForeColor="Red" Font-Size="10pt" ValidationGroup="abc123" OnServerValidate="CustomValidator1_ServerValidate"></asp:CustomValidator>
</ContentTemplate>
</asp:UpdatePanel>
</td>
</tr>
<tr>
<td>密碼:</td>
<td>
<asp:TextBox ID="txtPwd" runat="server" TextMode="Password" placeholder="8-12碼" MaxLength="12"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" Display="Dynamic" ControlToValidate="txtPwd" runat="server" ErrorMessage="密碼為必填欄位" Text="(必填)" ForeColor="Red" Font-Size="10pt"></asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="RegularExpressionValidator3" ControlToValidate="txtPwd" ValidationExpression="\S{8,12}" runat="server" ErrorMessage="(密碼不可含有空白)" ForeColor="Red" Font-Size="10pt"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td>密碼確認:</td>
<td>
<asp:TextBox ID="txtPwd2" runat="server" TextMode="Password" placeholder="請再輸入一次密碼"></asp:TextBox>
<%-- <asp:RequiredFieldValidator ID="RequiredFieldValidator6" ControlToValidate="txtPwd2" runat="server" ErrorMessage="(必填)" ForeColor="Red" Font-Size="10pt"></asp:RequiredFieldValidator>--%>
<asp:CompareValidator ID="CompareValidator3" runat="server" Operator="Equal" ControlToCompare="txtPwd2" ControlToValidate="txtPwd" ErrorMessage="(兩次密碼輸入不相同)" ForeColor="Red" Font-Size="10pt"></asp:CompareValidator>
</td>
</tr>
<tr>
<td>姓名:</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" ControlToValidate="txtName" runat="server" ErrorMessage="(必填)" ForeColor="Red" Font-Size="10pt"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>生日:</td>
<td>
<asp:TextBox ID="txtBirthday" runat="server" placeholder="1990-01-12"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator5" ControlToValidate="txtBirthday" Display="Dynamic" runat="server" ErrorMessage="(必填)" ForeColor="Red" Font-Size="10pt"></asp:RequiredFieldValidator>
<asp:CompareValidator ID="CompareValidator1" Display="Dynamic" runat="server" Operator="DataTypeCheck" Type="Date" ControlToValidate="txtBirthday" ErrorMessage="(格式錯誤)" ForeColor="Red" Font-Size="10pt"></asp:CompareValidator>
</td>
</tr>
<tr>
<td>E-mail:</td>
<td>
<asp:TextBox ID="txtEmail" runat="server" placeholder="[email protected]"></asp:TextBox>
<asp:RegularExpressionValidator ID="RegularExpressionValidator4" ControlToValidate="txtEmail" ValidationExpression="([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)" runat="server" ErrorMessage="(格式有誤)" ForeColor="Red" Font-Size="10pt"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td>性別:</td>
<td>
<asp:RadioButtonList ID="rblGender" runat="server" RepeatDirection="Horizontal" Width="120">
<asp:ListItem Text="男" Value="1" Selected="True"></asp:ListItem>
<asp:ListItem Text="女" Value="0"></asp:ListItem>
</asp:RadioButtonList>
</td>
</tr>
<tr>
<td>學歷:</td>
<td>
<asp:DropDownList ID="ddlEduLevel" runat="server">
<asp:ListItem Text="請選擇"></asp:ListItem>
</asp:DropDownList>
<asp:CompareValidator ID="CompareValidator2" runat="server" Operator="NotEqual" ValueToCompare="請選擇" ControlToValidate="ddlEduLevel" ErrorMessage="(請選擇)" ForeColor="Red" Font-Size="10pt"></asp:CompareValidator>
</td>
</tr>
<tr>
<td>照片:</td>
<td>
<asp:FileUpload ID="fulPhoto" runat="server" /><asp:Label ID="lblPhoto" Font-Size="10pt" ForeColor="Red" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td>備註:</td>
<td>
<asp:TextBox ID="txtNote" TextMode="MultiLine" runat="server" Width="200" Height="150"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="Button1" runat="server" Text="加入會員" OnClick="Button1_Click" />
<input id="Reset1" type="reset" value="重設" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Net.Mail;
namespace ASPnet
{
public partial class _33Member_Registeration : System.Web.UI.Page
{
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySystemConnectionString1"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySystemConnectionString1"].ConnectionString);
SqlCommand Cmd = new SqlCommand("select * from Edu order by EduLevel_Code desc", Conn);
SqlDataReader rd;
Conn.Open();
rd = Cmd.ExecuteReader();
ListItem item;
while (rd.Read())
{
item = new ListItem(rd["EduLevel"].ToString(), rd["EduLevel_Code"].ToString());
ddlEduLevel.Items.Add(item);
}
Conn.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
if (this.IsValid)
{
try
{
lblPhoto.Text = "";
SqlCommand Cmd = new SqlCommand("insert into members values(@account,hashbytes('sha2_256',@pwd),@name,@birthday,@email,@gender,@edu,@note,@photo,@IsAuth)", Conn);
Cmd.Parameters.AddWithValue("@account", txtAccount.Text);
Cmd.Parameters.AddWithValue("@pwd", txtPwd.Text);
Cmd.Parameters.AddWithValue("@name", txtName.Text);
Cmd.Parameters.AddWithValue("@birthday", txtBirthday.Text);
Cmd.Parameters.AddWithValue("@email", txtEmail.Text);
Cmd.Parameters.AddWithValue("@gender", rblGender.SelectedValue);
Cmd.Parameters.AddWithValue("@edu", ddlEduLevel.SelectedValue);
Cmd.Parameters.AddWithValue("@note", txtNote.Text);
Cmd.Parameters.AddWithValue("@photo", fulPhoto.FileBytes);
Cmd.Parameters.AddWithValue("@IsAuth", false);
Conn.Open();
if (fulPhoto.PostedFile.ContentType != "application/octet-stream")
{
if (fulPhoto.PostedFile.ContentType == "image/jpeg")
{
Cmd.ExecuteNonQuery();
SendAuthMail(txtEmail.Text,txtAccount.Text);
}
else
lblPhoto.Text = "格式有錯!!";
}
else
{
Cmd.ExecuteNonQuery();
SendAuthMail(txtEmail.Text, txtAccount.Text);
}
Conn.Close();
//Response.Redirect("17GridView_DataSource.aspx");
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
}
protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
{
SqlCommand Cmd = new SqlCommand("select dbo.fnCheckMemberAccount(@account)", Conn);
Cmd.Parameters.AddWithValue("@account", txtAccount.Text);
SqlDataReader rd;
Conn.Open();
rd = Cmd.ExecuteReader();
rd.Read();
if (rd[0].ToString() == "0")
args.IsValid = true;
else
args.IsValid = false;
Conn.Close();
}
protected void SendAuthMail(string mail, string account)
{
SmtpClient myMail = new SmtpClient("msa.hinet.net");
//SmtpClient myMail = new SmtpClient("smtp.gmail.com", 587);
//myMail.Credentials = new NetworkCredential("YourAccount", "YourPassword");
//myMail.EnableSsl = true;
//純文字
//string from = "[email protected]";
//string to = "[email protected]";
//string subject = "邀請您一同來參加我們的派對!!";
//string body = "ghhghghgkhgjjhghjrfdhg\nghfgdfdsgdhgjhgr\njhhgrfgfjhgj";
//myMail.Send(from, to, subject, body);
MailAddress from = new MailAddress("[email protected]", "杯具商城");
MailAddress to = new MailAddress(mail);
MailMessage Msg = new MailMessage(from, to);
Msg.Subject = "會員註冊認證信";
Msg.Body = "請點擊下列超鏈結完成會員註冊認證<br /><br /><a href='http://localhost:57023/35Auth_OK.aspx?account="+account+"'>請點我</a>";
Msg.IsBodyHtml = true;
myMail.Send(Msg);
Response.Write("<script>alert('恭禧您完成會員註冊資料填寫,請至您的信箱收取認證信進行認證,方能啟用會員!') </script>");
}
}
}
//create function fnCheckMemberAccount
// (@account varchar(10))
// returns int
//as
//begin
// declare @aa varchar(10)
// select @aa = account from Members where account = @account
// if @@ROWCOUNT=0
// return 0
// return 1
//end

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="35Auth_OK.aspx.cs" Inherits="ASPnet._35Auth_OK" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ASPnet
{
public partial class _35Auth_OK : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string account = Request.QueryString["account"].ToString();
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySystemConnectionString1"].ConnectionString);
SqlCommand Cmd = new SqlCommand("select * from members where [email protected]", Conn);
Cmd.Parameters.AddWithValue("@account", account);
SqlDataReader rd;
Conn.Open();
rd = Cmd.ExecuteReader();
if(rd.Read())
{
rd.Close();
Cmd.Dispose();
SqlCommand Cmd2 = new SqlCommand("update members set IsAuth=1 where [email protected]", Conn);
Cmd2.Parameters.AddWithValue("@account", account);
Cmd2.ExecuteNonQuery();
Response.Write("<script>alert('恭禧您完成會員認證') </script>");
}
Conn.Close();
}
}
}

忘記時直接查詢rd.Close();

Gridview 鏈結 members ,現在欄位photo是檔案不是資料, 用讀資料的方法讀出來該怎麼顯示?
Gridview 自己跑出來的 columns 完全沒有物件可以顯示圖片
那用boundfield去資料繫結讀出來呢?
出來也會是system bytes …
Copy link
On this page
SQLSERVER
觸發程序 TRIGGER
資料指標Cursor 不止往前指還能往後指
ASP.NET 系統寄送email
如何寄送?
以上為簡單的寄法
以下為較複雜的寄法
完成
33註冊帳號寄送認證信 前端
33註冊帳號寄送認證信 後端
35認證信內頁內容 前端
35認證信內頁內容 後端
明日待續… 要給會員自己修改資料,要如何讀圖片出來?