Sqlserver中的儲存過程

一、什麼是儲存過程（Stored Procedure）

　　 儲存過程是一段儲存在資料庫的“子程式”，本質是一個可重複使用的SQL程式碼塊，可以理解為資料庫端的“方法”。

　　 儲存過程的好處：

　　　　①提高效能：由於資料庫執行動作時，是先編譯後執行的。然而儲存過程是一個編譯過的程式碼塊，所以執行效率要比T-SQL語句高。

　　　　②提高通訊速率：網路通訊中傳輸的內容是儲存過程名字，相比傳輸大量的sql語句網路的要通訊量小，提高通訊速率。

　　　　③提高安全效能：儲存過程能夠使沒有許可權的使用者在控制之下間接地存取資料庫，從而確保資料的安全。　

　　　　舉例： 假如某個應用程式要向使用者提供修改密碼的功能，而不同使用者的使用者名稱和密碼都儲存在一張User表中。

　　　　如果，如果不使用儲存過程， 該應用程式在訪問資料庫時， 必須已具有操作“User”表許可權的使用者身份去連線資料庫。此時如果程式設計師編寫的程式碼出錯，或者程式設計師想惡意修改別人的password, 則資料庫User表的安全無法保證。

　　　　如果使用儲存過程，則可以定義一個具有修改User表許可權的儲存過程changePassword(username, oldPassword, newPassword), 該儲存過程實現了嚴格的校驗邏輯，即首先檢查useranme, oldpassword是否匹配， 如果匹配， 則僅僅修改username所對應的password。

二、Sql Server使用儲存過程

　　例子使用的UserInfo表只有 UserName,UserPass,Email和主鍵 Id 列

2.1  簡單的無參查詢（查詢使用者名稱和密碼）

--建立查詢使用者名稱和密碼的儲存過程
create proc pro_getUserList
as 
select username,userpass from UserInfo 
go

--執行
exec pro_getUserList

在C#中呼叫儲存過程的程式碼

using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlDataAdapter adapter = new SqlDataAdapter("pro_getUserList", conn);
                //設定CommandType
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataTable dt = new DataTable();
                //結果集存入dt中
                adapter.Fill(dt);
                //遍歷顯示結果集
                foreach (DataRow row in dt.Rows)
                {
                    Console.WriteLine(row["username"]+"---"+row["userpass"]);
                }
                Console.ReadKey();
            }

2.2  有返回值的簡單插入使用者（插入一條新紀錄，返回受影響的行數）

--建立名為InsertUserInfo的儲存過程
create proc InsertUserInfo
as 
insert into userinfo (username,userpass,email) values ('newuser','123','123@qq.com') 
return @@rowcount
go

--執行儲存過程
exec InsertUserInfo

在C#中呼叫儲存過程的程式碼

 using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand com=new SqlCommand("pro_insertUserInfo",conn))
                {
                    conn.Open();
                    com.CommandType = CommandType.StoredProcedure;//設定CommandType
                    //建立一個接受返回值的引數，設定該引數是返回值型別
                    SqlParameter par = new SqlParameter("count", SqlDbType.Int);
                    par.Direction = ParameterDirection.ReturnValue;
                    com.Parameters.Add(par);

                    int comResult = com.ExecuteNonQuery();//com.ExecuteNonQuery返回受影響的行數 1
                    Console.WriteLine(comResult);
                    Console.WriteLine(par.Value.ToString());//通過返回值獲取受影響的行數 1
                    Console.ReadKey();
                }
            }

2.3  有輸入輸出引數的簡單查詢(新增使用者，如果使用者名稱存在時不新增)

--插入一個新使用者，返回受影響行數
create proc pro_insertUserInfo2
@username nvarchar(20),
@userpass nvarchar(20),
@email nvarchar(50)='123@qq.com',
@count int out --記錄受影響的行數
as
declare @c int
select  @c = COUNT(*) from UserInfo where UserName=@username
if(@c!=0)--使用者名稱存在的話不新增，受影響行數為0
    set @count=0
else--使用者名稱不存在執行新增操作，受影響行數為1
    begin
        insert into UserInfo (UserName,UserPass,Email) values (@username,@userpass,@email)
        set @count=1
    end
go

--執行
exec pro_insertUserInfo2 'newuser','123','1234@qq.com' null

在C#中呼叫儲存過程的程式碼

using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand com=new SqlCommand("pro_insertUserInfo2",conn))
                {
                    conn.Open();
                    com.CommandType = CommandType.StoredProcedure;//設定CommandType
                    //建立引數集合
                    SqlParameter[] pars = {
                       new SqlParameter ("@username",SqlDbType.NVarChar,20){Value="ls"},
                       new SqlParameter("@userpass",SqlDbType.NVarChar,20){Value="123"},
                       new SqlParameter("@count",SqlDbType.Int){Direction=ParameterDirection.Output}
                   };
                    com.Parameters.AddRange(pars);
                    int comResult = com.ExecuteNonQuery();//第一次執行為1（新增一行），第二次執行結果為-1（未執行）
                    Console.WriteLine(comResult);
                    //通過out獲取受影響的行數
                    Console.WriteLine(pars[2].Value.ToString()); //第一次執行=1，第二次=0
                    Console.ReadKey();
                }
            }
        }

2.4  返回多個結果集

--返回多個結果集
create proc pro_GetLists
as
select * from userInfo                    --取所有資訊
select username,userpass from UserInfo  --只取使用者名稱和密碼
go


--執行
exec pro_GetLists

C#呼叫儲存過程

 using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlDataAdapter adapter = new SqlDataAdapter("pro_GetLists", conn);
                DataSet ds = new DataSet();
                //ds中是所有的結果
                adapter.Fill(ds);
                DataTable dt0=ds.Tables[0];//第一個select的結果集
                DataTable dt1 = ds.Tables[1];//第二個select的結果集               
            }

備註：在儲存過程中return只能返回int型別，out(output)可以返回多種型別,執行到return的時候儲存過程即結束,而out的變數可以重複設定。

　　   儲存過程中的return和out引數值，在C#中都是通過引數來接收的，select的結果集可以用DataTable或者DataSet進行接收。

本文參考：

1. http://www.cnblogs.com/knowledgesea/archive/2013/01/02/2841588.html
2. https://blog.csdn.net/lengxiao1993/article/details/53427266
3. http://www.cnblogs.com/aabbcc/p/6626372.html