2019/0325/TMD&Asp.net上傳圖片/產生QRCode
Previous2019/0322/DOM&JQ基礎&系統分析設計PPT&在職班補充Next2019/0326/SQLServer_預存程序+函數&Asp.net_Webforms半自動鏈結資料庫與自動化更新
Last updated
Last updated
編碼規則系統設計師會標註
nvarchar 變動長度
測驗種類 遞移相依問題 但這裡依照圖施工不考慮
實體本身就有pk就不用pfk所以使用fk 不可被識別的多對1
學生 多對多於 測驗 具有 明細
要有一個自己的pk 但通常不太據原生意義 ( 流水號
但這個pk是因為學生跟地址為1對多 但不曉得學生會填幾筆資料
組合鍵
TMD轉為圖檔
create database hw5
go
use hw5
go
/*
Created 2019/3/21
Modified 2019/3/25
Project
Model
Company
Author
Version
Database MS SQL 2005
*/
Create table [Students]
(
[學號] Char(4) NOT NULL,
[姓名] Nvarchar(20) NOT NULL,
[性別] Bit NOT NULL,
[電話] Varchar(16) NULL,
[生日] Datetime NULL,
Primary Key ([學號])
)
go
Create table [課程]
(
[課程編號] Char(1) NOT NULL,
[名稱] Nvarchar(20) NULL,
[學分] Integer NOT NULL,
Primary Key ([課程編號])
)
go
Create table [班級]
(
[學號] Char(4) NOT NULL,
[教授編號] Char(4) NOT NULL,
[課程編號] Char(1) NOT NULL,
[上課時間] Datetime NOT NULL,
[教室] Varchar(8) NOT NULL,
Primary Key ([學號],[教授編號],[課程編號])
)
go
Create table [教授]
(
[教授編號] Char(4) NOT NULL,
[科系] Varchar(5) NOT NULL,
[職稱] Nvarchar(4) NOT NULL,
[身份證字號] Char(10) NULL,
Primary Key ([教授編號])
)
go
Create table [員工]
(
[身份證字號] Char(10) NOT NULL,
[姓名] Nvarchar(20) NOT NULL,
[電話] Varchar(16) NULL,
[城市] Nvarchar(4) NOT NULL,
[街道] Nvarchar(10) NOT NULL,
[薪水] Money NOT NULL,
[保險] Money NOT NULL,
[扣稅] Money NOT NULL,
Primary Key ([身份證字號])
)
go
Create table [測驗]
(
[測驗編號] Char(4) NOT NULL,
[名稱] Nvarchar(20) NOT NULL,
[種類] Char(1) NULL,
[課程編號] Char(1) NULL,
Primary Key ([測驗編號])
)
go
Create table [結果]
(
[測驗編號] Char(4) NOT NULL,
[學號] Char(4) NOT NULL,
[成績] Integer NOT NULL,
[日期] Datetime NOT NULL,
Primary Key ([測驗編號],[學號])
)
go
Create table [地址]
(
[地址流水號] Bigint Identity NOT NULL,
[地址] Nvarchar(30) NOT NULL,
[學號] Char(4) NOT NULL,
Primary Key ([地址流水號],[學號])
)
go
Create table [家長]
(
[姓名] Nvarchar(20) NOT NULL,
[關係] Nvarchar(4) NULL,
[學號] Char(4) NOT NULL,
Primary Key ([姓名],[學號])
)
go
Alter table [班級] add foreign key([學號]) references [Students] ([學號]) on update no action on delete no action
go
Alter table [結果] add foreign key([學號]) references [Students] ([學號]) on update no action on delete no action
go
Alter table [地址] add foreign key([學號]) references [Students] ([學號]) on update no action on delete no action
go
Alter table [家長] add foreign key([學號]) references [Students] ([學號]) on update no action on delete no action
go
Alter table [班級] add foreign key([課程編號]) references [課程] ([課程編號]) on update no action on delete no action
go
Alter table [測驗] add foreign key([課程編號]) references [課程] ([課程編號]) on update no action on delete no action
go
Alter table [班級] add foreign key([教授編號]) references [教授] ([教授編號]) on update no action on delete no action
go
Alter table [教授] add foreign key([身份證字號]) references [員工] ([身份證字號]) on update no action on delete no action
go
Alter table [結果] add foreign key([測驗編號]) references [測驗] ([測驗編號]) on update no action on delete no action
go
Set quoted_identifier on
go
Set quoted_identifier off
go
TSQL 涉及資料操作等等的 將程式寫好 等需要時再執行
資料庫資料操作與定譯 ，
select 的程序能存成view 就類似預存程序
但不是所有程序都可以存成view
create Procedure 程序名稱
可簡寫 Proc
as begin 程序內容 end
--預存程序Stored Procedure
--建立
create Proc 學生選課總筆數
as
begin
select 班級.教授編號,員工.姓名,教授.科系,教授.職稱,班級.學號,學生.姓名,學生.性別,
班級.課程編號,課程.名稱,課程.學分,班級.上課時間,班級.教室
from 班級
inner join 學生 on 班級.學號=學生.學號
inner join 課程 on 班級.課程編號=課程.課程編號
inner join 教授 on 班級.教授編號=教授.教授編號
inner join 員工 on 教授.身份證字號=員工.身份證字號
order by 學生.學號
print '共有'+cast(@@rowcount as varchar)+'筆選課資料!!'
end--執行預存程序
execute 學生選課總筆數
--簡寫
exec 學生選課總筆數exec 如果後面是一整串字串後面可以當作命令執行
--再談execute
declare @tablename varchar(30)
set @tablename='學生'
exec('select * from '+@tablename)
--------------------------------------
declare @procname varchar(30)
set @procname='學生選課明細'
exec @procname功能會有括弧可傳遞參數 那預存程序要如何傳遞參數呢?
as 前定義名稱 資料型態
--有參數的預存程序
create proc 學生查詢
@name varchar(12)
as
begin
select * from 學生
where 姓名 like '%'+ @name+ '%'
endexec 學生查詢 @name='張'
exec 學生查詢 '張'具有隱藏資訊的特點
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:教務系統ConnectionString %>"
SelectCommand="學生選課明細" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"></asp:GridView> 輸入姓名關鍵字：<asp:TextBox ID="txtName" runat="server"></asp:TextBox><asp:Button ID="Button1" runat="server" Text="查詢" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:教務系統ConnectionString %>"
SelectCommand="學生查詢" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter Name="name" ControlID="txtName" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView2" runat="server" DataSourceID="SqlDataSource2"></asp:GridView>唯讀畫面->編輯畫面
按下發送->回寫資料庫
Product_Img:
<img src='ProductsImg/s<%# Eval("Product_Img") %>' />控制項 fileupload
後端鑄造物件用於抓更新值
先在後端判斷前端控制器是否有抓到東西 判斷是否有上傳檔案
監看式可以看檔名等等 也可用於除錯
做限制判斷圖檔 ( 判斷副檔名是不準的 (對方可以把病毒附檔為jpg
contentlength 檔案大小
contenttype 檔案類型
| | 或
呼叫方法 saveas 路徑+img.filename原主檔名+副檔名 ( 寫邏輯判斷副檔名 )
但今天不是要用原檔名而是利用pk當作檔名這樣才能夠讓資料庫後續可以回傳
mapping邏輯路徑對應相對路徑 呼叫物件來用
server.mappath 去找伺服器絕對路徑
protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
{
if(e.CommandName=="Edit")
{
DataList1.EditItemIndex = e.Item.ItemIndex;
DataBind();
int index = e.Item.ItemIndex;
string status = ((Label)DataList1.Items[index].FindControl("lblProduct_Status")).Text;
RadioButtonList rbl = (RadioButtonList)DataList1.Items[index].FindControl("rblProduct_Status");
if (status == "True")
{
rbl.Items[0].Selected = true;
}
else
rbl.Items[1].Selected = true;
}
else if (e.CommandName == "Cancel")
{
DataList1.EditItemIndex = -1;
DataBind();
}*問題是照片大小版面呢? (實務上通常是修改到完整大小才可更新
分別using 可以少寫一些
將一張圖鑄造成image物件
抓客戶端檔案路徑轉到參數中
讓客戶端檔案能夠轉到物件中
抓客戶端檔案格式轉至物件中
重新畫一張大圖 360*360
圖形驗證 QRcod都是用bitmap
//上傳新的產品圖檔
FileUpload img=(FileUpload)e.Item.FindControl("fulProductsImg");
Label proID = (Label)e.Item.FindControl("Product_IDLabel");
if (img.FileName!="")
{
System.Drawing.Image g = System.Drawing.Image.FromFile(img.PostedFile.FileName);
ImageFormat imgformat = g.RawFormat;
Bitmap newImg = new Bitmap(g,360,360);
Bitmap newSImg = new Bitmap(g, 120, 120);
if (img.PostedFile.ContentType == "image/jpeg")
{
newImg.Save(Server.MapPath("/ProductsImg/" + proID.Text + ".jpg"));
newSImg.Save(Server.MapPath("/ProductsImg/s" + proID.Text + ".jpg"));
//img.SaveAs(Server.MapPath("/ProductsImg/" + proID.Text + ".jpg"));
}
else if (img.PostedFile.ContentType == "image/png")
{
newImg.Save(Server.MapPath("/ProductsImg/" + proID.Text + ".png"));
newSImg.Save(Server.MapPath("/ProductsImg/s" + proID.Text + ".png"));
//img.SaveAs(Server.MapPath("/ProductsImg/" + proID.Text + ".png"));
}
}commandname重要=update
確認有無重新整理 ( post back
屬性 OnUpdateCommand 功能名稱
後端 將所有需要回寫的資料 鑄造物件 ( 包含索引直 pk ) 用於抓更新\值
前端 UpdateCommand 設置
讀取後端參數設置 ( 包含索引直 pk
將參數餵給後端變數 並寫入資料庫 ( 包含索引直 pk
並轉為唯讀程式
<div>
<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"
RepeatColumns="3" CellSpacing="20" RepeatDirection="Horizontal"
OnItemCommand="DataList1_ItemCommand"
OnUpdateCommand="DataList1_UpdateCommand"
>
<ItemTemplate>
<div style="text-align:center">
<asp:Button ID="Button1" runat="server" Text="編輯資料" CommandName="Edit" />
<%--<asp:LinkButton ID="LinkButton1" runat="server" CommandName="Select"> <%# Eval("Product_id") %></asp:LinkButton>--%>
<br />
<img src='ProductsImg/s<%# Eval("Product_Img") %>' />
<br />
<asp:Label ID="Label2" runat="server" Font-Names="微軟正黑體" Font-Bold="true" Font-Size="14pt" Text='<%# Eval("Product_Name") %>' />
<br />
原價：
<asp:Label ID="Label1" runat="server" Text='<%# Eval("Product_Price","{0:C0}") %>' ForeColor="#999999" CssClass="line_through" />
<br />
特價：
<asp:Label ID="Label3" runat="server" Text='<%# Eval("Product_Price2","{0:C0}") %>' ForeColor="#ff0066" Font-Names="Arial Black" Font-Size="18pt" />
</div>
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CommandName="Update">更新</asp:LinkButton>
<asp:LinkButton ID="LinkButton2" runat="server" CommandName="Cancel">取消</asp:LinkButton>
<br />
<asp:Label ID="lblProduct_Status" runat="server" Text='<%# Eval("Product_Status") %>'></asp:Label>
Product_ID:
<asp:Label ID="Product_IDLabel" runat="server" Text='<%# Eval("Product_ID") %>' />
<br />
Product_Name:
<asp:TextBox ID="txtProduct_Name" runat="server" Text='<%# Eval("Product_Name") %>' Width="200"></asp:TextBox>
<br />
Product_Img:
<img src='ProductsImg/s<%# Eval("Product_Img") %>' /><br />
<asp:FileUpload ID="fulProductsImg" runat="server" />
<br />
Product_Price:
<asp:TextBox ID="txtProduct_Price" runat="server" Text='<%# Eval("Product_Price","{0:0}") %>' Width="100"></asp:TextBox>
<br />
Product_price2:
<asp:TextBox ID="txtProduct_Price2" runat="server" Text='<%# Eval("Product_Price2","{0:0}") %>' Width="100"></asp:TextBox>
<br />
Product_Intro:
<asp:TextBox ID="txtProduct_Intro" runat="server" TextMode="MultiLine" Text='<%# Eval("Product_Intro") %>' Width="200" Height="100"></asp:TextBox>
<br />
Product_Status:
<asp:RadioButtonList ID="rblProduct_Status" runat="server" RepeatDirection="Horizontal">
<asp:ListItem Text="上架" Value="1"></asp:ListItem>
<asp:ListItem Text="下架" Value="0"></asp:ListItem>
</asp:RadioButtonList>
<br />
</EditItemTemplate>
</asp:DataList>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MySystemConnectionString1 %>"
SelectCommand="SELECT * FROM [Products]"
UpdateCommand="update Products set Product_Name=@Product_Name, Product_Price=@Product_Price,
Product_Price2=@Product_Price2, Product_Intro=@Product_Intro, Product_Status=@Product_Status where Product_ID=@Product_ID"
>
<UpdateParameters>
<asp:Parameter Name="Product_Name" Type="String" />
<asp:Parameter Name="Product_Price" Type="Int32" />
<asp:Parameter Name="Product_Price2" Type="Int32" />
<asp:Parameter Name="Product_Intro" Type="String" />
<asp:Parameter Name="Product_Status" Type="Int16" />
<asp:Parameter Name="Product_ID" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
</div>using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ASPnet
{
public partial class _27DataList_Edit : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
{
if(e.CommandName=="Edit")
{
DataList1.EditItemIndex = e.Item.ItemIndex;
DataBind();
int index = e.Item.ItemIndex;
string status = ((Label)DataList1.Items[index].FindControl("lblProduct_Status")).Text;
RadioButtonList rbl = (RadioButtonList)DataList1.Items[index].FindControl("rblProduct_Status");
if (status == "True")
{
rbl.Items[0].Selected = true;
}
else
rbl.Items[1].Selected = true;
}
else if (e.CommandName == "Cancel")
{
DataList1.EditItemIndex = -1;
DataBind();
}
}
protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
{
//上傳新的產品圖檔
//資料回寫資料庫
TextBox name =(TextBox)e.Item.FindControl("txtProduct_Name");
TextBox price = (TextBox)e.Item.FindControl("txtProduct_Price");
TextBox price2 = (TextBox)e.Item.FindControl("txtProduct_Price2");
TextBox intro = (TextBox)e.Item.FindControl("txtProduct_Intro");
RadioButtonList status = (RadioButtonList)e.Item.FindControl("rblProduct_Status");
Label proID=(Label)e.Item.FindControl("Product_IDLabel");
SqlDataSource1.UpdateParameters["Product_Name"].DefaultValue = name.Text;
SqlDataSource1.UpdateParameters["Product_Price"].DefaultValue = price.Text;
SqlDataSource1.UpdateParameters["Product_Price2"].DefaultValue = price2.Text;
SqlDataSource1.UpdateParameters["Product_Intro"].DefaultValue = intro.Text;
SqlDataSource1.UpdateParameters["Product_Status"].DefaultValue = status.SelectedValue;
SqlDataSource1.UpdateParameters["Product_ID"].DefaultValue = proID.Text;
SqlDataSource1.Update();
DataList1.EditItemIndex = -1;
DataBind();
}
}
}commandname重要=Cancel
確認有無重新整理 ( post back
if(e.CommandName=="Edit")
{
DataList1.EditItemIndex = e.Item.ItemIndex;
DataBind();
int index = e.Item.ItemIndex;
string status = ((Label)DataList1.Items[index].FindControl("lblProduct_Status")).Text;
RadioButtonList rbl = (RadioButtonList)DataList1.Items[index].FindControl("rblProduct_Status");
if (status == "True")
{
rbl.Items[0].Selected = true;
}
else
rbl.Items[1].Selected = true;
}
else if (e.CommandName == "Cancel")
{
DataList1.EditItemIndex = -1;
DataBind();
}直接存這個上傳檔案存到某個暫存資料夾 (保持原檔名與副檔名
這樣後端就能用server上的路徑做判斷
//上傳新的產品圖檔
FileUpload img=(FileUpload)e.Item.FindControl("fulProductsImg");
Label proID = (Label)e.Item.FindControl("Product_IDLabel");
if (img.FileName!="")
{
img.SaveAs(Server.MapPath("/tmpImage/temp.jpg"));
img.Dispose();
//System.Drawing.Image g = System.Drawing.Image.FromFile(img.PostedFile.FileName);
System.Drawing.Image g = System.Drawing.Image.FromFile(Server.MapPath("/tmpImage/temp.jpg"));
ImageFormat imgformat = g.RawFormat;
Bitmap newImg = new Bitmap(g,360,360);
Bitmap newSImg = new Bitmap(g, 120, 120);
if (img.PostedFile.ContentType == "image/jpeg")
{
newImg.Save(Server.MapPath("/ProductsImg/" + proID.Text + ".jpg"));
newSImg.Save(Server.MapPath("/ProductsImg/s" + proID.Text + ".jpg"));
//img.SaveAs(Server.MapPath("/ProductsImg/" + proID.Text + ".jpg"));
}
else if (img.PostedFile.ContentType == "image/png")
{
newImg.Save(Server.MapPath("/ProductsImg/" + proID.Text + ".png"));
newSImg.Save(Server.MapPath("/ProductsImg/s" + proID.Text + ".png"));
//img.SaveAs(Server.MapPath("/ProductsImg/" + proID.Text + ".png"));
}
}怕同時一堆人處理 可以將暫存檔案名稱亂數處理 ( 上面沒用
必須將catch釋放出來 不然圖跟上傳都會報錯
img.Dispose();釋放物件使用
( 最後有點聽不清楚 如果不懂這邊可以重聽
f5?
messagingtoolkit.qrcode
id 為自己打的亂碼
打物件名稱選擇正確位置自動using
graphics 也是圖形介面
public partial class _29QR_Code : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
QRCodeEncoder encoder = new QRCodeEncoder();
encoder.QRCodeVersion = 3;
encoder.QRCodeScale = 20;
string ProductID = "ufkjud8979302ikld";
Bitmap img = encoder.Encode(ProductID);
img.Save(Server.MapPath("/QR_Code/MyCdoe.jpg"), ImageFormat.Jpeg);
Image1.ImageUrl = "/QR_Code/MyCdoe.jpg";
}
}qrcode有芬版本
encoder.QRCodeVersion = 3;延展性
encoder.QRCodeScale = 20;前端
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="21GridView_BoundField.aspx.cs" Inherits="ASPnet._21GridView_BoundField" %>
<!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>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css" />
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MySystemConnectionString1 %>" SelectCommand="SELECT * FROM [Products]"></asp:SqlDataSource>
<div class="table-responsive">
<asp:GridView GridLines="None" CssClass="table table-hover" ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="Product_ID" HeaderText="Product_ID" SortExpression="Product_ID" />
<asp:BoundField DataField="Product_Name" HeaderText="Product_Name" SortExpression="Product_Name" />
<%-- <asp:BoundField DataField="Product_Img" HeaderText="Product_Img" SortExpression="Product_Img" />--%>
<asp:ImageField DataImageUrlField="Product_Img" DataImageUrlFormatString="~\ProductsImg\s{0}"></asp:ImageField>
<asp:BoundField DataField="Product_Price" DataFormatString="{0:C0}" HeaderText="Product_Price" SortExpression="Product_Price" />
<asp:BoundField DataField="Product_price2" DataFormatString="{0:C0}" HeaderText="Product_price2" SortExpression="Product_price2" />
<%--<asp:BoundField DataField="Product_Intro" HeaderText="Product_Intro" SortExpression="Product_Intro" />--%>
<asp:TemplateField HeaderText="Product_Intro" SortExpression="Product_Intro">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Product_Intro") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("Product_Intro").ToString().Replace("\n","<br>") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CheckBoxField DataField="Product_Status" HeaderText="Product_Status" SortExpression="Product_Status" />
<asp:TemplateField>
<ItemTemplate>
<asp:Image runat="server" ID="Image1"></asp:Image>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</div>
</form>
</body>
</html>後端
using MessagingToolkit.QRCode.Codec;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Drawing.Imaging;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ASPnet
{
public partial class _21GridView_BoundField : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowIndex != -1)
{
QRCodeEncoder encoder = new QRCodeEncoder();
string ProductID = e.Row.Cells[0].Text;
Bitmap img = encoder.Encode(ProductID);
img.Save(Server.MapPath("/QR_Code/"+ ProductID+".jpg"), ImageFormat.Jpeg);
((System.Web.UI.WebControls.Image)e.Row.Cells[7].FindControl("Image1")).ImageUrl = "/QR_Code/" + ProductID + ".jpg";
}
}
}
}
