2019/0326/SQLServer_預存程序+函數&Asp.net_Webforms半自動鏈結資料庫與自動化更新
sqlserver 預存程序 - 續
參數型預存程序
重點1. 變數 宣告型態
重點2. from後面只能接表用exec 強制執行
重點3.begin end為一段
create proc table_query
@tableName nvarchar(20)
as
begin
exec('select * from '+@tableName)
end
----------------------
exec table_query '班級'用gridview導出結果
1.用textbox讓使用者輸入表名稱是不好的，改用下拉式選單
2.取得資料庫所有的table ，
select*from information_schema.tables
where table)type='base table
3.dropdownlist 有分 text 與 value
4.就算是dropdownlist改動也算改變參數所以需要 ControlParameter
5.按下去後要變動顯示表單
OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" AutoPostBack="true"
6.sql使用exec 記得要 from 空格 exec('select * from '+@tableName)
<asp:GridView ID="GridView2" runat="server" DataSourceID="SqlDataSource2"></asp:GridView>
<hr />
<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:教務系統ConnectionString %>"
SelectCommand="table_query" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter Name="tableName" ControlID="DropDownList1" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:教務系統ConnectionString %>"
SelectCommand="select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='base table'"
></asp:SqlDataSource>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource4" DataTextField="TABLE_NAME" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" AutoPostBack="true"></asp:DropDownList>
<asp:GridView ID="GridView3" runat="server" DataSourceID="SqlDataSource3"></asp:GridView>兩個以上參數
建立預存程序跟 執行預存程序要搞懂 不然執行錯誤就會造成無窮迴圈
有指定參數名稱 不用按照順序也沒關係 不然就要按照順序
--有兩個以上參數的預存程序
create proc 員工薪資查詢
@name varchar(12),
@salary money
as
begin
select * from 員工
where 姓名 like '%'+ @name+ '%' and 薪水>=@salary
end
exec 員工薪資查詢 '陳', 20000
exec 員工薪資查詢 @salary=20000,@name='陳'修改預存程序 alter
參數給定預設值
兩個參數 如果有預設值 若只給1個 也不會抱錯 所以感覺預設值要給模糊查詢萬用字元
--有預設值的參數
alter proc 員工居住地查詢
@city char(5) ='台北',
@name varchar(12)
as
begin
select * from 員工
where 姓名 like '%'+ @name+ '%' or 城市=@city
end
exec 員工居住地查詢 '桃園 ', '陳'
exec 員工居住地查詢 @name='陳'
exec 員工居住地查詢 default,'陳'新增產品會有個問題 ( 要怎麼做?
如果是規則性的 則按照 邏輯 產生新的東西
要先寫一段程式確定是否重複
新的 與 現有的比較 如果不存在 則 ... ( 商業邏輯
可以寫在資料庫端 ( tsql 與 資料庫是最緊密的
alter proc check_productID
@productID char(4)
as
begin
declare @pid char(4)
select @pid=Product_ID from Products where Product_ID=@productID
if @@rowcount=0
select '可使用'
else
select '編號重複'
end
exec check_productID 'P011'為何要兩層方式傳遞，避免後台接收到兩筆資料
第一筆為判斷資料 第二筆為判斷後顯示資料
其實可以在前端或後端，判斷後顯示資料
第28asp.net模擬
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource4" DataTextField="TABLE_NAME" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" AutoPostBack="true"></asp:DropDownList>
<asp:GridView ID="GridView3" runat="server" DataSourceID="SqlDataSource3" AutoGenerateDeleteButton="true" AutoGenerateEditButton="true"></asp:GridView>
<hr />
產品編號:<asp:TextBox ID="txtPID" runat="server"></asp:TextBox><asp:Button ID="Button2" runat="server" Text="檢查可用性" />
<asp:GridView ID="GridView4" runat="server" DataSourceID="SqlDataSource5">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:MySystemConnectionString1 %>"
SelectCommand="check_productID" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter Name="productID" ControlID="txtPID" Type="String" />
</SelectParameters>
</asp:SqlDataSource>實務上 gridview datasource 通常用於顯示資料 管理資料 通常不做檢查 且很耗資源
傳出參數
通常預存程序是傳出結果 ， 傳入不同的參數得到不同的結果
如果這時需要將預存程序的值往外傳 ( 傳出參數
設置兩個預存程序參數 一個為輸入 一個為輸出
宣告一個變數在預存程序外 用於接收輸出
output 參數 寫法比較怪 是 等號左丟給右邊
可以預存程序呼叫另外一個預存程序 但是巢狀限制為32個
結構化的程式法 內聚力會高
--傳出參數
create proc 薪水查詢
@name varchar(12),
@salary money output
as
begin
select @salary=薪水 from 員工 where 姓名=@name
end
alter proc 加薪試算
@name varchar(12)
as
begin
declare @MySalary money
exec 薪水查詢 @name, @salary=@MySalary output
print @MySalary*1.03
end
exec 加薪試算 '王心零'內建的預存程序 sp開頭的都是
alter 只能改內容 不能改物件名稱
所以有個叫 sp_rename 舊名稱 , 新名稱
--內建預存程序
exec sp_rename '課程2','課程3'
exec sp_helptext '加薪試算'
exec sp_columns '課程'sqlserver函數 function
純量值函數(式) 因為是一段公式
資料表函數(式)
沒有參數的函數
自訂函數名稱通常會 +fn在最前面 不成文的習慣
( ) 內為函數參數
特殊的地方是 需要標註回傳值資料型態
通常要傳出一個值不會用預存程序通常會用函數
--函數(function)
--純量值函數(式)
create function fnGetSalary()
returns money
as
begin
declare @salary money
select @salary=薪水 from 員工
return @salary
end會抓到最後一筆 上一筆會被覆蓋
看returns 就會自動辨識是哪種函數
在資料庫寫函數主要是因為能夠跟資料庫做緊密結合
呼叫函數
自訂函數必須把型態寫清楚
--呼叫函數
print getdate()
select getdate()
print dbo.fnGetSalary()
select dbo.fnGetSalary()
go有參數的函數
tsql 語法通常先寫名稱在寫資料型態
函數裡面只要看到return以下就不會再執行 所以不用寫 else
--有參數的函數
alter function fnGetSalary2
(
@name varchar(12)
)
returns money
as
begin
declare @salary money
select @salary=薪水 from 員工 where 姓名=@name
if @@ROWCOUNT=0
return 0
return @salary
end函數要用在適合他的地方 預存程序也是
create proc 薪水查詢_fn
@name varchar(12)
as
begin
if dbo.fngetsalary2(@name)=0
print '查無此人'
else
print dbo.fngetsalary2(@name)
end
exec 薪水查詢_fn '王心7777零'資料表值函數
最大差別在於 returns table
--資料表值函數--
create function fnStudentQuery
(@stuNo char(4))
returns table
return(
select 班級.教授編號,員工.姓名 as 老師姓名,教授.科系,教授.職稱,班級.學號,學生.姓名,學生.性別,
班級.課程編號,課程.名稱,課程.學分,班級.上課時間,班級.教室
from 班級
inner join 學生 on 班級.學號=學生.學號
inner join 課程 on 班級.課程編號=課程.課程編號
inner join 教授 on 班級.教授編號=教授.教授編號
inner join 員工 on 教授.身份證字號=員工.身份證字號
where 學生.學號=@stuNo
)呼叫 資料表值函數
select * from dbo.fnStudentQuery('S001')進階資料表值函數應用
2008後才能用 offset fetch 所以在那之前的版本只能自己寫函數
select e.身份證字號,e.姓名,e.電話
from 員工 as e order by e.身份證字號
offset 3 rows
fetch next 3 rows only死機碼
唯一識別值 流水號 identity
撈出值在放入 資料表變數中 ( 資料表變數必須寫好型態
create function fnOffset
(@m int, @n int)
returns @resultTable table(
sn int identity,
id char(10),
name varchar(12),
tel varchar(20)
)
begin
insert into @resultTable
select e.身份證字號,e.姓名,e.電話
from 員工 as e order by e.身份證字號
delete @resultTable where sn<@m or sn>@n
return
end
select * from dbo.fnOffset(4,6)asp.net qrcode 查詢商品 實例
ado.net 實務上95%使用這個
sqldataadapter
sqldatadatareader
sqldatasourse 可以做的事情很多 但很多時候實務上並不會用到就會變成佔資源
大部分時間都使用 ado.net
以 qrcode 為例
不使用 sqldatasourse 半自動連接資料庫
<configuration>
<connectionStrings>
<add name="教務系統ConnectionString" connectionString="Data Source=127.0.0.1;Initial Catalog=教務系統;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings><asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:教務系統ConnectionString %>"
SelectCommand="學生選課明細" SelectCommandType="StoredProcedure"></asp:SqlDataSource>以上全自動作對照用
沒有自動連資料庫的物件變半自動
首先using 兩個東西
using System.Data;
using System.Data.SqlClient;與資料庫連結的物件
public partial class _30ADOnet_SqlDataAdapter : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySystemConnectionString1"].ConnectionString );
SqlDataAdapter da = new SqlDataAdapter("select * from products",Conn);
DataSet ds = new DataSet();
da.Fill(ds, "products");
}
}在用到 using
using System.Configuration;組態管理員 ConfigurationManager
取屬性 資料庫位址
才開始使用
sqldataadapter ( 搜尋指令 , 資料庫位址 )
以上並沒有做顯示安排 所以
安排一個資料表變數 做 斷點查詢 做資料庫是否有連成功判斷
將資料鏈結至gridview做顯示
繫節完後要呼叫一次才會真的繫節
GridView gv = new GridView();
gv.ID = "test";
gv.DataSource = ds;
//gv.AutoGenerateColumns = true;
gv.DataBind();
form1.Controls.Add(gv);利用adapter時機
一次做一些事情的時候使用 (記憶體??
修改dataset裡面資料 並沒有寫回資料庫 而是在佔存記憶體中
所以要確認就必須下斷點去監看是否成功
//修改dataset裡的某筆資料
ds.Tables["products"].Rows[7]["Product_Name"] = "111111Chef Topf薔薇系列不沾鍋 - 28公分炒鍋+28公分";
ds.Tables["products"].Rows[7]["Product_price"] = 123456;
//新增一筆資料進dataset
DataRow dr = ds.Tables["products"].NewRow();
dr[0] = "P011";
dr[1] = "uuuuuuuuu";
dr[2] = "P011.jpg";
dr[3] = 123;
dr[4] = 100;
dr[5] = "sssssss";
dr[6] = 1;
ds.Tables["products"].Rows.Add(dr);執行完後監看式記憶體改變
沒有變化的資料庫
刪除
//刪除DataSet裡的資料
ds.Tables["products"].Rows[5].Delete();
//gv.DataBind();
//form1.Controls.Add(gv);sqlcommandbuilder 自動物件自動比對 如果有新增就真的新增它
成本大但可以減少output&input disk kam
sqlcommandbuilder 物件 自動比對 如果有新增就真的新增它
da.update(ds,products)
常態性 新增修改刪除 很有可能會發生例外 因為sql那邊錯誤 c#=例外
建議丟到try
發生例外放在 catch (exception ex )
response.write("沒有成功,原因"+ex.message);
try
{
SqlCommandBuilder myUpdate = new SqlCommandBuilder(da);
da.Update(ds, "products");
}
catch(Exception ex)
{
Response.Write("沒有成功,原因"+ex.Message);
}設斷點完進入偵錯模式後也要顯示出預覽畫面才可使用監看式觀察數據
sqldatadatareader 物件 只能讀東西出來 無法下指令所以要透過
sqlcommand 可以做新增修改刪除
要將連線打開 資料庫位址.open()
要記得把連線關起來 資料庫位址.close () ( 不關起來就會被一直佔住 除非time out
執行這個方法 讀取資料 因為有很多筆所以要執行迴圈顯示 不然只會顯示最後一筆
while 條件下 每次一讀取就顯示一次
相較於adapter依次讀取近來
datareader則是一筆一筆讀近來顯示完就關掉了所以節省記憶體但是會損耗 pisk i/o
終於要來做qrcode 商品實例了
用29 做 產生qr code
按鈕 取得 現在所有產品的 qr code
事件功能寫在 on click 裡面
記得 using
讀取的 connection
讀取的 command
使用 datareader 讀一筆資料產生一筆qrcode 產生
看 p001 -p010 有沒有被讀出來
每一筆qrcode 就產生一個 img 控制像 用於顯示
後端物件與前端物件最大差異就在於
如果前端已經有那個物件 則在後端使用id
如果沒有是後端鑄造的則用鑄造時的名稱
前端
<asp:Button ID="Button1" runat="server" Text="取得商品QR-Code" OnClick="Button1_Click" />後端
protected void Button1_Click(object sender, EventArgs e)
{
QRCodeEncoder encoder = new QRCodeEncoder();
encoder.QRCodeVersion = 3;
encoder.QRCodeScale = 10;
string ProductID = "";
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySystemConnectionString1"].ConnectionString);
SqlCommand Cmd = new SqlCommand("select product_id from products", Conn);
SqlDataReader rd;
Conn.Open();
rd = Cmd.ExecuteReader();
while (rd.Read())
{
ProductID = rd["product_id"].ToString();
Bitmap img = encoder.Encode(ProductID);
img.Save(Server.MapPath("/QR_Code/"+ ProductID + ".jpg"), ImageFormat.Jpeg);
System.Web.UI.WebControls.Image image = new System.Web.UI.WebControls.Image();
image.ImageUrl = "/QR_Code/"+ ProductID+".jpg";
form1.Controls.Add(image);
}
Conn.Close();
}如果是自動更新物件錯誤 1.可能是資料表無pk無法查詢2.資料庫程式開啟資料表造成更新過但沒重新整理看步道
32 product_search_qr_code 掃描器查詢
後端:讓游標一開始就停在框裡面直接使用掃描器
藉由掃描器裡面的程式辨識qrcode產生值按下按鈕進行驗證
前端
<div>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><asp:Button ID="Button1" runat="server" Text="Button" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MySystemConnectionString1 %>"
SelectCommand="SELECT * FROM [Products] where Product_ID=@Product_ID">
<SelectParameters>
<asp:ControlParameter Name="Product_ID" ControlID="TextBox1" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"></asp:GridView>
</div>後端
protected void Page_Load(object sender, EventArgs e)
{
TextBox1.Focus();
}rs232 才能寫程式進掃描機 可以掃一為跟二為所以才會貴還可以縮小範圍放大
偵錯功能修理
Last updated
Was this helpful?