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 才能寫程式進掃描機 可以掃一為跟二為所以才會貴還可以縮小範圍放大

偵錯功能修理